Reading value from database and displaying in a text box

[leyla]

Hi again,

I'm writing a code to retrieve the StudentID from a table in my database and then display it in a textbox. The code to retrieve the ID is on one form and I want the value to display in the textbox in another form. The code that I'm using does not display an error but the value is not displayed in the textbox. This is the code I'm using, it could be completely wrong but I'm not sure:

  Dim SubjectHomepage As New SubjectHomepage
        SubjectHomepage.Show()
        Me.Close()
        provider = "Provider=Microsoft.ACE.OLEDB.12.0;"   
        dataFile = "Data Source=|DataDirectory|\UserDatabase.accdb"    
        connString = provider & dataFile        

        Using conn As OleDbConnection = New OleDbConnection(connString)

            Dim cmd As New OleDbCommand( _
                "SELECT StudentID FROM Users " & _
                "WHERE Username='" & LogIn.UsernameTextBox.Text & "'", conn)


            conn.Open()
            SubjectHomepage.StudentIDTextBox.Text = cmd.ExecuteScalar()
            conn.Close()
        End Using

Thanks in advance.

[djjeavons]

Hi

Are you sure that you are returning a value? Try adding:

MessageBox.Show(cmd.ExecuteScalar().ToString

Before assiging it to the Text Box to see if a value is actually returned.

HTH

[leyla]

Hi,

Thanks for the reply. When I insert that line of code I receive the following error:

An unhandled exception of type 'System.NullReferenceException' occurred in RevisionAid2.exe

Additional information: Object reference not set to an instance of an object.

[djjeavons]

Hi

That would suggest that no data is being returned which is inline with your original problem. I would suggest that you put the breakpoint on your conn.Open line and when it hits, goto the immediate window (instructions below if you can't find this) and enter:

"SELECT StudentID FROM Users " & _
                "WHERE Username='" & LogIn.UsernameTextBox.Text & "'"

Then take the resulting statement and in your database, create a new query, switch to SQL view and paste the statement and run it. You will probably find that no results are returned.

To access the immediate window, when the breakpoint has hit, goto View > Other Windows and select Command Window. Then type immed and the immediate window will appear.

HTH

[leyla]

This may seem silly but I cannot find Command Window. I am using Visual Studio Express 2012.

[Santanu.Das]

The codeblocks never returns any value. After Closing a form how could you think that the next codelins would be executed.

You have already closed the LogIn form by calling Me.Close(). After execution of this line nothing could be executed.

Your codes are quite right but some modification are needed.

Dim SubjectHomepage As New SubjectHomepage

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\UserDatabase.accdb;"        

Using conn As OleDbConnection = New OleDbConnection(connString)

    Dim cmd As New OleDbCommand( _
            "SELECT StudentID FROM Users " & _
            "WHERE Username='" & LogIn.UsernameTextBox.Text & "'", conn)
    conn.Open()

    Dim rd As OleDbDataReader = cmd.ExecuteReader()

    SubjectHomepage.Show()
    SubjectHomepage.StudentIDTextBox.Text = rd.Items("StudentID")
    conn.Close()

End Using

Me.Close()

Hope it can help you.

[djjeavons]

This may seem silly but I cannot find Command Window. I am using Visual Studio Express 2012.

I am not sure about the Express edition although I would imagine it is the same. If you select the "View" menu and then "Other Windows" you will see an entry called "Command Window". When the Command Window is open type immed and this will open the immediate window.

After Closing a form how could you think that the next codelins would be executed.

This was my first impression when seeing the original code and I did a check (below) which led me to believe that this was not the problem. If the form being closed is the main form (the one set as startup form in the properties) then yes, you are absolutely right. However, if it isn't then the code will still run.

Consider this scenario, a standard Windows Forms project with three forms (Form1, Form2 and Form3).

In Form1, you load Form2:

    Dim form2 As New Form2
    form2.Show()

In Form2, you load Form3 and close Form2 but have a message box statement after the Close call:

    Dim form3 As New Form3
    form3.Show()

    Me.Close()

    MessageBox.Show("Hello from Form2")

You will note that Form2 is closed and Form3 is displayed and the MessageBox statement is also executed.

However. If Form2 has a TextBox and you want to show the value of that TextBox in the MessageBox then the MessageBox will be empty as the form is closed even though the MessageBox is still displayed.

So moving the Me.Close() statement to the end as you have suggested should fix the problem using either your recommended method or the original post as ExecuteScalar will return the first value in the first row and column and as only the StudentID is being returned (for what I hope is a unique user name) then this would also work.

[leyla]

The startup form does not close because, as you suggested, this cleared the textbox. It is left open and I left the value in the textbox however I get the error message:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: No data exists for the row/column.

There is definitely a value in the database because I can see it but I'm not sure how to fix it?

[djjeavons]

Hi

Can you output the value of your generated SQL statement (either in the immediate window) or if you are still having difficulty finding this, try writing it to the output window using:

Console.WriteLine("SELECT StudentID FROM Users WHERE Username='" & LogIn.UsernameTextBox.Text & "'")

You will find the result in the Output window. Alternatively do it to a MessageBox

MessageBox.Show("SELECT StudentID FROM Users WHERE Username='" & LogIn.UsernameTextBox.Text & "'")

The main purpose here is to see what the SQL statement looks like as it really does seem that you are not getting any data returned.

Also, which code are you using now? If you are using the code suggested by Shark_1 then you can test to see if the DataReader has any data by using the HasRows property:

MessageBox.Show(rd.HasRows())

[leyla]

I am using the code that Shark_1 suggested, so I placed the MessageBox line in the code with the HasRows line and the message box returned 'False'

[djjeavons]

So that means that you are not getting any data returned. Try What I suggested by outputting your SQL statement and running this in Access to see if it is correct.

[leyla]

I placed the query in Access, typed in the value that I am searching for and the StudentID was returned, so I know that the SQL works and is correct

[djjeavons]

Ah, I see the problem.

The code that you are now using does not initialise the Read on the Reader which is required to start reading. So you have two options. If your user names are unique then you can revert to your ExecuteScalar option which works fine (I have tested), or if you continue with your current code then change it slightly too:

    Dim rd As OleDbDataReader = command.ExecuteReader

    Do While rd.Read
        SubjectHomepage.StudentIDTextBox.Text = rd("StudentID")
    Loop

[leyla]

I left the textbox in the code to see if the result that I got and I still received 'False'?

[djjeavons]

Can you post your full code for the routine as it is now please.

[leyla]

Dim SubjectHomepage As New SubjectHomepage

        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\UserDatabase.accdb;"

        Using conn As OleDbConnection = New OleDbConnection(connString)
            conn.Open()
            Dim cmd As New OleDbCommand( _
                "SELECT StudentID FROM Users " & _
            "WHERE Username='" & LogIn.UsernameTextBox.Text & "'", conn)


            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            MessageBox.Show(rd.HasRows())
            SubjectHomepage.Show()
            Do While rd.Read
                SubjectHomepage.StudentIDTextBox.Text = rd("StudentID")
            Loop

        End Using

        conn.Close()

[djjeavons]

Can you put MessageBox.Show(LogIn.UsernameTextBox.Text) in your code after the check for rd.HasRows() to see if there is a value there. The code you have now is fine so I am wondering if you really do have a value in the UserNameTextBox at this point.

[leyla]

You seem to be correct, the message box came up blank. However, since I kept the form open and did not clear the textbox, I can see that the value is still in it?

[djjeavons]

Is this code part of the LogIn form (assuming LogIn is a Form)? If it is, just use UsernameTextBox.Text rather than Login.UsernameTextBox.Text.

[Santanu.Das]

Do not need here the Do While rd.Read......Loop.
If your key is unique in SQL Statement, it should return a single row. So no need to use Do...Loop. You can do it in simple way by using Read() method. The codes should be

Dim rd As OleDbDataReader = cmd.ExecuteReader()

If rd.HasRows() Then
    rd.Read()
    SubjectHomepage.Show()
    SubjectHomepage.StudentIDTextBox.Text = rd("StudentID")
EndIf

[leyla]

Is this code part of the LogIn form (assuming LogIn is a Form)? If it is, just use UsernameTextBox.Text rather than Login.UsernameTextBox.Text.

Yes, it is, but the code is on a different form and so that does not quite work.

Do not need here the Do While rd.Read......Loop.
If your key is unique in SQL Statement, it should return a single row. So no need to use Do...Loop. You can do it in simple way by using Read() method. The codes should be

Dim rd As OleDbDataReader = cmd.ExecuteReader()
If rd.HasRows() 
Then    
rd.Read()    
SubjectHomepage.Show()    
SubjectHomepage.StudentIDTextBox.Text = rd("StudentID")EndIf

I have pasted this in to my code, still with message boxes to see the value returned and I am still receiving 'false' and then blank. The subject homepage does not open either.

Sorry for the late reply, I do not have VB.NET at home.

[leyla]

Hi everyone, I have come up with a solution, using your help, (although it's probably not the most efficient).

I put textboxes on every form throughout the route at which I wanted to follow. I then copied the value of the username textbox on the log in form to the next form and then copied that to the form after that. I then used your SQL string and IF statement to search the database for the username and it now returns the studentID.

Thank you all!