Solved

Error when updating Access db

Posted on 2007-03-29
10
192 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now