Link to home
Start Free TrialLog in
Avatar of RankenIS
RankenISFlag for United States of America

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.
<%@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>

Open in new window

Avatar of RankenIS
RankenIS
Flag of United States of America image

ASKER

Here is the stored procedure the asp page is calling.
USE [SSS]
GO
/****** Object:  StoredProcedure [dbo].[usp_ReferralActionSSS]    Script Date: 03/30/2009 09:03:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_ReferralActionSSS]
AS
 
SET CONCAT_NULL_YIELDS_NULL ON
select * from tblAcadReferrals where SSSEmailFaculty is not null
if @@ROWCOUNT > 0
  begin
 
DECLARE @counter int, @reccount int, @ID int
select @counter = 0
select @reccount = count(*) from tblAcadReferrals where SSSEmailFaculty is not null
DECLARE cur_refid cursor for select refid from tblAcadReferrals where SSSEmailFaculty is not null
OPEN cur_refid
WHILE @counter < @reccount
  BEGIN
 
		FETCH cur_refid into @ID
		--SET @ID = convert(varchar,@ID)
  		DECLARE @PSFirst varchar(30), @PSLast varchar(30), @PSCrs varchar(30)
		DECLARE @FacEmail varchar(50), @Email varchar(50), @CleanID varchar(6), @sql varchar(7000)
		DECLARE @SubjectTextAppend varchar(90) 
 
		SET @PSFirst = (select n.first_name from tblAcadReferrals r, vNameMaster n where r.id_num = n.id_num and r.RefID = @ID)
		SET @PSLast = (select n.Last_name from tblAcadReferrals r, vNameMaster n where r.id_num = n.id_num and r.RefID = @ID)
		SET @PSCrs = (select crs_cde from tblAcadReferrals where RefID = @ID)
 
		SET @CleanID = 	(select instrctr_id_num from tblAcadReferrals where RefID = @ID)
		SET @FacEmail = (select emailaddr from vRTCWebFacultyEmails where id_num = @CleanID)
		SET @Email = rtrim(@FacEmail) 
 
		SET @SubjectTextAppend = 'SSS Academic Referral Action:  ' + rtrim(@PSFirst) + ' ' + rtrim(@PSLast) + '    ' + rtrim(@PSCrs)
 
		SET @sql = ('SET NOCOUNT ON
			select ''---------------------''
			select ''SSS Action:  '',rtrim(ActionComment)
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			select ''---------------------''	
			select ''''
			select ''''
			select ''''
 
			select SubmitDate from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
 
			select ''Student:  '',r.id_num as StudentID, n1.first_name as StudentFirst, n1.last_name as StudentLast
			from SSS.dbo.tblAcadReferrals r, SSS.dbo.vNameMaster as n1  
			where r.id_num = n1.id_num and r.RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select ''Instructor:  '',r.instrctr_id_num as InstrID, n2.first_name as InstrFirst, n2.last_name as InstrLast
			from SSS.dbo.tblAcadReferrals r, SSS.dbo.vNameMaster as n2  
			where r.instrctr_id_num = n2.ID_NUM and r.RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select ''Course:  '',rtrim(crs_cde), ''Semester:  '',rtrim(trm_cde), yr_cde, ''Grade:  '',cur_grade
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
	
			select Q1, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q1'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q2, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q2'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q3, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q3'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q4, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q4'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q5, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q5'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q6, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q6'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q7, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q7'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q8, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q8'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
 
			select Q15, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q15'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q16, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q16'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q17, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q17'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q18, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q18'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q19, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q19'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
 
			select Q20, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q20'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
 
			select Q21, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q21'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
 
			select Q22, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q22'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
		
			select Q9, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q9'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q10, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q10'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q11, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q11'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
		
			select Q12, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q12'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select Q13, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q13'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
		
			select Q14, (select q1.QuestionText from SSS.dbo.tblQuestionDef q1 where q1.QuestionID = ''Q14'')
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''
			
			select ''Instructor Comment:  '',rtrim(InstrComment)
			from SSS.dbo.tblAcadReferrals where RefID = ' + '''' + convert(varchar,@ID) + '''')
EXEC msdb..sp_send_dbmail	
	@recipients = @Email,
	@copy_recipients = 'abc@abc.com,
--	@blind_copy_recipients = 'abc@abc.com',
	@subject = @SubjectTextAppend,
	@query = @sql,
	--@exclude_query_output=1
    @query_result_header=0
    SELECT @counter = @counter + 1
  END
 
update tblAcadReferrals
set SSSEmailFaculty = NULL, SSSEmailDate = getdate()
where SSSEmailFaculty is not null
 
CLOSE cur_refid
DEALLOCATE cur_refid    	
  end
else
	return
return

Open in new window

Avatar of David H.H.Lee
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
You have an error on line 132 of the stored procedure, you are missing an ' after the email address.
Cheers, Andrew
That was my mistake in anonymizing the stored proc just for this site.  The working stored procedure has the closing tick.
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)
		

Open in new window

Here is the code of the referral page that calls the confirmation page.  
I assume the
<form method="post" title="Form3" action="SSSWeb_ConfirmAcadRef.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?

<%@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> 

Open in new window

Hi RankenIS,
Can you show the exact line of hitting error above? The way to debug the variable is using Response.write(YOURvariable) to verify the correctness of the passed value. I know this is getting headache sometime...


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.  
 


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.



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
SOLUTION
Avatar of David H.H.Lee
David H.H.Lee
Flag of Malaysia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial