How do I fetch data from Access database records and display/save them as textboxes/variables in VB.-CodePudding

coding noob here. I'm looking for help after hours of googling and watching YouTube tutorials provided no success. I've also tried looking at many of the posts on this website. I keep running into the same problem.

In Visual Studio I am attempting to create a solution with VB.net, it is currently successfully linked to an Access Database with which I can view, add, edit, and save records live.

I am trying to fetch data from the Access database based on a search of one of the fields, and then save that data as a variable.

(Code shown below) Upon attempting to click the search button, I received the following exception: System.Data.OleDb.OleDbException: 'Syntax error (missing operator) in query expression 'First Name'.'

I believe it is because my field names are two words, but I have no idea how to fix this. Just typing it with one word gives this exception instead: System.Data.OleDb.OleDbException: 'No value given for one or more required parameters.'

I have been completely unable to make progress despite watching multiple different versions of tutorials on how to perform this. Could someone please help me out?

Here is the code:

Public Class FormTraits

    Private Sub FormTraits_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub FNButton_Click(sender As Object, e As EventArgs) Handles FNButton.Click

        Dim conn As New OleDbConnection
        conn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Access Database.accdb")
        conn.Open()

        Dim strsql As String
        strsql = "select ID, First Name, Last Name from PlayerData where First Name="   TextBoxSearch.Text   ""

        Dim cmd As New OleDbCommand(strsql, conn)

        Dim myreader As OleDbDataReader

        myreader = cmd.ExecuteReader
        myreader.Read()

        TextBoxFirstName.Text = myreader("First Name")
        TextBoxLastName.Text = myreader("Last Name")
        TextBoxID.Text = myreader("ID")

        conn.Close()

    End Sub

End Class

CodePudding user response：

You should avoid using names including spaces or other special characters for tables, columns, etc, in your database. Just as you would do for a variable name or the like in VB - and as you have done for your table name - you should name your columns FirstName and LastName. If you must include spaces or other special characters, you must escape the identifiers in your SQL code. You also need to wrap text literals in single quotes, much as you wrap them in double quotes in VB:

strsql = "SELECT ID, [First Name], [Last Name] FROM PlayerData WHERE [First Name] = '" & TextBoxSearch.Text & "'"

It would be better if you used parameters instead of string concatenation, but that's beyond the scope of this question.