none
Having issues with SqlDataAdapter.Fill, but SqlDataReader works fine? RRS feed

  • Question

  • Hello,

      I am having trouble filling a SqlDataSet via SqlDataAdapter.Fill in VB.NET, however using SqlDataReader I am able to pull data.  I can accomplish my task using SqlDataReader, but am terribly curious as to why I can't get SqlDataAdapter.Fill to work.

    My SqlDataAdapter.Fill code:

    Dim sConnection As String = "Server=192.168.1.10\SQLEXPRESS;Database=mydb;UID=user;PWD=password;" Dim objDataAdapter As SqlDataAdapter = New SqlDataAdapter("Select * FROM mytable", sConnection) Dim dsResult As New DataSet

    objDataAdapter.Fill(dsResult, "mytable") objDataAdapter.Dispose()

    I put a breakpoint at objDataAdapter.Dispose() and look at the current value of dsResult - it has 0 length.

    My SqlDataReader code:

    Dim objConnection As SqlConnection = New SqlConnection("Server=192.168.1.10\SQLEXPRESS;Database=mydb;UID=user;PWD=password;")
    Dim objCommand As SqlCommand = New SqlCommand("Select * FROM mytable", objConnection)
    objConnection.Open()
    Dim drReader As SqlDataReader = objCommand.ExecuteReader

    When using the SqlDataReader, drReader.HasRows is True, and I can pull the data just fine.

    What am I missing?  Thank you for any help.



    • Edited by George Brian Monday, March 25, 2013 8:36 PM
    • Moved by Bob Beauchemin Monday, March 25, 2013 10:19 PM Moved to the appropriate forum for SqlClient client-side issues
    Monday, March 25, 2013 8:11 PM

Answers

  • I've solved my problem. For anyone who encounters this same issue, here is the updated code that (for reasons unbeknownst to me) is working:

    Dim objConnection As SqlConnection = New SqlConnection("Data Source=192.168.1.10\SQLEXPRESS;Database=mydb;User ID=user;Password=password;")
    Dim objSelectCMD As SqlCommand = New SqlCommand("SELECT * FROM mytable", objConnection)
    Dim objDataAdapter As SqlDataAdapter = New SqlDataAdapter
    
    objDataAdapter.SelectCommand = objSelectCMD
    objConnection.Open()
    
    Dim objDataSet As DataSet = New DataSet
    objDataAdapter.Fill(objDataSet, "mytable")
    objConnection.Close()

    Then, to populate a ComboBox with the DataSet:

    myComboBox.DataSource = objDataSet.Tables("mytable")
    myComboBox.ValueMember = "Column name to use for value"
    myComboBox.DisplayMember = "Column name to display"

    I hope this helps someone.  I tried dozens of examples, and am not sure exactly what tipped the scales in my favor here.

    It is also helpful, for testing purposes, to use this code within myComboBox's parent form.


    Monday, March 25, 2013 9:42 PM

All replies

  • I've solved my problem. For anyone who encounters this same issue, here is the updated code that (for reasons unbeknownst to me) is working:

    Dim objConnection As SqlConnection = New SqlConnection("Data Source=192.168.1.10\SQLEXPRESS;Database=mydb;User ID=user;Password=password;")
    Dim objSelectCMD As SqlCommand = New SqlCommand("SELECT * FROM mytable", objConnection)
    Dim objDataAdapter As SqlDataAdapter = New SqlDataAdapter
    
    objDataAdapter.SelectCommand = objSelectCMD
    objConnection.Open()
    
    Dim objDataSet As DataSet = New DataSet
    objDataAdapter.Fill(objDataSet, "mytable")
    objConnection.Close()

    Then, to populate a ComboBox with the DataSet:

    myComboBox.DataSource = objDataSet.Tables("mytable")
    myComboBox.ValueMember = "Column name to use for value"
    myComboBox.DisplayMember = "Column name to display"

    I hope this helps someone.  I tried dozens of examples, and am not sure exactly what tipped the scales in my favor here.

    It is also helpful, for testing purposes, to use this code within myComboBox's parent form.


    Monday, March 25, 2013 9:42 PM
  • Hi  George,

    Welcome to the MSDN forum.

    Glad to know your problem is solved. Thank you for sharing.

    Good day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 27, 2013 1:45 AM