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
<JinWeiJie@dotnet.itags.org.gmail.com> wrote in message
news:1194578131.074394.231790@dotnet.itags.org.v23g2000prn.googlegroups.com...
> declare @dotnet.itags.org.someId int
> set @dotnet.itags.org.someId = 1
> select * from categories
> where categoryid = @dotnet.itags.org.someId
> hope it can help :)
>
> Guilherme Grillo wrote:
>"Guilherme Grillo" <webdev@.rncomtotal.com.br> wrote in message
news:%2301ImwuIIHA.2100@.TK2MSFTNGP03.phx.gbl...
> 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
> <JinWeiJie@.gmail.com> wrote in message
> news:1194578131.074394.231790@.v23g2000prn.googlegroups.com...
>
>
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" wrote:
> 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 t
he
> 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
> <JinWeiJie@.gmail.com> wrote in message
> news:1194578131.074394.231790@.v23g2000prn.googlegroups.com...
>
>
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" <webdev@.rncomtotal.com.br> wrote in message
news:%2301ImwuIIHA.2100@.TK2MSFTNGP03.phx.gbl...
> 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
> <JinWeiJie@.gmail.com> wrote in message
> news:1194578131.074394.231790@.v23g2000prn.googlegroups.com...
>
>
Thanks Everybody...
I 'll have much to do in the w
end with this informations...:)
Grillo
"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@.comcast.netNoSpamM> wrote in
message news:%23b0EFUwIIHA.5208@.TK2MSFTNGP04.phx.gbl...
> 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" <webdev@.rncomtotal.com.br> wrote in message
> news:%2301ImwuIIHA.2100@.TK2MSFTNGP03.phx.gbl...
>
Guilherme Grillo pretended :
> commCountQuestions = new SqlCommand("SELECT COUNT(*) AS total FROM
> Questions", conn);
> int t = readerPerguntas.GetOrdinal("total");
>
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
0 comments:
Post a Comment