Description:
My application is written in VB.NET 2008. My interface to MySQL server version 5.1.43 using localhost is through the MySQL .NET adapter version 6.2.2 using all dynamicly generated SQL statements. I do not have any static bound controls anywhere in my whole application. The SQL statement generated by my code was
Update casecalendar SET DueDateTime = STR_TO_DATE('20100305 05:00 PM','%Y%m%d %h:%i %p'), AssignedTo = 'charlotte', Priority = '3', Done = 'N', Action_cd = 'SGND-CNSNTJDGMNT2DEF', Narrative = 'test NARRATIVE test 2010-03-04 @ 10:14 , AppUID = ' joe', JobRunID = 0, PrgmFunction = 'Update Diary.btnSave_Click' WHERE casecalendar.ID = 12
The SQL syntax error is that immediately after ". . . @ 10:14" there should have been a single quote, but it is not there. That was my bug. I expect that this will cause a MySQL exception thrown with an error message like "There is an error in your SQL syntax in line 1. Check the manual for the version of MySQL server you are using for an error near '@ 10:14'". That is as good as I can remeber what that particular MySQL error message is like.
After I fixed my bug in my VB.NET program source code the program executed without throwing an exception, and the database table row was updated with the new data.
How to repeat:
I hope that you have a test environment that you can paste my SQL statement into and see what you get. I'll paste the snippet of source code where the exception was thrown. I hope it is not too long for you.
Try
If ii_PickParm1 > 0 Then
strSQL = "Update casecalendar " _
& "SET DueDateTime = " & "STR_TO_DATE('" & (Format(Me.DateTimePicker1.Value, "yyyyMMdd") & " " & Me.mtbDueTime.Text).Trim & "','%Y%m%d %h:%i %p')" & ", " _
& "AssignedTo = '" & Me.cmboAssignedTo.SelectedValue.ToString & "', " _
& "Priority = '" & Me.cmboPriority.SelectedValue.ToString & "', " _
& "Done = '" & is_Done & "', " _
& "Action_cd = '" & Me.cmboAction.SelectedValue.ToString & "', " _
& "Narrative = '" & Me.txtDiaryNarrative.Text & "', " _
& "AppUID = ' " & gs_UserID & "', " _
& "JobRunID = 0, " _
& "PrgmFunction = 'Update Diary.btnSave_Click' " _
& "WHERE casecalendar.ID = " & CStr(ii_PickParm1)
Else
strSQL = "INSERT INTO casecalendar " _
& "(CasesTableID, DueDateTime, CreatedBy, AssignedTo, Priority, Done, Action_cd, Narrative, UpdateDateTime, InsertDateTime, DBCONNID, AppUID, JobRunID, PrgmFunction) " _
& "VALUES (" & ii_CasesTableID.ToString & "," _
& "STR_TO_DATE('" & (Format(Me.DateTimePicker1.Value, "yyyyMMdd") & " " & Me.mtbDueTime.Text).Trim & "','%Y%m%d %h:%i %p')" & ",'" _
& gs_UserID & "','" _
& Me.cmboAssignedTo.SelectedValue.ToString & "','" _
& Me.cmboPriority.SelectedValue.ToString & "','" _
& is_Done & "','" _
& Me.cmboAction.SelectedValue.ToString & "','" _
& Me.txtDiaryNarrative.Text _
& "',null,now(),'" _
& gs_DBCONNID & "','" _
& gs_UserID & "'," _
& "0,'Insert Diary.btnSave_Click');"
End If 'ii_PickParm1 > 0 Then
Catch ex As Exception
MessageBox.Show("#12.0 In screen form Class Diary btnSave_Click event, there was an General Exception thrown while processing Diary.ID = " & CStr(ii_PickParm1) & ". CLR error message follows: " _
& ex.Message, "*************(TM) Exception:", MessageBoxButtons.OK, MessageBoxIcon.Stop)
End Try
Try
conn.Open()
Try 'This Try-Catch block performs the Insert/Update to the casecalendar database table.
'Debug Code -- Save 2007-02-01 by Ted Palmer
'MessageBox.Show("Value strSQL: " & strSQL, _
'"Subrogation Debug Message:", MessageBoxButtons.OK, MessageBoxIcon.Stop)
myCommand.Connection = conn
myCommand.CommandText = strSQL
intReturn = myCommand.ExecuteNonQuery()
Console.WriteLine("intReturn: " & CStr(intReturn))
Catch myerror As MySqlException
MessageBox.Show("#15.0 In screen form Class Diary >> btnSave_Click event there was a MySqlException writing casecalendar.ID = " & CStr(ii_PickParm1) & " to the casecalendar table in the Subrogation database. MySQL error message follows: " _
& myerror.Message, _
"*************(TM) Exception:", MessageBoxButtons.OK, MessageBoxIcon.Stop)
End Try
Catch myerror As MySqlException
MessageBox.Show("#13.0 In screen form Class Diary >> btnSave_Click event while processing casecalendar.ID = " & CStr(ii_PickParm1) & ", there was a MySqlException Connecting to the Subrogation Database. MySQL error message follows: " & myerror.Message, _
"*************(TM) Exception:", MessageBoxButtons.OK, MessageBoxIcon.Stop)
Catch ex As Exception
MessageBox.Show("#14.0 In screen form Class Diary >> btnSave_Click event while processing casecalendar.ID = " & CStr(ii_PickParm1) & ", there was a General Exception Connecting to the Subrogation Database. CLR error message follows: " & ex.Message, _
"*************(TM) Exception:", MessageBoxButtons.OK, MessageBoxIcon.Stop)
Finally
If conn.State <> ConnectionState.Closed Then conn.Close()
End Try
Me.io_IWasCalledBy_Object.SetDgDiaryRecords() 'Refreshes the DataGrid on the MyFile screen.
Me.Close()
//++++++++++++
My bug was in line
& "Narrative = '" & Me.txtDiaryNarrative.Text & "', " _
I didn't have the single quote that you see between the last double quotes at the end of the line just ahead of the comma. The CLR exception was thrown in the Catch clause identified by the literal #14.0
//++++++++++++
I didn't save any information about the CLR exception. If you want me to put my SQL syntax error back in my source code, build and run an executable, I can do that. I don't have much experience interperting CLR exceptions. The VS2008 IDE was running in debug mode when I figured this out. The VS2008 debugger had an exception object viewer that provided a lot of information, but I have no experience capturing that in a way that I could include it here except to make a screen shot of the part of the object viewer that you would find most helpful. If there is a way to accomplish capture of the content of the exception object viewer and you can communicate that to me, I would be thrilled to cooperate with you on that.
Suggested fix:
I have no clue. I have never written any C# or C++. This is my first bug report on MySQL. I hope you find if of value, and a useful investment of your time. I would be glad to cooperate and assist you in any way I can.
Description: My application is written in VB.NET 2008. My interface to MySQL server version 5.1.43 using localhost is through the MySQL .NET adapter version 6.2.2 using all dynamicly generated SQL statements. I do not have any static bound controls anywhere in my whole application. The SQL statement generated by my code was Update casecalendar SET DueDateTime = STR_TO_DATE('20100305 05:00 PM','%Y%m%d %h:%i %p'), AssignedTo = 'charlotte', Priority = '3', Done = 'N', Action_cd = 'SGND-CNSNTJDGMNT2DEF', Narrative = 'test NARRATIVE test 2010-03-04 @ 10:14 , AppUID = ' joe', JobRunID = 0, PrgmFunction = 'Update Diary.btnSave_Click' WHERE casecalendar.ID = 12 The SQL syntax error is that immediately after ". . . @ 10:14" there should have been a single quote, but it is not there. That was my bug. I expect that this will cause a MySQL exception thrown with an error message like "There is an error in your SQL syntax in line 1. Check the manual for the version of MySQL server you are using for an error near '@ 10:14'". That is as good as I can remeber what that particular MySQL error message is like. After I fixed my bug in my VB.NET program source code the program executed without throwing an exception, and the database table row was updated with the new data. How to repeat: I hope that you have a test environment that you can paste my SQL statement into and see what you get. I'll paste the snippet of source code where the exception was thrown. I hope it is not too long for you. Try If ii_PickParm1 > 0 Then strSQL = "Update casecalendar " _ & "SET DueDateTime = " & "STR_TO_DATE('" & (Format(Me.DateTimePicker1.Value, "yyyyMMdd") & " " & Me.mtbDueTime.Text).Trim & "','%Y%m%d %h:%i %p')" & ", " _ & "AssignedTo = '" & Me.cmboAssignedTo.SelectedValue.ToString & "', " _ & "Priority = '" & Me.cmboPriority.SelectedValue.ToString & "', " _ & "Done = '" & is_Done & "', " _ & "Action_cd = '" & Me.cmboAction.SelectedValue.ToString & "', " _ & "Narrative = '" & Me.txtDiaryNarrative.Text & "', " _ & "AppUID = ' " & gs_UserID & "', " _ & "JobRunID = 0, " _ & "PrgmFunction = 'Update Diary.btnSave_Click' " _ & "WHERE casecalendar.ID = " & CStr(ii_PickParm1) Else strSQL = "INSERT INTO casecalendar " _ & "(CasesTableID, DueDateTime, CreatedBy, AssignedTo, Priority, Done, Action_cd, Narrative, UpdateDateTime, InsertDateTime, DBCONNID, AppUID, JobRunID, PrgmFunction) " _ & "VALUES (" & ii_CasesTableID.ToString & "," _ & "STR_TO_DATE('" & (Format(Me.DateTimePicker1.Value, "yyyyMMdd") & " " & Me.mtbDueTime.Text).Trim & "','%Y%m%d %h:%i %p')" & ",'" _ & gs_UserID & "','" _ & Me.cmboAssignedTo.SelectedValue.ToString & "','" _ & Me.cmboPriority.SelectedValue.ToString & "','" _ & is_Done & "','" _ & Me.cmboAction.SelectedValue.ToString & "','" _ & Me.txtDiaryNarrative.Text _ & "',null,now(),'" _ & gs_DBCONNID & "','" _ & gs_UserID & "'," _ & "0,'Insert Diary.btnSave_Click');" End If 'ii_PickParm1 > 0 Then Catch ex As Exception MessageBox.Show("#12.0 In screen form Class Diary btnSave_Click event, there was an General Exception thrown while processing Diary.ID = " & CStr(ii_PickParm1) & ". CLR error message follows: " _ & ex.Message, "*************(TM) Exception:", MessageBoxButtons.OK, MessageBoxIcon.Stop) End Try Try conn.Open() Try 'This Try-Catch block performs the Insert/Update to the casecalendar database table. 'Debug Code -- Save 2007-02-01 by Ted Palmer 'MessageBox.Show("Value strSQL: " & strSQL, _ '"Subrogation Debug Message:", MessageBoxButtons.OK, MessageBoxIcon.Stop) myCommand.Connection = conn myCommand.CommandText = strSQL intReturn = myCommand.ExecuteNonQuery() Console.WriteLine("intReturn: " & CStr(intReturn)) Catch myerror As MySqlException MessageBox.Show("#15.0 In screen form Class Diary >> btnSave_Click event there was a MySqlException writing casecalendar.ID = " & CStr(ii_PickParm1) & " to the casecalendar table in the Subrogation database. MySQL error message follows: " _ & myerror.Message, _ "*************(TM) Exception:", MessageBoxButtons.OK, MessageBoxIcon.Stop) End Try Catch myerror As MySqlException MessageBox.Show("#13.0 In screen form Class Diary >> btnSave_Click event while processing casecalendar.ID = " & CStr(ii_PickParm1) & ", there was a MySqlException Connecting to the Subrogation Database. MySQL error message follows: " & myerror.Message, _ "*************(TM) Exception:", MessageBoxButtons.OK, MessageBoxIcon.Stop) Catch ex As Exception MessageBox.Show("#14.0 In screen form Class Diary >> btnSave_Click event while processing casecalendar.ID = " & CStr(ii_PickParm1) & ", there was a General Exception Connecting to the Subrogation Database. CLR error message follows: " & ex.Message, _ "*************(TM) Exception:", MessageBoxButtons.OK, MessageBoxIcon.Stop) Finally If conn.State <> ConnectionState.Closed Then conn.Close() End Try Me.io_IWasCalledBy_Object.SetDgDiaryRecords() 'Refreshes the DataGrid on the MyFile screen. Me.Close() //++++++++++++ My bug was in line & "Narrative = '" & Me.txtDiaryNarrative.Text & "', " _ I didn't have the single quote that you see between the last double quotes at the end of the line just ahead of the comma. The CLR exception was thrown in the Catch clause identified by the literal #14.0 //++++++++++++ I didn't save any information about the CLR exception. If you want me to put my SQL syntax error back in my source code, build and run an executable, I can do that. I don't have much experience interperting CLR exceptions. The VS2008 IDE was running in debug mode when I figured this out. The VS2008 debugger had an exception object viewer that provided a lot of information, but I have no experience capturing that in a way that I could include it here except to make a screen shot of the part of the object viewer that you would find most helpful. If there is a way to accomplish capture of the content of the exception object viewer and you can communicate that to me, I would be thrilled to cooperate with you on that. Suggested fix: I have no clue. I have never written any C# or C++. This is my first bug report on MySQL. I hope you find if of value, and a useful investment of your time. I would be glad to cooperate and assist you in any way I can.