number of query values and destination fields not the same [Archive]

doowle

09-15-2002, 11:30 PM

Okay, I know this should be obvious but I have been playing with it for hours and I haven't figured it out yet...maybe a fresh set of eyes will catch it easily.

I am getting the error message
"number of query values and destination fields not the same" referencing line 28 of the following code when I try to update a record:


<%
' declare variables
strAction = Request("Action")
intAID = Request("AID")
strApplicant = Request("Applicant")
strNextPage = Request("NextPage")

strSearchSQL = "Select AID, Applicant, NextPage from tblSurvey"

If UCase(Request.ServerVariables("REQUEST_METHOD")) = "POST" Then
Select Case strAction
Case "U"
strSQL = "UPDATE tblSurvey " & _
"SET Applicant = '" & formatString(strApplicant, 1) & "', " & _
"NextPage = '" & formatString(strNextPage, 1) & "', " & _
"WHERE AID = " & intAID & ""
Set cmd = objConn.Execute(strSQL)
Set cmd = Nothing
Response.Write("<HTML><BODY onLoad=""alert('Entry updated..!');""></BODY></HTML>")

Case "A"
strSQL = "INSERT INTO tblSurvey (AID, Applicant, NextPage) " & _
"VALUES (" & intAID & ", '" & formatString(strApplicant, 1) & "', " & _
"'" & formatString(strNextPage, 1) & "')"

Set cmd = objConn.Execute(strSQL) <-------------------------------------------- THIS IS LINE 28
Set cmd = Nothing
Response.Write("<HTML><BODY onLoad=""alert('Ran Case A');""></BODY></HTML>")
End Select
strAction = ""
intAID = ""
strApplicant = ""
strNextPage = ""

ElseIf strAction = "U" Then
strSQL = "SELECT * " & _
"FROM tblSurvey " & _
"WHERE AID = " & intAID & ""
Set rsE = objConn.Execute(strSQL)

intAID = rsE("AID")
strApplicant = rsE("Applicant")
strNextPage = rsE("NextPage")

set cmd = objConn.Execute(strSQL)
Set cmd = Nothing
strAction = ""
Response.Write("<HTML><BODY onLoad=""alert('ran strAction U');""></BODY></HTML>")

End If

Set rsC = objConn.Execute(strSearchSQL)
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> Applicants </TITLE>
<script language = "javascript">
function valPage()
{
if(document.frm.Action.value == '')
{
document.frm.Action.value = 'A';
}
document.frm.submit();
}

function valSearch()
{
document.frm.Action.value = 'S';
document.frm.submit();
}
</script>

</HEAD>

<BODY BGCOLOR="#FFFFFF">
<table width="100%" height="100%" valign="top" border="0">
<tr>
<td valign="top">
<form name="frm" method="post" action="<%=Request.ServerVariables("SCRIPT_NAME")%>">
<table border="1" cellspacing="0" cellpadding="0">
<tr>
<td align="center" valign="middle"> <table border=0 cellpadding=0 cellspacing=5>
<tr>
<td colspan="2" valign="top"> <P><span class="centerTopTitle">APPLICANT
INFORMATION</span></P>
<hr align=left width="40%"> </td>
</tr>
<tr>
<td align="right" valign="top" nowrap><STRONG>Social Security Number:</STRONG>
</td>
<td width="100%" valign="top">
<INPUT name="AID" type="text" id="AID" value="<%=intAID%>" size="25" maxlength="25">
</td>
</tr>
<tr>
<td height="24" align="right" valign="top" nowrap><STRONG>Last Name:</STRONG></td>
<td valign="top">
<INPUT name="Applicant" type="text" id="Applicant" value="<%=strApplicant%>" size="35" maxlength="35">
</td>
</tr>
<tr>
<td align="right" valign="top" nowrap><STRONG>Next Page:</STRONG>
</td>
<td valign="top">
<INPUT name="NextPage" type="text" id="NextPage" value="<%=strNextPage%>" size="35" maxlength="35">
</td>
</tr>
<tr>
<td colspan="2" align="right">
<input type="hidden" name="Action" value="<%=strAction%>">
<input type="hidden" name="AID" value="<%=Request("AID")%>">
<input type="button" name="btnSubmit" value="Submit" onClick="valPage();">
<input type="button" name="btnClear" value="Clear" onClick="location.replace('<%=Request.ServerVariables("SCRIPT_NAME")%>');">
</td>
</tr>
</table>
</td>
</tr>
</table>

<b>Current Applicants in Table</b></p>
<hr align="left" width="200">

</p>
<table border="0" cellspacing="5" cellpadding="0">
<tr>
<td nowrap> </td>
<td nowrap><u><b>Social Security Number</b></u></td>
<td nowrap><u><b>Last Name</b></u></td>
<td nowrap><u><b>Next Page</b></u></td>
</tr>
<%
If Not rsC.EOF Then
While Not rsC.EOF
%>
<tr>
<td nowrap><a href="?Action=U&AID=<%=rsC("AID")%>">edit</a></td>
<td nowrap><%=rsC("AID")%></td>
<td nowrap><%=rsC("Applicant")%></td>
<td nowrap><%=rsC("NextPage")%></td>
</tr>
<%
rsC.MoveNext
WEND
Else
Response.Write("<tr><td colspan=""5"">No records found...</td></tr>")
End If
%>
</form>
</table>
</td>
</tr>
</table>
</body>
</html>
<%
' cleanup objects
Set rsC = Nothing
Set rsE = Nothing
Set objConn = Nothing
%>

Count and count as I can, I still don't see where they are not equal. Do you?

Quandary

09-16-2002, 11:18 AM

Why are you setting the var "cmd" to the execute statement and then destroying it on the next line?

I think *that* is where the error is coming up, not in the query itself. If you could post the exact error message (is it ASP or SQL?) that would help a bit.

-- Quandary.

Availible by PM if you would like further assistance

doowle

09-17-2002, 07:46 PM

I reset to nothing each time because I reuse it. I don't have to do that? (Yes, obviously I am a newbie to ASP)

I tried removing it, moving it farther away, etc. error hasn't changed yet.

The exact error message is

Microsoft JET Database Engine error '80040e14'

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

/petbungalow/aspen/applicants1.asp, line 28

Quandary

09-18-2002, 10:51 AM

Print your variables (just Response.Write strSQL) and make sure that the resulting string that you are running the query with is turning out how it is supposed to. If it isn't formatted correctly, you have your problem, otherwise you need to look somewhere else...

-- Quandary

AltatemTC

09-18-2002, 12:25 PM

My guess is that you have 1 of the following problems.

1.) You have a value with a comma in it.
2.) You have a blank value.
3.) You are getting multiples of a control so that it is embedding a comma.

Write the strSQL to the Response object so you can see it and determine it's validity.

Karifan

09-20-2002, 12:01 PM

I know I may be outdated, but this is the code that I wrote to access a database in my asp website, and it works.

Dim dbConn
Dim dbQuery
Dim counter
Dim downloadRS

If Request("entry") = "true" then

Set dbConn = Server.CreateObject("ADODB.Connection")
Set downloadRS = Server.CreateObject("ADODB.Recordset")

dbConn.Open "DBQ=" & Server.Mappath("db/FinalProject.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"

'Create the SQL query and create the recordset
if not Request("CustomQuery") = "All" then
dbQuery = "SELECT Name FROM Downloads WHERE Category = '"& Request("CustomQuery")&"'"

I am not sure if ObjectConn.Execute creates the connection, the recordset and then probing a registered Odbc data source?

just my .02 cents