Results 1 to 8 of 8

Thread: Problem with a sting

  1. #1
    .NET Framework
    .NET 4.5
    Join Date
    Feb 2018
    Posts
    14
    Reputation
    10

    Question Problem with a sting

    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

  2. #2
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,995
    Reputation
    1727
    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.

  3. #3
    .NET Framework
    .NET 4.5
    Join Date
    Feb 2018
    Posts
    14
    Reputation
    10

    Smile

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

  4. #4
    .NET Framework
    .NET 4.5
    Join Date
    Feb 2018
    Posts
    14
    Reputation
    10

    Question

    Quote Originally Posted by itms View Post
    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?

  5. #5
    .NET Framework
    .NET 4.0
    Join Date
    Jun 2004
    Location
    Lansing, MI; USA
    Posts
    4,500
    Reputation
    1036
    Quote Originally Posted by itms View Post
    Thanks, but still getting Unclosed quotation mark after the character string ')'.Any other thoughts?
    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}')"
    Currently using VS 2015 Enterprise on Win10 Enterprise x64.

  6. #6
    .NET Framework
    .NET 4.5
    Join Date
    Feb 2018
    Posts
    14
    Reputation
    10
    Quote Originally Posted by JuggaloBrotha View Post
    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??

  7. #7
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,995
    Reputation
    1727
    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.

  8. #8
    .NET Framework
    .NET 4.5
    Join Date
    Feb 2018
    Posts
    14
    Reputation
    10

    Smile

    Quote Originally Posted by jmcilhinney View Post
    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

Similar Threads

  1. Question Obtaining Characters from a sting with the specified character positions in .ini
    By stork1990 in forum VB.NET General Discussion
    Replies: 2
    Last Post: 10-15-2014, 5:56 PM
  2. search for sting in database
    By hometech in forum MS Access
    Replies: 1
    Last Post: 05-13-2013, 11:01 PM
  3. Replies: 0
    Last Post: 02-22-2012, 9:00 PM
  4. sting to int error
    By Nocty in forum VB.NET General Discussion
    Replies: 1
    Last Post: 12-16-2008, 9:10 PM
  5. Replies: 4
    Last Post: 11-09-2007, 2:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •