sqldatasource.select.

  • Thread starter Thread starter Per W.
  • Start date Start date
P

Per W.

Hi, is there som other way to do this? i only want the result from the
select statement so i can use it in my code. I dont need the gridview, but
this is the only way i got it to work.
The SqlDataSource component is on the .aspx page and my code is on the
aspx.vb page



Protected sub in the aspx.vb page

dim gridview1 as new gridview

SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
SqlDataSource1.SelectCommand = "MyProc"
gridview1.DataSource = SqlDataSource1
gridview1.DataBind()
value = gridview1.Rows(0).Cells(0).Text


/Per W.
 
If you want to return a table:

Dim dt As DataTable
Using cnn As New SqlConnection(My.Settings.PTConnectionString)
cnn.Open()

'define the command
Dim cmd As New SqlCommand
cmd.Connection = cnn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "ProductRetrieveByID_sp"
cmd.Parameters.AddWithValue("@productid", 1)

'define the data adapter and fill the data table
Dim da As New SqlDataAdapter(cmd)
dt = New DataTable
da.Fill(dt)
End Using

Dim ProductID as Integer
Dim ProductName as String
With dt.Rows(0)
ProductID = CType(.Item("ProductID"), Integer)
ProductName = .Item("ProductName").ToString
End With

If you have multiple rows:

For Each dr As DataRow In dt.Rows
Dim ProductID As Integer = CType(dr.Item("ProductID"), Integer)
Dim ProductName As String = dr.Item("ProductName").ToString
Dim ProductNumber As String = dr.Item("ProductNumber").ToString
Dim Description As String = dr.Item("Description").ToString
Console.WriteLine(String.Format("ProductID {0}, " & _
"ProductName {1}, {2}ProductNumber {3}, " & _
"Description {4}", ProductID, ProductName, _
ControlChars.CrLf, ProductNumber, Description))
Next


There is also a method called ExecuteScalar that returns
1 row, 1 column, to be used when you're only going to
return 1 value. This is executed on the command object.

Using cnn As New SqlConnection(My.Settings.PTConnectionString)
cnn.Open()
Dim cmd As New SqlCommand
cmd.Connection = cnn
cmd.CommandText = "SELECT TOP 1 ProductID FROM Product"
Dim ProductID As Integer = DirectCast(cmd.ExecuteScalar, Integer)
Console.WriteLine("ProductId = {0}", ProductID.ToString)
End Using


Hope that helps.
Robin S.
 
Back
Top