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)

        objcommand.Execute()
        cmdreader.Update()


espsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)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.

Regards,

Wayne
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
SvenTech Lead Web-DevelopmentCommented:
stqSQLInsert = "INSERT INTO products(productname, Productdescription ) VALUES ('" & tt & "', '" & tt1 & "')"

You have to put string values in single quotation marks!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SStoryCommented:
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
0
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.
0
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?

regards
0
Wayne Taylor (webtubbs)Commented:
esps,

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.

Wayne
0
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.

regards
Zack
0
Wayne Taylor (webtubbs)Commented:
Zack,

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';")
        conn.Open()
        Dim stqSQLInsert As String = "INSERT INTO products(productname, Productdescription ) VALUES ('" _
                                                        & tt & "', '" & tt1 & "')"
        Dim cmd As New OleDbCommand(stqSQLInsert, conn)
        cmd.ExecuteNonQuery()

Regards,

Wayne
0
SStoryCommented:
Here is a site on classic ADO command objects
http://www.w3schools.com/ado/ado_ref_command.asp

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:
http://www.codeproject.com/vb/net/vinheritdform.asp

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
"SELECT * FROM ADDRESS WHERE CITY=@CITY"

@CITY represents the param in SQL server.

HTH,

Shane
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

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.