Insert Into sql statement errors - combo box

[vwalla]

I have a combo box connect to an acces db. If the user types something in the combo box that is not in the list, I wanted to have a msgbox popup & ask them if they want to add it.

Here is my code. I keep getting compile errors on the execute statement. I'm sure that I am doing this all wrong.

Private Sub cboCat_LostFocus()
  Dim db As New Connection
  Set db = New Connection
    If adoPrimaryRS.EOF Then
    'Value does exist in database, ask user if they would like to add value
    If MsgBox("Add Value", vbYesNo + vbQuestion, "Add Value") = vbYes Then
    adoPrimaryRS.Open "select * from cat WHERE category='" & Replace(cboCat.Text, "'", "''") & "'", db, adOpenStatic, adLockOptimistic
    db.Execute "INSERT INTO Cat VALUES('" & cboCat.Text & "'),,adCmdText"
   
    End If
End If

adoPrimaryRS.MoveNext

adoPrimaryRS.Close

End Sub]

Thanks,

Vwalla

 

[Golom]

The format of an INSERT statement is

INSERT INTO Cat [COLOR=red](FieldName1, FieldName2, ...)[/color]
                VALUES (Value1, Value2, ...)

You are missing the list of fields to be inserted.

 

[vwalla]

I chnged it, but still get an error on ".execute" part. "Method or data member not found"

Thanks

 

[zemp]

Check to make sure that there are no typos in your field names. You can also try qualifying them with Square brackets.

INSERT INTO [Cat] ([FieldName1],[FieldName2],...)
                VALUES (Value1,Value2, ...)

zemp

 

[vwalla]

Is my connection info correct? Am I going about this properly from a coding standpoint?

Thanks,

Vwalla

 

[zemp]

Here is something to try, quotes may be in the wrong place.

db.Execute "INSERT INTO Cat VALUES('" & cboCat.Text & "')[COLOR=red]"[/color],,adCmdText

zemp

 

[vwalla]

That is what I have. (Please look at the top)

 

[zemp]

No you have this,

db.Execute "INSERT INTO Cat VALUES('" & cboCat.Text & "'),,adCmdText[COLOR=red]"[/color]

Note the location of the red quote mark.


zemp

 

[vwalla]

Oh...I will change & let you know. Would this cause the ".exectue" stmt to error?

Thanks,

Vwalla

 

[vwalla]

That was it. Thanks zemp & all others that helped me on this PITA.

Here is the full code that works:

Private Sub cboCat_LostFocus()
  Dim db As New Connection
  Set db = New Connection
  
  db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Owner\My Documents\test.mdb;"

    Set addComboRS = New Recordset
    addComboRS.Open "select * from cat WHERE cat='" & Replace(cboCat.Text, "'", "''") & "'", db, adOpenStatic, adLockOptimistic


    If addComboRS.EOF Then
    db.CursorLocation = adUseClient
    db.Execute "INSERT INTO Cat VALUES('" & cboCat.Text & "')", , adCmdText
   
    End If

addComboRS.Close

End Sub

 

[vwalla]

I just ran into something else. Letters & number work, but if I type "Wendy's" instead of "Wendys" I get the following error:

I get a syntax error - missing operator. Apparently it has something to do with the apostrophe.

Thanks

 

[jjames]

Use the same Replace function you have in your SELECT statement in the INSERT statement.

jj

 

[vwalla]

Will this show as "Wendy''s" in my db or when I print out a list of records?

Thanks,

Vwalla

 

[zemp]

Take a look at faq709-1526.


zemp

 

[jjames]

Actually, I don't think you should even have the REPLACE function in the SELECT statement but you do need it in the INSERT. No, it will not show the extra apostrophe in the database or your reports.

 

[zemp]

IMHO you should never use the Replace function in conjunction with saving SQL Server data. Use ADO parameters, it allows the data to remain as is and prevents SQL Injection attacks. faq709-1526

zemp

 

[vwalla]

Ok. I will try later this evening & post results. Got busy here at my other job.

Thanks,

Vwalla

 

[vwalla]

That worked, but I had to leave the replace stmt on the SQL also.

Zemp, this is for personal use at my house. Would SQL injection be a concern?

Thanks to all,

Vwalla

 

[zemp]

Would SQL injection be a concern?

Depends on who has access to the program/database and how sensitive/important the data is. That's your call.


zemp

 

[vwalla]

Me & whoever tries to crack my computer !

The data is not sensitive, just tracking sales tax that we have paid throughout the year (We can write it off in Texas..wooHoo!)

I will use it on my company's price database program for my outside salesmen.

Thanks,

Vwalla