Monday, March 26, 2012
help with binding
secondly if you want to add the value of a specific row in a dataset to a textbox you can use something like
TxtLastName.Text = dsLastName.Tables[index].Rows[index].Columns["User_LastName"]
if however you want to bind it you might as well look over here and get some clue
http://www.velocityreviews.com/forums/t95227-bind-textbox-to-dataset.html
Help with an WHERE NOT EXISTS subquery please.
tblStations StationID
Station
tblStationUser RecordID
UserName
Station
I'm trying to come up with a dataset that contains the
tblStations.Station
EXCEPT for where that Station exists in tblStationUser where the
UserName = @.varUserName.
I've tried this but get 0 rows (I should get about 40):
SELECT tblStations.Station
FROM tblStations
WHERE NOT EXISTS
(SELECT tblStationUser.Station FROM tblStationUser WHERE
tblStationUser.UserName=@.varUserName)
ORDER BY Station
I tried the subquery separately which returns the correct number of
rows.
Any clues as to where I'm going wrong?
Thanks!
KathyKathy,
You forgot to join tables. Try one of these queries. I haven't tested them.
SELECT Station
FROM tblStations
WHERE NOT EXISTS (SELECT *
FROM tblStationUser
WHERE tblStationUser.Station = tblStations.Station
AND tblStationUser.UserName = @.varUserName)
ORDER BY Station
OR
SELECT Station
FROM tblStations
WHERE Station NOT IN (SELECT Station
FROM tblStationUser
WHERE UserName = @.varUserName)
ORDER BY Station
Good luck,
Shervin
"KathyB" <KathyBurke40@.attbi.com> wrote in message
news:75e8d381.0310160945.2b2df7e4@.posting.google.c om...
> Hi, I have 2 tables:
> tblStations StationID
> Station
> tblStationUser RecordID
> UserName
> Station
> I'm trying to come up with a dataset that contains the
> tblStations.Station
> EXCEPT for where that Station exists in tblStationUser where the
> UserName = @.varUserName.
> I've tried this but get 0 rows (I should get about 40):
> SELECT tblStations.Station
> FROM tblStations
> WHERE NOT EXISTS
> (SELECT tblStationUser.Station FROM tblStationUser WHERE
> tblStationUser.UserName=@.varUserName)
> ORDER BY Station
> I tried the subquery separately which returns the correct number of
> rows.
> Any clues as to where I'm going wrong?
> Thanks!
> Kathy|||Thanks again Shervin. I wish I knew I fraction of what you do!
Thanks, Kat
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You are welcome Kathy, I'm hanging around here to learn more about SQL
Server. :-)
Shervin
"Kathy Burke" <kathyburke40@.comcast.net> wrote in message
news:3f8fd569$0$193$75868355@.news.frii.net...
> Thanks again Shervin. I wish I knew I fraction of what you do!
>
> Thanks, Kat
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Friday, March 9, 2012
Help using a value from one dataset to lookup a value from a second dataset
Hi All,
I'm quite new to SSRS (and .net development as well) so any help would be useful. My problem is this: I'm running a report from a Sybase ASE datasource to pull data into a table control. One of the fields I'm pulling in is called assignment group. Now, in the same report output I also need to display who the manager of that group is... unfortunately this information is not stored in the same database. We store the manager information in a completely seperate Oracle database. So, I need some method of 'linking' the data from my Sybase dataset to a table in Oracle and I can't quite figure out how to go about it. Every record would have a manager and there could be 10k records returned so I need to be carefull about performance issues.
I was starting to go down the path of creating a .net dataset in a custom assembly which would be filled via a query to the Oracle database. What I was thinking is to create and fill a dataset with my lookup data from Oracle then, in the report, call a function from the table control which would search through the dataset in memory and return the actual manager name.
Am I on the right track or am I overcomplicating the solution? Will this be too inefficient from a performance perspective? Would a subreport be a more efficient solution?
There are a couple of solutions for you:
1) you can write a custom data extension that does the magic merge under the covers. A custom assembly won't help here because it operates on one row at a time - if you have a reasonable nuumber of rows, it might work, but with lots of rows it will probably be inefficient.
2) you can use the Linked server feature of SQL Server. This allows you to build a table/view that uses ODBC connections to other data bases to retrieve data. It has pretty reasonable performance and you don't have to create and maintain custom code. Downside, is you need to add a SQL database in addition to your existing Oracle and Sybase ones :-).
Hope that helps,
-Lukasz