Adding external data into database data before it is sent to Control (Repeater, Datagrid, etc.)

13 Nov 2005 4:33 AM

Neo Geshel

I am looking to add additional data into a "stream" that has been
extracted from a database, but before it is sent to a control (Repeater,
in this case).

I have found ZERO (0) articles about this on the Internet. Anyone care
to point me in the right direction? I am not looking for code that
rivals Gray's Anatomy in complexity, just something that can add a
static variable in a line or two of code (at the most).

My script so far looks like this:

   Dim myConn as New
OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
   Dim myCmd_img as New OleDbCommand("SELECT [ID], [Comment] FROM [tbl"
& strSlave & "] WHERE [" & strMaster & "ID] = " & intMaster & " AND [ID]
= " & intSlave, myConn)
   myConn.Open()
   Img.DataSource = myCmd_img.ExecuteReader(CommandBehavior.CloseConnection)
   Img.DataBind()
   myConn.Close()

What I want to do is add the table name, contained within the string
strSlave, into the data extracted from the table. I want to assign it a
variable of "Table". That way, I can call it by its variable in the
Repeater.

I do not want to add the name of the table TO the database, I just want
to add it to the stream of data extracted FROM the database. Ideally, it
would be something as simple as this:

Img.Items.Insert(0, new Item(strSlave, "Table"))

But this doesn't work.

TIA,
...Geshel

--
***********************************************************************
* My reply-to is an automatically monitored spam honeypot. Do not use *
* it unless you want to be blacklisted by SpamCop. Please reply to my *
* first name at my last name dot org. *
***********************************************************************
â€œAnyone who believes in Intelligent Design (â€œcreationismâ€) is just as
ignorant and ill-educated as someone who believes that the world is
flat, that the Sun circles the Earth or that there really is a tooth
fairy. Darwinism has an overwhelming foundation of evidence that can be
tested and reproduced. Intelligent Design, on the other hand, has no
evidence at all; not one single shred of testable proof. As such,
Intelligent Design is Religious Mythology, and has no right whatsoever
to be in our Science classrooms.â€ - 99.99+% of Scientists
***********************************************************************
Mignon McLaughlin once said that â€œA nymphomaniac is a woman [who is] as
obsessed with sex as the average man.â€ Unfortunately, since true
nymphomaniacs are so rare, this means that it takes an extraordinary
woman to keep up with an ordinary man.
***********************************************************************

13 Nov 2005 5:58 AM

Chris Priede

Hi,

Neo Geshel wrote:

> Dim myCmd_img as New OleDbCommand("SELECT [ID], [Comment] FROM
> [tbl" & strSlave & "] WHERE [" & strMaster & "ID] = " & intMaster & "
> AND [ID] = " & intSlave, myConn)

> I do not want to add the name of the table TO the database, I just want
> to add it to the stream of data extracted FROM the database.

You can achieve exactly what you want by adding to your SQL query. You can
SELECT not just table columns, but also literal values: nothing stops you
from doing the following:

Dim myCmd_img as New OleDbCommand("SELECT 'tbl" & strSlave & "' AS [Table],
[ID], [Comment] FROM
[tbl" & strSlave & "] WHERE [" & strMaster & "ID] = " & intMaster & "
AND [ID] = " & intSlave, myConn)

I hope I did the VB concatenation right (I am not a VB programmer). The
SQL we are going for is: "SELECT 'tblWhatever' AS [Table], ..." -- note the
single quotes around the literal value. This will return an additional
column named "Table" and containing the given value, for each returned row.

--
Chris Priede

P.S. I hate concatenated queries -- consider using parameters. :)

17 Nov 2005 12:53 AM

Neo Geshel

Chris Priede wrote:
Show quoteHide quote

> Neo Geshel wrote:
>> Dim myCmd_img as New OleDbCommand("SELECT [ID], [Comment] FROM
>>[tbl" & strSlave & "] WHERE [" & strMaster & "ID] = " & intMaster & "
>>AND [ID] = " & intSlave, myConn)
>
>>I do not want to add the name of the table TO the database, I just want
>>to add it to the stream of data extracted FROM the database.
>
> You can achieve exactly what you want by adding to your SQL query. You can
> SELECT not just table columns, but also literal values: nothing stops you
> from doing the following:
>
> Dim myCmd_img as New OleDbCommand("SELECT 'tbl" & strSlave & "' AS [Table],
> [ID], [Comment] FROM
> [tbl" & strSlave & "] WHERE [" & strMaster & "ID] = " & intMaster & "
> AND [ID] = " & intSlave, myConn)
>
> I hope I did the VB concatenation right (I am not a VB programmer). The
> SQL we are going for is: "SELECT 'tblWhatever' AS [Table], ..." -- note the
> single quotes around the literal value. This will return an additional
> column named "Table" and containing the given value, for each returned row.
>

Actually, this works beautifully, I just had to add my values after any
SQL SELECT operations (such as â€œSELECT TOP 1â€) and before the FROM.
Otherwise Access chokes on the statement.

Thanks.
...Geshel

--
***********************************************************************
* My reply-to is an automatically monitored spam honeypot. Do not use *
* it unless you want to be blacklisted by SpamCop. Please reply to my *
* first name at my last name dot org. *
***********************************************************************
â€œAnyone who believes in Intelligent Design (â€œcreationismâ€) is just as
ignorant and ill-educated as someone who believes that the world is
flat, that the Sun circles the Earth or that there really is a tooth
fairy. Darwinism has an overwhelming foundation of evidence that can be
tested and reproduced. Intelligent Design, on the other hand, has no
evidence at all; not one single shred of testable proof. As such,
Intelligent Design is Religious Mythology, and has no right whatsoever
to be in our Science classrooms.â€ - 99.99+% of Scientists
***********************************************************************
Mignon McLaughlin once said that â€œA nymphomaniac is a woman [who is] as
obsessed with sex as the average man.â€ Unfortunately, since true
nymphomaniacs are so rare, this means that it takes an extraordinary
woman to keep up with an ordinary man.
***********************************************************************