ADODB.Recordset error '800a0bb9'

[TheCandyman]

I am trying to do a simple insert into a DB with classic ASP. When i have it do a response.write on the SQL you can see it below before it tryings to insert. The table is named, tbl_AssignedCodes with two fields which are both numbers. I have checked and double checked the spellings. That part is correct. I also took the SQL statement and ran that in the DB and it worked.

INSERT INTO tbl_AssignedCodes (reg_id, TechCodeID) VALUES (177, 102)

ADODB.Recordset error '800a0bb9' 

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. 

/convention/exhibitor/TechCodes.asp, line 30

This part of the code that makes it work

	reg_id = CInt(request.QueryString("reg_id"))
	CODEx=CInt(Request("CODE" & x))

			SQL = "INSERT INTO tbl_AssignedCodes (reg_id, TechCodeID) "&_ 
		          "VALUES ("& reg_id &", "& CODEx &")"
			response.write SQL & "<br>"
			rs1.open SQL,0,2,1
			rs1.close

 

[johnwm]

I would usually run an Execute statement against a connection object to do an Insert, as Insert doesn't need a Recordset.

ObjCon.Execute(SQL)

where objCon is an open connection to your database

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[DotNetGnat]

yes..you dont need a recordset object to do an insert query...you just need a connection object...

-DNG

 

[TheCandyman]

I used your suggestions and tried this: Conn.Execute(SQL) but now i get this error

INSERT INTO tbl_AssignedCodes (reg_id, TechCodeID) VALUES (177, )

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. 

/convention/exhibitor/TechCodes.asp, line 30

This is my connection:

strCon = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=[i]DB goes here[/i]"
Set Conn = CreateObject("ADODB.Connection")
Conn.mode = 3 ' adModeReadWrite
Conn.open strCon
Set rs = Server.CreateObject("ADODB.Recordset")
Set rs1 = Server.CreateObject("ADODB.Recordset")

 

[DotNetGnat]

as you can see you are getting an empty value for your variable...make sure that you are getting the value of CODEx correctly...

-DNG

 

[TheCandyman]

If you look up at the top comment, the first code has the SQL it returns. It has both values.

 

[onpnt]

Development practices when using SQL/Interfaces faq333-4896
How can other tools help me develop ASP faq333-4770



General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[DotNetGnat]

i know it has both values...what i said is that one of the values is having empty value...can you see it here...

INSERT INTO tbl_AssignedCodes (reg_id, TechCodeID) VALUES (177, )

you got 177 for reg_id but for TechCodeID you have empty value...so that means you are doing something wrong in retrieving the value of TechCodeID...so check what you are doing wrong...

-DNG

 

[TheCandyman]

the code snip at the top rolls onto the second line: (177, 102) It has both values, it inserts into the Access DB if i run it as a SQL query. Just doesn't make sense. Has to be something in the ASP or how it connects.

			SQL = "INSERT INTO tbl_AssignedCodes (reg_id, TechCodeID) "&_ 
		          "VALUES ("& reg_id &", "& CODEx &")"
			response.write SQL & "<br>"
			Conn.Execute(SQL)

 

[onpnt]

there isn't much more to this error

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

then what it says. if you are recieving this error then I highly doubt it is working in any query interface either


General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[onpnt]

not to extend what DNG already stated but I see no carriage return in this line and see the error also. Maybe you should validate and repost the SQL write again so it is more clear.

INSERT INTO tbl_AssignedCodes (reg_id, TechCodeID) VALUES (177, )

This thread seems to be going the distance again


General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[Tarwn]

Double check thast you didn't accidentally remove the line that says

CODEx=CInt(Request("CODE" & x))

since you would then have no value in the SQL string.

If your posting a form to this page, double check that you have filled in a value for that field before submitting to do your test.
If your using Querystring values, make sure your variable and value show up in the querystring.


As DNG and Onpnt have mentioned, the fact that there is no value in that position indicates that CODEx has no value. Trace back from tere to determine why that variable is not being populated by a value and you will have the solution.

---

Now, as to your original question, the SQL error was stating that one of the values you were trying to insert was either the wrong type or outside the allowed range for the field it was trying to be placed in. To fix this error (which I assume you will see again wen you fix the disappearing value for CODEx) you will need to check and see if both your fields are Number fields. If either of the fields are not a Number field, then you will have found your original error.
Once you get this far we should be able to tell you more dependant on what type of field you're dealing with. I could list all of the possible fields and solutions, but then this book I am writing for a reply would be even longer

-T