Getting a single value from SqlDataSource

Hi,

I have a SqlDataSource(named SQLDS1) which retrieves 4 value from database(ProductName,ProductCost,ProductID). I Have a DropDownList(DDL1) control and its DataSource SQLDS1.

DDL1 Selected data field todisplay is ProductName
DDL1 Selected data field tovalue is ProductCost

I did all this in Visual Part without any line of code. But in the code behind , When i select an item from DDL1 i need its ProductName,ProductCost and Also ProductID. It is simple to get first two. But how can i get the ProductID. Is there anyway to get ProductID from SQLDS1.

Happy Coding

Hi

You will be able to obtain only the values that you bind to the drop down list. if you want non bound value then you will have to fire another query based on what you select from the drop down.

For example,

select productid from Table_name where product name = 'prodname you selected from ddl' and productcost= cost you got from ddl.

Then use productid as you need

HTH

Get value from datasource in codebehind

Lets say I have a Sqldatasource that uses the following SelectCommand="SELECT category,name FROM table". How do I get the value on category from my datasource in code behind if I know that my selectcommand always will return one row? Can I write something like datasource.items["category"].Value?

Thanks for your help!

You can retrieve the value from your datasource from either dataview or datareader. Here is a sample for your reference:

You can access your SqlDataSouce from code behind through a dataview or datareader by calling select() of the SqlDatasource. If theDataSourceMode property of the SqlDatasource is set to DataSet and you get the dataview(this is default), or a DataReader if it is set to DataReader.

'Programmatically access the SqlDataSource - get back a DataView
Dim dview As DataView = CType(yourSqlDataSource.Select(DataSourceSelectArguments.Empty), DataView)

Dim str1 as string = String.Empty

For Each drow As DataRow In dview.Table.Rows

str1 &= drow("yourcol1").ToString() & "<br />"

NEXT

Or through a datareader ( don't forget to set DataSourceMode property to DataReader)

Dim myreader as SqlDataReader=CType(rndProductsDataSource.Select(DataSourceSelectArguments.Empty), SqlDataReader)

Dim str1 as string=String.Empty
if myreader.Read()
str1= reader(0) ' or your first column name

else

end if

myreader.Close()

GET SCOPE_IDENTITY ON INSERCOMMAND

Hello,

I need to get the scope_identity value after the insert, but I couldn't see how when using the InsertCommand of SqlDataSource. I think I can try using ADO commands, doing it 'mannually', but how?

If I execute the insertcommand and after select, I don't have the value, it only exists 'when inserting'. So, I need to keep that value. It's possible to do it with a session on sql or how?

I'm really newba.

Thanks

Search the forums, this has been discussed a half dozen times already.|||

Motley wrote:

Search the forums, this has been discussed a half dozen times already.

I saw, but I need something like that:

sqlComm.CommandText ="INSERT INTO S_CUSTOMER(C_NAME, C_RECDEL) VALUES ('" & name.Text &"',0); DECLARE @.C_Customer_Id VARCHAR(50); SET @.C_Customer_Id = SCOPE_IDENTITY;"

I need to get the scope_identity after the insert. If I execute a select command after, I don't get anything. I want to know if I can get the @.C_Customer_Id, declarated on the sql command.

|||

JPope

I had your problem and realized that the only way to get the value back was to do it the "old fashioned" way! I actually use @.@.Indentity which I know is more prone to problems than scope_identity but below is some code that I have used many times and found it works fine for me.

By the way, I am not the world's best programmer - so you may be able to refine some of this down to make it a bit more glamorous!

'Set up the objects and variable

Dim connAsNew SqlConnection()

Dim commAsNew SqlCommand()

Dim adpAsNew SqlDataAdapter()

Dim datasetAsNew DataSet

Dim recordidentityAs String

'Create our INSERT command - do NOT put the identity line in here -

comm.CommandText ="INSERT INTO [Table] ([Field1], [Field2], etc...) VALUES (Some values)"

'set the connection up and open it - use the connection name from web.config - in this case I have called it MyConnection, subsitute yours in its place

conn.ConnectionString = ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString

conn.Open()

'set the insert command up

adp.InsertCommand =New SqlCommand(comm.CommandText, conn)

'execute the insert command

adp.InsertCommand.ExecuteNonQuery()

'now that we have completed the insert command but NOT closed the connection we can set up the select command - in your case it will be SELECT scope_identity as indent

comm.CommandText ="SELECT @.@.Identity as Ident"

adp.SelectCommand =New SqlCommand(comm.CommandText, conn)

adp.Fill(dataset,"Ident")

'Create a datatable to get the data from the first table in the dataset - referred to as "0"

Dim IdentityTableAs DataTable = dataset.Tables(0)

'create a reader for use with the loading in of data

Dim readerAs DataTableReader = dataset.CreateDataReader

'load in data

IdentityTable.Load(reader)

'get a row that we can use - once again the first row referred to as "0"

Dim rowAs DataRow

row = IdentityTable.Rows.Item(0)

'get the inserted record id back as a string so we can use it in another command if required

RecordIdentity = row(0).ToString()

Hope this works - let me know!

Stuart

Get PK for inserted record in SQLdatasource

I have a table named invoice that contains the following columns

-invoiceno - Primary key and is set to autonumber
-customerno
-incoicedate

and on my VB code i did the following InsertCommand

SqlDataSource1.InsertCommand = "INSERT INTO invoice(customerno, invoicedate) VALUES('" & Session("UID") & "', GetDate()) "
SqlDataSource1.Insert()

My Question is how do i get the Primary Key Value it generated during the insert operation(invoice['incoiceno'])? Besides the creationg a stored procedere like the one in the MSDN Library

Well, since you really aren't using the sqldatasource as a datasource, just do it manually.

Dim conn as new sqlconnection("{Your connect string}")
conn.open
dim cmd as new sqlcommand("INSERT INTO invoice(customerno, invoicedate) VALUES(@.customerno,getdate()) SELECT @.newid=SCOPE_IDENTITY()",conn)
cmd.parameters.add("@.customerno",sqldbtype.uniqueidentifier).value=session("UID")
cmd.parameters.add("@.newid",sqldbtype.int).direction=output
cmd.executenonquery()
dim newid as integer=cmd.parameters("@.newid").value
conn.close

I believe you can also try to create a parameter on the insert with a direction of output as well, but I think you have to actually pull the value in sqldatasource1_inserted by referencing e.command.parameters("@.newid").value, but you can try and see if you can pull it directly after your insert too.

Get number of results from SqlDataSource

Hi all,

Is there a way of counting the number of results/rows from an SqlDataSource which uses a select statement like: "SELECT * FROM TABLE1".

I need to get this value from the SqlDataSource into theSub Page_Load.

I don't want to bind to any gridviews, repeaters etc. I just want to get the number of rows/results from the SqlDataSource.

Is there a way of doing this?

Thanks

Public cnt as integerProtected Sub SqlDataSource1_Selected(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.Selected cnt = e.AffectedRowsEnd Sub

|||

Thanks, that helped. However I need to get this value many times throughout my sub page_load.

I see this value is calculated when SqlDataSource1 is selected. Is there a way to get this value each time I change the default value of a select parameter, or simply call this sub SqlDataSource1_Selected from the Page_load. Since when I change a select parameter, this then affects the select command statement which is when I need to re-calculate the number of rows (get value of integer cnt).

Im guessing I need to change theHandles SqlDataSource1.Selected to something else?

How could I get this working?

Thanks.

|||

using jMacs code you can then access the Variable cnt in your pageload.

The scope of the variable cnt allows you to access it and use it through out the life of the page in your Page_Load sub, or any other method for that matter. It will be set anytime SqlDataSource1_Selected event is called.

Public cnt as integerProtected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load'//work with the varialbe cnt here.End SubProtected Sub SqlDataSource1_Selected(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.Selected cnt = e.AffectedRowsEnd Sub

|||

Exactly. Here's some more info on the SqlDataSource.Selected Event -http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selected(vs.80).aspx