|
Dear all:
I am trying to use C# to communicate a small database build in Access 2007. I can read from the database and I can modify the values and save the changes back to database using the DataGridView. However, I am not sure how to modify the values programmatically.
Here is the code building the connection.
string g_ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\TestDB.accdb";
DataSet g_ds= new DataSet();
OleDbConnection g_con = new OleDbConnection(g_ConnectionString);
OleDbDataAdapter g_adapter = new OleDbDataAdapter("Select * from qrySuggestion", g_con);
g_adapter.Fill(g_ds, "qrySuggestion");
this.dataGridView1.DataSource = g_ds.Tables[0];
g_con.Close();
I have a button on the form and when the button is clicked, save the changes back to the database. The code is as follows:
public void SaveTable(string tblName, DataTable tbl)
{
g_adapter = new OleDbDataAdapter("select * from " + tblName, g_con);
OleDbCommandBuilder bu = new OleDbCommandBuilder(g_adapter);
g_adapter.UpdateCommand = bu.GetUpdateCommand();
g_adapter.Update(tbl);
tbl.AcceptChanges();
g_con.Close();
}
I can use the above code to read from the database and display the data in the DataGridView with no problem. And I can make modification in the DataGridView and save it back by calling the SaveTable method.
However, the problem is that I want to make modification programmatically and reflect the changes back to the database. What I did was to add two statement at the beginning of the SaveTable method as:
DataRow row = tbl.Rows[0];
row[0] = 30;
assuming the column 0 i.e. row[0] is a int field.
When I add those two statement at the beginning of the SaveTable method. An exception will occur at g_adapter.Update(tbl); the exception is something like:
Missing DataColumn"pk_ID" in SourceColumn "pk_ID" in DataTable "tblSuggestionBase"
Can anyone help me out with the problem please?
Thanks
Asura
|
|
|
|
|
Hi,
I want to know how can I split spilt in MySQL stored procedure.
I have this string Jassim Loves Banana
I want to split it by space and add x to it so it will be
xJassim xLove xBanana
|
|
|
|
|
use replace as
SELECT REPLACE('Jassim Loves Banana', ' ', ' x');
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
one more question please..
if I have the string as
jassim rahma
or
jassim ali rahma
or anything else
how can split that to be:
<pre>SELECT file_no, old_file_no, account_number, id_number, full_name, date_of_birth FROM patients WHERE MATCH(full_name) AGAINST('jassim' WITH QUERY EXPANSION) and MATCH(full_name) AGAINST('rahma' WITH QUERY EXPANSION) ORDER BY full_name;</pre>
|
|
|
|
|
I am using the following statement:
SELECT * FROM patients WHERE MATCH(full_name) AGAINST('jassim ali' WITH QUERY EXPANSION) ORDER BY full_name;
what I want to display all records contains jassim and should also contains ali but getting totally different result! here are names I am getting in the result:
Abdul Rahma Salma
Ali Jassim
Jassim Rahma
Jassim Rahma
Mohd Ali Ahmed
Mohd Jassim Maki
|
|
|
|
|
Never tried that. But I guess, and AND with two clauses could do the trick:
SELECT *
FROM patients
WHERE MATCH(full_name) AGAINST('jassim' WITH QUERY EXPANSION)
AND MATCH(full_name) AGAINST('ali' WITH QUERY EXPANSION)
ORDER BY full_name;
Please try, and report failure or success.
|
|
|
|
|
I tried it but still the same problem. I am getting result which has no relation to the string entered! here is my result:
Abdul Rahma Salma
Ali Jassim
Jassim Rahma
Jassim Rahma
Mohd Jassim Maki
because it's returning Jassim OR Rahma but I want Jassim AND Rahma
|
|
|
|
|
I have the developers edition of SQL SERVER 2008r2. The only choices showing up in my BIDS project types is Business Intellegence. Does anyone know how to get VB.net or Visual C# choices to show up??
|
|
|
|
|
I am developing C# winForm application. I have a textbox for searchign customer name but I want the user to enter the customer name like this:
john mathews
Or:
Robert Duff
then in MySQL I want to have a dynamic sql to search search the customer_name like this:
SELECT * FROM customers WHERE customer_name CONTAINS john AND customer_name CONTAINS mathews
|
|
|
|
|
See the sticky at the top, it tells you how to get an answer to your QUESTION, ou actually need to ask a question rather than make a statement.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Get the value of the texbox, split it into a string[] and use the elements of the array as parameters into your SQL query. Note, do not use string concatenation to create your query but proper SQL parameterisation.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
this is in C#.. but can this be done in SQL level?
|
|
|
|
|
In your opening question:
jrahma wrote: I am developing C# winForm application. So which is it to be?
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Invest some time here[^].
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
what is self hosting and IIS hosting?is there any difference of it
|
|
|
|
|
One difference of of course is that in one case one uses IIS (Internet Information Server) and in the other you don't.
Besides that, having used self hosting quite a bit but having never used IIS (in any way) my take (guess) is that there is some non-trivial part of WCF that is more apt for IIS than for self hosting. For example, the idea that a service 'restarts' isn't applicable for self hosting because the host app itself is either running or not running and that has nothing to do with WCF.
Also there is a WCF forum.
|
|
|
|
|
From your QA question: wcf information and web service information[^]
Why is common sense not common?
Never argue with an idiot. They will drag you down to their level where they are an expert.
Sometimes it takes a lot of work to be lazy
Please stand in front of my pistol, smile and wait for the flash - JSOP 2012
|
|
|
|
|
Hi
I need to run a query on a stock table. Each product may exist in either one or many warehouses. I am trying to find all items where the cost of the product is not the same in warehouse 01 and 02.
Example
Wh Prod Cost
01 A 10.00
02 A 10.00
01 B 64.00
01 C 98.00
02 C 10.00
In the example data above, I would like to get the last two rows returned.
This is what I have tried, but get lots of the same product code
SELECT stockm.warehouse, stockm.product, stockm.standard_material FROM
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B,
scheme_stockm AS stockm
WHERE (A.product = B.product AND A.standard_material <> B.standard_material)
ORDER BY stockm.product, stockm.warehouse
|
|
|
|
|
I think you are close ... but try this:
SELECT A.warehouse, A.product, A.standard_material,B.warehouse, B.product, B.standard_material FROM
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B
WHERE (A.product = B.product AND A.standard_material <> B.standard_material)
ORDER BY A.product, A.warehouse
I believe you included the scheme_stockm too many times in your original query.
|
|
|
|
|
Thanks David
I had to add warehouse in the two select statements for warehouse '01' and warehouse '02' since they were referred to in the outer SELECT as below and now it works.
SELECT A.warehouse, A.product, A.standard_material,B.warehouse, B.product, B.standard_material FROM
(SELECT warehouse, product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT warehouse, product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B
WHERE (A.product = B.product AND A.standard_material <> B.standard_material)
ORDER BY A.product, A.warehouse
I also got mine to work by adding an additional 'join' as below on stockm.product = A.product, but I prefer yours, since the result is one record per error, whereas mine is two records per error
SELECT stockm.warehouse, stockm.product, stockm.standard_material FROM
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B,
scheme_stockm AS stockm
WHERE (A.product = B.product AND A.standard_material <> B.standard_material AND stockm.product = A.product)
ORDER BY stockm.product, stockm.warehouse
|
|
|
|
|
Glad it worked ouy for you.
|
|
|
|
|
Your query will only work if you have 2 warehouses. What happens if there are 3 or more? Try:
SELECT
a.warehouse,
a.product,
a.standard_material
FROM
scheme_stockm AS a
WHERE
EXISTS (SELECT *
FROM scheme_stockm
WHERE product = a.product AND
standard_material <> a.standard_material)
ORDER BY
a.product,
a.warehouse
|
|
|
|
|
Hi Michael
I had wondered about multiple warehouses
In this particular case the product can exist in other warehouses, however the cost in the others does not need to be the same - only warehouse 01 and 02 must be the same.
I did test your query, and it works perfectly!
Thanks as I'm sure I'll need something like this in the future
|
|
|
|
|
Hi all,
If I were an employer searching for resumes which contain skill sets that match my search criteria, how is that done from a programmer's perspective. Can we actually go inside of text documents in a database and search their contents? If not, I will have to add a Skills field to the database so that it can be searched. Any suggestions will be greatly appreciated, thanks in advance.
|
|
|
|
|
ASPnoob wrote: Can we actually go inside of text documents in a database and search their contents?
That wouldn't be very efficient.
What type of documents do you expect, besides PDF, Word and the OpenOffice format? Whenever your end-user uploads a document, save it twice; once the original document, once a text-only version. Each of those formats can be "read" with the help of some libraries, and the text can be extracted. The text-version of the document can easily be searched, and once you find a "match", you display the original document.
For searching the text-versions of the uploaded documents, take a look at the Full Text Search[^] article on MSDN. If you're using another version than Sql2008, you can change it to match your specific version with a drop-down list at the top of the page.
Including a "Skills" field might still be beneficial, besides the FTS. Keep in mind that users will have different labels for the same role/function/job, and that managing those is the hardest part. For example, one of the major Dutch websites has different entries for "Software Developer", "Software Engineer" and the dutch word for "Software Developer".
Hope this helps a bit
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|