Results 1 to 4 of 4

Thread: [RESOLVED] textfileparser not working as expected - vb.net 4.6 - vs 2019 - mysql

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2021
    Posts
    7

    Resolved [RESOLVED] textfileparser not working as expected - vb.net 4.6 - vs 2019 - mysql

    Everything was working fine - now I am getting a SQL Syntax error. The textfileparser is passing the entire line, but not in double quotes, in single quotes which causes a problem as the Customer Concern has a single quote in it.

    Here is the error:

    MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'S NEEDED TO INSTALL NEW RUNNING LIGHTS - CHECK & ADVISE', '0.00', 'WAITING FOR A' at line 1'

    Here is the Data which is coming in as a csv file - here is the line that I am having an issues with

    "John's Auto Pros","1795 E. Valley Parkway","Escondido, CA. 92027","Phone - 760-741-2076 Fax - 760-741-6993","Current Technician Assignments","Report Date : 08/17/2021","Technician","Charged Hours","Pay Hours","Invoice / Estimate #","Hat Number","Job","Description","Status","Cody Carter",,"RO","CUSTOMER CONCERN / STATES: WHAT'S NEEDED TO INSTALL NEW RUNNING LIGHTS - CHECK & ADVISE",0.00,0.00,"WAITING FOR APPROVAL","24133","Totals:",8.26,8.26,"Grand Total Charged Hours: 64.02","Grand Total Pay Hours: 64.02","Page 1 of 1","(c) 2012 Mitchell Repair Information Company, LLC CurTechAssig.rpt 07.10.12"

    This is the query that it is trying to insert

    insert into johnsautopros.assignstagging (techname,rotype,vehicle,jobname,jobhours,jobstatus, ronumber) values ('Cody Carter', 'RO', '', 'CUSTOMER CONCERN / STATES: WHAT'S NEEDED TO INSTALL NEW RUNNING LIGHTS - CHECK & ADVISE', '0.00', 'WAITING FOR APPROVAL', '24133')

    Here is my code - Forgive me, I am not a programmer, at least not for 40 years now, and then it was assembler.
    Code:
    Private Sub parser()
    
    
            Using MyReader As New Microsoft.VisualBasic.
                          FileIO.TextFieldParser("C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\currentassign.csv")
                MyReader.TextFieldType = FileIO.FieldType.Delimited
                MyReader.SetDelimiters(",")
                Dim currentRow As String()
                While Not MyReader.EndOfData
                    Try
                        currentRow = MyReader.ReadFields()
                        Dim currentField As String
                        Dim i As Integer
                        For i = 1 To 29
                            For Each currentField In currentRow
                                drow(i) = currentField
                                If i < 30 Then
    
                                    i = i + 1
                                End If
                            Next
                            techname1 = drow(15)
                            rotype1 = drow(17)
                            vehicle1 = ""
                            jobname1 = drow(18)
                            jobhours1 = drow(19)
                            jobstatus1 = drow(21)
                            ronumber1 = drow(22)
                            putdrow()
                        Next
                    Catch ex As Microsoft.VisualBasic.
                                FileIO.MalformedLineException
                        MsgBox("Line " & ex.Message &
                        "is not valid and will be skipped.")
                    End Try
                End While
            End Using
        End Sub
    
    Private Sub putdrow()
            sqlConn = New MySqlConnection
            sqlConn.ConnectionString = "Server =" + server + ";" + "user id = " + username + ";" _
                + "password =" + password + ";" + "database =" + database
            Dim Query As String
            Dim reader As MySqlDataReader
            sqlConn.Open()
            Query = "insert into johnsautopros.assignstagging (techname,rotype,vehicle,jobname,jobhours,jobstatus, ronumber) values ('" & techname1 & "', '" & rotype1 & "', '" & vehicle1 & "', '" & jobname1 & "', '" & jobhours1 & "', '" & jobstatus1 & "', '" & ronumber1 & "')"
            sqlCmd = New MySqlCommand(Query, sqlConn)
            reader = sqlCmd.ExecuteReader
            sqlConn.Close()
        End Sub
    Last edited by si_the_geek; Aug 17th, 2021 at 03:39 PM. Reason: added Code tags

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: textfileparser not working as expected - vb.net 4.6 - vs 2019 - mysql

    1) Please use [code][/code] tags around your code when posting it. It keeps the formatting and the indenting, making it easier to read.
    2) textfileparser IS working AS expected... it's your code that isn't working.
    3) This is why no one uses string concatenation for sql... you really should be using parameters for this, then things like quotes wouldn't be the issue.
    4) You shouldn't also be opening/closing connections to the database like that. Open the connection, insert all of your data (loop), THEN close it.
    In fact you should open the connection once, create the command object once, add the parameters once, then inside the loop, just set the values of the parameters, execute the query, repeat loop...when done, then close the connection.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: textfileparser not working as expected - vb.net 4.6 - vs 2019 - mysql

    For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).

  4. #4

    Thread Starter
    New Member
    Join Date
    Aug 2021
    Posts
    7

    Re: textfileparser not working as expected - vb.net 4.6 - vs 2019 - mysql

    I was wondering how you guys got the code put into the post like you do. Thanks. Also I will investigate the parameters and modify my code and respond with resolved once I get it there.

Posting Permissions

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



Click Here to Expand Forum to Full Width