[RESOLVED] Invalid SQL Statement in my VB .NET Code - Can't figure it out

[NJDevils28]

Hello all,

I do not know enough about SQL to solve this on my own and Google was unexpectedly not helpful.

I have to do a search on a captured field that contains a ' (Tick-mark).

Example...

Dim Company_Code As String.

I Read the File.....
Then Load the values...
Eventually the field Company_Code has a value of "Moody's"

The formatted SQL Statement looks like this...

'SELECT Client_Number from clients where client_name=Moody's'
Then <Error>
The extra Tick-Mark trips up the statement

When I format my SQL Statement I error out because of the Tickmark. I can not figure a way to format it so SQL will accept it.
When I test all my SQL Statements in SQL Server Studio Manager 2012 I can not format the statement with double quotes.

SELECT Client_Number from clients where client_name="Moody's" <== SQL does not like this
SELECT Client_Number from clients where client_name='Some other company name' <== SQL likes this

Does anyone know of a way to fix my formatting issue?

Thanks,

-NJ

[Joacim Andersson]

Use two single quotes (or tick marks):

SELECT Client_Number FROM Clients WHERE client_name = 'Moody''s'

[NJDevils28]

Use two single quotes (or tick marks):

SELECT Client_Number FROM Clients WHERE client_name = 'Moody''s'

Thanks! That cleared up the SQL Error

Now I have another question... Would you know of an easy way to code for that eventuality in VB? 99% of the data will be without tick-marks but the 1% will have to be compensated for. I apologize for the noob question but I'm teaching myself VB as I go and it's not as ease as the work "Basic" would imply.

Thanks in advance,

-NJ

[.paul.]

use parameters:

http://code.msdn.microsoft.com/acces...-with-2fed4cc1

[Shaggy Hiker]

In VB, one can assume that you will not be typing in a string as in the example, but will be using a variable, right?

If so, you might look at the String.Contains method if you want to determine that there might be a problem, but the more significant method would be String.Replace, as you want to replace ' with ''

[Shaggy Hiker]

I like .Paul.s answer better than mine. Concatenating in a variable directly is not a good idea, though what I said for Replace would still be kind of ok (though the parameters would still be better).

[jmcilhinney]

For a bit more information on the whys and hows of parameters, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

[NJDevils28]

use parameters:

http://code.msdn.microsoft.com/acces...-with-2fed4cc1

Hi Paul,

Thank you for the advice. I seem to be coding the parameters incorrectly. If you could, would you take a look at my code and tell me where I went off the reservation?

Thanks,

-NJ

 mySQLCom = "Select Client_ID from Clients where Client_name=(ClientName) Values (@ClientName)"

        Try
            MySQLconnection = New SqlConnection(Myconstring)
            MySQLconnection.Open()

            MySQLcommand = New SqlCommand(mySQLCom, MySQLconnection)

            MySQLcommand.Parameters.Add(MySQLcommand.CreateParameter).ParameterName = "@ClientName"
            

            MySQLcommand.Parameters.Item("@ClientName").Value = ClientName

            If ConnectionState.Open Then
                MySQLreader = MySQLcommand.ExecuteReader()
            End If

        Catch ex As SqlException
            MessageBox.Show("Error while Reading a record from the database - " & ex.Message, "Client Table")
        End Try

[jmcilhinney]

Did you read my blog post? If you did then you'd know what to do.

[NJDevils28]

Did you read my blog post? If you did then you'd know what to do.

I did read your blog and you had one example of a SELECT statement and when I used your code structure I had the same syntax error with the extra quote.

This is the code I used from your blog....

Dim sql As String = "SELECT * " & _

                    "FROM Employee " & _

                    "WHERE PayrollNumber = '" & _

                    Me.payrollNumberField.Text & _

                    "'"

[techgnome]

wow... then you missed out on the whole point of the article... which is how to use parameters.... funny thing is that using parameters in a select is exactly the same as in an insert or an update... all you seemed to fixate on was the final code piece that was intended to illustrate why using concatenation is a bad idea... if that's the only code sample you took away from that article... I suggest going back and re-read the whole thing.

-tg

[NJDevils28]

wow... then you missed out on the whole point of the article... which is how to use parameters.... funny thing is that using parameters in a select is exactly the same as in an insert or an update... all you seemed to fixate on was the final code piece that was intended to illustrate why using concatenation is a bad idea... if that's the only code sample you took away from that article... I suggest going back and re-read the whole thing.

-tg

I did read your post and I was able to make it work by using this statement....

mySQLCom = "Select Client_ID from Clients where Client_name='" & ClientName.Replace("'", "''") & "'"

From what I gathered in your blog that is not using parameters and I would still be subject to further errors if the users incorporate other characters when they create the data set. I think I'm close but I still can't get it to work. While your blog is informative it is still hit or miss to the beginner programmer.

I still do not see where I made the error in my code but I'll keep plugging away.

Thank you for the time you have spent on my question.

-NJ

[Static]

LOL Parameters are the way to go.. And yeah.. re-read the article...(REALLY READ IT)
If you still dont get it.. google "vb.net sql parameters" its really easy...

this:

mySQLCom = "Select Client_ID from Clients where Client_name='" & ClientName.Replace("'", "''") & "'"

is NOT a parameter... all you are doing is concatenating strings... which WILL cause errors.


it will look something like this...(this code wont work on its own!)

DIm CMD as new SqlCommand
CMD.commandText = "SELECT Client_Number FROM Clients WHERE client_name=@client_name" <<<< @client_name is the parameter
CMD.parameters.AddWithValue("@client_name",Company_Code) <<< company_code has your value in it... single quote/tickmarks/etc wont matter
OpenYourConnection
CMD.connection = YourConnection
CMD.executenonquery
Close your connection

[NJDevils28]

LOL Parameters are the way to go.. And yeah.. re-read the article...(REALLY READ IT)
If you still dont get it.. google "vb.net sql parameters" its really easy...

this:

mySQLCom = "Select Client_ID from Clients where Client_name='" & ClientName.Replace("'", "''") & "'"

is NOT a parameter... all you are doing is concatenating strings... which WILL cause errors.


it will look something like this...(this code wont work on its own!)

DIm CMD as new SqlCommand
CMD.commandText = "SELECT Client_Number FROM Clients WHERE client_name=@client_name" <<<< @client_name is the parameter
CMD.parameters.AddWithValue("@client_name",Company_Code) <<< company_code has your value in it... single quote/tickmarks/etc wont matter
OpenYourConnection
CMD.connection = YourConnection
CMD.executenonquery
Close your connection

That Did it!!!!

Thank you very much!!!!

-NJ

[Static]

dont thank me! thank jmcilhinney for the article!

[jmcilhinney]

How is it that you read that blog post and then did exactly what I said NOT to do?

[NJDevils28]

How is it that you read that blog post and then did exactly what I said NOT to do?

I did read your post and used the example ...

mySQLCom = "Select Client_ID from Clients where Client_name='" & ClientName.Replace("'", "''") & "'"

As what was NOT a parameter but still a working SQL statement

I then added the parameter logic (I thought I did anyway) with the code below ...

 mySQLCom = "Select Client_ID from Clients where Client_name=(ClientName) Values (@ClientName)"

        Try
            MySQLconnection = New SqlConnection(Myconstring)
            MySQLconnection.Open()

            MySQLcommand = New SqlCommand(mySQLCom, MySQLconnection)

            MySQLcommand.Parameters.Add(MySQLcommand.CreateParameter).ParameterName = "@ClientName"
            

            MySQLcommand.Parameters.Item("@ClientName").Value = ClientName

            If ConnectionState.Open Then
                MySQLreader = MySQLcommand.ExecuteReader()
            End If

        Catch ex As SqlException
            MessageBox.Show("Error while Reading a record from the database - " & ex.Message, "Client Table")
        End Try

That code was syntactically correct (Clean Compile) but incorrect for the use I intended it for. I over thought things and my code reflected that. Hence the invalid SQL Statement at run time.

Static's simple example of how to use parameter logic incorporating my train of thought was much more helpful than than telling me to keep reading when it was obvious I did read but still did not get it.

Once again, I thank you for your blog as it did show me the right path to follow and I thank static for taking the time to make me understand your blog.


-NJ

[techgnome]

You're right... we screwed up big time... we made the fatal assumption you knew how to write a syntatcally correct select statement. mea culpa... won't happen again.

-tg

[NJDevils28]

You're right... we screwed up big time... we made the fatal assumption you knew how to write a syntatcally correct select statement. mea culpa... won't happen again.

-tg

Sir,

I seem to have offended you, it was not my intent. I have a strong need of this forum and the skills of people like you. You are correct, I do not know SQL nor do I know VB with the expertise I should, to be coding the project I find myself coding. My company has decided that they will not hire the replacement programmer for the one that quit and since I can spell Visual Basic it fell to me to take on the project.

I can assure you in the future my questions will be just as vague and full of assumptions as this one was. I will eventually correct that behavior but until then please look at my naive questions for what they are. Learning.

Once again, I thank you for your help.

-NJ

[Joacim Andersson]

It's OK, I think the reaction was because people told you to read a blog article and what you did was that you didn't really read it at all but instead took the first code example you saw and used it. If you had really read the article you would have noticed that it clearly stated that that was an example of how you shouldn't do it. If you're really here to learn then please take the time to do so and if that learning experience comes from a blog article then you should take the time to read all of it.

[NJDevils28]

It's OK, I think the reaction was because people told you to read a blog article and what you did was that you didn't really read it at all but instead took the first code example you saw and used it. If you had really read the article you would have noticed that it clearly stated that that was an example of how you shouldn't do it. If you're really here to learn then please take the time to do so and if that learning experience comes from a blog article then you should take the time to read all of it.

Thank you for the advice, I try very hard to pay attention to answers when I ask a question and I'll keep your words in mind.

In my defense I did read the whole blog. I believe this was just a misunderstanding, as I did not reply to the answers given to me in a coherent way to make my confusion apparent. I understand the parameters concept (Because of the blog and it was very helpful), I was just not coding it correctly in my program.
Once I understood the parameter concept as the answer to my question, I moved on to the coding logic to make it work. I should have changed the subject more smoothly but I instead left it lingering that I was still stuck on the Parameter Concept.

I will try to make my threads much less confusing in the future.

This is a very helpful forum and I'm glad I found it.

-NJ