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

VB to ACCESS headache!!!

P: 3
i have been struggling with a SQL statement that is supposed to save data entered via the VB interface to a MS access database. This is the code that i a using and i am receiving an "syntax error in insert into statement"
any assistance will be greatly appreciated



Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdsaverec_Click()
  2. Dim sql As String
  3. Dim SQL1 As String
  4. Dim cn As New ADODB.Connection
  5.  
  6.  
  7. ' First check to see whether the record already exists in the database
  8.  
  9. SQL1 = " Select * from Personal where Acctrefno = '" & txtrefno.Text & "'"
  10.  
  11. ' Execute save command if the record does not exist
  12.  
  13. sql = "Insert into Personal value ('" & txtlastname.Text & "','" _
  14. & txtfirstname.Text & "','" & txtothername.Text & "', '" & txtaddress.Text & "','" _
  15. & txtcontact.Text & "', '" & txtage.Text & "')"
  16.  
  17.  
  18. ' Create a connection to the MS Acess MACS database
  19.  
  20. cn.ConnectionString = "DSN=MACS"
  21.  
  22.  
  23. ' Open the connection to the MACS database
  24. Dim rs As ADODB.Recordset
  25.  
  26. cn.Open
  27.  
  28.  Set rs1 = cn.Execute(SQL1)
  29.  If rs1.EOF Then
  30.  Set rs = cn.Execute(sql)
  31.  MsgBox ("Record Saved")
  32.  Else
  33.  MsgBox ("Account already exists!")
  34.  
  35.  
  36.  End If
  37.  cn.Close
  38.  
  39. End Sub
If u can think of anything please help out
Dec 25 '07 #1
Share this Question
Share on Google+
4 Replies


QVeen72
Expert 100+
P: 1,445
Hi,

It should be "Values" not Value..

sql = "Insert into Personal Values ('" & txtlastname.Text & "','" _
& txtfirstname.Text & "','" & txtothername.Text & "', '" & txtaddress.Text & "','" _
& txtcontact.Text & "', '" & txtage.Text & "')"

Regards
Veena
Dec 25 '07 #2

debasisdas
Expert 5K+
P: 8,127
Instead of using SELECT * try to use COUNT(fieldname).

Performance wise that will be faster.
Dec 26 '07 #3

P: 3
It should be "Values" not Value..

sql = "Insert into Personal Values ('" & txtlastname.Text & "','" _
& txtfirstname.Text & "','" & txtothername.Text & "', '" & txtaddress.Text & "','" _
& txtcontact.Text & "', '" & txtage.Text & "')"
Hey and thanks for the response.
Here is an update.
I made the changes but now I am getting a runtime error, too few parameters, expected 1.
Please assist.
This is how the code looks now.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdsaverec_Click()
  2. Dim sql As String
  3. Dim SQL1 As String
  4.  
  5.  
  6. ' First check to see whether the record already exists in the database
  7.  
  8. SQL1 = " Select * from Personal where acctrefno = '" & txtrefno & "'"
  9.  
  10. ' Execute save command if the record does not exist
  11.  
  12. sql = "Insert into Personal values ('" & txtlastname & "','" _
  13. & txtfirstname & "','" & txtothername & "', '" & txtaddress & "','" _
  14. & txtcontact & "', '" & txtage & "' '" & txtrefno & "')"
  15.  
  16.  
  17. ' Create a connection to the MS Acess MACS database
  18.  
  19. Dim cn As New ADODB.Connection
  20. cn.ConnectionString = "DSN=MACS"
  21.  
  22.  
  23. ' Create a record set
  24.  
  25. Dim rs As ADODB.Recordset
  26. Dim rs1 As ADODB.Recordset
  27.  
  28.  
  29. 'Open the connection
  30.  
  31. cn.Open
  32.  
  33. Set rs1 = cn.Execute(SQL1)
  34.  
  35. If rs1.EOF Then
  36.   'Execute the statement
  37.  
  38.   Set rs = cn.Execute(sql)
  39.   MsgBox ("Record Saved")
  40. Else
  41.   MsgBox ("Account already exists!")
  42.  
  43.  
  44. End If
  45. cn.Close
  46.  
  47. End Sub
Dec 26 '07 #4

P: 3
Instead of using SELECT * try to use COUNT(fieldname).

Performance wise that will be faster.
Thanks a lot!
I have been really struggling with this
Dec 26 '07 #5

Post your reply

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