Read Value from Database in TextBox when Combobox text changes VB.NET

Question

I have a list of Users Names in ComboBox and Some TextBoxes. When ComboBox text changes (i.e I select some username from ComboBox), The TextBoxes are filled with user details from the database.

I have code to achieve this in SQL Database. But these queries are not working with MsAccess database.

    MysqlConn = New MySqlConnection
    Mysql.ConnectionString = "server=localhost;user=root;password=root;database=database"
    Dim READER As MySqlDataReader
    Try
        MysqlConn.open()
        Dim Query As String
        Query("select * from database.usernames where name='" & ComboBox1.Text & "'")
        Command = New MySqlCommand(Query, MysqlConn)
        READER = Command.ExecuteReader
        While READER.Read
        TextBox1.Text = READER.GetString("name") 
        End While

    End Try

Answer 1

Here is my answer. Please don't get overwhelmed by it. ;)

Broken code

First of all, as I see it, the code you provided cannot work at all, because:

1. your Query variable is initialized in an invalid (or at least a very exotic) way. You probably want to use something like:

   Dim Query As String
   Query = "select * from database.usernames where name='" & ComboBox1.Text & "'"
   

   or in a single line:

   Dim Query As String = "select * from database.usernames where name='" & ComboBox1.Text & "'"
   

2. you try to assign the connection string to the ConnectionString property of a nonexistent Mysql variable. Or the variable exists because it is declared somewhere else, which might be a bug in your code snippet here. But I assume you want to assign the connection string to the MysqlConn.ConnectionString property instead.

3. you have not declared the MysqlConn and Command variables anywhere. You only just assign to them. (I will simply assume you have declared the variables correctly somewhere else in your code...)

4. the IDataRecord interface does not provide a GetString(name As String) method overload. So unless you have defined a custom extension method for it, you probably need to use the IDataRecord.GetOrdinal(name As String) method as well, or use the column index instead of the column name.

Anyway, the code you provided uses MySQL. So I assume that MySQL is the "SQL Database" you are using successfully. And that seems to work, as you say? Well... Hmmm... Then I will simply assume your code snippet is completely correct and works perfectly with MySQL... :/

MS Access vs. MySQL

Using MS Access requires other data access classes (probably the ones in namespace System.Data.OleDb) and another connection string. You could take a look at this ADO.NET OleDb example for MS Access in the Microsoft documentation.

You probably even have to update your SQL query, because every database system uses its own SQL dialect. You might want to consult the Office documentation for that. But your query is quite simple, so perhaps all you have to do to make it work with MS Access is:

• remove the database name and use only the table name, and
• delimit the name identifier (since it is a reserved keyword in MS Access).

I personally delimit all identifiers in my SQL queries, just to avoid unintended conflicts with reserved keywords. So I would personally use something like this:

select * from [usernames] where [name] = '...'

Additional tips

Also, I would like to provide you some additional (unrelated) tips regarding improving your code:

• Use Using-statements with variables of an IDisposable type as much as possible. Those types/classes do not implement that interface if there isn't a good reason for it, so I consider it not unimportant to call Dispose when you are done with such disposable objects (or using a Using statement to call Dispose implicitly).
• Use SQL parameters (if possible) to avoid SQL injection vulnerabilities. Look at this StackOverflow question and its answer for an example of how to use SQL parameters with MS Access.

Example

You may take a look at the following code snippet. It might not provide a working example out-of-the-box, but you might get some useful/practical ideas from it:

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Data\\database.mdb;User Id=admin;Password="
Dim query As String = "select * from [usernames] where [name] = @Name"

Using conn As New OleDbConnection(connectionString)
    Using command As New OleDbCommand(query)
        command.Parameters.Add("@Name", OleDbType.VarChar, 50).Value = ComboBox1.Text
        conn.Open()

        Using reader As OleDbDataReader = command.ExecuteReader
            If reader.Read Then
                textbox1.Text = reader.GetString(reader.GetOrdinal("name"))
            End If
        End Using
    End Using
End Using