-
I know this is simple, but the following code gives me not a not enough parameters error from vb (Access backend). However, if I paste this SQL generated at runtime into Access, it creates the record. Can you use quotations in RDO or ADO? Am I doing something wrong?
Code:
strString = "Can't print on all computers"
sSQL = "INSERT INTO [MainRep] ([Service Description]) " & "VALUES (" & """" & strString & """" & ");"
txtTestSQL = sSQL
I used """" around each side to pass "Can't print on all computers" because of the ' in Can't
Thanks,
Wade
[This message has been edited by WadeD (edited 01-26-2000).]
-
You should use Apostrophes (') to enclose Text in a SQL Statement, or if you want or have to use DoubleQuotes (") then you can use Chr(34), otherwise VB interprets them as String Parameters, ie.
Code:
strString = "Can't print on all computers"
sSQL = "INSERT INTO [MainRep] ([Service Description]) " & "VALUES ('" & strString & "'" & ");"
txtTestSQL = sSQL
'Or..
strString = "Can't print on all computers"
sSQL = "INSERT INTO [MainRep] ([Service Description]) " & "VALUES (" & Chr(34) & strString & Chr(34) & ");"
txtTestSQL = sSQL
------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]
-
I tried the second one with chr(34) so I could send the ' in can't. VB gave me the following run-time error:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
I copied the sql statement that it put in txtTestSQL into Access and it runs from there without modification. I know I used to send quotations in DAO. Can you do the same in RDO or ADO?
Thanks,
Wade
-
-
I second the motion! Should we elect the entire cabinet for him?
-
I've just got test out your code, and didn't get any error.
I use ADO and Access2000.(If Access97, then the provider should be Provider = Microsoft.Jet.OLEDB.3.51)
Option Explicit
Private cn As ADODB.Connection
Private Sub Command1_Click()
Dim strSQL As String
Dim strString As String
Dim r As Integer
strString = "Can't print on all computers"
strSQL = "INSERT INTO [Test] ([Desc]) " & "VALUES (" & """" & strString & """" & ");"
cn.Execute strSQL, r
MsgBox "Appened " & r & " record"
End Sub
Private Sub Form_Load()
Set cn = New ADODB.Connection
Dim strCn As String
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& "C:\WINNT\Profiles\jpark.000\Desktop\NEW COPYOFKDS\empty.mdb;" _
& "Persist Security Info=False"
cn.Open strCn
End Sub
Private Sub Form_Unload(Cancel As Integer)
cn.Close
Set sn = Nothing
End Sub
Joon
-
Oh, ok..
I hadn't even noticed the contents of your String Vairable.. as SQL uses th Apostrophe to enclose Strings, it's interpreting the one in your string as the beginning of a string, but there isn't another so you get the Error.
to get around this, you must double up on any Apostrophes in your Strings, ie.
strString = "Can''t print on all computers"
If you have VB6, use the Replace Function, ie.
Code:
strString = "Can't print on all computers"
sSQL = "INSERT INTO [MainRep] ([Service Description]) VALUES ('" & Replace(strString, "'", "''") & "');"
txtTestSQL = sSQL
------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]
-
Aaron,
That works..thanks!
Jpark,
I'm not sure why it didn't work for me as is, if it works on yours, but I'll save your code. Thanks for the effort!
Wade
