Reading from database and inserting into textbox

[leyla]

Hi, I know that there have been a lot of questions on this topic and I have followed the solutions however my code still does not work. No error comes up, even after using Try and Catch and the code does not break, but the values are just not in the textboxes. It worked a few times but I must have changed the code slightly somewhere and now it no longer works.

I am trying to read the Subjects from the Subjects table in the database, depending on the StudentID that I have previously read. The StudentID gets read from the database and inserted into a textbox, no problem, but the subjects do not work. I have even tried the query in Access and it returns the results that I want.

If anyone could shed any light on why this does not work in VB, that would be great.

Thanks!

 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 * FROM Subjects WHERE [StudentID]= '" & StudentID.Text & "'", conn)
            conn.Open()
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Try
                If rd.HasRows() Then                              
                    rd.Read()
                    cmd.Parameters.AddWithValue("Subject1", Subject_1TextBox.Text).ToString()
                    cmd.Parameters.AddWithValue("Subject2", Subject_2TextBox.Text).ToString()
                    cmd.Parameters.AddWithValue("Subject3", Subject_3TextBox.Text).ToString()
                    cmd.Parameters.AddWithValue("Subject4", Subject_4TextBox.Text).ToString()
                End If

            Catch ex As Exception                           
                MessageBox.Show(ex.Message & " - " & ex.Source) 
            Finally                                        
            End Try

[djjeavons]

Hi

A couple of things:

1. Student ID, is this a numerical value in your database? I believe it is and if I am correct then your query does not need to use the ' characters as these are for strings. So change your query too: "SELECT * FROM Subjects WHERE [Student ID]=" & StudentID.Text

2. Why are you adding parameters to the command object . Are you attempting to read the fields Subject1, Subject2 etc into the text boxes? If so then change your code to:

   If rd.HasRows() Then
       rd.Read()
       Subject_1TextBox.Text = rd("Subject1").ToString()
       ....
   End If
   

Finally, have you tried using a breakpoint and stepping through the code. This will help you to identify what is going on in terms of flow and the values of your variables.

HTH

[leyla]

Hi, thanks for your reply. I have tried the code given in the if statement and it does not work, hence why I tried using parameters.

I've now changed the code back to your suggestion and taken out the single quotes. The error I receive is:

'Syntax error (missing operator) in query expression '[StudentID]='.

[djjeavons]

My guess is that you don't have a StudentID hence the empty value in your SQL statement.

Can you attach your project, I think it would be easier to see it in order to help further.

[leyla]

I do, I have tested the query in access and it returns the values that I want and you can also see the ID in the textbox on the form.

And how would you like me to do that?

[djjeavons]

If you zip up your solution you should be able to attach it to your next post.

[Santanu.Das]

'Syntax error (missing operator) in query expression '[StudentID]='.

From my assumption after seeing the exception StudentID is a Text Type field. So from my opinion write the SQL Statement as you did previously. i.e.

"SELECT * FROM Subjects WHERE [StudentID]= '" & StudentID.Text & "'"

Get sure that the feild names you write in SQL Statement are as same as the feild names in database.

Now read the table and show data as @djjeavons has shown.

[Minimalist]

Maybe put conn.Open() before executing:

Dim cmd As New OleDbCommand( _
            "SELECT * FROM Subjects WHERE [StudentID]= '" & StudentID.Text & "'", conn)

[leyla]

Hi everyone,

I have managed to get it working now. I used a mixture of everyone's solutions and I also deleted the Datafill pieces of code that are automatically inserted when adapter managers are added, so I guess it was a mixture of everything.

Thanks again.