You are not telling the DataAdapter what the UPDATE string is. You must assign dataAdapter.updatecommand = cmdbl.GetUpdateCommand();. After this then you can do dataAdapter.Update();.
You are not telling the DataAdapter what the UPDATE string is. You must assign dataAdapter.updatecommand = cmdbl.GetUpdateCommand();. After this then you can do dataAdapter.Update();.
I've seen so many people do that but it's completely unnecessary. The ONLY reason to call GetUpdateCommand is if you want to make changes to the Command object it returns. This:
I'm quite sure that you didn't get an error message that said:
Update is not possible since of a query
Please post the actual error message because that is not really very close to what it would have said.
The problem is that your query returns no primary key information. A CommandBuilder cannot generate SQL code unless it can uniquely identify each record, which it cannot do without a primary key. First of all you must set the MissingSchemaAction property to AddWithKey. That still won't help though unless your query returns the primary key column(s). Is the Name column your primary key? I'm guessing, and hoping, that it isn't. If you don't include the primary key in your query then you can't use a CommandBuilder, plain and simple.
Silly me. They aren't pulling the Primary Key in order for the commandbuilder object to create the WHERE statement. I am still intermediate in the SQL language.
JM, assuming this wasn't giving an error and was done properly, what should replace the "dataAdapter.updatecommand = cmdbl.GetUpdateCommand();" statement?
JM, assuming this wasn't giving an error and was done properly, what should replace the "dataAdapter.updatecommand = cmdbl.GetUpdateCommand();" statement?
Nothing. Like I said, you don't call GetUpdateCommand, GetInsertCommand or GetDeleteCommand at all unless you specifically want to edit the Command objects they return. The CommandBuilder implicitly creates whatever SQL statements are required on demand. Something like this will work as is:
Code:
OleDbDataAdapter adp = new OleDbDataAdapter("SELECT * FROM MyTable", con);
OleDbCommandBuilder bld = new OleDbCommandBuilder(adp);
adp.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adp.Fill(myDataTable);
// Make changes to data here.
adp.Update(myDataTable);
When you call Update the CommandBuilder implicitly generates the required Commands and temporarily assigns them to the DataAdapter's properties. If you wanted to edit those Commands, like if you needed to enlist them in a transaction, only then would you need to generate them explicitly.
I've seen so many people do that but it's completely unnecessary. The ONLY reason to call GetUpdateCommand is if you want to make changes to the Command object it returns. This:
I'm quite sure that you didn't get an error message that said:Please post the actual error message because that is not really very close to what it would have said.
The problem is that your query returns no primary key information. A CommandBuilder cannot generate SQL code unless it can uniquely identify each record, which it cannot do without a primary key. First of all you must set the MissingSchemaAction property to AddWithKey. That still won't help though unless your query returns the primary key column(s). Is the Name column your primary key? I'm guessing, and hoping, that it isn't. If you don't include the primary key in your query then you can't use a CommandBuilder, plain and simple.
I have given an screen shot of the exception in my previous message plz check that
You've attached a ZIP file. There's no reason for us to download and extract a ZIP to see an image. Just attach a JPG and it will be displayed right there in your post. Make it easy for us to help you and you're more likely to get help.
Besides, I'm almost 100% sure that the information I have given already will solve your problem. Make sure that your query returns the PK or a CommandBuilder simply cannot work.
No still not working.
I have tried all your methods
Another thing that I was told about Data Adapter is it does not change a cell value, You must overwrite all the values in a row
If it is true it is v bad for using a data Adapter
I m also posting the exception NOT In zipped format but a whole image.
Last edited by Abbas Haider; Mar 8th, 2007 at 12:50 PM.
You are selecting a single column from your table. And it is not a primary key. The adapter does not know how to reference the table and find out which row it needs to update. In your select statement, get the primary key along with [Name].
In fact you've completely ignored my advice from post #5:
Originally Posted by jmcilhinney
The problem is that your query returns no primary key information. A CommandBuilder cannot generate SQL code unless it can uniquely identify each record, which it cannot do without a primary key. First of all you must set the MissingSchemaAction property to AddWithKey. That still won't help though unless your query returns the primary key column(s). Is the Name column your primary key? I'm guessing, and hoping, that it isn't. If you don't include the primary key in your query then you can't use a CommandBuilder, plain and simple.
Your problem is still exactly the same, as mendhak says. Your query is not returning any primary key information. In my first post I said that you need to set the MissingSchemaAction property and add the PK column to the query. You have done neither, thus you still have the same issue.
Any one please give a sample Program of C# Console which insert values in a record, by taking input from user i.e System.Console.read(); which pass it to dataset and ultimately database table.
The program should be running as I have made a program but it is giving me exceptions and not writng to the DB
Thanks
You've already been told how to solve your problem and you've ignored the advice provided. People are trying to help you and you're refusing to accept that help. Why have you not done what we've already told you? Do you not understand what we're telling yo to do? If so then say so. If people try to help and you ignore their advice then they're unlikely to try to help to many more times.
look
I have read all your statements, but haven't you considered a situation in which you have no primary key as you stated that my columns aren't returning no primary key. Now if you have to insert a simple record in a simple with no primary key NECCESSARY table, how would you do it? I am just asking that and you are blaming me that I am ignoring your guide
Even I have used alternative method of Command Builder to do my job but I am unable to do it
Why cant you just paste a simple code here so I may match it with my code,
I even gave a screen shot of the problem.
Lastly I am saying again that I am NOT USING ANY PRIMARY KEY FIELD IN MY TABLE.
Last edited by Abbas Haider; Oct 3rd, 2007 at 03:52 AM.
It took you until post #16 to tell us that your table doesn't actually have a primary key. Also, the screen shot you posted shows you trying to update a record, not insert one. Insertion should work fine with a CommandBuilder even without a primary key because insertion doesn't use a WHERE clause. As long as your query involves only one table then the InsertCommand can be generated.
Now, in that screen shot you are editing an existing row in the table, i.e. you are performing an UPDATE. If it's your intention to perform an INSERT then you have to create a new row and add it to the table, not edit an existing row.
DataAdapter.Update() : Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable named "Table."
In the screen-shot you provided, you are passing a Select query, so exactly what are you trying to update or insert???
You passed SELECT query to DataAdpater,
filled DataSet,
then add 1 row to that DataSet using user input,
but when you use the Update(), you are not telling the DataAdapter that you are INSERTing or UPDATEing your database now!!!
In the screen-shot you provided, you are passing a Select query, so exactly what are you trying to update or insert???
You passed SELECT query to DataAdpater,
filled DataSet,
then add 1 row to that DataSet using user input,
but when you use the Update(), you are not telling the DataAdapter that you are INSERTing or UPDATEing your database now!!!
Actually the Inserts and Updates can be generated automatically by just passing a select statement to the DataAdapter and using the OleDbCommandBuilder
Now if the idiot would just put a primary key on the table.
Actually the Inserts and Updates can be generated automatically by just passing a select statement to the DataAdapter and using the OleDbCommandBuilder
Now if the idiot would just put a primary key on the table.
Oh how nice of you to poke in here and comment.
Well what can I say to the people like you? it is of no use for me to tell that I have tried all thse methods AND THEY ARE NOT AUTOMATICALLY WORKING.
Please if you think that I am idiot DO NOT BOTHER TO WRITE HERE AND ANY OF MY THREADS
Last edited by Abbas Haider; Oct 19th, 2007 at 12:14 PM.
Oh how nice of you to poke in here and comment.
Well what can I say to the people like you? it is of no use for me to tell that I have tried all thse methods AND THEY ARE NOT AUTOMATICALLY WORKING.
Please if you think that I am idiot DO NOT BOTHER TO WRITE HERE AND ANY OF MY THREADS
Did you ever put a primary key on the table or are you still ignoring the suggestion?
Reply With Quote
Originally Posted by tacoman667
