Referencing SQLDataSource In a Sub

I have a SqlDataSource - The select statementselects the DB row with an ID that equals a querystring value... So Iknow I am only selecting one row..

Now I want to create a littleSub that grabs any field(s) of my choice and then I can assign thevalue of it to a textbox on my page or a variable if I need to... Anyidea how I actually reference the fields via the SQLDataSource in asub?

I see you can reference parameters using..

SqlDataSource1.SelectParameters()

I was hoping I could use something similar to get the actual DB fields like the below...

SqlDataSource1.Select("MyDataBaseField").Value = TextBox.Text

Can anyone help please??

Thanks

You need to get the data from the control (gridview,detailsview, etc.) that the sqldatasource is attached to. something like GridView1.Rows[0]...|||

Hmmm I feared that... As I didn't want to use a gridview of details veiw etc...

I'll have a re-think... Thanks

|||

I wrote a blog post about doing what I think you need:

http://peterkellner.net/2006/10/27/smalllistaccessimproved/

just use sqldatasource instead of objectdatasource and the same technique should work.

|||Thanks Peter... I think I was trying to do something that just wasnt possible... I have had a re-think... Thanks for the link though|||

If you don't want to use the data controls, then don't use the sqldatasource.

In your sub, just do your own sqlconnection and command and get your data and assign the values to your controls...

Dim conn as new sqlconnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectString)

dim cmd as new sqlcommand("SELECT field1,field2 FROM MyTable WHEREID=@.ID",conn)

cmd.parameters.add("@.ID",sqldbtype.integer).value=request.querystring("ID")

dim dr as datareader

conn.open

dr=cmd.executereader

dr.read

textbox1.text=dr("field1")

textbox2.text=dr("field2")

dr.close

conn.close

|||

Hi Motley,,,, Thanks for the advice ... I had actually gone down that route on Friday, but had the problem below??

http://forums.asp.net/thread/1468801.aspx

Any ideas? Seems to work fine if I don't use the connection string from my web.config... Even though I use the connection for loads of other pages (SQLDataSources) and its fine? Any ideas? Its driving me nuts

|||Hmmm... Start with the code I posted above?

Referencing sql in IF THEN ELSE Statement

The Background:

From page 1 a search is created and this value is converted to a querystring and given the name "id=" and passed to page 2. Page 2 then does a lookup in the sql 2000 db useing this querystring.

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ListingDBConnectionString %>"SelectCommand="SELECT [ID], [CompName], [Package] FROM [CDetails] WHERE ([ID] = @.ID)"><SelectParameters><asp:QueryStringParameterName="ID"QueryStringField="id"Type="Int32"/></SelectParameters></asp:SqlDataSource>

The Problem:

What I then need to do is an IF THEN ELSE statement.

IF [Package in SQL1] = 4 Then

Package 4

IF [Package in SQL1] = 3 Then

Package 3

Only how do I reference the sql value in the if statement.

I have tried

<%IF Eval("Package") = 4 Then %> and with Bind - No good

Hi asiddle,

If you want to proceed IF then in SQL statement, I suggest you use CASE...WHEN...

You can first put the SelectCommand into a stored procedure, then use CASE...WHEN... to do the switch.

For more information, please check the following link:

http://msdn2.microsoft.com/en-us/library/ms181765.aspx

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Hello Kevin,

Many thanks for your reply. Unfortunately I cant make head nor tails of it. I followed your link to msdn but as usual all its actually done is confused the hell out of me. Is it possible you could drop an example?

Many thanks

|||

Hi asiddle,

I suggest you put the CASE...WHEN into a stored procedure, and take @.ID as parameter.

For example:

SELECT [ID], [CompName], [Package] FROM [CDetails] WHERE [ID] =
CASE
WHEN @.ID>1 Then XXXX
WHEN XXXX Then XXXX

|||

[Package] is an integer field ?

SELECT [ID], [CompName], 'Package ' + convert(varchar(10), [Package]) as [Package_Desc]

FROM [CDetails] WHERE ([ID] = @.ID