| Author |
 Topic  |
|
sogoth
New Member
United Kingdom
73 Posts |
 Posted - 20 July 2001 : 12:09:55
|
Heya
Now normally I wouldn't have a problem passing a variable from a form to another page , but I've just hit a problem with my current project.
The variable that I need to pass is a project code in the form CDP 4XXX or some such similar two part thing. The way I would normally pass the information is having great difficulties (code included below) as it reads the CDP part and assumes that is it and gets confused when it finds the 4XXX part afterwards. Anyone got the solution please?!
Thanks
Paul
<------ code from the page collecting the info and displaying the results ------->
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/cro.asp" -->
<%
Dim Recordset1__var
Recordset1__var = "0"
if (Request.Form("Proj_1") <> "") then Recordset1__var = Request.Form("Proj_1")
%>
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_cro_STRING
Recordset1.Source = "SELECT * FROM dbo.project WHERE dbo.project.Proj_no like " + Recordset1__var + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p><%=(Recordset1.Fields.Item("Proj_no").Value)%> </p>
<p><%=(Recordset1.Fields.Item("ph_ref").Value)%></p>
</body>
</html>
<%
Recordset1.Close()
%>
|
|
|
Spoon
Average Member
Ireland
507 Posts |
Posted - 20 July 2001 : 14:31:35
|
quote:
Heya
Now normally I wouldn't have a problem passing a variable from a form to another page , but I've just hit a problem with my current project.
The variable that I need to pass is a project code in the form CDP 4XXX or some such similar two part thing. The way I would normally pass the information is having great difficulties (code included below) as it reads the CDP part and assumes that is it and gets confused when it finds the 4XXX part afterwards. Anyone got the solution please?!
Thanks
Paul
<------ code from the page collecting the info and displaying the results ------->
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/cro.asp" -->
<%
Dim Recordset1__var
Recordset1__var = "0"
if (Request.Form("Proj_1") <> "") then Recordset1__var = Request.Form("Proj_1")
%>
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_cro_STRING
Recordset1.Source = "SELECT * FROM dbo.project WHERE dbo.project.Proj_no like " + Recordset1__var + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p><%=(Recordset1.Fields.Item("Proj_no").Value)%> </p>
<p><%=(Recordset1.Fields.Item("ph_ref").Value)%></p>
</body>
</html>
<%
Recordset1.Close()
%>
I dont think variables can contain spaces, try it as CPD_4XXX , maybe ive got the wrong end of the stick, most likely :)
Regards,
Spoon, (ya all love me right?) |
 |
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 20 July 2001 : 14:55:20
|
ah right, thanks :)
eek! any idea why I'm now getting this please?
---
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ','.
/cro/pds_proj.asp, line 15
---
Paul
Complete Muppet, borderline Feeble!
|
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 20 July 2001 : 15:18:16
|
You're using some Javascript syntax in your VBScript code. VBS uses the & character to concatenate strings.
======
Doug G
====== |
|
|
|
Kat
Advanced Member
United Kingdom
3065 Posts |
Posted - 21 July 2001 : 04:26:32
|
quote:
quote:
Heya
Now normally I wouldn't have a problem passing a variable from a form to another page , but I've just hit a problem with my current project.
The variable that I need to pass is a project code in the form CDP 4XXX or some such similar two part thing. The way I would normally pass the information is having great difficulties (code included below) as it reads the CDP part and assumes that is it and gets confused when it finds the 4XXX part afterwards. Anyone got the solution please?!
Thanks
Paul
<------ code from the page collecting the info and displaying the results ------->
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/cro.asp" -->
<%
Dim Recordset1__var
Recordset1__var = "0"
if (Request.Form("Proj_1") <> "") then Recordset1__var = Request.Form("Proj_1")
%>
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_cro_STRING
Recordset1.Source = "SELECT * FROM dbo.project WHERE dbo.project.Proj_no like " + Recordset1__var + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p><%=(Recordset1.Fields.Item("Proj_no").Value)%> </p>
<p><%=(Recordset1.Fields.Item("ph_ref").Value)%></p>
</body>
</html>
<%
Recordset1.Close()
%>
I dont think variables can contain spaces, try it as CPD_4XXX , maybe ive got the wrong end of the stick, most likely :)
Regards,
Spoon, (ya all love me right?)
You can have spaces in values passed to the database if you encode the value:
if (Request.Form("Proj_1") <> "") then Recordset1__var = Server.HTMLEncode(Request.Form("Proj_1"))
then:
Recordset1.Source = "SELECT * FROM dbo.project WHERE dbo.project.Proj_no like " & Recordset1__var & ""
I have only used this with SQL Server but I believe it should work for other database types too. It also takes account of any funny values in the form field, like singlequotes, less than, greater than signs etc and stops the need to do manual replaces of these characters. (Funny characters can cause problems when you try to insert into SQL Server.)
Also, Remember to change your 'plus' signs to '&' like Doug said.
KatsKorner
|
|
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 23 July 2001 : 04:50:29
|
Thanks for the suggestions. I've made the changes you both kindly suggested as well as bringing in the include file to the code instead, yet I'm still recieving the same error message despite the fact that I don't have any commas in the code now!.
I'm getting very confuzzled and bemused :
-----
Current code
-----
<%@LANGUAGE="VBSCRIPT"%>
<%
Dim Recordset1__var
Recordset1__var = "0"
if (Request.Form("Proj_1") <> "") then Recordset1__var = Server.HTMLEncode(Request.Form("Proj_1"))
%>
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "dsn=cro_sql;uid=cro_login;pwd=project;"
Recordset1.Source = "SELECT * FROM dbo.project WHERE dbo.project.Proj_no like " & Recordset1__var & ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
%>
<html>
<head>
<title>Test variable pass</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p><%=(Recordset1.Fields.Item("Proj_no").Value)%> </p>
<p><%=(Recordset1.Fields.Item("ph_ref").Value)%></p>
</body>
</html>
<%
Recordset1.Close()
%>
Thanks
Paul
Complete Muppet, Borderline Feeble! |
|
|
|
Kat
Advanced Member
United Kingdom
3065 Posts |
Posted - 23 July 2001 : 05:22:03
|
Can you post the line that the error is on? And an actual print of the the sql statement you are using?
KatsKorner
|
|
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 23 July 2001 : 05:38:48
|
The error is
--
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ','.
/cro/pds_proj.asp, line 15
---
which would be :-
Recordset1.Open()
and the data is being pulled from a form with a drop down box populated from the database.and goes thusly...
---
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/cro.asp" -->
<%
set project = Server.CreateObject("ADODB.Recordset")
project.ActiveConnection = MM_cro_STRING
project.Source = "SELECT * FROM dbo.project ORDER BY Proj_no"
project.CursorType = 0
project.CursorLocation = 2
project.LockType = 3
project.Open()
project_numRows = 0
%>
<html>
<head>
<title>Search</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="STYLESHEET" type="text/css" href="http://gww.chiroscience.com/css/group.css">
<script LANGUAGE="JavaScript" SRC="/javascript/group.js"></script>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div align="center">
<h2>CSO Database<br>
CSO Performance data search </h2>
</div>
<blockquote>
<div align="left">
<blockquote>
<p><b>View by :</b></p>
<p><a href="pds_all.asp">All Entries</a></p>
<form name="Proj_1" method="post" action="pds_proj.asp">
<b>***Project :</b>
<select name="Proj_1">
<%While (NOT project.EOF)%>
<option value="<%=(project.Fields.Item("Proj_no").Value)%>" ><%=(project.Fields.Item("Proj_no").Value)%></option>
<% project.MoveNext()
Wend
If (project.CursorType > 0) Then
project.MoveFirst
Else
project.Requery
End If
%>
</select>
<input type="submit" name="Proj_1" value="Submit">
</form>
</blockquote>
</div>
</blockquote>
</body>
</html>
<%project.Close()%>
---
Thanks
Paul
Complete Muppet, Borderline Feeble! |
|
|
|
Kat
Advanced Member
United Kingdom
3065 Posts |
Posted - 23 July 2001 : 06:08:37
|
Sorry Paul but could you post the actual sql that is being used to open that recordset.
I think it is stored in Recordset1.Source and you will need to do a response.write on this to be able to see what is in the actual sql.
Recordset1.Source = "SELECT * FROM dbo.project WHERE dbo.project.Proj_no like " + Recordset1__var + ""
<%Response.Write Recordset1.Source%>
Recordset1.CursorType = 0
Without this printout, it is really difficult to identify a syntax error.
KatsKorner
|
|
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 23 July 2001 : 06:12:15
|
Ahhh sorry. I get you now (still learning you see!)
here it is
SELECT * FROM dbo.project WHERE dbo.project.Proj_no like CDO492, Submit
ta
Paul
Complete Muppet, Borderline Feeble! |
|
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 23 July 2001 : 06:17:04
|
Where's the ", Submit" bit coming from? I've just tried tht on another page that works and it's not got that on the end, but the code looks the same as the code from my first post in this thread!?
confused
Paul
Complete Muppet, Borderline Feeble! |
|
|
|
Spoon
Average Member
Ireland
507 Posts |
Posted - 23 July 2001 : 07:22:06
|
quote:
Where's the ", Submit" bit coming from? I've just tried tht on another page that works and it's not got that on the end, but the code looks the same as the code from my first post in this thread!?
confused
Paul
Complete Muppet, Borderline Feeble!
I dont think the extra ")" should be at the end like you have above
Server.HTMLEncode(Request.Form("Proj_1"))
If your not using dashes in your "Proj_1" dont use Server.HtmlEncode.
VARIABLES cant contain spaces, however STRINGS can
eg. THIS VARIABLE = " SPaced out " - will not work,
THIS_VARIABLE = " spaced out " - will work
Try this to open your recordset
Set RECORDSET1 = Myconn.Execute(SQL)
where myconn is your connection and sql is your statement
Regards,
Spoon, (ya all love me right?) |
|
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 23 July 2001 : 08:21:02
|
Hi
the bracket needs to be there to complete the pairing opened earlier in the line.
As for the connection string I'm afraid that made no difference what so ever. It still claims that there's a comma in there somewhere that's causing a problem :(
Complete Muppet, Borderline Feeble! |
|
|
|
Kat
Advanced Member
United Kingdom
3065 Posts |
Posted - 23 July 2001 : 09:10:59
|
That ,submit is part of the variable returned from the request.form value at a guess.
Try putting this on the page and see what it returns:
<%Response.Write(Request.Form("Proj_1"))%>
If you see "CDO492, Submit" as the result then that is the value the form field contains. Then check the way that form field is defined to see if you have accidently missed or included something you shouldn't have.
Your SQL is as follows:
"SELECT * FROM dbo.project WHERE dbo.project.Proj_no like " & Recordset1__var & ""
What type of data is Proj_no in the db?
btw - Spoon's not about Server.HTMLEncode is not strictly correct about not using it unless you have dashes in the field. You can use it - all it does is replace funny characters before it inserts into the database so that you don't get any syntax errors with the SQL, which is why I suggested using it in this case.
KatsKorner
|
|
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 23 July 2001 : 09:21:32
|
I tracked down that rogue ", Submit"... I'd accidentally named the submit button the same as the variable *DOH!*
*hangs head in shame*
Thanks for all the help tracking it down!
Paul -
Complete Muppet, Borderline Feeble! |
|
|
|
Kat
Advanced Member
United Kingdom
3065 Posts |
Posted - 23 July 2001 : 09:51:04
|
Well done Paul! Glad you sorted it. Easy to do!!! 
KatsKorner
|
|
|
|
Topic |
|
Topic Locked
