Categories: MSDN / DotNet / Java / Scripts / Linux / PHP Ask - La ask - La Answer

SQLAdd - i get bug - what to change

Sub SQLAdd()
Dim cnn As Connection
Dim cmn As Command
Set cmn = New Command
Set cnn = New Connection

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\prog\a.mdb"
cnn.Open strConnectionString
Set cmn.ActiveConnection = cnn

'Define SQL query.
cmn.CommandText = "INSERT INTO book (name, phone) VALUES (?, ?)"

'Save a prepared (or pre-compiled) version of the query specified in CommandText
'property before a Command object's first execution.
cmn.Prepared = True

'Define query parameter configuration information.
cmn.Parameters.Append cmn.CreateParameter(Name, adVarChar, , 255)
cmn.Parameters.Append cmn.CreateParameter(phone, adVarChar, , 255)

'Define and execute first insert.
cmn("Name") = txtName.Text
cmn("phone") = txtPhone.Text
cmn.Execute , , adCmdText + adExecuteNoRecords

cnn.Close
Set cnn = Nothing
Set cmn = Nothing
End Sub
[1008 byte] By [q8z] at [2007-11-11 8:42:25]
# 1 Re: SQLAdd - i get bug - what to change
Name is a Jet reserved word. If you use it in a SQL statement it must be enclosed within brackets.
pclement at 2007-11-11 17:25:10 >
# 2 Re: SQLAdd - i get bug - what to change
Sub SQLAdd()
Dim cnn As Connection
Dim cmn As Command
Set cmn = New Command
Set cnn = New Connection

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\prog\a.mdb"
cnn.Open strConnectionString
Set cmn.ActiveConnection = cnn

'Define SQL query.
cmn.CommandText = "INSERT INTO book (personName, phone) VALUES (?, ?)"

'Save a prepared (or pre-compiled) version of the query specified in CommandText
'property before a Command object's first execution.
cmn.Prepared = True

'Define query parameter configuration information.
cmn.Parameters.Append cmn.CreateParameter(personName, adVarChar, , 255)
cmn.Parameters.Append cmn.CreateParameter(phone, adVarChar, , 255)

'Define and execute first insert.
cmn("personName") = txtName.Text 'here is the bug
'
cmn("phone") = txtPhone.Text
cmn.Execute , , adCmdText + adExecuteNoRecords

cnn.Close
Set cnn = Nothing
Set cmn = Nothing
End Sub
q8z at 2007-11-11 17:26:10 >
# 3 Re: SQLAdd - i get bug - what to change
In your CreateParameter statements are personName and phone variables or literals? If they are literals then you need to enclose them in double quotes.
pclement at 2007-11-11 17:27:16 >
# 4 Re: SQLAdd - i get bug - what to change
so what should i write?
q8z at 2007-11-11 17:28:11 >
# 5 Re: SQLAdd - i get bug - what to change
cmn.Parameters.Append cmn.CreateParameter("personName", adVarChar, , 255)
cmn.Parameters.Append cmn.CreateParameter("phone", adVarChar, , 255)
pclement at 2007-11-11 17:29:09 >