Solved

Error when updating Access db

Posted on 2007-03-29
10
198 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 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 400 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 50 total points
ID: 18815035
stqSQLInsert = "INSERT INTO products(productname, Productdescription ) VALUES ('" & tt & "', '" & tt1 & "')"

You have to put string values in single quotation marks!
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 25

Assisted Solution

by:SStory
SStory earned 50 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 47

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 400 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 47

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 400 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

737 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