[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Error formatting query, probably invalid parameters

Posted on 2009-03-30
12
Medium Priority
?
1,235 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:RankenIS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 

Author Comment

by:RankenIS
ID: 24019079
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

0
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 24026470
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
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24027665
You have an error on line 132 of the stored procedure, you are missing an ' after the email address.
Cheers, Andrew
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:RankenIS
ID: 24028334
That was my mistake in anonymizing the stored proc just for this site.  The working stored procedure has the closing tick.
0
 

Author Comment

by:RankenIS
ID: 24049824
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

0
 

Author Comment

by:RankenIS
ID: 24049893
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

0
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 24057362
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...


0
 

Author Comment

by:RankenIS
ID: 24080242
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.  
 


0
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 24083812
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.



0
 

Author Comment

by:RankenIS
ID: 24086747
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
0
 
LVL 29

Assisted Solution

by:David H.H.Lee
David H.H.Lee earned 400 total points
ID: 24094349
Hi RankenIS,
Shouldn't "@PSUsername" returned as varchar() instead of integer type?
eg:
cmdStoredProc.parameters.append cmdStoredProc.createparameter ("@PSUsername", adInteger, adParamInput, , frmUsername)

Change to
cmdStoredProc.parameters.append cmdStoredProc.createparameter ("@PSUsername", adVarChar, adParamInput, 30, StudFirst)

Lastly, i didn't see the declaration of "@PSUsername" variable inside stored procedure - "usp_ReferralActionSSS" ?
The declared items like @PSFirst, @PSLast or @PSCrs not being used inside the stored procedure - "usp_ReferralActionSSS" seem these mentioned variables is using returned result inside?

eg:
...
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)
 

0
 

Accepted Solution

by:
RankenIS earned 0 total points
ID: 24149263
changed over to varchar as suggested.  still same error:
   
Microsoft OLE DB Provider for SQL Server error '80040e14'
Error formatting query, probably invalid parameters
/RTCCustom/sssweb/SSSWeb_ConfirmAcadRef.asp, line 79  
Also commented out the questioned variables mentioned in last post:
  'cmdStoredProc.parameters.append cmdStoredProc.createparameter ("@PSUsername", adInteger, adParamInput, , frmUsername)
  cmdStoredProc.parameters.append cmdStoredProc.createparameter ("@PSUsername", adVarChar, 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)
 
Still same issue.  can anyone see which variable is trying to insert a null?  I'm not sure how i can debug the asp and check the variables using MS Expression Web.
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question