Help? Update query works in Access not from VB

[sweth]

Hai All,
I got strange Error. Syntax error in update stmt. Same query is running in Access.

I use same routine for all update stmt all are working only this one is working.

Please help to ressolve

VB Code

lsSql = ""

lsSql = "Update vehiclemaster set regno ='" & TxtRegnno.Text & "',"
lsSql = lsSql & " VehicleType='" & CbovechType.Text & "',"
lsSql = lsSql & " dateofregn ='" & CStr(Format(DTregnDate.Value, "dd/MM/yyyy&quot) & "',"
lsSql = lsSql & " placeofregn ='" & TxtRto.Text & "',"
lsSql = lsSql & " roadtaxfrom ='" & CStr(Format(DTroadtaxfrom.Value, "dd/MM/yyyy&quot) & "',"
lsSql = lsSql & " roadtaxto ='" & CStr(Format(DTroadtaxto.Value, "dd/MM/yyyy&quot) & "',"
lsSql = lsSql & " ownername ='" & TxtOwner.Text & "',"
lsSql = lsSql & " zone ='" & TxtZone.Text & "',"
lsSql = lsSql & " regnrenew ='" & CStr(Format(DTRtoRen.Value, "dd/MM/yyyy&quot) & "'"
lsSql = lsSql & " where vechid =" & Val(TxtVehid.Text)

Call update(lsSql)


Public Function update(lsSql As String) As Boolean
On Error GoTo DBErr
Dim conn As Object
Dim cmd As New ADODB.Command
Dim lngRtn As Long

'On Error GoTo UPDATEERR

nthExecute = True
Debug.Print lsSql

Set conn = New ADODB.Connection
'Dim conn As ADODB.Connection

With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open App.Path & "\calltaxi.mdb"
End With

cmd.ActiveConnection = conn
cmd.CommandText = lsSql
cmd.Execute lsSql, lngRtn, adCmdText

Set cmd = Nothing
Set conn = Nothing
Exit Function
DBErr:
MsgBox Err.Description, vbCritical + vbOKOnly, "Error Message"
nthExecute = False

'Err.Raise Err.Number, Err.Source, Err.Description

End Function

------------------------------------------------
Data Base Fields
field name DataType Required Primary key
-----------------------------------------------------------
vechileid number yes yes
regno TEXT YES
vehicleType text yes
dateofregn text no
placeofregn text no
roadtaxfrom text no
roadtaxto text no
ownername text no
zone text no
regnnew text no

Thanks and regrs

 

[CCLINT]

Post the results of

Debug.Print lsSql

 

[sweth]

Thanks for your reply the debug.print lssql displays
Update vehiclemaster set regno ='TN 09 9800', VehicleType='XXX', dateofregn ='12/03/2003', placeofregn ='YYY', roadtaxfrom ='10/08/2003', roadtaxto ='23/04/2004', ownername ='ZZZ', zone ='AAAASSS', regnrenew ='16/06/2005' where vechid =1

 

[Spellman]

Try using ## to enclose your dates within. Mark

The key to immortality is to make a big impression in this life!!

 

[CCLINT]

Wouldn't matter, as the field types are text fields, and not date fields, as mentioned in the original question.

sweth: What happens if you use the Execute method of the Connection object for this?

Conn.Execute lsSql

And, is it just not working, or does an error occure?

 

[tedsmith]

I can only get my SQLs with a date in VB6 to work by making date format in the SQL statement US(m/d/yyyy) even though your local machine is set to d/m/yyyy

 

[Schroeder]

Do any of the text strings in your SQL statement have apostrophes in them? They look like closing quotation marks when your statement is assembled.

 

[masenca]

Hi,
You are trying to filter by vechid =1. I don't see a vechid field in your table.

 

[harebrain]

The syntax for the Execute method of the ADO COMMAND object is:

{set rst = } command.Execute RecordsAffected, Parameters, Options

You've already set the CommandText propery; it doesn't belong as the first parameter to Execute.

And I'd suggest supplying adExecuteNoRecords as the Options parameter because you apparently don't want to create a recordset.

HTH,
David

 

[tedsmith]

You spelt it Vechid in the SQL but VechileID in the table

 

[sweth]

hai all,
Thanks for taking pain for me. Sorry the fields in table and my query are same ie vechid.

Regrs

 

[CCLINT]

Sorry for not getting back sooner. I just want to clean up some threads waiting answers:

I overlooked that "Zone" is a reserved word so enclose it in brackets:

lsSql = lsSql & " [zone] ='" & TxtZone.Text & "',"