I'm trying to read and return info from an SQL database. To ensure you have the details, here's the basic info:
The database is called "DataDesignSolution" The table is called "INVENTORY"
I'm trying to read the columns "INV-PART-NUMBER" and "INV-DESCRIPTION".
I've written the following code hoping to SEARCH through the "INV-PART-NUMBER" column for a part that begins with "AO", return that value to a checkbox called "cbxPartNo" along with the description information from the same row and from the column "INV-DESCRIPTION:
Dim connstr = "Data Source=.;AttachDbFilename=C:\Users\Don\Documents\DataDesignSolutions.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
Dim sqlquery = "SELECT INV-PART-NUMBER, INV-DESCRIPTION FROM INVENTORY where INV-PART-NUMBER like'%" & Me.txbPartNo.Text & "%'"
Dim connection As SqlConnection = New SqlConnection(connstr)
Dim command As SqlCommand = connection.CreateCommand()
command.CommandText = sqlquery
Dim reader As SqlDataReader = command.ExecuteReader()
cbxPartNo.Text = reader.GetString(0)
txbDesc.Text = reader.GetString(1)
I'm getting an error that says "Incorrect Syntax near 'AO'" ("AO" is the first characters of the part number I'm trying to return. I know there is one part number in the table that beings with these characters). I'm not sure what is causing this. Does anyone have any thoughts as to why I'm getting an error. Is my connection string wrong in some manner? If it is a syntax error, what is it?
The hypen is usually understood as a minus sign, so the parser tries to calculate INV minus PART minus NUMBER !!! The solution is to surround the field names between [ ] Ex: "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM INVENTORY where [INV-PART-NUMBER] like " & search1 Hope this helps
I may have found the error. I have made the following correction:
Dim search1 As String = "'%" & txbPartNo.Text & "%'"
Dim sqlquery = "SELECT INV-PART-NUMBER, INV-DESCRIPTION FROM INVENTORY where INV-PART-NUMBER like " & search1
The latest error I'm getting says, "Invalid Column Name 'INV' Invalid Column Name 'PART' Invalid Column Name 'NUMBER' Invalid Column Name 'INV' Invalid Column Name 'PART' Invalid Column Name 'NUMBER' "
My assumption is that it doesn't like the hyphen which is part of the table column. Or is it something else? I'm incredibly confused. HELP!!