SQL Query problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

SQL Query problems

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2003-10-06 : 14:11:50

Hi all,

I have the below asp page which populates the drop down box with the name of surgeons (this page works fine). When someone selects a particular surgeon and clicks on the submit button, I want to display all the surgeries performed by that particular surgeon. But I am unable to do this and getting the below error message:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: '[string: "Antoine"]'
/departments/OR/OR.asp, line 23

The database was created by an ex-employee and names of the tables and columns have spaces. Is it the reason for this error or something else in my SQL query. Is there any other way to work around if we have spaces in the column values.

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

<html>



<%Response.Buffer = True%>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Status of OR REX</title>
</head>

<body>
<%
Set rsName = Server.CreateObject("ADODB.Recordset")
Set MdConnection = Server.CreateObject("ADODB.Connection")
MdConnection.Open strConnect
rsName.Open "SELECT [Name of Surgeon] FROM [Surgeons list]", MdConnection
%>
<form method="POST" action="OR.asp" name="FrontPage_Form">
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="620" id="AutoNumber1">
<tr>
<td>
<p align="right"><b>Surgeon Name:</b></td>
<td>
<p align="left">
<select size="1" name="cbName">
<% While Not rsName.EOF %>
<option value=<%Response.Write rsName.Fields("Name of Surgeon")%>><%Response.Write rsName.Fields("Name of Surgeon")%></option>

<%
rsName.MoveNext
Wend
%>
</select></td>
</tr>
<tr>
<td colspan="2">
<p align="center"><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></td>
</tr>
</table>
</form>

</body>

</html>
*******************************************************************************

The below is the page which I am trying to Post all details when a particular surgeon is selected from the drop down list.

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



<%Response.Buffer = True%>

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>OR Queries</title>
</head>

<body>
<%
Dim Name
Set rsOR = Server.CreateObject("ADODB.Recordset")
Set MdConnection = Server.CreateObject("ADODB.Connection")
MdConnection.Open strConnect

IF Request.Form("cbName") = 0 Then
Response.Write "You must choose a Name"
Else
%><b> Here is the information you requested.</b><BR><%
%><BR><%
strSQL = "SELECT * FROM [OR Record] WHERE [Name of Surgeon] LIKE ‘%cbName%’ OR [Surgeon 2] LIKE ‘%cbname%’ "

**** I am not sure of the above query, I want to retrieve all the records from the “OR Record” table if the selected name in the drop down list is same as the name under two columns named “Name of Surgeon” and “Surgeon 2” in the OR Record table. **********

rsOR.Open strSQL, MdConnection

While Not rsOR.EOF
%><b>Surgeries Performed:</b> <%Response.Write rsOR.Fields("Procedure")%><BR><%

** here I just tried selecting one of the columns to start with **

rsOR.MoveNext
Wend
End If%><BR>
<p>
<applet code="fphover.class" codebase="../../" width="120" height="24">
<param name="color" value="#000080">
<param name="hovercolor" value="#0000FF">
<param name="textcolor" value="#FFFFFF">
<param name="text" value="Close">
<param name="effect" value="glow">
<param name="url" valuetype="ref" value="../../Default.htm">
</applet>
</p>
</body>

</html>
************************************************************************

I am new to both Sql and asp. Please help me to solve this problem.

Thanks in advance.

Vijay

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-06 : 14:17:45

Why don't you run the queries inside Query Analyzer to see what the problem is? Forget about ASP at this point. You need to figure out the queries before you code them in ASP. It is very hard for us to figure out the problem when you display all of your code. We are only interested in the actual query. Please point out that section that is having the problem and show us some sample data.

Also, do this inside stored procedures instead of what you are doing. You will get better performance and better security out of it.

Tara

dupati1
Posting Yak Master

123 Posts

Posted - 2003-10-06 : 15:11:35

Thanks,

Let me try to explain what actually i am puzzled with.

On the first page, the user select the name of the surgeon from the drop down list which gets populated from one the tables in the database.

How can I compare this selected name with one of the columns in other table of the database and retrieve all the details of the selected surgeon.

my question is with the below query, can retrieve the data from the table by comparing column vlaues with "cbName"( cbName is the name of the drop down box).

strSQL = "SELECT * FROM [OR Record] WHERE [Name of Surgeon] LIKE ‘%cbName%’ OR [Surgeon 2] LIKE ‘%cbname%’ "

Thanks.

VJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-06 : 15:16:09

Run your query inside Query Analyzer. Does it return the results that you want? This is how you start debugging. This is also how you should start coding.

SELECT *
FROM [OR Record]
WHERE [Name of Surgeon] LIKE '%SomeName%' OR [Surgeon 2] LIKE '%SomeOtherName%'

Forget about putting the query in your code at this point. You need to take baby steps first.

Tara

dupati1
Posting Yak Master

123 Posts

Posted - 2003-10-06 : 15:39:32

Hi Tara,

I tried this query

SELECT *
[OR Record]
WHERE [Name of Surgeon] LIKE '%susan%';

and it works fine. now if the user selects the susan as the name of the surgeon, does it reflect in the query if i use LIKE '%cbName%'

Thanks for guiding me..

VJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-06 : 15:45:09

I believe that your string needs to look something like this:

strSQL = "SELECT * FROM [OR Record] WHERE [Name of Surgeon] LIKE ‘%" + cbName + "%’ OR [Surgeon 2] LIKE ‘%" + cbname + "%’"

Do you have an ASP book that you can refer to? Although this question deals with a SQL query, the problem isn't with the query but rather with the ASP code.

Tara

dupati1
Posting Yak Master

123 Posts

Posted - 2003-10-06 : 15:59:35

Hi Again,
Thanks for the instant reply, but

Still i get this error:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: '[string: "Susan"]'
/departments/OR/OR.asp, line 23

-VJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-06 : 16:03:41

Do you have an ASP book that you can refer to?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources