Member Avatar for natsume57

hi good day, i am having a problem about my program (VB 6.0)
i want to save data from my program to the access database (mS Access 2003)
the problem is that when i try to run the program, and click the button, theres an error about insert into syntax
this syntax is from my friend and it works perfectly for him but i dont know why did this happen to mine

heres my code

Private Sub registercmd_Click()
Set rcdst = New ADODB.Recordset
link.Execute "INSERT INTO Accounts(Username, Password, Fname, Lname, Age, Cnumber, Eadd, Add) VALUES ('" & logintxt.Text & "', '" & passwordtxt.Text & "', '" & firstnametxt.Text & "', '" & lastnametxt.Text & "', '" & agetxt.Text & "', '" & contacttxt.Text & "', '" & addresstxt.Text & "', '" & eaddtxt.Text & "')"
Unload Me
MsgBox "Successful"
homefrm.Show
End Sub

heres my module

Public link As New ADODB.Connection
Public rcdst As New ADODB.Recordset

Public Sub main()
Set link = New ADODB.Connection
link.Provider = "Microsoft.jet.OLEDB.4.0"
link.ConnectionString = "Data Source =" & App.Path & "\movierental.mdb"
link.CursorLocation = adUseClient
link.Open
End Sub

please help

  • 3 Contributors
  • 3 Replies
  • 231 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by jhai_salvador

Dani AI

Generated

A short diagnosis and practical checklist based on the thread: the most common causes of an "INSERT INTO syntax" error here are (a) a reserved column name in the field list, (b) the VALUES list not matching the column list positionally, (c) unescaped single quotes in text values, and (d) type mismatches (e.g., inserting a quoted string into a numeric field). was right to flag reserved words; Access/JET publishes a list of words to avoid, and the INSERT columns must align with the VALUES order. (support.microsoft.com)

Minimal checklist to run through (do these in order):

  1. Confirm the INSERT column list and the VALUES list match exactly (same count and intended mapping).
  2. Rename or bracket any problematic column names — for example, a column named Add conflicts with the SQL keyword ADD; renaming to Address or using brackets avoids that.
  3. Avoid quoting numeric fields (Age should be passed as a number), and make sure the connection is open before executing the command.
  4. Prevent syntax errors from user text by either parameterizing the query or reliably escaping single quotes. Parameterized ADO commands are the safest approach. (learn.microsoft.com)

Example (parameterized ADO Command — avoids manual escaping and type problems):

Dim cmd As New ADODB.Command
With cmd
  .ActiveConnection = link
  .CommandType = adCmdText
  .CommandText = "INSERT INTO Accounts ([Username],[Password],[Fname],[Lname],[Age],[Cnumber],[Eadd],[Address]) VALUES (?,?,?,?,?,?,?,?)"
  .Prepared = True
  .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 50, Trim(logintxt.Text))
  .Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, 50, Trim(passwordtxt.Text))
  .Parameters.Append .CreateParameter("p3", adVarChar, adParamInput, 50, Trim(firstnametxt.Text))
  .Parameters.Append .CreateParameter("p4", adVarChar, adParamInput, 50, Trim(lastnametxt.Text))
  .Parameters.Append .CreateParameter("p5", adInteger, adParamInput, , Val(agetxt.Text))
  .Parameters.Append .CreateParameter("p6", adVarChar, adParamInput, 20, Trim(contacttxt.Text))
  .Parameters.Append .CreateParameter("p7", adVarChar, adParamInput, 255, Trim(eaddtxt.Text))
  .Parameters.Append .CreateParameter("p8", adVarChar, adParamInput, 255, Trim(addresstxt.Text))
  .Execute
End With

Using Command + CreateParameter avoids the usual pitfalls of string concatenation and quoting. (learn.microsoft.com)

If parameterization is not immediately possible, at minimum escape single quotes in text values (double them) before building a query:

safeName = Replace(rawName, "'", "''")

Doubling single quotes is the standard SQL escape; parameterized queries remain the recommended fix. For further diagnosis, capture the exact SQL string shown in the Immediate window and the exact Access error text — those two items pinpoint where the parser fails. (geeksforgeeks.org)

Recommended Answers

All 3 Replies

Member Avatar for Reverend Jim

Change it to

dim qry as string
qry = "INSERT INTO Accounts(Username, Password, Fname, Lname, Age, Cnumber, Eadd, Add) VALUES ('" & logintxt.Text & "', '" & passwordtxt.Text & "', '" & firstnametxt.Text & "', '" & lastnametxt.Text & "', '" & agetxt.Text & "', '" & contacttxt.Text & "', '" & addresstxt.Text & "', '" & eaddtxt.Text & "')"

debug.writeline(qry)

and post the output here.

Member Avatar for Reverend Jim

Just an afterthought, password is a reserved word in some databases. Try

link.Execute "INSERT INTO Accounts(Username, [Password], Fname, Lname, Age, Cnumber, Eadd, Add) VALUES ('" & logintxt.Text & "', '" & passwordtxt.Text & "', '" & firstnametxt.Text & "', '" & lastnametxt.Text & "', '" & agetxt.Text & "', '" & contacttxt.Text & "', '" & addresstxt.Text & "', '" & eaddtxt.Text & "')"

Note that I replaced Password with [Password]

Edited by Reverend Jim
Member Avatar for jhai_salvador

how about posting the error?..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.