Help needed with Update Command

[johncassell]

Hi There,

could someone tell me where I am going wrong on this please..

I am updating my jobs table using the following code but it is saying 'Data type mismatch in criteria expression'.

Me._adapter.UpdateCommand.CommandText = "UPDATE `Jobs` SET `Product Name` = ?, `Quantity` = ? WHERE (`Job Number` = '1-22/06/2007')"

My Job Number column in the table is Text so I don't know what it's problem is?

Would really appreciate some help on this.

Thanks
John

 

[cjard]

Howcome youre writing this manually? Youre on .NET 3.5!

 

[johncassell]

I thought I had to write (I mean edit) manually, when I originally did it it was saying something about that it had updated 0 out of 1 records.

I edited to get this error so I thought I was getting close.

Guess not?

 

[johncassell]

Hi cjard,

i'm not actually on .net 3.5. Don't know why i put that when i set my user account up!

I've changed it now. Don't know if that makes a difference to my predicament?

This is the first time I have had a problem with an update command.

Think the difference this time is that my datagrid loads filtered by a particular job number which is retrieved from a control not inside the datagrid. This job number is not on the datagrid when i try to save.

Does that go anyway to making sense of my problem?

Thanks

John

 

[cjard]

I thought I had to write (I mean edit) manually, when I originally did it it was saying something about that it had updated 0 out of 1 records.

I edited to get this error so I thought I was getting close.

Guess not?

THat's usually caused by using statements set up for optimistic concurrency (download, hope noone else downloads, edit, send back, if someone else has downloaded and sent back, your statement updates 0 records). It is not necessarily an "error"

 

[cjard]

my datagrid loads filtered by a particular job number which is retrieved from a control not inside the datagrid

Thats okay, filling a datatable with a job as entered in a text box by the user is normal. Editing the record and returning it is also normal. It matters not that you didnt download the whole database..

This job number is not on the datagrid when i try to save.

THis statement doesnt make any sense to me.

I presume you have read the DW2 link, section "creating a form to search data" - the update process is no different to that of "saaving data to the database" section

i.e. you used a filtering query to fill your datatable, its OK to use the original update commands to send the data back. I dont really know how else to explain this, but i'll try:

You write a tableadapter query of:

SELECT * FROM TABLE

and updating commands are generated, and they work


And then you write

SELECT * FROM table WHERE jobNumber = ?


So that only fills record where jobNumber equals (whatever you supplied)


This has no bearing on the original update commands. Suppose your first query (SELECT * with NO where clause) downlaods just one record, job number 1234. You edit it and save it.

Now you use the other query. (SELECT * WHERE jobnumber =1234) And you edit it. And you save it. The same record is being saved both times. Why would you think you needed a different UPDATE sql?