none
vb.net desktop app parsing a long string value RRS feed

  • Question

  • In a vb.net desktop application, I want to find the substring value of '896', '558957', '5/2/2016', '3150', '1', '018.99', '', '18.99', '8277', '17', '12/4/2017', '1789655895720160502315018.99', 'Y'
    that is contained with a 'sqlid' string field.
    An example of the field that comes in is the following:
    "INSERT INTO tblInvoiceData(SchNum,InvNum,InvDate,ItemNum, Quantity,UnitPrice,Credit,Amount,VendorId,CatId,Boarddate,RecordId,Imported )VALUES ('896', '558957', '5/2/2016', '3150', '1', '018.99', '', '18.99', '8277', '17', '12/4/2017', '1789655895720160502315018.99', 'Y')"
    .
    I want to display these values so the user of the application can find the record within the input file that is causing the error. This message is
    displayed basically when there are duplicate records.
    Thus can you show me the code in vb.net on how to accomplish my goal?

    Tuesday, June 26, 2018 4:30 PM

Answers

  • OK assuming you have a query string similar to the one posted, I would suggest that you do:

    Dim idx As Integer = queryString.IndexOf("VALUES")
    Dim valueString As String = queryString.SubString(idx + 6)
    

    This assumes that "queryString" contains the SQL query - replace it with whatever variable or property you have containing the query.

    The resulting "valueString" variable will then contain just the values portion of the string... "('896', '558957', '5/2/2016', '3150', '1', '018.99', '', '18.99', '8277', '17', '12/4/2017', '1789655895720160502315018.99', 'Y')" in your example.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Tuesday, June 26, 2018 7:44 PM
    Moderator
  • I believe what you suggested will work. However the opening "("  and closing ")" will be not parsed out. How would I get that parenthesis not to display in the message the user will see.
    String.Replace is a simple way to remove unwanted characters - replace the braces with an empty string.  There is also a Trim function if they occur at the beginning or end. Note that these are functions - they return the amended string as the result of the function.
    Wednesday, June 27, 2018 12:03 AM

All replies

  • Hi Wendy,

    This question is a bit confusing as you mention an "sqlid string field" and then show a SQL query.  I'm guessing it is the query string that you want to analyze?

    My first question would be, how do you generate that SQL query string?  Presumably those values are taken from some other input.  If the query fails you should be able to access those same input sources to generate a new string to show the user which contains the values that the query attempted to use.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Tuesday, June 26, 2018 4:56 PM
    Moderator
  • This is an extremely old application that was converted to using visual studio 2012. Where the error is being displayed is the only try catch block in the application. Everything has level of access protected. I have no other way to access the input string from how the application runs. It takes in all the input records from an input and then works with each record individually.

    I am just trying to help the  users out since this error has been occurring for awhile in the past. 

    I only want them to see the input values. Thus can you just show me the code on how to parse out the values I am looking for. I do not want the users to see the insert statement?

    Tuesday, June 26, 2018 5:56 PM
  • OK assuming you have a query string similar to the one posted, I would suggest that you do:

    Dim idx As Integer = queryString.IndexOf("VALUES")
    Dim valueString As String = queryString.SubString(idx + 6)
    

    This assumes that "queryString" contains the SQL query - replace it with whatever variable or property you have containing the query.

    The resulting "valueString" variable will then contain just the values portion of the string... "('896', '558957', '5/2/2016', '3150', '1', '018.99', '', '18.99', '8277', '17', '12/4/2017', '1789655895720160502315018.99', 'Y')" in your example.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Tuesday, June 26, 2018 7:44 PM
    Moderator
  • I believe what you suggested will work. However the opening "("  and closing ")" will be not parsed out. How would I get that parenthesis not to display in the message the user will see.
    Tuesday, June 26, 2018 10:33 PM
  • I believe what you suggested will work. However the opening "("  and closing ")" will be not parsed out. How would I get that parenthesis not to display in the message the user will see.
    String.Replace is a simple way to remove unwanted characters - replace the braces with an empty string.  There is also a Trim function if they occur at the beginning or end. Note that these are functions - they return the amended string as the result of the function.
    Wednesday, June 27, 2018 12:03 AM