Need a fresh to find the syntax error 1

[haneen97]

Hi,
I can't see the syntax error in this. Please help.

Set RS = MyConn.Execute("SELECT * FROM '" & TableName & "' WHERE format('" & WhrCls & "') = '" & id & "'")


Thanks

Mo

 

[steven290]

whats this format('" & WhrCls & "') should be the fieldname

 

[steven290]

also if the field is a numeric field remove the "'" surrounding the id

 

[steven290]

is format handled like this

Format( expression, [ format ] )

what are you trying to do

 

[haneen97]

I am trying to do two things in this query.
1- Use one record set to query the patient and the physicians.
2- Convert the Physician_Id or the Patient_Id to a text since the request returns the id as text.

I am open for better ways to do it.

Mo

 

[steven290]

what do you mean by this "request returns the id as text"

as for the 1 query you can use left join

ex.

select patienttb.*,physiciantb.fullname
from patienttb
left join physiciantb
on physiciantb.pat_id=patienttb.id
where patienttb.id=5

 

[steven290]

at the moment Physician_Id and Patient_Id are numeric, right?

so if id is something like request("id") a number, whats the problem

 

[steven290]

even if Physician_Id or Patient_Id wasn't numeric it would still work

 

[haneen97]

I mean this line return a text value

id = request("id")

The two queries are separate. The application is assigning a patient from the list of patients and a physician from the list of physicians. There is no relation at this point of the application.

Mo

 

[steven290]

thats fine
lets say your url is

page.asp?patid=5&phyid=7

patid=request("patid")
phyid=request("phyid")

it would still work to do this

Set RS = MyConn.Execute("SELECT * FROM pattb WHERE id =" & patid)

 

[haneen97]

The patient_Id and the Physician_Id are numeric. The request("id") was not working until I used that Format("Patient_Id") thing. Before the format I was getting a syntax error about invalid oprator or something like that.

Mo

 

[haneen97]

I think I had a problem doing that because the application leaves page 1 (order) twoice. Once to get the patient and once to get the physician. It was working for the patient but it was giving and EOF error on the physician.

Mo

 

[steven290]

to select both you can also do this

patid=request("patid")
phyid=request("phyid")

"select pattb.*,phytb.* from pattb,phytb where pattb.id="&patid&" and phytb.id="&phyid

 

[haneen97]

I think this works like a UNION query. But, look at the message above. The two searchs, patient and physician, are indipendant.

Mo

 

[steven290]

is the purpose of the page to show the info of the patient, including their doctor

 

[steven290]

they are independent, so you create 2 sql's with if statement

if patid<>"" then
Set patRS = MyConn.Execute("SELECT * FROM pattb WHERE id =" & patid)


...'show patient info here

end if


furtherdown the page on the physician side

if phyid<>"" then
Set phyRS = MyConn.Execute("SELECT * FROM phytb WHERE id =" & phyid)

...'show physician info here
end if

 

[haneen97]

I think we are onto something here:
This is the patient part of the page. where in this code, I mean between which two lines should I insert the code to execute the query?

<td width="46%"><form action="Pat_Search.asp" method="post" name="frmPat" id="frmPat">
<div align="left"> <strong>
<label>
<input name="PatSearch" type="submit" id="PatSearch" value="Find Patient">
<input name="txtSearchPat" type="text" id="txtSearchPat">
</label>
<label> </label>
</strong></div>
<p align="left"> <strong>
<label>Patient First Name :
<input name="PatFirstName" type="text" id="PatFirstName" value='<%=PatFName%>' >
</label>
</strong></p>
<div align="left"> <strong>
<label> </label>
<label>Patient Middle Name :
<input name="PatMidName" type="text" id="PatMidName" value='<%=PatMName%>'>
</label>
</strong></div>
<p align="left"> <strong>
<label></label>
<label>Patient Last Name :
<input name="PatLastName" type="text" id="PatLastName" value='<%=PatLName%>'>
</label>
</strong></p>
</form></td>


Mo

 

[steven290]

if patid<>"" then

your sql here
%>
<td width="46%"><form action="Pat_Search.asp" method="post" name="frmPat" id="frmPat">
<div align="left"> <strong>
<label>
<input name="PatSearch" type="submit" id="PatSearch" value="Find Patient">
<input name="txtSearchPat" type="text" id="txtSearchPat">
</label>
<label> </label>
</strong></div>
<p align="left"> <strong>
<label>Patient First Name :
<input name="PatFirstName" type="text" id="PatFirstName" value='<%=PatFName%>' >
</label>
</strong></p>
<div align="left"> <strong>
<label> </label>
<label>Patient Middle Name :
<input name="PatMidName" type="text" id="PatMidName" value='<%=PatMName%>'>
</label>
</strong></div>
<p align="left"> <strong>
<label></label>
<label>Patient Last Name :
<input name="PatLastName" type="text" id="PatLastName" value='<%=PatLName%>'>
</label>
</strong></p>
</form></td>
<%else
response.write "<td>&nbsp;</td>"
end if
if phyid<>"" then
%>
... phy side

 

[steven290]

actually you want to show the fields even if patient isn't picked, you would do a

if patid<>"" then
you sql here

create variables here
PatFName=rs("fname")
PatMName=rs("mname")
PatLName=rs("lname")
end if

%>
 <td width="46%"><form action="Pat_Search.asp" method="post" name="frmPat" id="frmPat">
        <div align="left"> <strong>
          <label>
          <input name="PatSearch" type="submit" id="PatSearch" value="Find Patient">
          <input name="txtSearchPat" type="text" id="txtSearchPat">
          </label>
          <label> </label>
          </strong></div>
       <p align="left"> <strong>
          <label>Patient First Name :
          <input name="PatFirstName" type="text" id="PatFirstName"  value='<%=PatFName%>' >
          </label>
          </strong></p>
        <div align="left"> <strong>
          <label> </label>
          <label>Patient Middle Name :
          <input name="PatMidName" type="text" id="PatMidName" value='<%=PatMName%>'>
          </label>
          </strong></div>
        <p align="left"> <strong>
          <label></label>
          <label>Patient Last Name :
          <input name="PatLastName" type="text" id="PatLastName" value='<%=PatLName%>'>
          </label>
          </strong></p>
      </form></td>

 

[haneen97]

Ok,
It worked in the patient area but I am getting this error in the physician ara:

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.



Mo