p2p.wrox.com: Archives - asp

asp_databases thread: Passing Query variables via a URL

Message #1 by "Bill Sisemore" <bsisemore@m...> on Sat, 23 Sep 2000 15:18:43 -0400

Hello again Ladies and Gentlemen,



I receive an error when trying to pass one or two variables to query an 

Access 2000 database.  What I am doing is this:



I have a page that when called by a hyperlink, displays a table full of 

data of open and critical issues.  This page works perfectly.  Within the 

table of results that are automatically generated I have inserted what I 

believe to be the appropriate code to pass a URL querystring to the next 

page.  The code for this page is as follows:



<%



Dim strpriority, strstat



strpriority = strpriority & "critical"

strstat = strstat & "Open"



Set objGconn = Server.CreateObject("ADODB.Connection")

strConn = "globaladmin"

objGconn.Open strConn



Set objGrs = Server.CreateObject("ADODB.Recordset")



sSQL2 = "SELECT * FROM helpdesk WHERE status='" & strstat & "' AND 

priority='" & strpriority & "' ORDER BY index ASC"



Set objGrs = objGconn.Execute(sSQL2)



If objGrs.EOF Then

	Response.Write _

	"<FONT Size=3 COLOR=BLUE>" & _

	"There are no Critical Open Issues at this time." & _

	"</FONT>"

	Else

	Response.Write _

	"<TABLE BORDER=""1"" WIDTH=85% CELLSPACING=""1"">" & _

	"<TR>" & _

	"<ALIGN=LEFT><FONT Size=1 COLOR=BLUE> Ticket </FONT><FONT Size=1 

COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE> Priority 



</FONT><FONT Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE> 

Status </FONT><FONT Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1 



COLOR=BLUE> eMail Address </FONT><FONT Size=1 

COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE> Issue </FONT></LEFT>" 

& _

	"</TR>"

Do While Not objGrs.EOF

Set strindex = objGrs("index")

Set stremail = objGrs("email")

Set strissue = objGrs("issue")

Set strupsta = objGrs("status")

Response.Write _

	"<TR>" & _

	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & "<a href=updateby.asp?

ticket=" & (strindex) & ">" & objGrs("index") & "</a>" & "</FONT>" & 



"</TD>" & _

	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("priority") 

& "</FONT>" & "</TD>" & _

	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & "<a href=updateby.asp?

status=" & (strupsta) & "?ticket=" & (strindex) & ">" & 



objGrs("status") & "</a>" & "</FONT>" & "</TD>" & _

	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & "<a href=mailto:" & 

(stremail) & "?Subject=" & (strissue) & ">" & objGrs("email") & "</a>" 



& "</FONT>" & "</TD>" & _

	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("issue") 

& "</FONT>" & "</TD>" & _

	"</TR>"

objGrs.MoveNext

Loop

objGrs.Close

Response.Write "</TABLE>"

Set objGrs = Nothing

End If



objGconn.Close

Set objGconn = Nothing



%>



The above code has a link within it to the update.asp page which contains 

the following code:



<%



strindex = Request.QueryString("ticket")

strupsta = Request.QueryString("status")



Set objGconn = Server.CreateObject("ADODB.Connection")

objGconn.Open "globaladmin"

Set objGrs = Server.CreateObject("ADODB.Recordset")

objGrs.Open "helpdesk", objGconn, , , adCmdUnknown



If strupsta = "" Then

sSQL = "SELECT * FROM helpdesk WHERE index='" & strindex & "'"

Set objGrs = objGconn.Execute(sSQL)

Response.Write(sSQL)

Else

sSQL2 = "SELECT * FROM helpdesk WHERE index='" & strindex & "' AND 

status='" & strupsat & "'"

Set objGrs = objGconn.Execute(sSQL2)

Response.Write(sSQL2)

End If



Response.Write _

	"<FONT Size=3 COLOR=RED>" & _

	"Please select the new status of this Support Order from the 

available options:" & "<BR>" & _

	"</FONT>" & _

	"<TR>" & _

	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("index") 

& "</FONT>" & "</TD>" & _

	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("priority") 

& "</FONT>" & "</TD>" & _

	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("status") 

& "</FONT>" & "</TD>" & _

	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("email") 

& "</FONT>" & "</TD>" & _

	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("issue") 

& "</FONT>" & "</TD>" & _

	"</TR>"



objGrs.Close

Set objGrs = Nothing

objGconn.Close

Set objGconn = Nothing



%>



What happens is that the variables "strindex" and "strupsta" are being 

passed as their own respective names and not as the data for which it 

represents.  For example, if there is a Ticket Number 143 and it's Status 

is Critical, the data for "strindex" and "strupsta" is being referred to 

as "ticket" and "status" in the following page though it is represented 

in the subsequent Url as "143" for the strindex variable and "Critical"  

for the strupsta" variable.  Furthermore, I receive the following error 

message once the page has run:



**************************************************************

Microsoft OLE DB Provider for ODBC Drivers error '80040e07' 



[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in 



criteria expression. 



/helpdesk/admin/updateby.asp, line 45

**************************************************************





Please help!  And trust me when I say I have put forth significant effort 

to resolve this on my own, but I am new to programming in general, am 

inexperienced and have reached an impass.  Any help would be greatly 

appreciated.



Thank You,



Bill Sisemore

Message #2 by "David E" <registerukh@h...> on Sun, 24 Sep 2000 09:34:01 EDT

try using

<a href="updateby.asp?ticket='<%=server.URLEncode(strindex)%>'">









>From: "Bill Sisemore" <bsisemore@m...>

>Reply-To: "ASP Databases" <asp_databases@p...>

>To: "ASP Databases" <asp_databases@p...>

>Subject: [asp_databases] Passing Query variables via a URL

>Date: Sat, 23 Sep 2000 15:18:43 -0400

>

>Hello again Ladies and Gentlemen,

>

>I receive an error when trying to pass one or two variables to query an

>Access 2000 database.  What I am doing is this:

>

>I have a page that when called by a hyperlink, displays a table full of

>data of open and critical issues.  This page works perfectly.  Within the

>table of results that are automatically generated I have inserted what I

>believe to be the appropriate code to pass a URL querystring to the next

>page.  The code for this page is as follows:

>

><%

>

>Dim strpriority, strstat

>

>strpriority = strpriority & "critical"

>strstat = strstat & "Open"

>

>Set objGconn = Server.CreateObject("ADODB.Connection")

>strConn = "globaladmin"

>objGconn.Open strConn

>

>Set objGrs = Server.CreateObject("ADODB.Recordset")

>

>sSQL2 = "SELECT * FROM helpdesk WHERE status='" & strstat & "' AND

>priority='" & strpriority & "' ORDER BY index ASC"

>

>Set objGrs = objGconn.Execute(sSQL2)

>

>If objGrs.EOF Then

>	Response.Write _

>	"<FONT Size=3 COLOR=BLUE>" & _

>	"There are no Critical Open Issues at this time." & _

>	"</FONT>"

>	Else

>	Response.Write _

>	"<TABLE BORDER=""1"" WIDTH=85% CELLSPACING=""1"">" & _

>	"<TR>" & _

>	"<ALIGN=LEFT><FONT Size=1 COLOR=BLUE> Ticket </FONT><FONT Size=1

>COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE> Priority

>

></FONT><FONT Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE>

>Status </FONT><FONT Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1

>

>COLOR=BLUE> eMail Address </FONT><FONT Size=1

>COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE> Issue </FONT></LEFT>"

>& _

>	"</TR>"

>Do While Not objGrs.EOF

>Set strindex = objGrs("index")

>Set stremail = objGrs("email")

>Set strissue = objGrs("issue")

>Set strupsta = objGrs("status")

>Response.Write _

>	"<TR>" & _

>	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & "<a href=updateby.asp?

>ticket=" & (strindex) & ">" & objGrs("index") & "</a>" & "</FONT>" &

>

>"</TD>" & _

>	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("priority")

>& "</FONT>" & "</TD>" & _

>	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & "<a href=updateby.asp?

>status=" & (strupsta) & "?ticket=" & (strindex) & ">" &

>

>objGrs("status") & "</a>" & "</FONT>" & "</TD>" & _

>	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & "<a href=mailto:" &

>(stremail) & "?Subject=" & (strissue) & ">" & objGrs("email") & "</a>"

>

>& "</FONT>" & "</TD>" & _

>	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("issue")

>& "</FONT>" & "</TD>" & _

>	"</TR>"

>objGrs.MoveNext

>Loop

>objGrs.Close

>Response.Write "</TABLE>"

>Set objGrs = Nothing

>End If

>

>objGconn.Close

>Set objGconn = Nothing

>

>%>

>

>The above code has a link within it to the update.asp page which contains

>the following code:

>

><%

>

>strindex = Request.QueryString("ticket")

>strupsta = Request.QueryString("status")

>

>Set objGconn = Server.CreateObject("ADODB.Connection")

>objGconn.Open "globaladmin"

>Set objGrs = Server.CreateObject("ADODB.Recordset")

>objGrs.Open "helpdesk", objGconn, , , adCmdUnknown

>

>If strupsta = "" Then

>sSQL = "SELECT * FROM helpdesk WHERE index='" & strindex & "'"

>Set objGrs = objGconn.Execute(sSQL)

>Response.Write(sSQL)

>Else

>sSQL2 = "SELECT * FROM helpdesk WHERE index='" & strindex & "' AND

>status='" & strupsat & "'"

>Set objGrs = objGconn.Execute(sSQL2)

>Response.Write(sSQL2)

>End If

>

>Response.Write _

>	"<FONT Size=3 COLOR=RED>" & _

>	"Please select the new status of this Support Order from the

>available options:" & "<BR>" & _

>	"</FONT>" & _

>	"<TR>" & _

>	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("index")

>& "</FONT>" & "</TD>" & _

>	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("priority")

>& "</FONT>" & "</TD>" & _

>	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("status")

>& "</FONT>" & "</TD>" & _

>	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("email")

>& "</FONT>" & "</TD>" & _

>	" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("issue")

>& "</FONT>" & "</TD>" & _

>	"</TR>"

>

>objGrs.Close

>Set objGrs = Nothing

>objGconn.Close

>Set objGconn = Nothing

>

>%>

>

>What happens is that the variables "strindex" and "strupsta" are being

>passed as their own respective names and not as the data for which it

>represents.  For example, if there is a Ticket Number 143 and it's Status

>is Critical, the data for "strindex" and "strupsta" is being referred to

>as "ticket" and "status" in the following page though it is represented

>in the subsequent Url as "143" for the strindex variable and "Critical"

>for the strupsta" variable.  Furthermore, I receive the following error

>message once the page has run:

>

>**************************************************************

>Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

>

>[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in

>

>criteria expression.

>

>/helpdesk/admin/updateby.asp, line 45

>**************************************************************

>

>

>Please help!  And trust me when I say I have put forth significant effort

>to resolve this on my own, but I am new to programming in general, am

>inexperienced and have reached an impass.  Any help would be greatly

>appreciated.

>

>Thank You,

>

>Bill Sisemore

>

>

>

>

Return to Index

P2P Archives