Aaron - Help Please

[WadeD]

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?

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).]

[Aaron Young]

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.

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]

[WadeD]

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

Aaron For President !!!

[netSurfer]

I second the motion! Should we elect the entire cabinet for him?

[Aaron Young]

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.

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]

[jpark]

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

[WadeD]

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