Loop in SQL Part II

  • Thread starter Thread starter Guilherme Grillo
  • Start date Start date
G

Guilherme Grillo

Thanks for all.

But my problem is a little different.

Example:

The system SELECT COUNT the table "QUESTIONS" on DB

commCountQuestions = new SqlCommand("SELECT COUNT(*) AS total FROM
Questions", conn);

Then,

int t = readerPerguntas.GetOrdinal("total");

Then I do a SELECT to Select the Questions in table Questions and Select the
ANSWERS in the table ANSWERS.

for (i=1, i<16, i++)
{
sqlcommQUESTION = "SELECT * FROM Questions WHERE id=" + i + "";
sqlcomm = "SELECT * FROM Answers WHERE id =" + i + "";
}

The problem is: I can't do two readers READ at the same time. I don't know
how to do a JOIN that works.

INFO: One Question have 2 questions or more...

The results should be:

1. What's the color of the sea?

[checkbox] Blue
[checkbox] Red
[checkbox] Yellow
[checkbox] Green

2. Do you like cookies?

[checkbox] Yes
[checkbox] No

3. ... ... ..
[checkbox] ...


I don't know how to solve this problem!!!!

Sorry my english...

Grillo
 
Guilherme Grillo said:
Thanks for all.

But my problem is a little different.

Example:

The system SELECT COUNT the table "QUESTIONS" on DB

commCountQuestions = new SqlCommand("SELECT COUNT(*) AS total FROM
Questions", conn);

Then,

int t = readerPerguntas.GetOrdinal("total");

Then I do a SELECT to Select the Questions in table Questions and Select
the
ANSWERS in the table ANSWERS.

for (i=1, i<16, i++)
{
sqlcommQUESTION = "SELECT * FROM Questions WHERE id=" + i + "";
sqlcomm = "SELECT * FROM Answers WHERE id =" + i + "";
}

The problem is: I can't do two readers READ at the same time. I don't know
how to do a JOIN that works.

INFO: One Question have 2 questions or more...

The results should be:

1. What's the color of the sea?

[checkbox] Blue
[checkbox] Red
[checkbox] Yellow
[checkbox] Green

2. Do you like cookies?

[checkbox] Yes
[checkbox] No

3. ... ... ..
[checkbox] ...


I don't know how to solve this problem!!!!

Sorry my english...

Grillo

declare @someId int
set @someId = 1
select * from categories
where categoryid = @someId

hope it can help :)
Click to expand...
Click to expand...

I may have a couple of ideas that can help.

First when you are executing a SQL command that returns one item such as the
count(*) you should use ExecuteScalar. This is much faster than a reader
since it does not have the overhead of creating things it does not need.

Then you could use:

commCountQuestions = new SqlCommand("SELECT COUNT(*) AS total FROM
Questions", conn);
int t = (int)commCountQuestion.ExecuteScalar();

The same can be done when you are getting sqlcommQUESTION = "SELECT * FROM
Questions WHERE id=" + i + "";. If all you are doing is getting the
question text, use the fieldname for the question text rather than *. Then
once you have the question text you can use the reader to get the possible
answers.

Hope this helps
LLoyd Sheen
 
You can solve this with nested reads, a sql loop, a join or a data table.

nested readers, a connection can only handle on reader, so you just need to
use two connections. normally you woudl use the same transaction scope so
they don't lock each other, but not neccessary in this case.

sql loop (returns mutilple result sets):

set nocount on
declare @i int
set @i =1
while @i < 16
begin
SELECT * FROM Questions WHERE id=@i
SELECT * FROM Answers WHERE id =@i
set @i = @i + 1
end

sql join:

select *
from question q
join answes a on a.id = q.id

datatable:

sql = "select * from question;select * from answers;"

load into dataset (creates two tables), add parent/child constaint

-- bruce (sqlwork.com)


Guilherme Grillo said:
Thanks for all.

But my problem is a little different.

Example:

The system SELECT COUNT the table "QUESTIONS" on DB

commCountQuestions = new SqlCommand("SELECT COUNT(*) AS total FROM
Questions", conn);

Then,

int t = readerPerguntas.GetOrdinal("total");

Then I do a SELECT to Select the Questions in table Questions and Select the
ANSWERS in the table ANSWERS.

for (i=1, i<16, i++)
{
sqlcommQUESTION = "SELECT * FROM Questions WHERE id=" + i + "";
sqlcomm = "SELECT * FROM Answers WHERE id =" + i + "";
}

The problem is: I can't do two readers READ at the same time. I don't know
how to do a JOIN that works.

INFO: One Question have 2 questions or more...

The results should be:

1. What's the color of the sea?

[checkbox] Blue
[checkbox] Red
[checkbox] Yellow
[checkbox] Green

2. Do you like cookies?

[checkbox] Yes
[checkbox] No

3. ... ... ..
[checkbox] ...


I don't know how to solve this problem!!!!

Sorry my english...

Grillo

declare @someId int
set @someId = 1
select * from categories
where categoryid = @someId

hope it can help :)
Click to expand...
Click to expand...
 
Set up two tables in a DataSet instead of using two readers. you can then do
whatever you desire with the Questions and Answers. You can even get teh
count by counting rows in one or the other (assuming they are matched). No
reason to do three queries when a single stored proc will do the job.

In a DataSet, the join can be placed prior to doing any filling (ie, when
you are designing). This will mean you have to fill the parent (most likely
QUESTIONS) first. They will be joined without having any issues, as the
tables in the DataSet will be joined.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
Guilherme Grillo said:
Thanks for all.

But my problem is a little different.

Example:

The system SELECT COUNT the table "QUESTIONS" on DB

commCountQuestions = new SqlCommand("SELECT COUNT(*) AS total FROM
Questions", conn);

Then,

int t = readerPerguntas.GetOrdinal("total");

Then I do a SELECT to Select the Questions in table Questions and Select
the
ANSWERS in the table ANSWERS.

for (i=1, i<16, i++)
{
sqlcommQUESTION = "SELECT * FROM Questions WHERE id=" + i + "";
sqlcomm = "SELECT * FROM Answers WHERE id =" + i + "";
}

The problem is: I can't do two readers READ at the same time. I don't know
how to do a JOIN that works.

INFO: One Question have 2 questions or more...

The results should be:

1. What's the color of the sea?

[checkbox] Blue
[checkbox] Red
[checkbox] Yellow
[checkbox] Green

2. Do you like cookies?

[checkbox] Yes
[checkbox] No

3. ... ... ..
[checkbox] ...


I don't know how to solve this problem!!!!

Sorry my english...

Grillo

declare @someId int
set @someId = 1
select * from categories
where categoryid = @someId

hope it can help :)
Click to expand...
Click to expand...
 
Thanks Everybody....

I 'll have much to do in the weekend with this informations...

:)

Grillo


Cowboy (Gregory A. Beamer) said:
Set up two tables in a DataSet instead of using two readers. you can then
do whatever you desire with the Questions and Answers. You can even get
teh count by counting rows in one or the other (assuming they are
matched). No reason to do three queries when a single stored proc will do
the job.

In a DataSet, the join can be placed prior to doing any filling (ie, when
you are designing). This will mean you have to fill the parent (most
likely QUESTIONS) first. They will be joined without having any issues, as
the tables in the DataSet will be joined.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box! |
*************************************************
Guilherme Grillo said:
Thanks for all.

But my problem is a little different.

Example:

The system SELECT COUNT the table "QUESTIONS" on DB

commCountQuestions = new SqlCommand("SELECT COUNT(*) AS total FROM
Questions", conn);

Then,

int t = readerPerguntas.GetOrdinal("total");

Then I do a SELECT to Select the Questions in table Questions and Select
the
ANSWERS in the table ANSWERS.

for (i=1, i<16, i++)
{
sqlcommQUESTION = "SELECT * FROM Questions WHERE id=" + i + "";
sqlcomm = "SELECT * FROM Answers WHERE id =" + i + "";
}

The problem is: I can't do two readers READ at the same time. I don't
know
how to do a JOIN that works.

INFO: One Question have 2 questions or more...

The results should be:

1. What's the color of the sea?

[checkbox] Blue
[checkbox] Red
[checkbox] Yellow
[checkbox] Green

2. Do you like cookies?

[checkbox] Yes
[checkbox] No

3. ... ... ..
[checkbox] ...


I don't know how to solve this problem!!!!

Sorry my english...

Grillo

declare @someId int
set @someId = 1
select * from categories
where categoryid = @someId

hope it can help :)


Guilherme Grillo wrote:
Hi...

I need to create a SELECT with a variable in WHERE...

Example:

SELECT * FROM ANSWERS WHERE id = 1

then return the results and execute

SELECT * FROM ANSWERS WHERE id = 2

... ... ...

It's possible?

Thanks and sorry my english.

Grillo
Click to expand...
Click to expand...
Click to expand...
 
Guilherme Grillo pretended :
commCountQuestions = new SqlCommand("SELECT COUNT(*) AS total FROM
Questions", conn);

int t = readerPerguntas.GetOrdinal("total");
Click to expand...

Note: the value that gets set in "t" is not the requested total, but
the index of the column containing that total (and that would be 0
here).
Use the GetInt32( ) method to get that total.

Hans Kesting
 
Back
Top