I see a couple of possibilities:
First off, you're leaving yourself open to SQL injection attacks
(Google it to see exactly what I'm referring to - basically, users can
insert bad things into the textbox, and they will make their way into
your SQL statement and do potentially damaging stuff to your
database). Look into parameterized statements, possibly even with a
stored procedure.
If "part_no" is a character field, and not a number, then your
statement will fail because there are no quotes being generated before
and after the string - if you use parameterized statements, then it's
not an issue, but if you use your string concatenation method, then
you need to have the quotes surrounding it. But if part_no is a
number, then this isn't the problem.
It's possible that the semi-colon at the end is causing the problem.
I've never used MySQL, but I know that if you have that semi-colon in
an OracleCommand, it will error out, even though the semi-colon is
perfectly normal when executing statements outside of .NET. Try
removing the semi-colon and see if that works.
Also, an additional suggestion, assuming you're on .NET 2.0 or above:
When dealing with data access, you want to ensure that things get
properly cleaned up - your connection, command, and reader should all
be inside "Using" blocks. This guarantees that they are properly
disposed when you're finished, even if an exception is thrown.
Using conn as New Connection
Using comm as New Command
Using rdr as New DataReader
' do your stuff
End Using
End Using
End Using
On Jan 16, 1:33 am, Helvin <[email protected]> wrote:
> Hi,
>
> im very new at this whole programming thing, and Im writing a page
> which has a search function. the click event of the submit button runs
> the sub which follows. Its supposed to search the part number entered
> by the user from my mysql database. But everytime I try it,
> ExecuteReader causes an error.
>
> Sub Submit_Search(ByVal obj As System.Object, ByVal e As
> System.EventArgs) Handles btn_submit.Click
> 'This function gets the part number entered by the user and finds it
> in the database, and returns the data for this part number.
>
> 'Link to database
> Dim myConnection As New MySqlConnection("server=localhost; user
> id=myuser; database=database1; pooling=false;")
> myConnection.Open()
>
> 'Find the matching part number
> Dim strSearch
> strSearch = "SELECT part_no, cust_part_no, customer FROM table1
> WHERE part_no=" & search_part_no.Text & ";"
> Dim sqlComm_search As New MySqlCommand(strSearch, myConnection)
> Dim sqlRead As MySqlDataReader = sqlComm_search.ExecuteReader()
>
> 'sqlRead.Read()
>
> 'Display the row of data
> myConnection.Close
>
> end sub
>
> And here is the error message that I get:
>
> Server Error in '/' Application.
> --------------------------------------------------------------------------------
>
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near '' at line 1
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
>
> Exception Details: MySql.Data.MySqlClient.MySqlException: You have an
> error in your SQL syntax; check the manual that corresponds to your
> MySQL server version for the right syntax to use near '' at line 1
>
> Does anyone have any idea why this is happening? I know the connection
> has to be open, but i thought i already opened it. One thing to note,
> in my page_load sub, i also connect to and read data from my mysql
> database. But does this matter?
>
> Thanks in advance!
> Helvin
