Error when updating Access db

When running the code below, in VB 2005, I get the error:
No value given for one or more required parameters        
at the line ;
 cmdreader.Open(stqSQLInsert, conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

Help please!

        Dim tt As String
        Dim tt1 As String
        tt = "test1"
        tt1 = "test2"
        Dim objcommand As New ADODB.Command
        Dim conn As ADODB.Connection = New ADODB.Connection()
        Dim cmdreader As ADODB.Recordset = New ADODB.Recordset

        conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= 'c:\duratech.mdb';")
        objcommand.ActiveConnection = conn

        Dim stqSQLInsert As String
        stqSQLInsert = "INSERT INTO products(productname, Productdescription ) VALUES (tt, tt1)"

        objcommand.CommandText = stqSQLInsert
        cmdreader.Open(stqSQLInsert, conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, -1)


Who is Participating?
Wayne Taylor (webtubbs)Connect With a Mentor Commented:
Hi esps,

You've enclosed your variables within the sql string. Try replacing it with this....

stqSQLInsert = "INSERT INTO products(productname, Productdescription ) VALUES (" & tt & ", " & tt1 & ")"

Also, how many fields are there in the table "products"? The error is saying there is a required field that is not being assigned a value.


espsAuthor Commented:
Hi Wayne,
I have tried your code and now get the error;

Syntax error (comma) in query expression '(55555, ggggggggggggg)'.

which is the values I assign to the variables tt and tt1 from the database in question.

The table products does have more fields than in the test example
SvenConnect With a Mentor Tech Lead Web-DevelopmentCommented:
stqSQLInsert = "INSERT INTO products(productname, Productdescription ) VALUES ('" & tt & "', '" & tt1 & "')"

You have to put string values in single quotation marks!
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

SStoryConnect With a Mentor Commented:
The best way to avoid this is to use Command objects.  The way you are doing it is open for SQL injections. If this is ASP.NET it is really a bad thing to do.  If just VB app, then it still could be.  Command objects look over the data and simplify the syntax used above.

I highly recommend you rewrite this as a command object
Anthony PerkinsCommented:
>>If this is ASP.NET it is really a bad thing to do. <<
If it is, they are using classic ADO and not ADO.NET.  Go figure.
espsAuthor Commented:
I'm not quite there yet. After putting the variables in single quotes I get to the next step at least. I now assume that I have to provide variables for all fields. Based on SStory'  your recomendation to re-writing as Command Object, can I please have more information on this and possible a code example or link?

Wayne Taylor (webtubbs)Connect With a Mentor Commented:

Regardless of how you do it, if the table has a field marked as required, you need to assign a value to it. Not doing so will cause the error you recieved initially.

espsAuthor Commented:
That is fine, Wayne, thank you.
My remaining problem is the field with the autonumber, primary key.
I would still like some notes on Command Object and some samples or a link.
Will accept your next response as solution. You have helped me a great deal.

Wayne Taylor (webtubbs)Connect With a Mentor Commented:

You might do it like this....

        Dim tt As String = "test1"
        Dim tt1 As String = "test2"
        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= 'c:\duratech.mdb';")
        Dim stqSQLInsert As String = "INSERT INTO products(productname, Productdescription ) VALUES ('" _
                                                        & tt & "', '" & tt1 & "')"
        Dim cmd As New OleDbCommand(stqSQLInsert, conn)


Here is a site on classic ADO command objects

As for dotnet, Wayne gave you sort of an answer, but it still suffers from SQL injects I think, becuase it doesn't use parameters

you would need to modify the above to use Access param placeholder syntax. I can't remember if that is a ?TT and ?TT1 or not, in the string,

Check out:

which has lots of examples of Parameters and such--although for SQL server which means it uses SQLCommand instead of OleDbCommand, etc. and the way you put placeholders for SQL is like

@CITY represents the param in SQL server.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.