Insert Error

rb74 · Mar 5, 2003

Hi all,

This one is really confusing me because I think I have all of my quotes in the right spot, and I have the fields in the insert statement matching the fields in the table. When I submit the information on the form, I am receiving this error message: "Number of query values and destination fields are not the same."

Here is my code:
<%
Option Explicit
Dim strConnect
%>
<html>
<head>


<title>Receipt of submitance</title>
</head>
<body>
<%
Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection&quot

objConn.Open strConnect
Dim objCommand
Set objCommand = Server.CreateObject("ADODB.Command&quot

Dim strFName, strLName, strAddress1, strAddress2, strCity, strState, strZip, strPhone, strDistrict, strEmail
Dim SQLResponders

strFName = Request.Form("first_name&quot

strLName = Request.Form("last_name&quot

strAddress1 = Request.Form("address1&quot

strAddress2 = Request.Form("address2&quot

strCity = Request.Form("city&quot

strState = Request.Form("state&quot

strZip = Request.Form("zip&quot

strPhone = Request.Form("phone&quot

strDistrict = Request.Form("school_district&quot

strEmail = Request.Form("email&quot

SQLResponders = "INSERT INTO Members VALUES ('" & strFName & "', '" & strLName & "', " & _
"'" & strAddress1 & "', '" & strAddress2 & "', '" & strCity & "', '" & strState & "', " & _
"'" & strZip & "', '" & strPhone & "', '" & strDistrict & "', '" & strEmail & "')"

objCommand.ActiveConnection = objConn
objCommand.CommandText = SQLResponders
objCommand.CommandType = adCmdText
objCommand.Execute
Set objCommand = Nothing

My Access database has a Member table with the following fields:

memberID (AutoNumber)
first_name (text)
last_name (text)
address1 (text)
address2 (text)
city (text)
state (text)
zip (text)
phone (text)
school_district (text)
email (text)

Any idea as to why I am getting this error?

Thanks in advance,

Rob

[lightsaber]

JamesLean · Mar 5, 2003

After you build your SQL string, Response.Write it to the screen so you can see what is actually being sent to the DB. I'd guess there is a problem with some of the values in the form fields - maybe containing quotes which is affecting the query.

Post the resultant SQL query here if you need to. --James

palbano · Mar 5, 2003

This is a string concatenation problem and you are asking us to "debug" your code. Instead see this FAQ faq333-3048

Reference 10

-pete

rb74 · Mar 5, 2003

Here is the output to the page -

INSERT INTO Members VALUES ('Joe', 'Smith', '123 Main Street', '', 'New York', 'NY', '10010', '888-888-8888', 'home', 'jsmith@email.com')

All fields in this table are text fields. Could the problem lie in the autonumber field in the table?

Thanks,

Rob

JamesLean · Mar 5, 2003

I don't think Access likes empty strings (''). You should use NULL to indicate no value.

To test this, try filling in a value for EVERY box in the form and submit that? --James

palbano · Mar 5, 2003

Rob, ok now you've got a MS Access forum question. Do you know where they are here at Tek-Tips?

-pete

rb74 · Mar 5, 2003

Guys,

I tried your suggestions prior to submitting this post, including putting data into the address2 field and this did not work. All my access fields are set to allow zero length. Do you know of any problems using the auto-increment field as a primary key in Access and doing an insert statement via ASP. I code in VB all of the time and I never have a problem like this.

Rob

Tarwn · Mar 5, 2003

Ok, the problem is that when you do an insert into a table without specifying the fields your going to suplly data for, than the database expects you to supply data for every single field in the new record. The issues that comes up, however, is that you neither wantor are allowed to supply data to an autonumber field. There are two solutions here,
1) Insert a null for that field:
INSERT INTO Members VALUES (,'Joe', 'Smith', '123 Main Street',, 'New York', 'NY', '10010', '888-888-8888', 'home', 'jsmith@email.com')

This should work if I remember correctly. The problem here is that it is messy and you have to make sure everything is in the right order and it may actually take longer for the database to resolve because it first has to do an internal query to figure out what the fields are.

2) Specify the Fields:
INSERT INTO Members(first_name,last_name,address1,address2,city,state,zip,phone,school_district,email) VALUES ('Joe', 'Smith', '123 Main Street',, 'New York', 'NY', '10010', '888-888-8888', 'home', 'jsmith@email.com')

Here I am specifying the fields i want to fill and the order in which i will be doing so. It wasn't necessary in this case to include address2 field since that will be a null, but I am doing it for completeness.

Hope this helps,
-Tarwn _{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

palbano · Mar 5, 2003

Tarwin, so that's an Access issue yes? If Rob runs his INSERT statement in an Access Query window he gets the same error result yes? If so it has nothing to do with ASP yes?

Tiernok.com ?? It's registered but no site running, what is it?

Also what is the bottom row?

-pete

rb74 · Mar 5, 2003

Tarwn,

Yep, the problem is Access wants me to indicate all fields in the statement. I tested this as well, specifying the fields, excluding the autonumber field and naturally it worked. I did try using a Null value in the values list and unfortunately this did not work. I am not really SOL, but it is just that I have never had this problem before and I was thinking maybe I missed something. Oh well, worse thing is that I supply the fields in the statement.

Thanks,

Rob

rb74 · Mar 5, 2003

Pete,

If this is an "Access" problem, how come I have NEVER had this problem using Visual Basic, ADO and an Access database, meanwhile I am using ASP, ADO and an Access database and I am having this problem??

Rob

palbano · Mar 5, 2003

Rob,

logic:

>> If Rob runs his INSERT statement in an Access Query
>> window he gets the same error result yes? If so it has
>> nothing to do with ASP yes?

seems right to me, of course, maybe im an idiot!

-pete

Tarwn · Mar 5, 2003

Tiernok.com will be up next week-ish. Nowehere near complete to my satisfaction, but onpnt told me to put it up anyways, so I'll let him boss me around this time

. The last row is another subject altogether from my website

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

Tarwn · Mar 5, 2003

Also, rob sorry the null for the autonumber field didn't work, I may be remembering this from a differant language or it could be that other databases allow this and MS Access doesn't. AS I said it has been a long time since I relied on remembering the field order in order to do an insert, I always specify it myself now.

-Tarwn _{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Insert Error

rb74

Programmer

JamesLean

Programmer

palbano

Programmer

rb74

Programmer

JamesLean

Programmer

palbano

Programmer

rb74

Programmer

Tarwn

Programmer

palbano

Programmer

rb74

Programmer

rb74

Programmer

palbano

Programmer

Tarwn

Programmer

Tarwn

Programmer

Similar threads

Part and Inventory Search

Sponsor