By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,681 Members | 1,854 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,681 IT Pros & Developers. It's quick & easy.

syntax error near keyword"where"???????

P: 29
I am getting a syntax error but I cant seem to spot it. need help with this. Its when I click the save button that fires my SQL UPDATE query.
thansk in advance

Expand|Select|Wrap|Line Numbers
  1. 'module level declarations
  2. Dim rsNames As ADODB.Recordset
  3. Dim cnDb As ADODB.Connection
  4. Dim strConnection As String
  5. Dim blnAddMode As Boolean
  6.  
  7. Sub SaveCurrentRecord()
  8.  
  9. Dim cmdCommand As ADODB.Command
  10. Set cmdCommand = New ADODB.Command
  11. Dim strSQL As String
  12.  
  13. If Not rsNames.BOF And Not rsNames.EOF Then
  14.  
  15.     'create a new connection instance and open it using the connection string
  16.     Set cnDb = New ADODB.Connection
  17.     cnDb.Open strConnection
  18.  
  19.     Dim intCurName As Integer
  20.     intCurName = 0
  21.  
  22.     'if adding a new record
  23.     If blnAddMode = True Then
  24.   On Error GoTo HandleError
  25.         'create SQL to insert a new record into the database
  26.         'containing the values on the form
  27.         strSQL = "INSERT INTO tblName(" & _
  28.             "fldLastName, fldFirstName, fldTitle) " & _
  29.             "VALUES (" & _
  30.             "'" & Me.txtLastName & "', " & _
  31.             "'" & Me.txtFirstName & "', " & _
  32.             "'" & Me.txtTitle & "') "
  33.  
  34.     Else
  35.     On Error GoTo HandleError
  36.         'create SQL to update the existing record in the
  37.         'database with the values on the form
  38.         strSQL = "UPDATE tblName SET " & _
  39.             "fldLastName = '" & Me.txtLastName & "', " & _
  40.             "fldFirstName = '" & Me.txtFirstName & "', " & _
  41.             "fldTitle = '" & Me.txtTitle & "', " & _
  42.             "WHERE fldNameId = " & Me.txtNameId
  43.  
  44.         'save the id of the current record
  45.         intCurName = rsNames!fldNameId
  46.     End If
  47.  
  48. 'set the command to the current connection
  49. Set cmdCommand.ActiveConnection = cnDb
  50. 'set the insert or update SQL statement to the command text
  51. cmdCommand.CommandText = strSQL
  52. 'execute the delete command against the database
  53. cmdCommand.Execute
  54.  
  55. 'while connected to the database, go ahead and
  56. 'repopulate the recordset to make sure it contains
  57. 'the most current values from the database.
  58. Set rsNames.ActiveConnection = cnDb
  59. rsNames.Requery
  60. Set rsNames.ActiveConnection = Nothing
  61.  
  62. 'move back to the contact that was current before the
  63. 'requery
  64. If intCurName > 0 Then
  65.     'move back to the contact that was just updated
  66.     rsNames.Find "[fldNameId] = " & intCurName
  67. Else
  68.     'if just added new record, move to the beginning of
  69.     'the recordset
  70.     rsNames.MoveFirst
  71. End If
  72.  
  73. 'reset add mode flag to false
  74. blnAddMode = False
  75.  
  76. 'populate the controls on the form
  77. Call PopulateControlsOnForm
  78. End If
  79. Exit Sub
  80.  
  81. HandleError:
  82.     GeneralErrorHandler Err.Number, Err.Description, BUS_LOGIC, "Update"
  83.     Exit Sub
  84.  
  85. End Sub
  86.  
Oct 18 '07 #1
Share this Question
Share on Google+
4 Replies


JKing
Expert 100+
P: 1,206
Hi there!

I believe the error is that you have added one too many commas.

Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE tblName SET " & _
  2.             "fldLastName = '" & Me.txtLastName & "', " & _
  3.             "fldFirstName = '" & Me.txtFirstName & "', " & _
  4.             "fldTitle = '" & Me.txtTitle & "' " & _ //<----- here is where i removed the comma
  5.             "WHERE fldNameId = " & Me.txtNameId
  6.  
The extra comma is expecting you declare another field that needs to be set but instead it finds the WHERE clause.

Give that a try and let me know if it works out for you.
Oct 18 '07 #2

FishVal
Expert 2.5K+
P: 2,653
Remove comma before WHERE
Expand|Select|Wrap|Line Numbers
  1. "fldTitle = '" & Me.txtTitle & "', " & _
will run better this way
Expand|Select|Wrap|Line Numbers
  1. "fldTitle = '" & Me.txtTitle & "' " & _
Oct 18 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Here is additional problem I've noticed - no single quotes around the value supposed to be text.

Expand|Select|Wrap|Line Numbers
  1. ...........
  2.         strSQL = "UPDATE tblName SET " & _
  3.             "fldLastName = '" & Me.txtLastName & "', " & _
  4.             "fldFirstName = '" & Me.txtFirstName & "', " & _
  5.             "fldTitle = '" & Me.txtTitle & "', " & _
  6.             "WHERE fldNameId = '" & Me.txtNameId & "';"  '<------------
  7.  
  8.  
  9.  
Oct 18 '07 #4

P: 29
great, thanks I solved this.
Oct 18 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.