please help me - 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

SQL Server 2005 Forums

Transact-SQL (2005)

please help me

Author

Topic

rabail684
Starting Member

8 Posts

Posted - 2007-04-24 : 05:36:54

SqlDataAdapter da2 = new SqlDataAdapter("SELECT Personal.NumberofBeds FROM Personal WHERE Personal.HospitalID==Patients.HospitalID AND Personal.HospitalName==PName", con);

i've an application built in c#.net and connected with sql server 2005's database,so executing this command it gives exception as invalid syntax...WHAT'S rong with this query? PName is a string taken as this ::

SqlDataAdapter da = new SqlDataAdapter("select HospitalName,Totalpatients from Patients", con);
DataSet ds = new DataSet();
da.Fill(ds,0,0,"Patients");
SqlCommandBuilder cb = new SqlCommandBuilder(da);
string PName=System.Convert.ToString(ds.Tables["Patients"].Rows[ii]["HospitalName"]);

also someone may tell me in this current scenario,,whts the best way to select data from multiple tables based on some clauses?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 05:39:58

You have to JOIN the PATIENTS table too.

Peter Larsson
Helsingborg, Sweden

rabail684
Starting Member

8 Posts

Posted - 2007-04-24 : 05:43:03

means like this???

("SELECT Personal.NumberofBeds FROM Personal,Patients WHERE Personal.HospitalID==Patients.HospitalID AND Personal.HospitalName==PName", con);

rabail684
Starting Member

8 Posts

Posted - 2007-04-24 : 05:47:54

i also tried this query in sql but it gives error as "" The multi-part identifier "Patients.HospitalName" could not be bound.""
However patients table has a field named HospitalName..

SELECT Personal.NumberofBeds
FROM dbo.Personal,dbo.Patients
Where Personal.HospitalID = Patients.HospitalID
UNION ALL
SELECT Personal.NumberofBeds
FROM dbo.Personal
where Personal.HospitalName = Patients.HospitalName

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 05:56:47

try this


SELECT Personal.NumberofBeds 
FROM   Personal INNER JOIN Patients 
ON     Personal.HospitalID = Patients.HospitalID 
AND    Personal.HospitalName = Patients.HospitalName

Do you need the part in red ? You can remove that if HospitalID alone can uniquely identified the record.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 05:58:54

combining both of your query into single query


SELECT Personal.NumberofBeds, Patients.HospitalName, Patients.Totalpatients 
FROM   Personal INNER JOIN Patients 
ON     Personal.HospitalID   = Patients.HospitalID 
AND    Personal.HospitalName = Patients.HospitalName

rabail684
Starting Member

8 Posts

Posted - 2007-04-24 : 06:06:15

yes i want a record to b uniquely identified by both the arguments so i need part in red,,and well this query can be used in c#.net?

tell me if this query gives me list of records and i just want to take the upper most means only first one then wht should i do?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 06:09:42

"tell me if this query gives me list of records and i just want to take the upper most means only first one then wht should i do?"
You only want ONE record ? Which one do you want ? The one with Most NumberofBeds ?


SELECT TOP 1 Personal.NumberofBeds, Patients.HospitalName, Patients.Totalpatients 
FROM   Personal INNER JOIN Patients 
ON     Personal.HospitalID   = Patients.HospitalID 
AND    Personal.HospitalName = Patients.HospitalName
ORDER BY Personal.NumberofBeds DESC

Most Totalpatients ?

rabail684
Starting Member

8 Posts

Posted - 2007-04-24 : 06:12:07

it gives error as

Invalid object name 'dbo.Personal'. However it exists :(

rabail684
Starting Member

8 Posts

Posted - 2007-04-24 : 06:15:01

infact im currently writting this query in sql however it'll actually b used in c# code where i've to compare Personal.HospitalName = PName where PName is a string in which a hospital's name will b taken on run time so it'll b compared with tht hospital name and i want NumberofBeds and totalpatients of tht hospital....i hope u got it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 06:15:43

Can you post the query you use ?

rabail684
Starting Member

8 Posts

Posted - 2007-04-24 : 06:20:23

string PName=System.Convert.ToString(ds.Tables["Patients"].Rows[ii]["HospitalName"]);
SqlDataAdapter da2 = new SqlDataAdapter("SELECT Personal.NumberofBeds FROM Personal,Patients Where Personal.HospitalID = Patients.HospitalID && Personal.HospitalName = PName", con);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources