473,289 Members | 2,089 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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

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
4 3197
JKing
1,206 Expert 1GB
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
2,653 Expert 2GB
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
2,653 Expert 2GB
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
cluce
29
great, thanks I solved this.
Oct 18 '07 #5

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

Similar topics

5
by: Rachel Weeden | last post by:
I'm working on an ASP Web application, and am having syntax issues in a WHERE statement I'm trying to write that uses the CInt Function on a field. Basically, I want to select records using...
0
by: Lauren Wilson | last post by:
The error does not have an error number. It's not even a normal Access error message box. Hope someone has some insight to this problem. I have a form that contains a large (5" x 4") text box...
6
by: Tony | last post by:
Dear All, When I run an example program on the http://www.dotnetjunkies.com/quickstart/util/srcview.aspx?path=/quickstart/aspplus/samples/webforms/data/datagrid1.src&file=VB\datagrid1.aspx&font=3 ...
3
by: Michael | last post by:
Hi Everyone, I'm having a slight problem, I hope I didn't overlook something. I'm getting the following error when I try to execute the function below: Incorrect syntax near 'intake_GetListSet'."...
3
by: bughunter | last post by:
IMHO, statements like this is mistake typically. May be more better made this construction - I said about empty WHERE - invalid? A lot of data will saved... :-) Andy
3
by: Arthur Dent | last post by:
Hi all, Im just curious, what is the purpose of the keyword "Overloads" in VB nowadays? I understand conceptually what overloads are and what they do, but im a little puzzled, because if you...
1
by: itamar82 | last post by:
I am getting the following error: Microsoft OLE DB Provider for SQL Server error '80040e14' Incorrect syntax near the keyword 'WHERE'. for the sql below: SELECT TourId FROM (SELECT...
10
by: jonathanemil | last post by:
Hello, I am a 1st semester Computer Science student in a Python class. Our current assignment calls for us to read a list from a file, create a 2-dimensional list from the file, and check to see...
0
kmartinenko
by: kmartinenko | last post by:
Hello, I am wondering if there is a sly workaround in ArcGIS 9.2 where I can write a "near" analysis script in Python and create my own tool for the purpose of identifying the distance values...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.