asked on

VB.Net - Trouble inserting values from a textbox in to an access database

Afternoon,

I am trying to save the values of several textboxes on my windows form in to an Access 2010 table and am having trouble figuring out the code to do this.

What I have so far is below.

Private Sub SaveM_Record()

        Dim conn As New OleDbConnection
        Dim cmd As New OleDbCommand
        Dim sSQL As String = String.Empty

        Try
            'get connection string declared in the modFunctions.vb and assing it to conn variable
            conn = New OleDbConnection(sConnString)
            conn.Open()
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text

If Me.txtMUnit.Text = "JBR" Then

                sSQL = "INSERT INTO tblSBTHist ( [ChqNum], [ChqDate], [Amount], [PayTo], [Reference], [DebitAcc], [CreditAcc], [Currency], [PayeeBank], [Unit])"
                sSQL = sSQL & "  VALUES(@ChqNum, @ChqDate, @Amount, @PayTo, @Reference, @DebitAcc, @CreditAcc, @Currency, @PayeeBank, @Unit)"
                cmd.CommandText = sSQL

            ElseIf Me.txtMUnit.Text = "HGHB" Then

                sSQL = "INSERT INTO tblBSBILHist ( [ChqNum], [ChqDate], [Amount], [PayTo], [Reference], [DebitAcc], [CreditAcc], [Currency], [PayeeBank], [Unit])"
                sSQL = sSQL & "  VALUES(@ChqNum, @ChqDate, @Amount, @PayTo, @Reference, @DebitAcc, @CreditAcc, @Currency, @PayeeBank, @Unit)"
                cmd.CommandText = sSQL

            Else

                MsgBox("Unit not defined in database." & vbNewLine & "Contact Administrator for assistance", MsgBoxStyle.Exclamation, "WARNING")

            End If


            'set paramaters
            cmd.Parameters.Add("@ChqNum", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMChqNum.Text)) > 0, Me.txtMChqNum.Text, DBNull.Value)
            cmd.Parameters.Add("@ChqDate", OleDbType.VarChar).Value = IIf(Len(Trim(Me.DateTimePickerM.Text)) > 0, Me.DateTimePickerM.Text, DBNull.Value)
            cmd.Parameters.Add("@Amount", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMAmt.Text)) > 0, Me.txtMAmt.Text, DBNull.Value)
            cmd.Parameters.Add("@PayTo", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMPayTo.Text)) > 0, Me.txtMPayTo.Text, DBNull.Value)
            cmd.Parameters.Add("@DebitAcc", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMDbtAccNum.Text)) > 0, Me.txtMDbtAccNum.Text, DBNull.Value)
            cmd.Parameters.Add("@CreditAcc", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMCrdAccNum.Text)) > 0, Me.txtMCrdAccNum.Text, DBNull.Value)
            cmd.Parameters.Add("@Currency", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMCurr.Text)) > 0, Me.txtMCurr.Text, DBNull.Value)
            cmd.Parameters.Add("@PayeeBank", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMBnkPayee.Text)) > 0, Me.txtMBnkPayee.Text, DBNull.Value)
            cmd.Parameters.Add("@Unit", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMUnit.Text)) > 0, Me.txtMUnit.Text, DBNull.Value)
cmd.ExecuteNonQuery()

MsgBox("Data has been saved.")

        Catch ex As Exception
            MsgBox(ErrorToString)
        Finally
            conn.Close()
        End Try

    End Sub

Open in new window

Is someone able to assist please?

Kind Regards,
N

Éric Moreau

what't wrong with your code?

Kevin

ASKER

Apologies forgot to include the error.

Am getting an error for line 43 that says, oldbexception was unhandled, "No value given for one or more required parameters."

So I think Im missing something somewhere.

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Lokesh B R