-
Aug 17th, 2021, 03:23 PM
#1
Thread Starter
New Member
[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
-
Aug 17th, 2021, 03:31 PM
#2
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
-
Aug 17th, 2021, 03:40 PM
#3
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).
-
Aug 17th, 2021, 03:56 PM
#4
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|