Solved

Error formatting query, probably invalid parameters

Posted on 2009-03-30
12
1,178 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
  • 7
  • 4
12 Comments
 

Author Comment

by:RankenIS
Comment Utility
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
Comment Utility
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
Comment Utility
You have an error on line 132 of the stored procedure, you are missing an ' after the email address.
Cheers, Andrew
0
 

Author Comment

by:RankenIS
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 29

Expert Comment

by:David H.H.Lee
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 100 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now