Data Adapter Update Command

D

DonnaF

Active member
Joined
Mar 20, 2003
Messages
30
Im trying to do an update on the database, after changes are made, but I keep getting errors on my update command statement. Im sure its a syntax error on my part, but I cant seem to find it. Can someone take a look at my code, and let me know what Im doing wrong? All the examples I have only use only field for the WHERE, instead of two like Im using. Thanks, Donna

DataAdapter.UpdateCommand.CommandText = _
"UPDATE TasksTable SET Task_Date=" & _
"" & txtDate.Text & "," & _
"Task_Description =" & txtDesc.Text & "" & _
"WHERE (Task_Date = SaveDate)" & _
"AND (Task_Desc = SaveDesc); "
Try
DataAdapter.UpdateCommand.ExecuteNonQuery()
Catch exceptionparameter As Exception
MessageBox.Show("Bad update")
End Try
 
"AND (Task_Desc = SaveDesc); "

You have );" at the end there. Remove the ;

"AND (Task_Desc = SaveDesc) "

I dont see anything else wrong at a quick glance. If that didnt solve the problem then can you please copy/paste the error message that you are getting? Thanks.
 
Are you getting a datatype mismatch on the task_date and txtDate.Text? If you are storing the task_date as a datetime data type, trying to set its value to a string will cause an exception to be thrown from the database.

Seeing the error message would be helpful.

Incidently, SQL statements in Oracle and MySQLs Command Prompt interface must end with a semicolon. MySQLs Admin interface does not require the semicolon.

What db are you writing too? That will also determine how you input your dates in a column that is configured with a datatime datatype.

Jon
 
Jon, Im getting the Message Box Display saying Bad Update (see my code above). How do you display what the error was on the execute nonquery? I am using an Access database. The SaveDate was stored as a date field, based on a selected record from the database. For a date field, do I need to include a # sign? If so, then do you know what the format should be for my update command text?

Thanks, Donna
 
If you look at the update command there is a mistake in the following lines.

Code: 
"Task_Description =" & txtDesc.Text & "" & _
"WHERE (Task_Date = SaveDate)" & _

You are missing a space between the description and where clause try...

Code: 
DataAdapter.UpdateCommand.CommandText = _
"UPDATE TasksTable SET Task_Date=" & _
"" & txtDate.Text & "," & _
"Task_Description =" & txtDesc.Text & "" & _
" WHERE (Task_Date = SaveDate)" & _
" AND (Task_Desc = SaveDesc); "
 
I tried the suggestions made, and I still cant get the update to work. Im still getting my messagebox display "Bad Update". Does anyone have suggestions on what the syntax should be to use a WHERE with two conditions, using an AND? Thanks, Donna
 
Do Message.Show(exceptionparameter.Message) .. or is it .Message()? That way we can know exactly what the error is. :) "Bad Update" doesnt tell us much for debugging purposes.

Also paste your current code so we can see what updates youve made.
 
I did the messagebox.show, and I got this message "Parameter SaveDate has no default value". It seems to me that I got this message before, and that it had to do with the syntax of a date field in an Insert Statement. Heres my code:

DataAdapter.UpdateCommand.CommandText = _
"UPDATE TasksTable SET Task_Date=" & _
"" & txtDate.Text & "," & _
"Task_Description =" & txtDesc.Text & "" & _
" WHERE (Task_Date = #SaveDate#) & _"
" AND (Task_Desc = SaveDesc); "
"WHERE (Task_Date = SaveDate)" & _
"AND (Task_Desc = SaveDesc); "

Try
DataAdapter.UpdateCommand.ExecuteNonQuery()
Catch exceptionparameter As Exception

MessageBox.Show(exceptionparameter.Message)
MessageBox.Show("Bad update")
End Try

I just made another change in my code, and now Im getting the message "Syntax error in query expression (Task_Date =#SaveDate#) AND (Task_Desc = SaveDesc). Heres the latest code:
DataAdapter.UpdateCommand.CommandText = _
"UPDATE TasksTable SET Task_Date=" & _
"" & txtDate.Text & "," & _
"Task_Description =" & txtDesc.Text & "" & _
"WHERE (Task_Date = #SaveDate#)" & _
"AND (Task_Desc = SaveDesc); "

Try
DataAdapter.UpdateCommand.ExecuteNonQuery()
Catch exceptionparameter As Exception

MessageBox.Show(exceptionparameter.Message)
MessageBox.Show("Bad update")
End Try

Thanks, Donna
 
Where does the value SaveDate get assigned?
Also, have you considered using a datatime picker instead of a textbox? Would remove a whole bunch of validation issues.


Jon
 
Public Sub SelectNewDate(ByVal newDate As Date)
Dim SaveDate As Date
SaveDate = newDate

The SaveDate gets assigned everytime this routine is called - which is everytime the user selects a new date to view tasks. I have several routines that pass dates to this routine. I still think I just have a syntax problem with the Updatecommand, but I cant seem to figure it out.
 
[VB]
DataAdapter.UpdateCommand.CommandText = _
"UPDATE TasksTable SET Task_Date=" & _
"" & txtDate.Text & "," & _
"Task_Description =" & txtDesc.Text & "" & _
"WHERE (Task_Date = #" & SaveDate & "#)" & _
"AND (Task_Desc = " & ""SaveDesc & "" ); "

Try
DataAdapter.UpdateCommand.ExecuteNonQuery()
Catch exceptionparameter As Exception

MessageBox.Show(exceptionparameter.Message)
MessageBox.Show("Bad update")
End Try
[/VB]

As far I can see that ammended version should work.

One thing I also do, is if you are experiencing problems with a insert, delete or update statement write it to a string for example

Code: 
string sqlStatement = "SELECT * FROM " & tableName & "......." etc.

Then put a break point on the statement and you can actually look at what the code has interpretted the statement to be and you can run it in access or SQL Server by copying and pasting into the query window of the database you are using.
 
Code: 
Dim SQL,SaveDate As String 
SaveDate=06/01/2003
txtDate.txt=06/02/2003
SQL="UPDATE Task SET Task_Date=#"+txtDate.txt+"#,task_Desc="+txtDesc.Text +"
WHERE Task_Date=#"+SaveDate+"# AND Task_Desc = "+SaveDesc+""
DataAdapter.UpdateCommand.CommandText = SQL

In the above, SaveDate & txtDate.txt you would have noticed in the form of mm/dd/yyyy. Try it in that format. It might work. It is becos of that you might get the error. Eventhough date is stored in dd/mm/yyyy while querying i think it should be of the form mm/dd/yyyy. Try it out & see whether it is working.

Hope it helps in solving the problem
 
Thanks everyone for your suggestions. I tried the latest suggestion from zy_abc, and it now seems to be getting around the problem with the date. Now, Im getting the error "Parameter Task_Desc has no default value". It doesnt like something in the syntax for the Task_Desc field. Does anyone have any ideas on whats wrong with my syntax?

Thanks, Donna


DataAdapter.UpdateCommand.CommandText = _
"UPDATE TasksTable SET Task_Date=" & _
"" & SaveDate & "," & _
"Task_Description =" & txtDesc.Text & "" & _
"WHERE Task_Date=#" + SaveDate + "# AND Task_Desc = " + SaveDesc + ""


Try
DataAdapter.UpdateCommand.ExecuteNonQuery()
Catch exceptionparameter As Exception

MessageBox.Show(exceptionparameter.Message)
MessageBox.Show("Bad update")
End Try
 
I found the latest problem! In the SET Statement, it says Task_Description, which is the correct field name. However, in the WHERE statement, it says Task_Desc. When I corrected Task_Desc to Task_Description, the Update worked!

Thanks again everyone for all your helpful suggestions.

Donna
 
Back
Top