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.