Cursor and other table to fill dataset

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 2000 Forums

Transact-SQL (2000)

Cursor and other table to fill dataset

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-01 : 09:56:52

I need to fill a dataset from this sp.

I first read some info from table 1:

DECLARE c_userAccount CURSOR FOR
SELECT userId
FROM tblRoles
WHERE roleId = 'projLead'
FOR READ ONLY

--Now, I need to take that info above and fill a dataset.
--I need to go through each userId found somehow and do this:

SELECT @fname = firstName, @lname = lastName, @email = email
FROM tblUserAccount WHERE userId = USERIDfromCURSOR

SELECT firstName, lastName, email
FROM tblUserAccount WHERE userId = USERIDfromCURSOR

I know you need to open the cursor and fetch next from, but just can't get this right.

I was filling the dataset this way until I moved the field over to the roles table to improve the db and code behind:

BEGIN

DECLARE @fname VARCHAR
DECLARE @lname VARCHAR
DECLARE @email VARCHAR

SELECT @fname = firstName, @lname = lastName, @email = email
FROM tblUserAccount WHERE isProjLead = 1

SELECT firstName, lastName, email
FROM tblUserAccount WHERE isProjLead = 1

Suggestions?

Thanks,

Zath

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-01 : 10:15:55

why are you trying to do this with cursors??
what kind of dataset? ado.net's dataset or something else?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-01 : 10:17:14

Sure, don't use a cursor, just join the cursor and the select statement

what do you mean by a "dataset"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-01 : 10:19:05

Yes, it's for .Net.

Need the dataset to fill a ddl. It can even be an arraylist.

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-01 : 10:21:25

Here's the code behind but can easily be changed to arraylist.

But, if there's a better way to do it other than using a cursor, I'd like to see an example.

Dim DS As New DataSet
Dim sqlAdpt As New SqlDataAdapter

Dim conn As SqlConnection = New SqlConnection(DBconn.CONN_STRING)
Dim Command As SqlCommand = New SqlCommand("retrieveTestLeads", conn)

Command.CommandType = CommandType.StoredProcedure
Command.Connection = conn
sqlAdpt.SelectCommand = Command

sqlAdpt.Fill(DS)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-01 : 10:53:25

your dataset will be filled with results from this query:
SELECT firstName, lastName, email
FROM tblUserAccount WHERE isProjLead = 1

why do you need a cursor for this?

I don't quite understand your logic behind this.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-01 : 11:16:03

Yes, the dataset will be filled with this:
SELECT firstName, lastName, email
FROM tblUserAccount WHERE isProjLead = 1

BUT, that is the way I had it. The field isProjLead is no longer in that table.

I moved it and made it an additional role.

In the table called tblUserRoles is userId and roleId.

Now, I need to take every userId from tblUserRoles in a certain roleId = 'projLead' and use that info to get other info from tblUserAccount.

I need to fill a dataset from tblUserAccount all the userId's from previous table that have that role. I need their fname,lname,email.

Hope it's clear as mud

Zath

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-01 : 11:48:14

emmm join to the other table?

acctualy it is clear as mud. i can't see a thing.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-01 : 12:12:10

Ok, that sounds like it may be better.

Something like this????

SELECT U.userId
FROM tblUserRoles U
WHERE U.roleId = 'projLead'
UNION
SELECT A.firstName, A.lastName, A.email
FROM tblUserAccount A
WHERE A.userId = U.userId

But, it get an error in the analyzer when run:

The column prefix 'U' does not match with a table name or alias name used in the query.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-11-01 : 12:15:22

Follow the first link in my signature and follow the instructions, and someone will be able to give you an answer a LOT faster.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-11-01 : 14:10:19

Zath,

Try this:


SELECT a.firstName, a.lastName, a.email
from tbluseraccount a
inner join tblUserRoles U on u.userid = a.userid
and u.roleid = 'projLead'

Ken

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-01 : 14:50:47

Thanks for the reply.

It seems to be working, that is the info is there.

But one last question, how do I get this information out of there and back to my dataset?

As it is now, the DS is empty. Nothing is being returned out of the sp.

And as a reminder, I did fill it this way:

DECLARE @fname VARCHAR
DECLARE @lname VARCHAR
DECLARE @email VARCHAR

SELECT @fname = firstName, @lname = lastName, @email = email
FROM tblUserAccount WHERE isProjLead = 1

SELECT firstName, lastName, email
FROM tblUserAccount WHERE isProjLead = 1

But I deleted the field isProjLead and made it a role in tblUserRoles.

Thanks,

Zath

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-11-02 : 13:30:12

[code]
SELECT a.firstName, a.lastName, a.email
from tbluseraccount a
inner join tblUserRoles U on u.userid = a.userid
and u.roleid = 'projLead' and u.isProjLead = 1
[/code]

quote:

But one last question, how do I get this information out of there and back to my dataset?

Remove the variables. Just run the SELECT query, and it will populate the dataset properly.

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-03 : 09:07:06

Ok, I got it working.

Problem turned out to be there was a typo in the userRole table.
I fixed that then made that field a constraint for a new table that has all the roles listed....

:DOH:

Thanks everyone!

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources