Retrieve Data to Populate Text Boxes

[dukeofawesome]

Hi,

I am having an issue where for some reason the following code only returns the ID but no other fields. Can someone see where the problem lies?

Dim lstVal As String = lstUSerID.SelectedValue.ToString
        Dim sstrSQL As String
        Dim cls As New clsGlobals

        sstrSQL = "SELECT * FROM tblUsers WHERE ID=" & NZ(lstVal, 0)

        dbs.ConnectDB()
        ds = dbs.sqlSelect(sstrSQL)

        If ds.Tables("Results").Rows.Count > 0 Then
            With ds.Tables("Results").Rows(0)
                txtID.Text = .Item("ID").ToString
                txtUsername.Text = .Item("Username").ToString
                txtFirstName.Text = .Item("UserFirstName").ToString
                txtLastName.Text = .Item("UserLastName").ToString
                txtAddressLine1.Text = .Item("UserAddress1").ToString
                txtAddressLine2.Text = .Item("UserAddress2").ToString
                txtCity.Text = .Item("UserCity").ToString
                txtState.Text = .Item("UserState").ToString
                txtPostcode.Text = .Item("UserPostcode").ToString
                txtPhone.Text = .Item("UserPhone").ToString
                txtMobile.Text = .Item("UserMobile").ToString
                txtEmail.Text = .Item("UserEmail").ToString
            End With
        End If

        dbs.CloseDB()

Many thanks...

 

[jmcilhinney]

Firstly, what does that actually mean? Are you saying that each field in the DataRow other than in the ID column is NULL? If so then that's what you should say. Are you saying that an exception is thrown somewhere? If so then that's what you should say. Also, that code assumes that your `dbs` object is implemented correctly but we have no idea if that it the case.

What you should be doing is debugging the code and pinpointing the exact location where what actually happens differs from what you expect. You can then look at the values of all variables, etc, immediately before and immediately after that step. That's how you get a clear picture of what's going on, not simply by reading part of the code without any idea of what data is being used. It's very often the case that code is perfectly correct for the majority of cases but doesn't work for specific data but it's very hard to see that unless you actually run it with that data. Debug the code and come back when you can provide a more detailed description of the issue.

 

[dukeofawesome]

Are you saying that each field in the DataRow other than in the ID column is NULL?

Yes that's what I mean sorry. Every field is empty other than the ID column.

I have stepped through the code and can't see why this is ocurring. The dbs object is created in a class like so:

Public Class clsDatabase
    Dim cnn As New OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String

    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    Sub ConnectDB()
        dbProvider = My.Settings.DatabaseCon '"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\cadata.accdb;" 
        'MsgBox(My.Settings.DatabaseCon)
        cnn.ConnectionString = dbProvider
        cnn.Open()
    End Sub
    Sub CloseDB()
        cnn.Close()
        cnn.Dispose()
    End Sub

And is referenced from from the form itself like so:

Dim dbs As New clsDatabase
    Dim ds As New DataSet

Is there a problem with the way it's set up that you can see?

 

[jmcilhinney]

The critical line of code is where you call Fill on your data adapter and we haven't even seen that, suggesting that you haven't actually looked at it in relation to this issue. That's where the data is retrieved from the database and the DataTable is populated. The very first thing you should be doing is placing a breakpoint on that line. When execution breaks you test everything relevant to make sure that the app state is what you expect. You then step to the next line and test again. If everything is not as you expect at that point then either there's something wrong with your expectations or something is broken. Are you looking at the right DataTable? Are you looking at the right DataRow? Are you executing the right SQL? Etc, etc, etc.

 

[dukeofawesome]

I have tried everything you have suggested. Here is where the dataset gets filled:

Function sqlSelect(ByVal strSQL As String)
        da = New OleDbDataAdapter(strSQL, cnn)
        da.Fill(ds, "Results")
        Return ds

    End Function

 

[jmcilhinney]

I have tried everything you have suggested. Here is where the dataset gets filled:

Function sqlSelect(ByVal strSQL As String)
        da = New OleDbDataAdapter(strSQL, cnn)
        da.Fill(ds, "Results")
        Return ds

    End Function

Does your DataSet contain a DataTable named "Results" before that call to Fill? If so, does that DataTable contain any DataRows? What is the exact value of `strSql`?

Also, why does your function not a return type specified? You must have Option Strict Off for that to even compile so, apart from anything else, you should turn that On and then fix all the issues that arise.

 

[dukeofawesome]

Geez I'm an idiot. I had opened another instance of "Results" in a list box. My God what a tool I am!