By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,440 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

Selecting Query

P: 39
DoctorMaster

DRID nvarchar(50)
FirstName nvarchar(100)
LastName nvarchar(100)
Gender bit
Address1 nvarchar(255)
Address2 nvarchar(255)
City nvarchar(100)
Pincode numeric(6,0)
State nvarchar(100)
Country nvarchar(100)
Telephone nvarchar(50)
Mobile nvarchar(50)
EmailID nvarchar(100)
Designation nvarchar(50)
Suffix nvarchar(100)
Language nvarchar(10)
SpecialityId nvarchar(100)

Speciality

SPID nvarchar(12)
Speciality vnarchar(50)

DrClinicDetails

DRID nvarchar(12)
CDID nvarchar(12)
HPID nvarchar(12)
HospitalName nvarchar(50)
Address1 nvarchar(255)
Address2 nvarchar(255)
VisitOn char(1)
weekly nvarchar(15)
monthly datetime
status char(1)


I have

Name_TextBox
Specialty_DropDownList
Location_DropDownList

in my page. According to the data in above 3 controls I have to select the value from DB by using the above 3 tables. The selected should be like below

Name: (by appending FirstName,LastName & MiddleName from DoctorMaster)
Gender: From DoctorMaster
Address1: From DrClinicDetails
Phone: (by appending Telephone,Mobile from DoctorMaster)
Suffix: From DoctorMaster

where

FirstName(from DoctorMaster table)=Name_TextBox
Specialty(from specialty table)=Specialty_DropDownList
Country(from DoctorMaster)=Location_DropDownList

The query I have given in .Net is

Select DM.FirstName||DM.MiddleName||DM.LastName "Doctors Name",DM.Gender,DC.Address1,DM.Telephone||DM.Mobil e "Phone",DM.Suffix from DoctorMaster DM,DrClinicDetails DC,Specialty SP where DM.FirstName='aaa' or SP.Specialty='001' or DM.Country='India' and SP.SPID='001'

Its not working.
Jun 23 '08 #1
Share this Question
Share on Google+
4 Replies


deepuv04
Expert 100+
P: 227
Hi,
Try the query in the following way...

Expand|Select|Wrap|Line Numbers
  1. Select DM.FirstName + '||' + DM.MiddleName + '||' + DM.LastName AS Doctors Name,
  2.        DM.Gender,DC.Address1,DM.Telephone + '||' + DM.Mobile AS Phone,
  3.        DM.Suffix 
  4. from DoctorMaster DM INNER JOIN 
  5.      DrClinicDetails DC DM.DRID  = DC.DRID  INNER JOIN
  6.      Specialty SP SP.SPID = DM.SpecialityId  
  7. where ( DM.FirstName='aaa' or SP.Specialty='001' or DM.Country='India') and SP.SPID='001'
  8.  
  9.  
thanks
Jun 23 '08 #2

P: 39
Hi

If I run the following query in sql server 2005, its displaying the data.

Select DM.FirstName+' '+DM.MiddleName+' '+DM.LastName "DoctorsName",DM.Gender,DC.Address,DM.Telephone+DM .Mobile "Phone",PT.PrimaryTitle from DoctorMaster DM,DrClinicDetails DC,Specialty SP,PrimaryTitle PT where DM.FirstName='aaa' or SP.Specialty='aaa' or DM.City='madurai' and SP.SPID=DM.SPID and PT.PTID=DM.PrimaryTitleID

But if I give the same query as

string sql="Select DM.FirstName+''+DM.MiddleName+''+DM.LastName "DoctorsName",DM.Gender,DC.Address,DM.Telephone+'' +DM.Mobile "Phone",PT.PrimaryTitle from DoctorMaster DM,DrClinicDetails DC,Specialty SP,PrimaryTitle PT where DM.FirstName='" + obj.ToString() + "' or SP.Specialty='" + obj1.ToString() + "' or DM.City='" + obj2.ToString() + "' and SP.SPID=DM.SPID and PT.PTID=DM.PrimaryTitleID";

in asp.net then it is showing the error as

; expected
Jun 23 '08 #3

P: 39
Than U Deepuv04. Its not showing any error but still I have a doubt, in the above query if I add another table

PrimaryTitle

PTID nvarchar(12)
PrimaryTitle nvarchar(20)

and give the query like

Select DM.FirstName + '' + DM.MiddleName + '' + DM.LastName AS DoctorsName, DM.Gender,DC.Address,DM.Telephone + '' + DM.Mobile AS Phone,PT.PrimaryTitle from DoctorMaster DM INNER JOIN DrClinicDetails DC DM.DRID = DC.DRID INNER JOIN Specialty SP SP.SPID = DM.SPID INNER JOIN PrimaryTitle PT PT.PTID=DM.PrimaryTitleID where ( DM.FirstName like 'a' or SP.Specialty='aaa' or DM.City='madurai')


It is showing the following error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DM'.
Jun 24 '08 #4

deepuv04
Expert 100+
P: 227
Hi,
coming to your first error, since you are building the query as a string in .net you need to follow the .net syntax to build the string. like when you are moved to next line you need to add " & _" at the end of previous.

the code will be:
Expand|Select|Wrap|Line Numbers
  1.  
  2. string sql="Select DM.FirstName+''+DM.MiddleName+''+DM.LastName  ""DoctorsName""," & _
  3.            "DM.Gender,DC.Address,DM.Telephone+''+DM.Mobile ""Phone"",PT.PrimaryTitle " & _
  4.            "from DoctorMaster DM,DrClinicDetails DC,Specialty SP,PrimaryTitle PT " & _
  5.            "where (DM.FirstName='" + obj.ToString() + "' or SP.Specialty='" + obj1.ToString() + "' or DM.City='" + obj2.ToString() + "')" & _
  6.            " and SP.SPID=DM.SPID and PT.PTID=DM.PrimaryTitleID";
  7.  
about second error: you missed the key word "ON" in the joining syntax.
the code is:
[code =sql]
Select DM.FirstName + '' + DM.MiddleName + '' + DM.LastName AS DoctorsName,
DM.Gender,DC.Address,DM.Telephone + '' + DM.Mobile AS Phone,PT.PrimaryTitle
from DoctorMaster DM INNER JOIN
DrClinicDetails DC on DM.DRID = DC.DRID INNER JOIN
Specialty SP on SP.SPID = DM.SPID INNER JOIN
PrimaryTitle on PT PT.PTID=DM.PrimaryTitleID
where ( DM.FirstName like 'a' or SP.Specialty='aaa' or DM.City='madurai')
[/code]
Jun 24 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.