Adding a column during SQL data retrieval

[Fion]

Hi all,
I've got a problem that seems like it should be a pretty simple one, but I can't get it going... I'm hoping someone on here can help me out.
The essential issue is this:
I am returning rows from 3 seperate databases (although each database is set up identically, same columns and such, they have different records).
What I have been doing is, each time I return rows from one of the three databases, I add them into the table that is the source for my datagridview object. This works fine. My issue is that I cannot differentiate which record came from which database, especially if I sort them in some way. I do have a blank column in the datagridview, that I would like to fill in with the database name for each record, but I'm not sure how to get that working correctly. Is there some way to add a column to the reader object that is automatically populated with a specific word (the database name)? Or maybe to perform an action on the most recently added rows to the table?
I have tried to run through the table each time and fill in the database name for all records that have nothing in that column, but that causes problems...
Anyone have any ideas as to how I can get this going?
Thanks!

Fion

 

[TechGnome]

What do your queries look like?

-tg

 

[Fion]

The SQL query is below. The only thing that changes between the 3 databases is the database name, every other part of the query is exactly the same.

cmd.CommandText = "SELECT vMachines.MachineName, vMachines.MachineStateName, vMachines.CurrentRunID, vMachines.ReservedForRunID, vMachines.ReservedBy, vMachines.ReservedDate, vMachines.LabID, vMachines.MachineTypeName FROM Data.dbo.vMachines vMachines WHERE (vMachines.ReservedBy Is Not Null) AND (vMachines.LabID=5) AND (vMachines.MachineTypeName Like '%client%')"

 

[TechGnome]

[vbcode]
cmd.CommandText = "SELECT 'DatabseName1' AS DBNAme, vMachines.MachineName, vMachines.MachineStateName, vMachines.CurrentRunID, vMachines.ReservedForRunID, vMachines.ReservedBy, vMachines.ReservedDate, vMachines.LabID, vMachines.MachineTypeName FROM Data.dbo.vMachines vMachines WHERE (vMachines.ReservedBy Is Not Null) AND (vMachines.LabID=5) AND (vMachines.MachineTypeName Like '%client%')"
Reply With Quote
[/vbcode]

If you want to make it a bit more dynamic... you could use a variable:
[vbcode]
cmd.CommandText = "SELECT '" & strDBName & "' AS DBName, vMachines.MachineName, vMachines.MachineStateName, vMachines.CurrentRunID, vMachines.ReservedForRunID, vMachines.ReservedBy, vMachines.ReservedDate, vMachines.LabID, vMachines.MachineTypeName FROM Data.dbo.vMachines vMachines WHERE (vMachines.ReservedBy Is Not Null) AND (vMachines.LabID=5) AND (vMachines.MachineTypeName Like '%client%')"
Reply With Quote
[/vbcode]
just change the variable value between calls. Then your data will contain the name of the database in it....

-tg