Problem Updating SQL Table

Pelky · Apr 26, 2012

I have used a panel to capture additional data for my table and need to update the record.

Sub WriteRecord

Purpose = edittext1.Text
Expenses = edittext4.Text
Destination = edittext2.text
Comments = edittext3.Text

recordstr = "UPDATE schedule SET "
recordstr = recordstr & " Expenses = " & Expenses
recordstr = recordstr & ", Destination = " & Destination
recordstr = recordstr & ", Purpose = " & Purpose
recordstr = recordstr & ", Comments = " & Comments
recordstr = recordstr & " where "
recordstr = recordstr & "StartDate = " & nStartDate & " and "
recordstr = recordstr & "StartTime = " & nStartTime
'Msgbox(recordstr,"Record layout")

Try
SQL1.ExecNonQuery(recordstr)
Catch
Msgbox(recordstr,"failed to find and update record")
End Try
End Sub

all I get is the Msgbox and the record not being updated. The records are unique in that the StartDate and Time together can only ever occur once.
Can anyone explain why it is not updating - I suspect my code is suspect?

Thank you in advance for your perusal and assistance

mc73 · Apr 26, 2012

In case you have fields of text-type, like for e.g. the "comments" field I saw, I think you should alter your query by putting ' before and after the string field. For e.g.
UPDATE schedule SET Comments = "'" & comments & "'"

I've also seen in Erel's tutorial, the use of execNonQuery2 where you set "?" as the field's content, and then use an object array to denote the corresponding values of each field. This is the method I use so far, with no problems.

Finally, you could simply remove the 'try' command and check in debugging mode what went wrong. (I am no expert, so when I write SQl, I prefer using uppercase. I've noticed you wrote "where" in lowercase, but I really don't know if this is really a problem).

keirS · Apr 26, 2012

Lower case where isn't a problem. A string should be encased in single quotes

B4X:

UPDATE ATABLE SET ACHARCOLUMN = 'ZZZZ' WHERE AINTCOLUMN= 23

Without knowing it's a string sql iwill interpret the values of your edittext's as column names.

Pelky · Apr 26, 2012

thanks for all your comments however surely if i put a 'Variable' string into quotes all i will get is the variable name and not the contents of the variable itself. The complete UPDATE string looks good in the error message, where variable names have been replaced correctly by varaiable contents. Is it possible that it is not finding the record in the WHERE command and therefore cannot update it.

Pelky · Apr 26, 2012

however whatever the reason or why it works you guys are right and by enclosing them in single quotes it worked... I am sorry but i do find it strange .... Wouldnt it be nice if there was a manual that actually showed you the various ways of allocating strings etc ....

Thank you all very much for your help - it really is appreciate.

DouglasNYoung · Apr 26, 2012

Pelky,
You're not putting the variable name in quotes but the value of the variable e.g.

B4X:

recordstr = "UPDATE schedule SET "
recordstr = recordstr & " Expenses = '" & Expenses
recordstr = recordstr & "', Destination = " & Destination
recordstr = recordstr & ", Purpose = '" & Purpose
recordstr = recordstr & "', Comments = '" & Comments
recordstr = recordstr & "' where "
recordstr = recordstr & "StartDate = " & nStartDate & " and "
recordstr = recordstr & "StartTime = " & nStartTime
'Msgbox(recordstr,"Record layout")

Not easy to see the difference, but look closely!

Douglas

mc73 · Apr 26, 2012

Pelky said:
however whatever the reason or why it works you guys are right and by enclosing them in single quotes it worked... I am sorry but i do find it strange .... Wouldnt it be nice if there was a manual that actually showed you the various ways of allocating strings etc ....

Thank you all very much for your help - it really is appreciate.

Using execNonQuery2 would elliminate the need to worry for variable type (as long as they are the same as the database's ones), at least this is what I understand so far.
Perhaps you could try this, I don't know...

B4X:

queryString="UPDATE schedule SET Expenses = ?, Destination = ?, Purpose = ?,Comments = ? WHERE StartDate = ? AND StartTime = ?"
sql1.execNonQuery2(queryString,array as object(expenses,destination,purpose,comments,nstartdate,nstarttime)

Mahares · Apr 26, 2012

I think the last code is missing a couple of single quotes or so here and there. Here is what I came up with:

B4X:

recordstr = "UPDATE schedule SET "
recordstr = recordstr & " Expenses = '" & Expenses
recordstr = recordstr & "', Destination = '" & Destination
recordstr = recordstr & "', Purpose = '" & Purpose
recordstr = recordstr & "', Comments = '" & Comments
recordstr = recordstr & "' WHERE "
recordstr = recordstr & "StartDate = '" & nStartDate & "' AND "
recordstr = recordstr & "StartTime = '" & nStartTime & "'"
SQL1.ExecNonQuery(recordstr)

Or if you do not want to mess around with the variables, use the text boxes values directly like this:

B4X:

recordstr = "UPDATE schedule SET "
recordstr = recordstr & " Expenses = '" & edittext4.Text
recordstr = recordstr & "', Destination = '" & edittext2.text
recordstr = recordstr & "', Purpose = '" & edittext1.Text
recordstr = recordstr & "', Comments = '" & edittext3.Text
recordstr = recordstr & "' WHERE "
recordstr = recordstr & "StartDate = '" & nStartDate & "' AND "
recordstr = recordstr & "StartTime = '" & nStartTime & "'"
SQL1.ExecNonQuery(recordstr)

Problem Updating SQL Table

Pelky

Active Member

mc73

Well-Known Member

keirS

Well-Known Member

Pelky

Active Member

Pelky

Active Member

DouglasNYoung

Active Member

mc73

Well-Known Member

Mahares

Expert

Similar Threads

Problem Updating SQL Table

Active Member

Well-Known Member

Well-Known Member

Active Member

Active Member

Active Member

Well-Known Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency