Problem with a sting

[itms]

Hi I have this string that I am trying to write, but somehow I keep getting the last part wrong; it keeps saying "Unclosed quotation mark after the character string ' + ')' '."
And I have tried various combinations of double and single

quotas, but nothing works​

Dim commandString As String = "Select file_no From [DPs].[dbo].[DpsInfo] Where file_no in (select file_no from DpsPhone where Phone_no = '" + ph + "' + '')'' "

Can someone please tell me what I ma doing wrong?

Thank you

 

[jmcilhinney]

There are a number of things to address here.

When performing concatenation operations, ALWAYS use the concatenation operator (&) rather than the addition operator (+). In many cases, addition maps to concatenation and there's no issue but there are cases where it doesn't. Do you know which is which? If you never use addition when you want to concatenate, you don't have to know. Fixing that and your mismatched quotes, we get this:

Dim commandString As String = "Select file_no From [DPs].[dbo].[DpsInfo] Where file_no in (select file_no from DpsPhone where Phone_no = '" & ph & "')"

It's really easy to construct that without confusing yourself. Here are the steps I performed just now:

1. Just write it out as a single String containing the variable name:

Dim commandString As String = "Select file_no From [DPs].[dbo].[DpsInfo] Where file_no in (select file_no from DpsPhone where Phone_no = ph)"

2. Add the single quotes for the SQL text literal:

Dim commandString As String = "Select file_no From [DPs].[dbo].[DpsInfo] Where file_no in (select file_no from DpsPhone where Phone_no = 'ph')"

3. Add the concatenation operators with required double quotes, which means one double quote per concatenation operator:

Dim commandString As String = "Select file_no From [DPs].[dbo].[DpsInfo] Where file_no in (select file_no from DpsPhone where Phone_no = '" & ph & "')"

Easy.

That said, this demonstrates why you should use String.Format or string interpolation instead of string concatenation. Concatenation becomes hard to read and thus error-prone very quickly. Using String.Format:

Dim commandString As String = String.Format("Select file_no From [DPs].[dbo].[DpsInfo] Where file_no in (select file_no from DpsPhone where Phone_no = '{0}')", ph)

or, in VB 2015 or later, string interpolation:

Dim commandString As String = $"Select file_no From [DPs].[dbo].[DpsInfo] Where file_no in (select file_no from DpsPhone where Phone_no = '{ph}')"

makes the code much easier to read and thus less error-prone. Personally, I always use one of those options when I would otherwise need three or more concatenation operators and often even when only one or two concatenation operators would be needed. Code readability should be a paramount concern.

That said, you shouldn't be using any of those options for writing SQL code. You should ALWAYS use parameters to insert values into SQL code. Doing so has a number of advantages, the most important of which is that it closes a huge security hole. To learn why and how to use parameters in ADO.NET, follow the Blog link in my signature below and check out my post on Using Parameters In ADO.NET.

 

[itms]

Thanks this was a big help, and thanks for the explanation of each point here.

 

[itms]

Thanks this was a big help, and thanks for the explanation of each point here.

Thanks, but still getting Unclosed quotation mark after the character string ')'.Any other thoughts?​

 

[JuggaloBrotha]

[FONT=&]Thanks, but still getting [/FONT][FONT=&]Unclosed quotation mark after the character string ')'.[/FONT][FONT=&]Any other thoughts?[/FONT]​

Try doing it this way:

Dim commandString As String = $"Select file_no From [DPs].[dbo].[DpsInfo] Where file_no in (select file_no from DpsPhone where Phone_no = '{ph}')"

 

[itms]

Try doing it this way:

Dim commandString As String = $"Select file_no From [DPs].[dbo].[DpsInfo] Where file_no in (select file_no from DpsPhone where Phone_no = '{ph}')"

Unfortuantly it is the same thing:
Unclosed quotation mark after the character string ')'.

This is what I was saying it is really bazar UI have tried so many things, including all the things you have listed??

 

[jmcilhinney]

That suggests that the value of 'ph' actually includes a quotation mark and that is EXACTLY why you should be using parameters and NOT using string concatenation. Did you think to actually look at the value of 'commandString' after executing that line to see what it contained for yourself? Then you could have shared it with us too, and we wouldn't have to keep guessing. I'm fairly confident about this guess though. If you check out the blog post I directed you to then you'll see that I mention this issue specifically.

 

[itms]

That suggests that the value of 'ph' actually includes a quotation mark and that is EXACTLY why you should be using parameters and NOT using string concatenation. Did you think to actually look at the value of 'commandString' after executing that line to see what it contained for yourself? Then you could have shared it with us too, and we wouldn't have to keep guessing. I'm fairly confident about this guess though. If you check out the blog post I directed you to then you'll see that I mention this issue specifically.

I did look at this and do not see any, but I am sorry to baother you with the and do appreaciate yur help. I will change it wo a parameter, I you are right I have used that in the past and it will be better I am sure .
Thanks again