INSERT INTO Problem

[streborr]

I keep getting this error message:

Microsoft JET Database Engine error '80040e14'

Number of query values and destination fields are not the same.

/choice.asp, line 276

Heres the INSERT statement:

insSTRING = "INSERT INTO Draft (draft_id, draft_sales_rep, draft_date, "
insSTRING = insSTRING & "draft_dist_name, draft_dist_city, draft_dist_state, draft_dist_contact, "
insSTRING = insSTRING & "draft_oem_cus_name, draft_oem_cus_city, draft_oem_cus_state, draft_oem_cus_contact, "
insSTRING = insSTRING & "draft_end_cus_name, draft_end_cus_city, draft_end_cus_state, draft_end_cus_contact)"
insSTRING = insSTRING & " VALUES ('" & strID & "','" & strSalesRepName & "' & '" & strdate & "', "
insSTRING = insSTRING & "'" & strDistName & "','" & strDistCity & "','" & strDistState & "','" & strDistContact & "', "
insSTRING = insSTRING & "'" & strOEMName & "','" & strOEMCity & "','" & strOEMState & "','" & strOEMContact & "', "
insSTRING = insSTRING & "'" & strEndName & "','" & strEndCity & "','" & strEndState & "','" & strEndContact & "')"

rsADO.Execute insSTRING <!--this is line 276 -->


The numbers look like they match up to me!
Am I looking in the wrong place for the problem?

Thanks for any help!
Rick

 

[streborr]

Oops...
Found the problem.

insSTRING = insSTRING & &quot; VALUES ('&quot; & strID & &quot;','&quot; & strSalesRepName & &quot;' & '&quot; & strdate & &quot;', &quot;

Should be a comma.

Rick

 

[asiby]

Hi man,

I put together your insert query contained in insSTRING and I ended up with a amazing 644 long string. From what I know, I think that you are limited to only 255 charraters.

Keep me posted

 

[streborr]

--asiby--

I didn't know there was a limit. The code I posted above is actually shorter(15 fields) than the full insString which contains 57 fields!
And it works fine!

Rick

 

[Tarwn]

String length is limited by the driver your using. In the case of SQL Server I believe this to be several thousand characters, while in other db's it may be less. I know Oracle requires you to split up CLOB fields that are on the order of several Gb, but I believe the length here is also quite large, just not large enough.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)

 

[streborr]

Actual count:

characters without spaces = 2587
with spaces = 3116

Rick

 

[asiby]

Hello Tarwn:

I think you are right BUT, when you send a query to the engine, at this time since the string is handled by the scripting language, don't you think that it maybe possible that the limit will be the one in VB? Well I am not sure, it is just an idea.

Maybe we can try it by printing out (in the page) the SQL statement before its execution. We will see what is actually been sent to the DB engine.

I have a suggestion that can make things easier. The use of the object ADODB.Command

Here is an example:

<%
Option explicit

'Cursor types
Const adOpenUnpecified = -1
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'Lock Types
Const adLockUnspecified = -1
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'CommandeTypeEnum
' ....
Const adCmdTable = 2
'.....


Dim strConn
Dim strFullName, strAddress, strStreet, strCity, strProvince, strPostalCode, strAge, strSchool, strClass, strUsername, strPassword, strEmail, strPermissions
Dim objConn, objRec
Dim booRecordAdded

set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
Set objRec = Server.CreateObject(&quot;ADODB.Recordset&quot

strConn = &quot;jeusante&quot; ' a DSN on my server

'Open connection

objConn.Open strConn

' Open Players table.

objRec.CursorType = adOpenKeyset
objRec.LockType = adLockOptimistic
objRec.Open &quot;players&quot;, objConn, , , adCmdTable

' Get data from the user.

strFullName = &quot;Abdoulaye Siby&quot;
strAddress = &quot;123&quot;
strStreet = &quot;everywhere Street&quot;
strCity = &quot;SmallTown&quot;
strProvince = &quot;Moon&quot;
strPostalCode = &quot;L0L 0L0&quot;
strAge = &quot;8.023&quot;
strSchool = &quot;&quot;
strClass = &quot;Univ&quot;
strUsername = &quot;asiby&quot;
strPassword = &quot;myself&quot;
strEmail = &quot;siby@abdoulaye.com&quot;
strPermissions = &quot;102003&quot; ' Some permission code in my database

' Proceed only if the user actually entered something
' for both the first and last names.
If (strUsername <> &quot;&quot And (strFullName <> &quot;&quot _
And (strPassword <> &quot;&quot Then 'You can add any other condition before adding the new record

objRec.AddNew ' Create the new record

' Adding fields. As many as you need

objRec(&quot;fullname&quot = replace(strFullName,&quot;'&quot;,&quot;''&quot
objRec(&quot;address&quot = replace(strAddress,&quot;'&quot;,&quot;''&quot
objRec(&quot;street&quot = replace(strStreet,&quot;'&quot;,&quot;''&quot
objRec(&quot;city&quot = replace(strCity,&quot;'&quot;,&quot;''&quot
objRec(&quot;province&quot = replace(strProvince,&quot;'&quot;,&quot;''&quot
objRec(&quot;postalcode&quot = replace(strPostalCode,&quot;'&quot;,&quot;''&quot
objRec(&quot;school&quot = replace(strSchool,&quot;'&quot;,&quot;''&quot
objRec(&quot;class&quot = replace(strClass,&quot;'&quot;,&quot;''&quot
objRec(&quot;username&quot = replace(strUsername,&quot;'&quot;,&quot;''&quot
objRec(&quot;pass&quot = replace(strPassword,&quot;'&quot;,&quot;''&quot
objRec(&quot;permissions&quot = replace(strPermissions,&quot;'&quot;,&quot;''&quot

objRec.Update ' Update the change and store the new record

booRecordAdded = True

' Show the newly added data.
response.write &quot;New record added : &quot; & objRec(&quot;fullname&quot
' Note: player_id is an auto_increment field in my database

Else
response.write &quot;Please your fullname, username and password are required.&quot;
End If


objRec.Close
objConn.Close
%>


Keep me posted

ASiby