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.
-------------------------------------------
"Per W." <pe**@tiscali.nowrote in message
news:ek**********@troll.powertech.no...
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.
