esps
asked on
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(stqSQLInser t, conn, ADODB.CursorTypeEnum.adOpe nDynamic, ADODB.LockTypeEnum.adLockO ptimistic)
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=Micros oft.Jet.OL EDB.4.0;Da ta Source= 'c:\duratech.mdb';")
objcommand.ActiveConnectio n = conn
Dim stqSQLInsert As String
stqSQLInsert = "INSERT INTO products(productname, Productdescription ) VALUES (tt, tt1)"
objcommand.CommandText = stqSQLInsert
cmdreader.Open(stqSQLInser t, conn, ADODB.CursorTypeEnum.adOpe nDynamic, ADODB.LockTypeEnum.adLockO ptimistic, -1)
objcommand.Execute()
cmdreader.Update()
No value given for one or more required parameters
at the line ;
cmdreader.Open(stqSQLInser
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=Micros
objcommand.ActiveConnectio
Dim stqSQLInsert As String
stqSQLInsert = "INSERT INTO products(productname, Productdescription ) VALUES (tt, tt1)"
objcommand.CommandText = stqSQLInsert
cmdreader.Open(stqSQLInser
objcommand.Execute()
cmdreader.Update()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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.
If it is, they are using classic ADO and not ADO.NET. Go figure.
ASKER
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
regards
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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