What's wrong with my update query ?

cmM
10-25-2004, 03:13 PM
I am trying to edit a record in a database using a command button to display the current values in text boxes, then another command button to read those text boxes and update the record displayed with the inputted data (if a textbox is not edited, then the inputted data is the same as the display data).
When I run it I get a syntax error in my query. Anyone know why?
Here's the code:

If lsEditToolNumber = Null Then
lsEditToolNumber = "NULL"
Else
lsEditToolNumber = "'" & lsEditToolNumber & "'"
End If

If lsEditMachine = Null Then
lsEditMachine = "NULL"
Else
lsEditMachine = "'" & lsEditMachine & "'"
End If

If lsEditStation = Null Then
lsEditStation = "NULL"
Else
lsEditStation = "'" & lsEditStation & "'"
End If

If lsEditToolType = Null Then
lsEditToolType = "NULL"
Else
lsEditToolType = "'" & lsEditToolType & "'"
End If

If lsEditBoltOD = Null Then
lsEditBoltOD = "NULL"
Else
lsEditBoltOD = "'" & lsEditBoltOD & "'"
End If

If lsEditQuantity = Null Then
lsEditQuantity = "NULL"
Else
lsEditQuantity = lsEditQuantity
End If

If lsEditDescription = Null Then
lsEditDescription = "NULL"
Else
lsEditDescription = "'" & lsEditDescription & "'"
End If

If lsEditAPBP = Null Then
lsEditAPBP = "NULL"
Else
lsEditAPBP = "'" & lsEditAPBP & "'"
End If



Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\LEP Software\BoltMaker DB\BM.mdb;"
MyConn.Open

' this is all one line in my program :
MyConn.Execute ("UPDATE ToolsMain
SET (ToolNumber, Machine, Station, ToolType, BoltOD, Quantity, Description, APBP) =
(" & lsEditToolNumber & ", " & lsEditMachine & ", " & lsEditStation & ", " &
lsEditToolType & ", " & lsEditBoltOD & ", " & lsEditQuantity & ", " &
lsEditDescription & ", " & lsEditAPBP & ")
WHERE ToolID = " & lsEditToolID)

MyConn.Close

And here is the actual querry that's being run (as shown above):
UPDATE ToolsMain
SET (ToolNumber, Machine, Station, ToolType, BoltOD, Quantity, Description, APBP) =
('test', '122B', '1ST', 'DIE', '1/2', 2, 'tryin the database', 'AP-24005')
WHERE ToolID = 2253

What's wrong with it?

Thanks

DRW
10-25-2004, 05:04 PM
I think you need to do it like this. Perhaps build up an SQL string e.g.

Dim strSQL as string

strSQL = " UPDATE ToolsMain SET ToolNumber = '" & lsEditToolNumber & "', "

strSQL = strSQL & "Machine = '" & lsEditMachine & "', "

strSQL = strSQL & "ToolType = '" & IsEditToolType & "', "

etc. etc.

:)

VBJoe
10-25-2004, 06:34 PM
What's the error you're getting?

cmM
10-26-2004, 08:55 AM
What's the error you're getting?

Syntax error in UPDATE statement.

The way I see it though, my update query is correct from an SQL point of view. Why am I getting this error?

cmM
10-26-2004, 09:12 AM
Aaah, ok I got it.

The mistake was in my SQL querry.
DRW, you're right, the fields have to be reassigned values individually
field1 = field1Input, field2 = field2Input rather than doing it all in one (field1, field2) = (field1Input, field2Input)

Thank you very much for your help.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum