locked
Accessing Data in Dataset RRS feed

  • Question

  • I am new to Visual Basic. I am trying to create a SQL Select in Visual Basic 2005. The code I have so far is:

     Dim conn As New SqlConnection ("Server=.\SQLEXPRESS;Database=inv_sys;Trusted_Connection=True;")

     Dim ds As New DataSet

     conn.Open()
     Dim adp As New SqlDataAdapter("Select Max(idx_holding) from tbl_holding", conn)

     adp.Fill(ds)

     conn.Close()

    How do I get the information out of the dataset. In this case it should be a single value but in other case I need to get mutliple records.

    Any example would be appreciated.

    Thanks in Advance

     

     

     

    Friday, September 29, 2006 12:13 PM

Answers

  • you simply look at the DataTable rows/columns:

    Dim theValue as Object = ds.Tables(0).Rows(index)(ColumnName)

    If you are retrieving a single value, the way you have done so is very ineffecient - to retrieve a single value I would recommend to use SqlDataReader (fast forward only reader) or ExecuteScaler() which will get the first column of the first row and ignore the rest of the data returned

    Friday, September 29, 2006 12:37 PM
  • Hello Heimdaloz

     

    Assuming that you actually want a scaler value back as in your example; the following code would be the most efficient.

    Private Function GetMaxValue() As Integer

    Dim MyConn As SqlClient.SqlConnection = Nothing

    Dim MyCMD As SqlClient.SqlCommand = Nothing

    Dim Mycount As Integer

    Try

    MyConn = New SqlClient.SqlConnection("YOUR CONNECTION STRING")

    MyCMD = New SqlClient.SqlCommand

    MyConn.Open()

    With MyCMD

    .CommandType = CommandType.Text

    .Connection = MyConn

    .CommandText = "Select Max(idx_holding) from tbl_holding"

    Mycount = .ExecuteScalar

    End With

    Catch ex As Exception

    MsgBox(ex.Message)

    Finally

    If MyConn IsNot Nothing AndAlso MyConn.State <> ConnectionState.Closed Then

    MyConn.Close()

    MyConn.Dispose()

    End If

    End Try

    Return Mycount

    End Function

    Friday, September 29, 2006 3:18 PM
  • Thanks Ibrahim Y

    That is exactly what I was after. I have search on the internet and via numerous Visual Basic books without success so appreciate the help.

    I now have a problem with my connection string but otherwise it seems to be what I was after.

     

    Saturday, September 30, 2006 11:08 AM

All replies

  • you simply look at the DataTable rows/columns:

    Dim theValue as Object = ds.Tables(0).Rows(index)(ColumnName)

    If you are retrieving a single value, the way you have done so is very ineffecient - to retrieve a single value I would recommend to use SqlDataReader (fast forward only reader) or ExecuteScaler() which will get the first column of the first row and ignore the rest of the data returned

    Friday, September 29, 2006 12:37 PM
  • Hello Heimdaloz

     

    Assuming that you actually want a scaler value back as in your example; the following code would be the most efficient.

    Private Function GetMaxValue() As Integer

    Dim MyConn As SqlClient.SqlConnection = Nothing

    Dim MyCMD As SqlClient.SqlCommand = Nothing

    Dim Mycount As Integer

    Try

    MyConn = New SqlClient.SqlConnection("YOUR CONNECTION STRING")

    MyCMD = New SqlClient.SqlCommand

    MyConn.Open()

    With MyCMD

    .CommandType = CommandType.Text

    .Connection = MyConn

    .CommandText = "Select Max(idx_holding) from tbl_holding"

    Mycount = .ExecuteScalar

    End With

    Catch ex As Exception

    MsgBox(ex.Message)

    Finally

    If MyConn IsNot Nothing AndAlso MyConn.State <> ConnectionState.Closed Then

    MyConn.Close()

    MyConn.Dispose()

    End If

    End Try

    Return Mycount

    End Function

    Friday, September 29, 2006 3:18 PM
  • Thanks Ibrahim Y

    That is exactly what I was after. I have search on the internet and via numerous Visual Basic books without success so appreciate the help.

    I now have a problem with my connection string but otherwise it seems to be what I was after.

     

    Saturday, September 30, 2006 11:08 AM