vb.net parsing of string data

dcof

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?

Lost User

You need to write an SQL SELECT query to find all records containing those values. Or just get a subset and manually check for the others. And, saving date values as text strings in a database is not the best design.

dcof

Can you show me the query you are referring to? The values you see come in from a text input file from the user. Thus there are no direct dates being accessed directly from the database

Lost User

dcof wrote:

Can you show me the query you are referring to?

I already told you, it will need to be a SELECT statement. And I am afraid your second sentence above does not make sense. Your INSERT statement is clearly inserting date values as strings.

Richard Deeming

If I've understood what you're asking for, you have a string containing an INSERT command, and you want to extract the values. The best way to do that would be with a Regular Expression: Regular Expression Language - Quick Reference | Microsoft Docs[^] For example:

Public Class StatementInfo
Private ReadOnly Shared Parser As New Regex(
"INSERT\s+(INTO\s+)?(?<table>[^(]+)\s*\((?<col>[^,)]+)(,\s*(?<col>[^,)]+))*\)\s*VALUES\s*\((?<val>[^,)]+)(,\s*(?<val>[^,)]+))*\)",
RegexOptions.IgnoreCase Or RegexOptions.ExplicitCapture)

Public Shared Function Parse(ByVal input As String) As StatementInfo
    Dim match As Match = Parser.Match(input)
    If Not match.Success Then Return Nothing
    
    Dim tableName As String = match.Groups("table").Value
    Dim columnNames As List(Of String) = match.Groups("col").Captures.Cast(Of Capture)().Select(Function (c) c.Value.Trim()).ToList()
    Dim values As List(Of String) = match.Groups("val").Captures.Cast(Of Capture)().Select(Function (c) c.Value.Trim()).ToList()
    If values.Count <> columnNames.Count Then Throw New ArgumentException("Mis-matched columns and values")
    
    Return New StatementInfo(tableName, columnNames, values)
End Function

Private Sub New(ByVal tableName As String, ByVal columnNames As List(Of String), ByVal values As List(Of String))
    Me.TableName = tableName
    Me.Values = columnNames \_
        .Zip(values, Function (c, v) New With { .Key = c, .Value = v }) \_
        .ToDictionary(Function (p) p.Key, Function (p) p.Value, StringComparer.OrdinalIgnoreCase)
End Sub

Public ReadOnly Property TableName As String
Public ReadOnly Property Values As IReadOnlyDictionary(Of String, String)

End Class

Usage:

Dim line As String = "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')"
Dim statement As StatementInfo = StatementInfo.Parse(line)
I

dcof

Thanks. That answers the question