fatal error when using command.executenonquery

Question

I'm getting an error when I put the command.ExecuteNonQuery() but when I remove the code it says

register done

but it is not inserting anything into the database.

Try

    If TextBox1.Text = Nothing Or TextBox2.Text = Nothing Or TextBox3.Text = Nothing Or TextBox4.Text = Nothing Or TextBox5.Text = Nothing Or TextBox6.Text = Nothing Or TextBox7.Text = Nothing Or TextBox8.Text = Nothing Or ComboBox1.Text = Nothing Then
        MsgBox("please complete the fields!", MsgBoxStyle.Critical)

    Else
        MysqlConn.Open()
        command.Connection = MysqlConn
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT * FROM customer WHERE email = @email"
        READER = command.ExecuteReader
        If READER.HasRows Then
            MsgBox("e-mail is already in use!", MsgBoxStyle.Critical)
            MysqlConn.Close()
        Else
            If Not READER.IsClosed Then
                READER.Close()
            End If
            command.Connection = MysqlConn
            command.CommandType = CommandType.Text
            command.CommandText = "INSERT INTO customer (`username`, `firstname`, `lastname`, `password`, `confirmpass`, `securityq`, `securitya`, `gender`, `email`, `contactno`) VALUES (@uname,@fname,@lname,@pword,@confirm,@secuq,@secuqa,@gender,@email,@contactno)"
            With command.Parameters
                .AddWithValue("@uname", TextBox1.Text)
                .AddWithValue("@fname", TextBox2.Text)
                .AddWithValue("@lname", TextBox3.Text)
                .AddWithValue("@pword", TextBox4.Text)
                .AddWithValue("@confirm", TextBox5.Text)
                .AddWithValue("@secuq", ComboBox1.SelectedItem)
                .AddWithValue("@secuqa", TextBox6.Text)
                If RadioButton1.Checked Then
                    .AddWithValue("@gender", "male")
                ElseIf RadioButton2.Checked Then
                    .AddWithValue("@gender", "female")
                End If
                .AddWithValue("@email", TextBox7.Text)
                .AddWithValue("@contactno", TextBox8.Text)
            End With
            command.ExecuteNonQuery()

            MsgBox("Registration done!", MsgBoxStyle.OkOnly)

            TextBox1.Clear()
            TextBox2.Clear()
            TextBox3.Clear()
            TextBox4.Clear()
            TextBox5.Clear()
            TextBox6.Clear()
            RadioButton1.Checked = False
            RadioButton2.Checked = False
            TextBox7.Clear()
            TextBox8.Clear()
            ComboBox1.ResetText()

            Form1.Show()
            Hide()


            MysqlConn.Close()
        End If

    End If
Catch ex As mysqlException

    MessageBox.Show(ex.Message)

Finally

    MysqlConn.Dispose()


End Try

Password is a reserved word for MySql, you need to put it between backticks — Steve, Jun 26 '17 at 20:01
It might have something to do with "@contacno" in your INSERT text, and the variable name "@contactno" but it's hard to tell with the provided. — Aaron, Jun 26 '17 at 20:01
@Aaron I already change the contacno to contactno still having the same problem T_T — Troy Ricamata, Jun 26 '17 at 20:05
He's saying to use \`password\` ... The standard key for tilde ~, not quote ... The standard key for " ... — Aaron, Jun 26 '17 at 20:09
Press the ALT key and type 096 before and after the word _password_ in your field names list — Steve, Jun 26 '17 at 20:09
Also see here https://stackoverflow.com/questions/261455/using-backticks-around-field-names — Steve, Jun 26 '17 at 20:10
Wrap your command.ExecuteNonQuery() inside a Try...Catch block and display the exception that is being thrown. From the 1st two comments, I would expect a Syntax Error in statement or a parameter error. — Charles May, Jun 26 '17 at 20:10
@CharlesMay it is already inside a try catch :( I don't get it when I remove the executenonquery there is no error but wont insert the data into database. — Troy Ricamata, Jun 26 '17 at 20:14
you don't get it without the executenonquery because without that, you're not executing anything. Care to show the extent of the try...catch area. Sometimes people bury the exception so it's good to see. — Charles May, Jun 26 '17 at 20:15
@CharlesMay that is all the code inside the try catch. the code is too long how can I paste it here — Troy Ricamata, Jun 26 '17 at 20:20
You are not adding the parameter `@email` for `command.CommandText = "SELECT * FROM customer WHERE email = @email"` before the statement `READER = command.ExecuteReader`. — TnTinMn, Jun 26 '17 at 20:22
@TnTinMn I've already done it and the error is still there. and there's another error for that. the parameter email is already bla bla forgot what exact message error. sorry ^_^ — Troy Ricamata, Jun 26 '17 at 20:23
Can you at least show the Catch block area (start at Catch and end at End Try) It sounds like you have this all wrapped in a single Try Catch and there should be some exceptions being thrown. — Charles May, Jun 26 '17 at 20:25
@CharlesMay Catch ex As mysqlException MessageBox.Show(ex.Message) Finally MysqlConn.Dispose() End Try End Sub yes it is wrapped in a single try catch. sorry. I'm just a beginner T_T — Troy Ricamata, Jun 26 '17 at 20:28
ok, do you get a different error if you change to `Catch ex as Exception` instead of `mysqlException` — Charles May, Jun 26 '17 at 20:50
OK, Not 100% sure about this but I just saw a statement on another site that the @ sign is not a valid parameter in MySQL and to use ? so maybe change your parameters to ?uname etc... and fix that in your sql statment. — Charles May, Jun 26 '17 at 21:03
@CharlesMay it didn't change anything. :( the fatal error encountered during command execution is still there T_T — Troy Ricamata, Jun 26 '17 at 21:07
Try to look at the exception.InnerException.Message if any is present and tell us what you see — Steve, Jun 26 '17 at 21:53
Then what is the schema of your customer table? You add everything with AddWithValue but this is dangerous for its implicit conversion — Steve, Jun 26 '17 at 22:25
I wish to know the definitions of the columns in the table customer. The datatype, the size and if they allow nulls or not. — Steve, Jun 26 '17 at 22:44
its okay now. there's a new error. after checking for duplicate email. after the messagebox ok and after inputting new email. error is parameter email is already bla bla. how to fix that ? — Troy Ricamata, Jun 26 '17 at 22:52
I'd say it's because you've already added a parameter called email when you added parameters to the command and you need to clear the parameter list prior to adding parameters to the command for the insert statement. command.parameters.clear() before you start your new set of parameters. And for future reference, please don't use bla bla when you have people trying to troubleshoot your issues. The best thing you can give us is descriptive errors that occur. — Charles May, Jun 26 '17 at 23:01
Dont use global COnnections; dont create a Global reader object, DOnt use AddWithValue and never ever store passwords as plain text — Ňɏssa Pøngjǣrdenlarp, Jun 26 '17 at 23:39

score 0 · Answer 1 · answered Jun 26 '17 at 20:22

0

If you're using the same command object, you'll need to close that reader before you can execute another command:

If Not READER.IsClosed Then
    READER.Close()
End If

answered Jun 26 '17 at 20:22

Ed Mooers

26
4

where should I add that sir ? – Troy Ricamata Jun 26 '17 at 20:25
Right before you close the connection. In that case, unless you're using it to connect to a different database, you won't need to close the connection and reopen it - you can just use the existing open connection you have to the database. So, to recap, add those lines to REPLACE MysqlConn.Close() and MysqlConn.Open() immediately following the Else statement. – Ed Mooers Jun 26 '17 at 20:31
can you edit your code with all the updates you've made to the code you've provided so that we can ensure that all the fixes were added properly? – Charles May Jun 26 '17 at 20:41
@CharlesMay ive updated the code. but the fatal error is still there. – Troy Ricamata Jun 26 '17 at 20:45

fatal error when using command.executenonquery

1 Answers1