RankenIS
asked on
Error formatting query, probably invalid parameters
Continually having issues with this web page ever since I switched to msdb..sp_send_dbmail from master.dbo.xp_sendmail.
Keep getting error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Error formatting query, probably invalid parameters
and then the line number of the error, which is just the cmdStoredProc.Execute command.
All the asp variables match the sql stored procedure variables. Can't seem to get this working to save me. Attached is the asp page. My second post will be the stored proc.
Keep getting error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Error formatting query, probably invalid parameters
and then the line number of the error, which is just the cmdStoredProc.Execute command.
All the asp variables match the sql stored procedure variables. Can't seem to get this working to save me. Attached is the asp page. My second post will be the stored proc.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/SSS.asp" -->
<%
Const adCmdStoredProc = 4
Const adChar = 129
Const adVarChar = 30
Const adInteger = 3
Const adNumeric = 131
Const adParamInput = 1
Const adParamOutput = 2
Const adParamReturnValue = 4
Const adExecuteNoRecords = 128
dim cnnStoredProc, cmdStoredProc
dim conn, rsDump, sqlDumpIns
dim CrsCde,frmUsername,CrsStud,StudTrm,StudYr,StudFirst,StudLast
dim Checkbox1,Checkbox2,Checkbox3,Checkbox4,Checkbox5,Checkbox6,Checkbox7,Checkbox8,Checkbox9,Checkbox10
dim Checkbox11,Checkbox12,Checkbox13,Checkbox14,Checkbox15,Checkbox16,Checkbox17,Checkbox18,Checkbox19,Checkbox20
dim Checkbox21,Checkbox22,Comment,CurGrade
frmUsername = trim(request.form ("frmUsername"))
StudFirst = trim(request.form ("StudFirst"))
StudLast = trim(request.form ("StudLast"))
CrsCde = trim(Request.Form("CrsCde"))
Session.timeout = 5
set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=coldbeer;Initial Catalog=SSS;Data Source=Jenzabar_ex"
Set Session("Jenzabar_ex_conn") = conn
'sqlDumpIns = "INSERT INTO tblAcadReferrals (id_num,instrctr_id_num,crs_cde,trm_cde,yr_cde,cur_grade,Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10,Q11,Q12,Q13,Q14,Q15,Q16,Q17,Q18,Q19,Q20,Q21,Q22,InstrComment) values ('" & CrsStud & "','" & frmUsername & "','" & CrsStud & "','" & StudTrm) & "','" & trim(StudYr) & "','" & trim(Request.Form("Checkbox1")) & "','" & trim(Request.Form("Checkbox2")) & "','" & trim(Request.Form("Checkbox3")) & "','" & trim(Request.Form("Checkbox4")) & "','" & trim(Request.Form("Checkbox5")) & "','" & trim(Request.Form("Checkbox6")) & "','" & trim(Request.Form("Checkbox7")) & "','" & trim(Request.Form("Checkbox8")) & "','" & trim(Request.Form("Checkbox9")) & "','" & trim(Request.Form("Checkbox10")) & "','" & trim(Request.Form("Checkbox11")) & "','" & trim(Request.Form("Checkbox12")) & "','" & trim(Request.Form("Checkbox13")) & "','" & trim(Request.Form("Checkbox14")) & "','" & trim(Request.Form("Checkbox15")) & "','" & trim(Request.Form("Checkbox16")) & "','" & trim(Request.Form("Checkbox17")) & "','" & trim(Request.Form("Checkbox18")) & "','" & trim(Request.Form("Checkbox19")) & "','" & trim(Request.Form("Checkbox20")) & "','" & trim(Request.Form("Checkbox21")) & "','" & trim(Request.Form("Checkbox22")) & "','" & trim(Request.Form("Comment")) & "')"
sqlDumpIns = "INSERT INTO tblAcadReferrals (id_num,instrctr_id_num,crs_cde,trm_cde,yr_cde,cur_grade ,Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q15,Q16,Q17,Q18,Q19,Q20,Q21,Q22,Q9,Q10,Q11,Q12,Q13,Q14,InstrComment) values ('" & trim(Request.Form("CrsStud")) & "','" & trim(Request.Form("frmUsername")) & "','" & trim(Request.Form("CrsCde")) & "','" & trim(Request.Form("StudTrm")) & "','" & trim(Request.Form("StudYr")) & "','" & trim(Request.Form("CurGrade")) & "','" & trim(Request.Form("Checkbox1")) & "','" & trim(Request.Form("Checkbox2")) & "','" & trim(Request.Form("Checkbox3")) & "','" & trim(Request.Form("Checkbox4")) & "','" & trim(Request.Form("Checkbox5")) & "','" & trim(Request.Form("Checkbox6")) & "','" & trim(Request.Form("Checkbox7")) & "','" & trim(Request.Form("Checkbox8")) & "','" & trim(Request.Form("Checkbox15")) & "','" & trim(Request.Form("Checkbox16")) & "','" & trim(Request.Form("Checkbox17")) & "','" & trim(Request.Form("Checkbox18")) & "','" & trim(Request.Form("Checkbox19")) & "','" & trim(Request.Form("Checkbox20")) & "','" & trim(Request.Form("Checkbox21")) & "','" & trim(Request.Form("Checkbox22")) & "','" & trim(Request.Form("Checkbox9")) & "','" & trim(Request.Form("Checkbox10")) & "','" & trim(Request.Form("Checkbox11")) & "','" & trim(Request.Form("Checkbox12")) & "','" & trim(Request.Form("Checkbox13")) & "','" & trim(Request.Form("Checkbox14")) & "','" & trim(Request.Form("Comment")) & "')"
Set rsDump = Server.CreateObject("ADODB.Recordset")
rsDump.Open sqlDumpIns, conn, 3, 3
'rsDump.Close
'set rsDump=nothing
'conn.Close
'conn=nothing
%>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Academic Support Referral</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
</head>
<body link="red" alink="red" vlink="red">
<table width="100%" border="0" cellspacing="0" cellpadding="0" id="Table2">
<tr>
<td align="left" background="WebImages/background.jpg"><img src="WebImages/topleft.jpg" width="600" height="65" align="left" hspace="0" vspace="0" border="0"/></td>
<td background="WebImages/background.jpg"></td>
<td align="right" background="WebImages/background.jpg"><img src="WebImages/topright.jpg" width="317" height="65" align="right" hspace="0" vspace="0" border="0"/></td>
</tr>
</table><br />
Thank you for your submission. An email will follow with your copy of the referral.
<form method="post" title="Form" action="AcadReferral.asp">
<input name="frmUsername" type="hidden" visible="false" value="<%response.write(frmUsername)%>"/><br/><br/>
<input name="Submit" type="submit" value="Submit another referral"/>
</form>
<%
Set cnnStoredProc = Server.CreateObject("ADODB.Connection")
cnnStoredProc.Open "Provider=SQLOLEDB.1;User ID=xxxxxx;Password=xxxxx;Initial Catalog=SSS;Data Source=xxxxxxx"
Set cmdStoredProc = Server.CreateObject("ADODB.Command")
cmdStoredProc.activeConnection = cnnStoredProc
cmdStoredProc.CommandText = "usp_ReferralSubmitted"
cmdStoredProc.CommandType = adCmdStoredProc
cmdStoredProc.parameters.append cmdStoredProc.createparameter ("@PSUsername", adInteger, adParamInput, , frmUsername)
cmdStoredProc.parameters.append cmdStoredProc.createparameter ("@PSFirst", adVarChar, adParamInput, 30, StudFirst)
cmdStoredProc.parameters.append cmdStoredProc.createparameter ("@PSLast", adVarChar, adParamInput, 30, StudLast)
cmdStoredProc.parameters.append cmdStoredProc.createparameter ("@PSCrs", adVarChar, adParamInput, 30, CrsCde)
cmdStoredProc.Execute, ,adExecuteNoRecords
%>
</body>
</html>
Hi RankenIS,
>>..Microsoft OLE DB Provider for SQL Server error '80040e14'
Error formatting query, probably invalid parameters
It seemed like you're trying to insert NULL value into a column that not allow NULL value. Check the passed parameters that encountered this error:
Further clarification.
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-80040e14-errors.html
>>..Microsoft OLE DB Provider for SQL Server error '80040e14'
Error formatting query, probably invalid parameters
It seemed like you're trying to insert NULL value into a column that not allow NULL value. Check the passed parameters that encountered this error:
Further clarification.
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-80040e14-errors.html
You have an error on line 132 of the stored procedure, you are missing an ' after the email address.
Cheers, Andrew
Cheers, Andrew
ASKER
That was my mistake in anonymizing the stored proc just for this site. The working stored procedure has the closing tick.
ASKER
Everything on the sql side looks good - the form puts in the proper information into a row in the database, just the confirmation page listed above errors out. I'm a bit foggy on asp - can the following lines pull info from another page into this page? All those variables are input on a different page.
cmdStoredProc.parameters.append cmdStoredProc.createparameter ("@PSUsername", adInteger, adParamInput, , frmUsername)
cmdStoredProc.parameters.append cmdStoredProc.createparameter ("@PSFirst", adVarChar, adParamInput, 30, StudFirst)
cmdStoredProc.parameters.append cmdStoredProc.createparameter ("@PSLast", adVarChar, adParamInput, 30, StudLast)
cmdStoredProc.parameters.append cmdStoredProc.createparameter ("@PSCrs", adVarChar, adParamInput, 30, CrsCde)
ASKER
Here is the code of the referral page that calls the confirmation page.
I assume the
<form method="post" title="Form3" action="SSSWeb_ConfirmAcad Ref.asp">
line dumps all the variables to the page I'm having the issue with.
Is there a tool I can use to debug the page and verify the variables are being passed properly?
I assume the
<form method="post" title="Form3" action="SSSWeb_ConfirmAcad
line dumps all the variables to the page I'm having the issue with.
Is there a tool I can use to debug the page and verify the variables are being passed properly?
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/SSS.asp" -->
<%
Const adCmdStoredProc = 4
Const adChar = 129
Const adInteger = 3
Const adNumeric = 131
Const adParamInput = 1
Const adParamOutput = 2
Const adParamReturnValue = 4
Const adExecuteNoRecords = 128
dim contin, sql, sqlStud, sqlStudData, sqlDumpIns, conn, rsCrs, rsStudent, rsStudent2, rsDump
dim frmUsername, frmPswd
dim CrsCde, CrsDesc, Yr, Trm, FacID
dim StudID, StudLast, StudFirst, StudYr, StudTrm, StudCrs, CurCrs, CurStudent
'frmUsername = trim(request.form ("frmUsername"))
'PSPswd = trim(request.form ("frmPswd"))
'frmUsername = trim(request.form ("frmUsername"))
'frmPswd = trim(request.form ("frmPswd"))
frmUsername = session("frmUsername")
frmPswd = session("frmPswd")
if Request.Form("CrsCde") = "" Then
CurCrs = "Select a course"
Else
CurCrs = Request.Form("CrsCde")
End if
if Request.Form("CrsStud") = "" Then
CurStudent = "Select a student"
Else
CurStudent = Request.Form("CrsStud")
End if
%>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Academic Support Referral</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
</head>
<body link="red" alink="red" vlink="red">
<table width="100%" border="0" cellspacing="0" cellpadding="0" id="Table2">
<tr>
<td align="left" background="WebImages/background.jpg"><img src="WebImages/topleft.jpg" width="600" height="65" align="left" hspace="0" vspace="0" border="0"/></td>
<td background="WebImages/background.jpg"></td>
<td align="right" background="WebImages/background.jpg"><img src="WebImages/topright.jpg" width="317" height="65" align="right" hspace="0" vspace="0" border="0"/></td>
</tr>
</table><br />
Notice to Instructors: Please speak to the student before completing this form.
Check the appropriate boxes below to indicate the specific behaviors which you
believe contribute to the student's poor performance in your course. Provide
supportive, detailed descriptions of your observations in the area labeled
"Comments". Then click SUBMIT to forward the referral to the SSS office. <br/><br/>
<b>Please note: The YELLOW fields are required for submission.</b><br/>
<% ''''''''''''''''''''''''''''''''Form 1--Course Code input''''''''''''''''''''''''''''''''
'response.Write(frmUsername)
Session.timeout = 5
If IsObject(Session("Jenzabar_ex_conn")) Then
Set conn = Session("Jenzabar_ex_conn")
Else
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;User ID=xxx;Password=xxxxx;Initial Catalog=xxxx;Data Source=xxxxx"
Set Session("Jenzabar_ex_conn") = conn
End If
'filter for course
sql = "select * from dbo.vFacultyCurCourses where instrctr_id_num = '" & frmUsername & "'"
Set rsCrs = Server.CreateObject("ADODB.Recordset")
Set Session("rs_CrsFilter") = rsCrs
rsCrs.Open sql, conn, 3, 3
rsCrs.MoveFirst
%>
<form method="post" name="form1" action="AcadReferral.asp">
Course: <select name="CrsCde" id="CrsCde" style="background-color:Yellow" onchange="form1.submit()">
<% response.Write "<option value = '" & CurCrs & "'>" & CurCrs & vbCRLf
do while not rsCrs.EOF
FacID = rsCrs("instrctr_id_num")
CrsCde = rsCrs("crs_cde")
CrsDesc = rsCrs("course_name")
Yr = rsCrs("yr_cde")
Trm = rsCrs("trm_cde")
response.Write "<option value = '" & CrsCde & "'>" & CrsCde & vbCRLf
rsCrs.MoveNext
loop
%> </select>
<!--<input type="submit" visible="false"/>-->
<input name="frmUsername" type="hidden" accesskey="h" value="<%response.write (frmUsername)%>"/>
<input name="frmPassword" type="hidden" accesskey="h" value="<%response.write (frmPassword)%>"/>
</form>
<% 'rsCrs.Close
'set rsCrs=nothing
''''''''''''''''''''''''''''''''Form 2--Course Student input''''''''''''''''''''''''''''''''
'filter for student
if Request.Form("CrsCde") = "" Then
Else
dim frmCrsCde
frmCrsCde = request.Form("CrsCde")
Session.timeout = 5
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;User ID=xxx;Password=xxxxx;Initial Catalog=xxxx;Data Source=xxxxx"
Set Session("Jenzabar_ex_conn") = conn
sqlStud = "select distinct stud_id, stud_last, stud_first from dbo.vStudentFilterByCourse where crs_cde = '" & frmCrsCde & "' order by stud_last, stud_first"
Set rsStudent = Server.CreateObject("ADODB.Recordset")
Set Session("rs_StudentFilter") = rsStudent
rsStudent.Open sqlStud, conn, 3, 3
rsStudent.MoveFirst
%>
<form method="post" name="form2" action="AcadReferral.asp">
Student ID: <select name ="CrsStud" id="CrsStud" style="background-color:Yellow" onchange="form2.submit()">
<% response.Write "<option value = '" & CurStudent & "'>" & CurStudent & vbCRLf
do while not rsStudent.EOF
StudID = rsStudent("stud_id")
StudLast = rsStudent("stud_last")
StudFirst = rsStudent("stud_first")
response.Write "<option value = '" & StudID & "'>" & StudLast & " " & StudFirst & " " & StudID & vbCRLf
rsStudent.MoveNext
loop%>
</select>
<input name="CrsCde" type="hidden" visible="true" accesskey="h" value="<% response.write (frmCrsCde) %>" />
<input name="frmUsername" type="hidden" accesskey="h" value="<%response.write (frmUsername)%>"/>
<input name="frmPassword" type="hidden" accesskey="h" value="<%response.write (frmPassword)%>"/>
<!--<input type="submit" visible="false"/>-->
</form>
<%
'rsStudent.Close
'set rsStudent=nothing
End if
%>
<% ''''''''''''''''''''''''''''''''Form 3--Acad Referral data input''''''''''''''''''''''''''''''''
If Request.Form("CrsStud") = "" then
Else
dim frm3CrsCde, frm3CrsStud, frm3Username
frm3CrsCde = request.Form("CrsCde")
frm3CrsStud = trim(request.Form("CrsStud"))
frm3Username = trim(request.Form("frmUsername"))
Session.timeout = 5
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;User ID=xxx;Password=xxxxx;Initial Catalog=xxxx;Data Source=xxxxx"
Set Session("Jenzabar_ex_conn") = conn
sqlStudData = "select * from dbo.vStudentFilterByCourse where crs_cde = '" & frm3CrsCde & "' and stud_id ='" & frm3CrsStud & "'"
Set rsStudent2 = Server.CreateObject("ADODB.Recordset")
Set Session("rs_StudentFilter2") = rsStudent2
rsStudent2.Open sqlStudData, conn, 3, 3
rsStudent2.MoveFirst
'do while not rsStudent2.EOF
StudID = rsStudent2("stud_id")
StudLast = rsStudent2("stud_last")
StudFirst = rsStudent2("stud_first")
StudYr = rsStudent2("yr_cde")
StudTrm = rsStudent2("trm_cde")
StudCrs = rsStudent2("crs_cde")
'rsStudent2.MoveNext
'loop
'response.Write frmCrsCde & "," & frmCrsStud & "," & frmUsername
%>
<!--<table><tr>
<td><% response.Write StudID %> </td>
<td><% response.Write StudLast %> </td>
<td><% response.Write StudFirst %></td>
<td><% response.Write StudCrs %></td>
<td><% response.Write StudTrm %></td>
<td><% response.Write StudYr %></td>
</tr></table>-->
<%
'rsStudent2.Close
'rsStudent2=nothing
'conn.Close
'conn=nothing
%>
<form method="post" title="Form3" action="SSSWeb_ConfirmAcadRef.asp">
<table>
<tr><td>Student Name: <b><input name="StudFirst" type="text" value="<% response.write (StudFirst) %>" /></b></td>
<td><b><input name="StudLast" type="text" value="<% response.write (StudLast) %>" /></b></td></tr>
</table>
<input name="CrsStud" type="hidden" value="<% response.write (frm3CrsStud) %>" />
<input name="CrsCde" type="hidden" value="<% response.write (frm3CrsCde) %>" />
<input name="StudYr" type="hidden" value="<%response.write (StudYr)%>"/>
<input name="StudTrm" type="hidden" value="<%response.write (StudTrm)%>"/>
<input name="frmUsername" type="hidden" value="<% response.write (frm3Username) %>" />
<input name="frmPassword" type="hidden" value="<%response.write (frmPassword)%>"/>
<br />
Current Grade: <select name ="CurGrade" id="CurGrade" style="background-color:Yellow">
<option>Select a grade</option>
<option value="No grade">No grade</option>
<option value="A">A</option>
<option value="B">B</option>
<option value="B+">B+</option>
<option value="C">C</option>
<option value="C+">C+</option>
<option value="D">D</option>
<option value="D+">D+</option>
<option value="F">F</option>
<option value="I">I</option>
<option value="P">P</option>
<option value="W">W</option>
<option value="WF">WF</option>
</select>
<br /><br /> Attendance/Class Participation<br />
<input name="Checkbox1" type="checkbox" value="Y"/>Chronic tardiness<br />
<input name="Checkbox2" type="checkbox" value="Y"/>Leaves class early<br />
<input name="Checkbox3" type="checkbox" value="Y"/>Excessive absenteeism<br />
<input name="Checkbox4" type="checkbox" value="Y"/>Poor attitude<br />
<input name="Checkbox5" type="checkbox" value="Y"/>Poor class participation<br />
<input name="Checkbox6" type="checkbox" value="Y"/>Sleeps in class<br />
<input name="Checkbox7" type="checkbox" value="Y"/>Does not have books/tools/materials<br />
<input name="Checkbox8" type="checkbox" value="Y"/>Appears immature/uncommitted<br />
<br /><br /> Academic Reasons for Referral<br />
<input name="Checkbox15" type="checkbox" value="Y"/>Does not take notes<br />
<input name="Checkbox16" type="checkbox" value="Y"/>Lacks prerequisite skills<br />
<input name="Checkbox17" type="checkbox" value="Y"/>Inefficient use of class/lab time<br />
<input name="Checkbox18" type="checkbox" value="Y"/>Not utilizing LD accommodations<br />
<input name="Checkbox19" type="checkbox" value="Y"/>Poor/missing/incomplete homework<br />
<input name="Checkbox20" type="checkbox" value="Y"/>Low/failing test scores<br />
<input name="Checkbox21" type="checkbox" value="Y"/>Unable to keep with pace of course<br />
<input name="Checkbox22" type="checkbox" value="Y"/>Career/major choice questionable<br />
<br /><br /> Non Academic Reasons for Referral<br />
<input name="Checkbox9" type="checkbox" value="Y"/>Reports personal problems<br />
<input name="Checkbox10" type="checkbox" value="Y"/>Reports illness<br />
<input name="Checkbox11" type="checkbox" value="Y"/>Overt changes in demeanor (i.e., anxiety, aggression, depression)<br />
<input name="Checkbox12" type="checkbox" value="Y"/>Reports family conflict<br />
<input name="Checkbox13" type="checkbox" value="Y"/>Reports working many hours<br />
<input name="Checkbox14" type="checkbox" value="Y"/>Does not associate with others<br /><br /><br /><br />
Comments: <textarea name="comment" cols="20" rows="5" id="comment"></textarea><br />
<input name="Submit1" type="submit" value="Submit"/>
</form>
<%
End if
%>
</body>
</html>
Hi RankenIS,
Can you show the exact line of hitting error above? The way to debug the variable is using Response.write(YOURvariabl e) to verify the correctness of the passed value. I know this is getting headache sometime...
Can you show the exact line of hitting error above? The way to debug the variable is using Response.write(YOURvariabl
ASKER
The error is produced on line 79 of the asp page, when the user hits the submit button & executes the sql.
Again, the whole process worked until i switched from sql 2000's xp_sendmail to sql 2005's sp_send_dbmail.
Again, the whole process worked until i switched from sql 2000's xp_sendmail to sql 2005's sp_send_dbmail.
Hi RankenIS,
Ok, i've checked again the comments that posted at ID:24019079 line 130 - EXEC msdb..sp_send_dbmail. I've noticed there are 2 dot after the db owner(msdb) declaration for sp_send_dbmail? Lastly, please use a very simple test to verify if you able to send the mail via built-in function - sp_send_dbmail? Could be some settings issue here.
Ok, i've checked again the comments that posted at ID:24019079 line 130 - EXEC msdb..sp_send_dbmail. I've noticed there are 2 dot after the db owner(msdb) declaration for sp_send_dbmail? Lastly, please use a very simple test to verify if you able to send the mail via built-in function - sp_send_dbmail? Could be some settings issue here.
ASKER
sp_send_dbmail is functioning fine globally. The format used for the send_dbmail section is a mirror copy across all my procedures that need to send out mail. Everywhere else, the mail section is working which is pointing me to the asp side.
Also, the double dot is common short hand since dbo is implied:
EXEC msdb..sp_send_dbmail
is the same as
EXEC msdb.dbo.sp_send_dbmail
Also, the double dot is common short hand since dbo is implied:
EXEC msdb..sp_send_dbmail
is the same as
EXEC msdb.dbo.sp_send_dbmail
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Open in new window