Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error when updating Access db

Posted on 2007-03-29
10
Medium Priority
?
201 Views
Last Modified: 2013-12-25
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()


0
Comment
Question by:esps
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 1600 total points
ID: 18814788
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
 

Author Comment

by:esps
ID: 18814867
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
 
LVL 11

Assisted Solution

by:Sven
Sven earned 200 total points
ID: 18815035
stqSQLInsert = "INSERT INTO products(productname, Productdescription ) VALUES ('" & tt & "', '" & tt1 & "')"

You have to put string values in single quotation marks!
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 25

Assisted Solution

by:SStory
SStory earned 200 total points
ID: 18815509
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18820966
>>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
 

Author Comment

by:esps
ID: 18821632
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
 
LVL 48

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 1600 total points
ID: 18822345
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
 

Author Comment

by:esps
ID: 18822457
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
 
LVL 48

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 1600 total points
ID: 18822480
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
 
LVL 25

Expert Comment

by:SStory
ID: 18822818
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question