VB.net Error: cmd.ExecuteNonQuery

[kugan80]

Hi Guys,

im facing problem at cmd.ExecuteNonQuery()
kindly correct me wer is the mistake? n how to correct?

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim sqlinsert As String
        ' We use the INSERT statement which tells our program to add the information
        ' from the Forms Text fields into the Databases columns.

        sqlinsert = "INSERT INTO Contacts(FirstName, LastName, Address, CompanyName, CompanyAddress, PersonalAC, CompanyAC, PhoneNumber, Remarks, IDNum)" & _
      "VALUES(@FirstName, @LastName, @Address, @CompanyName, @CompanyAddress, @PersonalAC, @CompanyAC, @PhoneNumber, @Remarks, @IDNum)"
        Dim cmd As New OleDbCommand(sqlinsert, con1)
        ' This assigns the values for our columns in the DataBase.
        ' To ensure the correct values are written to the correct column
        cmd.Parameters.Add(New OleDbParameter("@FirstName", txtFirst.Text))
        cmd.Parameters.Add(New OleDbParameter("@LastName", txtLast.Text))
        cmd.Parameters.Add(New OleDbParameter("@Address", txtAddress.Text))
        cmd.Parameters.Add(New OleDbParameter("@CompanyName", txtCompanyN.Text))
        cmd.Parameters.Add(New OleDbParameter("@CompanyAddress", txtCompanyA.Text))
        cmd.Parameters.Add(New OleDbParameter("@PersonalAC", txtPersonalAC.Text))
        cmd.Parameters.Add(New OleDbParameter("@CompanyAC", txtCompanyAC.Text))
        cmd.Parameters.Add(New OleDbParameter("@PhoneNumber", txtPhone.Text))
        cmd.Parameters.Add(New OleDbParameter("@Remarks", txtRemarks.Text))
        cmd.Parameters.Add(New OleDbParameter("@IDNum", txtID.Text))
        ' This is what actually writes our changes to the DataBase.
        ' You have to open the connection, execute the commands and
        ' then close connection.
        con1.Open()
        cmd.ExecuteNonQuery() ' ERROR AT HERE
        con1.Close()
        ' This are subs in Module1, to clear all the TextBoxes on the form
        ' and refresh the DataGridView on the MainForm to show our new records.
        ClearTextBox(Me)
        RefreshDGV()
        Me.Close()
    End Sub

[M.Waqas Aslam]

try

cmd.parameters.addwithvalue("@param",value)

may be this will help you , but it is better to tell us what error you got at cmd.executenonquery()

Regards

[rponraj]

I am getting the following error at cmd.ExecuteNonQuery()

SqlException was unhandled by user

[Pgmer]

Dear rponraj please open a new thread for your problem. do not post in some one else post.

[Maligui]

Use a Try..Catch Block and capture the error.

Try
    ' the procedure
Catch Ex as Exception
    MsgBox (ex.toString)
End Try

You can also try to print out the parameters.

For Each p As OleDb.OleDbParameter In cmd.Parameters
    Console.WriteLine("Name:{0} Value{1}", p.ParameterName, p.Value.ToString)
Next

Be sure that if a parameter is blank, empty, or is nothing, it must be set to DBNull.

cmd.Parameters.Add(New OleDbParameter("@FirstName", IIf(txtFirst.Text = "", DBNull.Value, txtFirst.Text)))

[Ahmad_23]

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        If TextBox1.Text = "" Then
            MsgBox("Nomor Faktur Belum di isi !! ", MsgBoxStyle.Exclamation,
            "Peringatan")
        Else
            Dim simpan1 As String
            CMD = Conn.CreateCommand
            Call bukaDB()
            simpan = "INSERT INTO tbbeli (nofak,tanggal,kodesup,totalitem,totalbayar)VALUES (@p1,@p2,@p3,@p4,@p5)"
            simpan1 = "INSERT INTO tbdetailbeli (nofak,kodebarang,jumlah) VALUES(@p6,@p7,@p8) "
            ubah = "UPDATE tbbarang SET stok=stok+@p9 WHERE kodebarang = @p10"
            With CMD
                .CommandText = simpan
                .Connection = Conn
                .Parameters.AddWithValue("p1", MySqlDbType.String).Value = TextBox1.Text
                .Parameters.AddWithValue("p2", MySqlDbType.DateTime).Value = Format(Now, "dd-MM-yyyy")
                .Parameters.AddWithValue("p3", MySqlDbType.String).Value = ComboBox1.Text
                .Parameters.AddWithValue("p4", MySqlDbType.Int32).Value = Label9.Text
                .Parameters.AddWithValue("p5", MySqlDbType.Int32).Value = Label10.Text
                .ExecuteNonQuery()   "(ERROR IN HERE)"
                Conn.Close()
            End With
            For i As Integer = 0 To DataGridView1.Rows.Count - 2
                CMD = Conn.CreateCommand
                With CMD
                    .CommandText = simpan
                    .Connection = Conn
                    .Parameters.AddWithValue("p6", MySqlDbType.String).Value = TextBox1.Text
                    .Parameters.AddWithValue("p7", MySqlDbType.String).Value = DataGridView1.Rows(i).Cells(0).Value
                    .Parameters.AddWithValue("p8", MySqlDbType.Int32).Value = DataGridView1.Rows(i).Cells(3).Value
                    .ExecuteNonQuery()        "(ERROR IN HERE)"
                    Conn.Close()
                End With
                CMD = Conn.CreateCommand
                With CMD
                    .CommandText = ubah
                    .Connection = Conn
                    .Parameters.AddWithValue("p9", MySqlDbType.UInt32).Value = DataGridView1.Rows(i).Cells(3).Value
                    .Parameters.AddWithValue("p10", MySqlDbType.String).Value = DataGridView1.Rows(i).Cells(0).Value
                    .ExecuteNonQuery()  "(ERROR IN HERE)"
                    Conn.Close()
                End With
            Next
        End If
        Conn.Close()
        CMD.Dispose()
        bersih()
    End Sub

[xrjf]

What an old thread, isn't it?

[Ahmad_23]

I am having trouble ... storing data changes to tbbarang and tbsuplier from input transaction purchase data

[adam_k]

1st of all this is a very old thread. You should have opened a new one.
2nd I see you closing the Conn but never open it. Try to open it before or after you assign it to the command.
If this doesn't solve it, open a new thread, post the code again and let us know the specific error you are getting.

Good luck

[Neena_3]

I believe, you have not started connection with database

Please review below code and try to connect with database

Public Sub CreateCommand(ByVal queryString As String, _
ByVal connectionString As String)
Using connection As New SqlConnection(connectionString)
Dim command As New SqlCommand(queryString, connection)
command.Connection.Open()
command.ExecuteNonQuery()
End Using
End Sub