Data Adapter Update Command

[DonnaF]

I'm trying to do an update on the database, after changes are made, but I keep getting errors on my update command statement. I'm sure it's a syntax error on my part, but I can't seem to find it. Can someone take a look at my code, and let me know what I'm doing wrong? All the examples I have only use only field for the WHERE, instead of two like I'm 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

[wyrd]

"AND (Task_Desc = SaveDesc); "

 

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

 

"AND (Task_Desc = SaveDesc) "

 

I don't see anything else wrong at a quick glance. If that didn't solve the problem then can you please copy/paste the error message that you are getting? Thanks.

[jfackler]

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 it's 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 MySQL's 'Command Prompt' interface must end with a semicolon. MySQL's '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

[DonnaF]

Jon, I'm 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

[wyrd]

Try Task_Date = #SaveDate#

[dsgreen57]

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

 

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

 

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

 

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

[DonnaF]

I tried the suggestions made, and I still can't get the update to work. I'm 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

[wyrd]

Do Message.Show(exceptionparameter.Message) .. or is it .Message()? That way we can know exactly what the error is. :) "Bad Update" doesn't tell us much for debugging purposes.

 

Also paste your current code so we can see what updates you've made.

[DonnaF]

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. Here's 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 I'm getting the message "Syntax error in query expression (Task_Date =#SaveDate#) AND (Task_Desc = SaveDesc). Here's 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

[jfackler]

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

[DonnaF]

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 can't seem to figure it out.

[dsgreen57]

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

 

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

 

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.

[zy_abc]

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

[DonnaF]

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, I'm getting the error "Parameter Task_Desc has no default value". It doesn't like something in the syntax for the Task_Desc field. Does anyone have any ideas on what's 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

[DonnaF]

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