1,105,406 Community Members

Error in ConnectionString?

Member Avatar
doncwilson_1
Junior Poster
188 posts since Feb 2013
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 2 [?]
 
0
 

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)
                connection.Open()

                Dim command As SqlCommand = connection.CreateCommand()
                command.CommandText = sqlquery
                Dim reader As SqlDataReader = command.ExecuteReader()
                reader.Read()
                Console.WriteLine(reader.GetString(0), reader.GetString(1))
                connection.Close()
                Console.Read()

                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?

In advance, thanks for your help.

Don

Member Avatar
doncwilson_1
Junior Poster
188 posts since Feb 2013
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 2 [?]
 
0
 

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!!

Thanks,

Don

Member Avatar
lolafuertes
Posting Shark
912 posts since Oct 2008
Reputation Points: 145 [?]
Q&As Helped to Solve: 196 [?]
Skill Endorsements: 7 [?]
 
1
 

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

Member Avatar
doncwilson_1
Junior Poster
188 posts since Feb 2013
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 2 [?]
 
0
 

Lola,

You are my hero!! This worked perfectly!!

Thanks!

Question Answered as of 1 Year Ago by lolafuertes
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article