how to run multiple insert query on single click in vb.net-CodePudding

Hi i'm making an accounting software for small business as a freelance where I have to store invoice also make ledger from it's query I had created another table for ledger and one table for invoice items but now problem is when I insert in ledger first all the details of ledger is insert into invoice too if I move to invoice before ledger it gives error of parameter has no default value. my code looks like this

    Private Sub add_Click(sender As Object, e As EventArgs) Handles add.Click
    If client_name.Text = "" Or company_name.Text = "" Or email.Text = "" Or contact_no.Text = "" Or address.Text = "" Or delivery_address.Text = "" Then
        MessageBox.Show("Please Fill all fields", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Else
        If issaved = 1 Then
            MessageBox.Show("Fees already saved", "Fees saved", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            saveorder()
            issaved = 1
        End If
    End If

End Sub

Public Sub saveorder()
    cn.Close()
    cn.Open()
    Dim debit As Int64 = Convert.ToInt64(total_basic_amount.Text)   Convert.ToInt64(freigh_rate.Text)   Convert.ToInt64(delivery_rate.Text)
    cmd.Connection = cn
    cmd.CommandText = "insert into ledger([client_name],[registration_no],[company_name],[date],[credit],[debit])Values(@client_name,@registration_no,@company_name,@date,@credit,@debit)"
    cmd.Parameters.Add(New OleDbParameter("@client_name", client_name.Text.ToString))
    cmd.Parameters.AddWithValue("@registration_no", reg_no.Text.ToString)
    cmd.Parameters.AddWithValue("@company_name", company_name.Text.ToString)
    cmd.Parameters.AddWithValue("@date", datetime.Value.ToShortDateString)
    cmd.Parameters.AddWithValue("@credit", 0)
    cmd.Parameters.Add(New OleDbParameter("@debit", debit))
    cmd.ExecuteNonQuery()
    MessageBox.Show("ledger debit stored")
    cn.Close()
    cn.Open()

    cmd.Connection = cn
    cmd.CommandText = "insert into ledger([client_name],[registration_no],[company_name],[date],[credit],[debit])Values(@client_name,@registration_no,@company_name,@date,@credit,@debit)"
    cmd.Parameters.AddWithValue("@client_name", client_name.Text.ToString)
    cmd.Parameters.AddWithValue("@registration_no", reg_no.Text.ToString)
    cmd.Parameters.AddWithValue("@company_name", company_name.Text.ToString)
    cmd.Parameters.AddWithValue("@date", datetime.Value.ToShortDateString)
    cmd.Parameters.AddWithValue("@credit", Convert.ToInt64(advance_received_amount.Text.ToString))
    cmd.Parameters.AddWithValue("@debit", 0)
    cmd.ExecuteNonQuery()
    MessageBox.Show("ledger credit stored")
    cn.Close()
    cn.Open()

    cmd.Connection = cn
    cmd.CommandText = "insert into invoice([invoice_date],[client_name],[client_company_name],[client_email],[client_contact_no],[client_address],[client_delivery_address],[total_basic_amount],[freight_rate],[delivery_rate],[advanced_amount],[pending_amount])
        values(@invoice_date,@client_name,@client_company_name,@client_email,@client_contact_no,@client_address,@client_delivery_address,@total_basic_amount,@freight_rate,@delivery_rate,@advanced_amount,@pending_amount)"
    cmd.Parameters.AddWithValue("@invoice_date", datetime.Value.ToShortDateString)
    cmd.Parameters.AddWithValue("@client_name", client_name.Text.ToString)
    cmd.Parameters.AddWithValue("@client_company_name", company_name.Text.ToString)
    cmd.Parameters.AddWithValue("@client_email", email.Text.ToString)
    cmd.Parameters.AddWithValue("@client_contact_no", contact_no.Text.ToString)
    cmd.Parameters.AddWithValue("@client_address", address.Text.ToString)
    cmd.Parameters.AddWithValue("@client_delivery_address", delivery_address.Text.ToString)
    cmd.Parameters.AddWithValue("@total_basic_amount", total_basic_amount.Text.ToString)
    cmd.Parameters.AddWithValue("@freight_rate", freigh_rate.Text.ToString)
    cmd.Parameters.AddWithValue("@delivery_rate", delivery_rate.Text.ToString)
    cmd.Parameters.AddWithValue("@advanced_amount", advance_received_amount.Text.ToString)
    cmd.Parameters.AddWithValue("@pending_amount", total_pending_amount.Text.ToString)
    cmd.ExecuteNonQuery()
    MessageBox.Show("invoice stored")
    cn.Close()
    cn.Open()

    For i As Integer = 0 To DataGridView1.Rows.Count - 1
        cmd.Connection = cn
        cmd.CommandText = "insert into invoice_items([invoice_id],[item_name],[qty],[rate])Values(@invoice_id,@item_name,@qty,@rate)"
        cmd.Parameters.AddWithValue("@invoice_id", invoice_id.Text.ToString)
        cmd.Parameters.AddWithValue("@item_name", DataGridView1.Rows(i).Cells(2).Value)
        cmd.Parameters.AddWithValue("@qty", DataGridView1.Rows(i).Cells(3).Value)
        cmd.Parameters.AddWithValue("@rate", DataGridView1.Rows(i).Cells(4).Value)
        cmd.ExecuteNonQuery()
    Next

End Sub

Public Sub saveorder()
    cn.Close()
    cn.Open()
    Dim debit As Int64 = Convert.ToInt64(total_basic_amount.Text)   Convert.ToInt64(freigh_rate.Text)   Convert.ToInt64(delivery_rate.Text)
    cmd.Connection = cn
    cmd.CommandText = "insert into ledger([client_name],[registration_no],[company_name],[date],[credit],[debit])Values(@client_name,@registration_no,@company_name,@date,@credit,@debit)"
    cmd.Parameters.Add(New OleDbParameter("@client_name", client_name.Text.ToString))
    cmd.Parameters.AddWithValue("@registration_no", reg_no.Text.ToString)
    cmd.Parameters.AddWithValue("@company_name", company_name.Text.ToString)
    cmd.Parameters.AddWithValue("@date", datetime.Value.ToShortDateString)
    cmd.Parameters.AddWithValue("@credit", 0)
    cmd.Parameters.Add(New OleDbParameter("@debit", debit))
    cmd.ExecuteNonQuery()
    MessageBox.Show("ledger debit stored")
    cn.Close()
    cn.Open()

    cmd.Connection = cn
    cmd.CommandText = "insert into ledger([client_name],[registration_no],[company_name],[date],[credit],[debit])Values(@client_name,@registration_no,@company_name,@date,@credit,@debit)"
    cmd.Parameters.AddWithValue("@client_name", client_name.Text.ToString)
    cmd.Parameters.AddWithValue("@registration_no", reg_no.Text.ToString)
    cmd.Parameters.AddWithValue("@company_name", company_name.Text.ToString)
    cmd.Parameters.AddWithValue("@date", datetime.Value.ToShortDateString)
    cmd.Parameters.AddWithValue("@credit", Convert.ToInt64(advance_received_amount.Text.ToString))
    cmd.Parameters.AddWithValue("@debit", 0)
    cmd.ExecuteNonQuery()
    MessageBox.Show("ledger credit stored")
    cn.Close()
    cn.Open()

    cmd.Connection = cn
    cmd.CommandText = "insert into invoice([invoice_date],[client_name],[client_company_name],[client_email],[client_contact_no],[client_address],[client_delivery_address],[total_basic_amount],[freight_rate],[delivery_rate],[advanced_amount],[pending_amount])
        values(@invoice_date,@client_name,@client_company_name,@client_email,@client_contact_no,@client_address,@client_delivery_address,@total_basic_amount,@freight_rate,@delivery_rate,@advanced_amount,@pending_amount)"
    cmd.Parameters.AddWithValue("@invoice_date", datetime.Value.ToShortDateString)
    cmd.Parameters.AddWithValue("@client_name", client_name.Text.ToString)
    cmd.Parameters.AddWithValue("@client_company_name", company_name.Text.ToString)
    cmd.Parameters.AddWithValue("@client_email", email.Text.ToString)
    cmd.Parameters.AddWithValue("@client_contact_no", contact_no.Text.ToString)
    cmd.Parameters.AddWithValue("@client_address", address.Text.ToString)
    cmd.Parameters.AddWithValue("@client_delivery_address", delivery_address.Text.ToString)
    cmd.Parameters.AddWithValue("@total_basic_amount", total_basic_amount.Text.ToString)
    cmd.Parameters.AddWithValue("@freight_rate", freigh_rate.Text.ToString)
    cmd.Parameters.AddWithValue("@delivery_rate", delivery_rate.Text.ToString)
    cmd.Parameters.AddWithValue("@advanced_amount", advance_received_amount.Text.ToString)
    cmd.Parameters.AddWithValue("@pending_amount", total_pending_amount.Text.ToString)
    cmd.ExecuteNonQuery()
    MessageBox.Show("invoice stored")
    cn.Close()
    cn.Open()

    For i As Integer = 0 To DataGridView1.Rows.Count - 1
        cmd.Connection = cn
        cmd.CommandText = "insert into invoice_items([invoice_id],[item_name],[qty],[rate])Values(@invoice_id,@item_name,@qty,@rate)"
        cmd.Parameters.AddWithValue("@invoice_id", invoice_id.Text.ToString)
        cmd.Parameters.AddWithValue("@item_name", DataGridView1.Rows(i).Cells(2).Value)
        cmd.Parameters.AddWithValue("@qty", DataGridView1.Rows(i).Cells(3).Value)
        cmd.Parameters.AddWithValue("@rate", DataGridView1.Rows(i).Cells(4).Value)
        cmd.ExecuteNonQuery()
    Next

End Sub

I also tried connection open close before after to reset but it didn't work

CodePudding user response：

Always clear parameters before starting a new run using the same command:

cmd.Parameters.Clear();