SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 How to get value from more than one table
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/17/2013 :  09:15:23  Show Profile  Reply with Quote
Hai frieds,

This is my stored procedure

ALTER procedure [dbo].[id_selection]
@id varchar(20),
@ct varchar
as
begin
select CMBooks,Magazine from Book_Issue where Stu_Stf_Id=@id
if @ct = '1'
begin
if exists (select StuId from StudReg where StuId=@id )
begin
select name from StudReg where StuId=@id
end
end
else
begin
if exists (select StfId from staff_reg where StfId=@id )
begin
select Name from staff_reg where StfId=@id
end
end
end

In coding,

SqlDataReader reader = ss.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
MessageBox.Show(reader[0].ToString());
MessageBox.Show(reader[1].ToString());
txtname.Text = reader[2].ToString();

}

I used this coding, I want to retrieve 3 fields (2 from Book_issue table, 1 from Student or staff table) value.

But i received error message "Index out of bounds".

then i tried

txtname.text=reader[0].tostring();

it means display same value of Book_issue CMBook column value.

Kindly help for me to resolve it.

Thank you


James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 10/17/2013 :  10:03:28  Show Profile  Reply with Quote
You are getting two record sets from the stored procedure. The first record has CMBooks and Magazine, and the second has the Name. To move from one recordset to the next use NextResult method of SqlDataReader. This page has an example: http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

Alternatively, and perhaps a better option, might be to rewrite your query like this:
ALTER PROCEDURE [dbo].[id_selection]
    @id VARCHAR(20) ,
    @ct VARCHAR
AS 
    IF EXISTS ( SELECT  StuId
                FROM    StudReg
                WHERE   StuId = @id ) 
               
        SELECT  CMBooks ,
                Magazine
        FROM    Book_Issue b
				INNER JOIN StudReg s ON s.StuId = b.Stu_Stf_Id
        WHERE   Stu_Stf_Id = @id
    ELSE IF EXISTS ( SELECT  StfId
                            FROM    staff_reg
                            WHERE   StfId = @id ) 
        SELECT  CMBooks ,
                Magazine
        FROM    Book_Issue b
				INNER JOIN staff_reg s ON s.StfId = b.Stu_Stf_Id
        WHERE   StfId = @id
GO
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/17/2013 :  10:09:54  Show Profile  Reply with Quote
you just need this

ALTER procedure [dbo].[id_selection]
@id varchar(20),
@ct varchar
as
begin
select CMBooks,Magazine,
case when @ct='1' then  StuId end as StuId,
case when @ct='1' then  StfId end as StfId 
from Book_Issue bi 
left join StudReg sr
on sr.StuId = bi.Stu_Stf_Id
left join staff_reg sfr
on sfr.StfId = bi.Stu_Stf_Id
where bi.Stu_Stf_Id=@id


now take this in datareader and use fields[0],fields[1] etc to get corresponding fields

ALso if you've planning to introduce two columns as per earlier fk post your query will change

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/17/2013 :  11:01:01  Show Profile  Reply with Quote
Thank you for your valuable code.
But it retrieve CMBook, Magazine, Stu_id,StfId.
I want to retrieve CMBook, Magazine, Name (Student Table or Staff Table.
Kindly tel wat i do..


quote:
Originally posted by visakh16

you just need this

ALTER procedure [dbo].[id_selection]
@id varchar(20),
@ct varchar
as
begin
select CMBooks,Magazine,
case when @ct='1' then  StuId end as StuId,
case when @ct='1' then  StfId end as StfId 
from Book_Issue bi 
left join StudReg sr
on sr.StuId = bi.Stu_Stf_Id
left join staff_reg sfr
on sfr.StfId = bi.Stu_Stf_Id
where bi.Stu_Stf_Id=@id


now take this in datareader and use fields[0],fields[1] etc to get corresponding fields

ALso if you've planning to introduce two columns as per earlier fk post your query will change

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/17/2013 :  11:09:35  Show Profile  Reply with Quote
Thank you this code worked done as my wish.

quote:
Originally posted by James K

You are getting two record sets from the stored procedure. The first record has CMBooks and Magazine, and the second has the Name. To move from one recordset to the next use NextResult method of SqlDataReader. This page has an example: http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

Alternatively, and perhaps a better option, might be to rewrite your query like this:
ALTER PROCEDURE [dbo].[id_selection]
    @id VARCHAR(20) ,
    @ct VARCHAR
AS 
    IF EXISTS ( SELECT  StuId
                FROM    StudReg
                WHERE   StuId = @id ) 
               
        SELECT  CMBooks ,
                Magazine
        FROM    Book_Issue b
				INNER JOIN StudReg s ON s.StuId = b.Stu_Stf_Id
        WHERE   Stu_Stf_Id = @id
    ELSE IF EXISTS ( SELECT  StfId
                            FROM    staff_reg
                            WHERE   StfId = @id ) 
        SELECT  CMBooks ,
                Magazine
        FROM    Book_Issue b
				INNER JOIN staff_reg s ON s.StfId = b.Stu_Stf_Id
        WHERE   StfId = @id
GO



Edited by - Arunavally on 10/17/2013 11:17:16
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/18/2013 :  01:28:31  Show Profile  Reply with Quote
Thank you for your reply. Your query only retrieve if existing records available in both table.

But i want to display Name in text box from Student table. its needed one

After that, if existing data in Book_issue table means, i wanna data or empty. Its optional one depending upon the condition




quote:
Originally posted by Arunavally

Thank you this code worked done as my wish.

quote:
Originally posted by James K

You are getting two record sets from the stored procedure. The first record has CMBooks and Magazine, and the second has the Name. To move from one recordset to the next use NextResult method of SqlDataReader. This page has an example: http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

Alternatively, and perhaps a better option, might be to rewrite your query like this:
ALTER PROCEDURE [dbo].[id_selection]
    @id VARCHAR(20) ,
    @ct VARCHAR
AS 
    IF EXISTS ( SELECT  StuId
                FROM    StudReg
                WHERE   StuId = @id ) 
               
        SELECT  CMBooks ,
                Magazine
        FROM    Book_Issue b
				INNER JOIN StudReg s ON s.StuId = b.Stu_Stf_Id
        WHERE   Stu_Stf_Id = @id
    ELSE IF EXISTS ( SELECT  StfId
                            FROM    staff_reg
                            WHERE   StfId = @id ) 
        SELECT  CMBooks ,
                Magazine
        FROM    Book_Issue b
				INNER JOIN staff_reg s ON s.StfId = b.Stu_Stf_Id
        WHERE   StfId = @id
GO




Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/18/2013 :  01:34:35  Show Profile  Reply with Quote
Thank you visakh16.

My doubt is, When i select a student or staff id from combo box. it will automatically display Student or Staff name to text box.

This value is taken from StudReg Table.

At that same time, i select a student or staff id from combo box, want to check student or staff already got course material and magazine. Then i want to retrieve CMBook, Magazine field from Book_issue table.

I want to use 2 logic of queries in same stored procedure.

How can i resolve it? Kindly help me and tel what is that querey

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/18/2013 :  03:00:47  Show Profile  Reply with Quote
in that case it should be


ALTER procedure [dbo].[id_selection]
@id varchar(20),
@ct varchar
as
begin

select t.name,CMBooks,Magazine
from
(
select StuId as ID, name as studname from StudReg where StuId = @id
union 
select StfId, name from Staff_Reg where StfId = @id
)t
left join Book_Issue bi 
on bi.Stu_Stf_Id = t.ID


you'll get name and Book details in same resultset itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/18/2013 :  04:42:57  Show Profile  Reply with Quote
Thank you. It will work as my wish.

quote:
Originally posted by visakh16

in that case it should be


ALTER procedure [dbo].[id_selection]
@id varchar(20),
@ct varchar
as
begin

select t.name,CMBooks,Magazine
from
(
select StuId as ID, name as studname from StudReg where StuId = @id
union 
select StfId, name from Staff_Reg where StfId = @id
)t
left join Book_Issue bi 
on bi.Stu_Stf_Id = t.ID


you'll get name and Book details in same resultset itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/18/2013 :  08:07:33  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/19/2013 :  06:51:57  Show Profile  Reply with Quote
Hai visakh murukes sir,

I have maintained batch table

This field have

Stu_Id(Primary Key) BatchId (Not Primary key)
STU0001 APR042013
STU0002 APR062013
STU0003 APR042013


In form_load i want to load batch id frm DB to Combo Box. But i want to only one set of Id allow in combo box. That means..

In 1st row BatchId is APR042013
In 3rd row BatchId is APR042013 (Same)

But i want to display one Id (APR042013)instead of these two. and also 2nd row Id.

Kindly help me sir.

Thanks in advance
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/20/2013 :  03:42:57  Show Profile  Reply with Quote
Do you mean just BatchID alone. then

SELECT DISTINCT BatchID FROM Table

would do

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/21/2013 :  00:54:24  Show Profile  Reply with Quote
yes. Thank you sir. It will work as my wish. Thank you


quote:
Originally posted by visakh16

Do you mean just BatchID alone. then

SELECT DISTINCT BatchID FROM Table

would do

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/21/2013 :  06:38:10  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/22/2013 :  08:57:09  Show Profile  Reply with Quote
Hai visakh murukes sir,

I used reportviewer to display all test marks in particular student

Table: TestTable
StuId
BatchId
Testname
Mark
QualMark
Max Mark
Date

It displayed correct in reportviewer. But i want to display StuId, Batch Id, Course, BatchStartDate,BatchEndDate, Attendance from Batch Table.

It will display only once in report. But TestTable value displayed more records.

How can i do?
Kindly help me sir.

Thanks in advance


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/22/2013 :  09:09:43  Show Profile  Reply with Quote
do you mean this?

SELECT  StuId, Batch Id, Course, BatchStartDate,BatchEndDate, Attendance,
Testname,Mark,QualMark,[Max Mark],Date
FROM Batch b
JOIN TestTable t
ON t.StuId = b.StuId
AND t.BatchId = b.BatchId

the other requirement is presentation requirement
depending on how you want you may apply grouping on SSRS on Batch fields or you could set hide duplicates property true for all batch id fields.
If you can provide info on how exactly you want data to come I can suggest.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/22/2013 :  09:27:26  Show Profile  Reply with Quote
ya.I wrote query this way. but in presentation view, i dont knw how i do?
Then StuId:RRR BatchId: WWWWW B.StartDate: ddd B.End date:ddd Atttn: 12

Date TestName Mark QulMark MaxMark
ddd xxxxxx 45 25 50
ddd yyyyy 32 25 50
ddd zzzzz 41 25 50

I want to display this format.
Kindly tel how i do?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/22/2013 :  10:08:55  Show Profile  Reply with Quote
apply grouping in SSRS including batchid fields and then in details add the testtable fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/22/2013 :  10:33:55  Show Profile  Reply with Quote
Thank you. I will try
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/22/2013 :  11:08:27  Show Profile  Reply with Quote
This error is occurring .
"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
I dnt knw Why?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/22/2013 :  14:32:33  Show Profile  Reply with Quote
hmm...This has nothing to do with earlier question.
Where did you get this? What were you trying to do?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000