Fatal Error Encounter During Command Execution MySQL VB

Question

ijust finish my code for inserting data using the vb and mySQL but when i run my webpage it seem have an error Fatal Error Encounter During Command Execution . Please help some how to solve it. below is my code.

Imports System.Data.SqlClient
Imports MySql.Data.MySqlClient


Partial Class Request

    Inherits System.Web.UI.Page

    Dim MessageBox As Object

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        txt1.Focus()
        txt2.Focus()
        txt3.Focus()
        txt4.Focus()
        txt5.Focus()
        txt6.Focus()
        txt7.Focus()
        ddl1.Focus()
        ddl2.Focus()
        ddl3.Focus()
        ddl4.Focus()
    End Sub

    Protected Sub btnsubmit_Click(sender As Object, e As EventArgs) Handles btnsubmit.Click


        'Create sql connection and fetch data from database based on employee id

        Dim conn As New MySql.Data.MySqlClient.MySqlConnection

        Dim strConnectionString As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString

        Try
            conn.ConnectionString = strConnectionString
            conn.Open()

        Catch ex As MySql.Data.MySqlClient.MySqlException
            MessageBox.Show(ex.Message)
        End Try
        '  Dim cr_id As String
        ' cr_id = "CR004"
        Dim iReturn As Boolean
        Using SQLConnection As New MySqlConnection(strConnectionString)
            Using sqlCommand As New MySqlCommand()
                sqlCommand.Connection = SQLConnection
                With sqlCommand




                    .CommandText = "INSERT INTO cr_record(idcr_record,Emplid,Nama,date,DeptDesc,email,change,reasonchange,problem,priority,reasondescription,systemrequest) VALUES (@IDCR,@Emplid,@Nama,@date,@DeptDesc,'@email,@change,@reasonchange,@problem,@priority,@reasondescription,@systemrequest)"
                    ' .CommandTimeout = 5000000
                    .CommandType = Data.CommandType.Text

                    .Parameters.AddWithValue("@Emplid", txt1.Text)
                    .Parameters.AddWithValue("@Nama", TextBox1.Text)
                    .Parameters.AddWithValue("@date", txt5.Text)
                    .Parameters.AddWithValue("@DeptDesc", txt2.Text)
                    .Parameters.AddWithValue("@email", txt4.Text)
                    .Parameters.AddWithValue("@change", ddl2.Text)
                    .Parameters.AddWithValue("@reasonchange", txt6.Text)
                    .Parameters.AddWithValue("@problem", ddl3.Text)
                    .Parameters.AddWithValue("@priority", rbl1.Text)
                    .Parameters.AddWithValue("@reasondescription", txt7.Text)
                    .Parameters.AddWithValue("@systemrequest", ddl4.Text)




                End With
                Try
                    SQLConnection.Open()
                    ' sqlCommand.ExecuteNonQuery()
                    sqlCommand.ExecuteNonQuery()
                    iReturn = True
                    MsgBox("Added Successfully")
                Catch ex As MySqlException
                    MsgBox(ex.Message.ToString & Err.Description)
                    iReturn = False
                Finally
                    SQLConnection.Close()
                End Try
            End Using
        End Using
        Return
    End Sub



End Class

@Szymon i not really understand what you mean but in my code this message box is only for test connection. — kolapopo, Mar 20 '14 at 02:04
You cannot display message boxes in a website. Message boxes will be displayed on the server (if at all) and there will be no one to click them. — Szymon, Mar 20 '14 at 02:06
If you do it for testing only, put a breakpoint and step through the code. — Szymon, Mar 20 '14 at 02:21
@Szymon i dont do for testing.. this webpage is one of my study..because i new in asp.net vb..i just learn from google.. im good at php. bytheway i just learn about 2week.. — kolapopo, Mar 20 '14 at 02:26
@Szymon right now it seem no error but noting happen the page just reload the page and when i check to database ,it not save? — kolapopo, Mar 20 '14 at 02:48

score 2 · Accepted Answer · answered Mar 20 '14 at 03:52

2

you probably forgot to add this parameter @IDCR

.Parameters.AddWithValue("@IDCR", toyourvariable)

answered Mar 20 '14 at 03:52

Codemunkeee

1,585
5
17
29

yes i forget this command . then i should not set my dataname in database using the reserved word CHANGE. for now all the solution is done. thanks for guide. it works @codemunkeee – kolapopo Mar 28 '14 at 07:49

Marc B · Answer 2 · 2014-03-20T02:05:13.080

1

Syntax error in your query:

[...snip...]tDesc,'@email,@change,@rea[...snip...]
                  ^---mis-placed quote.

Reserved words:

[...snip...]c,email,change,reasonc[...snip...]
                    ^^^^^^---- quote with backticks: `change`

edited Mar 20 '14 at 02:05

answered Mar 20 '14 at 01:55

Marc B

356,200
43
426
500

i have fix that error but it still give the Fatal Error again.? – kolapopo Mar 20 '14 at 02:03
That's not how this site works. You need to do debugging yourself too. – Marc B Mar 20 '14 at 02:06
no dont be miss understand what i mean. i dont you to do my code ..but i want you see my code.. im still working by myself for my code but it still error on my code the same error even i have fix the error that come suggestion from you guys.. – kolapopo Mar 20 '14 at 02:22
@March B thanks i found the error it in database because of using reserved word..the command it ok. i change it n functioning . – kolapopo Mar 28 '14 at 07:41

score 0 · Answer 3 · edited Feb 10 '16 at 22:16

Solution that i used and it really works. This error is mostly caused by a MISSING or Incorrectly Spelled Parameter declaration. eg. @FirstName mistakenly spelled for @FirtName.

Make sure that all the parameters that are declared in the sql query are all declared in the AddwithValue Parameter declaration. (It helps to count the query versus the Addwithvalues).

The best solution is for visual studio to provide information about the missing Parameter. Use a Try-Catch block. In the catch block use Messagebox.show(ex.Innerexception.Message) instead of Messagebox.show(ex.message). This will show the exact Parameter that is missing. eg. below

Try

      conCommand.Parameters.Addwithvalue("@FirstName", txtFirstName.text)
      conCommand.Parameters.Addwithvalue("@MiddleName", txtMiddleName.text)
      conCommand.Parameters.Addwithvalue("@LastName", txtLastName.text)
      conCommand.Parameters.Addwithvalue("@PhoneNo", txtPhoneno.text) 

catch ex as exception          
Messagebox.show(ex.innerexception.Message)

End Try

Hope this helps. Its really great that we share our ideas in the world of programming.

Fatal Error Encounter During Command Execution MySQL VB

3 Answers3

Linked