Member Avatar for Lejan

Hi,
Here is the code I’m trying to execute using MS Access database to return a dataset.
And i get the error "No value given for one or more required parameters".

Public Class Main_Form
    Dim conn As New OleDb.OleDbConnection

    Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
        Me.txtID.Text = ""
        Me.cboCode.Text = ""
        Me.txtExpln.Text = ""
        Me.txtExpln2.Text = ""
        Me.txtQuantity.Text = ""
        Me.txtAmt.Text = ""
        Me.txtAmt2.Text = ""
        Me.txtTotal.Text = ""
        Me.txtID.Tag = ""
        'enable button edit
        Me.btnEdit.Enabled = True
        'set button add to add label
        Me.btnAdd.Text = "Add"
        Me.txtID.Focus()
    End Sub

    Private Sub RefreshData()
        If Not conn.State = ConnectionState.Open Then
            'open connection
            conn.Open()
        End If

        Dim da As New OleDb.OleDbDataAdapter("SELECT ID as [ID], " & _
                                             "Code as [Code], Date, Explaination, Explaination2, Quantity, Amount, Amount2, Total " & _
                                             " FROM Acctg ORDER BY ID", conn)
        Dim dt As New DataTable
        'fill data to datatable
        da.Fill(dt)

        'offer data in data table into datagridview
        Me.DataGridView1.DataSource = dt

        'close connection
        conn.Close()
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim cmd As New OleDb.OleDbCommand
        If Not conn.State = ConnectionState.Open Then
            'open connection if it is not yet open
            conn.Open()
        End If

        cmd.Connection = conn
        'check whether add new or update
        If Me.txtID.Tag & "" = "" Then
            'add new 
            'add data to table
            cmd.CommandText = "INSERT INTO Acctg(ID, Code, Date, Explaination, Explaination2, Quantity, Amount, Amount2, Total) " & _
                            " VALUES(" & Me.txtID.Text & ",'" & Me.cboCode.Text & "','" & _
                            Me.DateTimePicker1.Text & "','" & Me.txtExpln.Text & "','" & _
                            Me.txtExpln2.Text & "','" & Me.txtQuantity.Text & "','" & _
                            Me.txtAmt.Text & "','" & Me.txtAmt2.Text & "','" & _
                            Me.txtTotal.Text & "')"
            cmd.ExecuteNonQuery()
        Else
            'update data in table
            cmd.CommandText = "UPDATE Acctg " & _
                        " SET ID=" & Me.txtID.Text & _
                        ", Code='" & Me.cboCode.Text & "'" & _
                        ", Date='" & Me.DateTimePicker1.Text & "'" & _
                        ", Explaination='" & Me.txtExpln.Text & "'" & _
                        ", Explaination2='" & Me.txtExpln2.Text & "'" & _
                        ", Quantity='" & Me.txtQuantity.Text & "'" & _
                        ", Amount='" & Me.txtAmt.Text & "'" & _
                        ", Amount2='" & Me.txtAmt2.Text & "'" & _
                        ", Total Amount='" & Me.txtTotal.Text & "'" & _
                        " WHERE ID=" & Me.txtID.Tag
            cmd.ExecuteNonQuery()
        End If
        'refresh data in list
        RefreshData()
        'clear form
        Me.btnReset.PerformClick()

        'close connection
        conn.Close()
    End Sub

    Private Sub MainForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        conn = New OleDb.OleDbConnection
        conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Application.StartupPath & "\data.mdb"
        '
        'get data into list
        Me.RefreshData()
    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        'check for the selected item in list
        If Me.DataGridView1.Rows.Count > 0 Then
            If Me.DataGridView1.SelectedRows.Count > 0 Then
                Dim intID As Integer = Me.DataGridView1.SelectedRows(0).Cells("id").Value
                'get data from database followed by Acctg id
                'open connection
                If Not conn.State = ConnectionState.Open Then
                    conn.Open()
                End If
                'get data into datatable
                Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Acctg " & _
                                                     " WHERE ID=" & intID, conn)
                Dim dt As New DataTable
                da.Fill(dt)

                Me.txtID.Text = intID
                Me.cboCode.Text = dt.Rows(0).Item("Code")
                Me.txtExpln.Text = dt.Rows(0).Item("Explaination")
                Me.txtExpln2.Text = dt.Rows(0).Item("Explaination2")
                Me.txtQuantity.Text = dt.Rows(0).Item("Quantity")
                Me.txtTotal.Text = dt.Rows(0).Item("address")
                '
                'hide the id to be edited in TAG of txtID in case id is changed
                Me.txtID.Tag = intID
                'change button add to update
                Me.btnAdd.Text = "Update"
                'disable button edit
                Me.btnEdit.Enabled = False
                'close connection
                conn.Close()
            End If
        End If
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        'check for the selected item in list
        If Me.DataGridView1.Rows.Count > 0 Then
            If Me.DataGridView1.SelectedRows.Count > 0 Then
                Dim intID As Integer = Me.DataGridView1.SelectedRows(0).Cells("id").Value
                'open connection
                If Not conn.State = ConnectionState.Open Then
                    conn.Open()
                End If

                'delete data
                Dim cmd As New OleDb.OleDbCommand
                cmd.Connection = conn
                cmd.CommandText = "DELETE FROM Acctg WHERE ID=" & intID
                cmd.ExecuteNonQuery()
                'refresh data
                Me.RefreshData()

                'close connection
                conn.Close()
            End If
        End If
    End Sub
End Class

Anyone who can help me? Thanks in advance!




Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
    Me.txtID.Text = ""
    Me.cboCode.Text = ""
    Me.txtExpln.Text = ""
    Me.txtExpln2.Text = ""
    Me.txtQuantity.Text = ""
    Me.txtAmt.Text = ""
    Me.txtAmt2.Text = ""
    Me.txtTotal.Text = ""
    Me.txtID.Tag = ""
    'enable button edit
    Me.btnEdit.Enabled = True
    'set button add to add label
    Me.btnAdd.Text = "Add"
    Me.txtID.Focus()
End Sub

Private Sub RefreshData()
    If Not conn.State = ConnectionState.Open Then
        'open connection
        conn.Open()
    End If

    Dim da As New OleDb.OleDbDataAdapter("SELECT ID as [ID], " & _
                                         "Code as [Code], Date, Explaination, Explaination2, Quantity, Amount, Amount2, Total " & _
                                         " FROM Acctg ORDER BY ID", conn)
    Dim dt As New DataTable
    'fill data to datatable
    da.Fill(dt)

    'offer data in data table into datagridview
    Me.DataGridView1.DataSource = dt

    'close connection
    conn.Close()
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
    Dim cmd As New OleDb.OleDbCommand
    If Not conn.State = ConnectionState.Open Then
        'open connection if it is not yet open
        conn.Open()
    End If

    cmd.Connection = conn
    'check whether add new or update
    If Me.txtID.Tag & "" = "" Then
        'add new 
        'add data to table
        cmd.CommandText = "INSERT INTO Acctg(ID, Code, Date, Explaination, Explaination2, Quantity, Amount, Amount2, Total) " & _
                        " VALUES(" & Me.txtID.Text & ",'" & Me.cboCode.Text & "','" & _
                        Me.DateTimePicker1.Text & "','" & Me.txtExpln.Text & "','" & _
                        Me.txtExpln2.Text & "','" & Me.txtQuantity.Text & "','" & _
                        Me.txtAmt.Text & "','" & Me.txtAmt2.Text & "','" & _
                        Me.txtTotal.Text & "')"
        cmd.ExecuteNonQuery()
    Else
        'update data in table
        cmd.CommandText = "UPDATE Acctg " & _
                    " SET ID=" & Me.txtID.Text & _
                    ", Code='" & Me.cboCode.Text & "'" & _
                    ", Date='" & Me.DateTimePicker1.Text & "'" & _
                    ", Explaination='" & Me.txtExpln.Text & "'" & _
                    ", Explaination2='" & Me.txtExpln2.Text & "'" & _
                    ", Quantity='" & Me.txtQuantity.Text & "'" & _
                    ", Amount='" & Me.txtAmt.Text & "'" & _
                    ", Amount2='" & Me.txtAmt2.Text & "'" & _
                    ", Total Amount='" & Me.txtTotal.Text & "'" & _
                    " WHERE ID=" & Me.txtID.Tag
        cmd.ExecuteNonQuery()
    End If
    'refresh data in list
    RefreshData()
    'clear form
    Me.btnReset.PerformClick()

    'close connection
    conn.Close()
End Sub

Private Sub MainForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    conn = New OleDb.OleDbConnection
    conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Application.StartupPath & "\data.mdb"
    '
    'get data into list
    Me.RefreshData()
End Sub

Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
    'check for the selected item in list
    If Me.DataGridView1.Rows.Count > 0 Then
        If Me.DataGridView1.SelectedRows.Count > 0 Then
            Dim intID As Integer = Me.DataGridView1.SelectedRows(0).Cells("id").Value
            'get data from database followed by Acctg id
            'open connection
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            'get data into datatable
            Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Acctg " & _
                                                 " WHERE ID=" & intID, conn)
            Dim dt As New DataTable
            da.Fill(dt)

            Me.txtID.Text = intID
            Me.cboCode.Text = dt.Rows(0).Item("Code")
            Me.txtExpln.Text = dt.Rows(0).Item("Explaination")
            Me.txtExpln2.Text = dt.Rows(0).Item("Explaination2")
            Me.txtQuantity.Text = dt.Rows(0).Item("Quantity")
            Me.txtTotal.Text = dt.Rows(0).Item("address")
            '
            'hide the id to be edited in TAG of txtID in case id is changed
            Me.txtID.Tag = intID
            'change button add to update
            Me.btnAdd.Text = "Update"
            'disable button edit
            Me.btnEdit.Enabled = False
            'close connection
            conn.Close()
        End If
    End If
End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
    'check for the selected item in list
    If Me.DataGridView1.Rows.Count > 0 Then
        If Me.DataGridView1.SelectedRows.Count > 0 Then
            Dim intID As Integer = Me.DataGridView1.SelectedRows(0).Cells("id").Value
            'open connection
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If

            'delete data
            Dim cmd As New OleDb.OleDbCommand
            cmd.Connection = conn
            cmd.CommandText = "DELETE FROM Acctg WHERE ID=" & intID
            cmd.ExecuteNonQuery()
            'refresh data
            Me.RefreshData()

            'close connection
            conn.Close()
        End If
    End If
End Sub

End Class

Anyone who can help me? Thanks in advance!

  • 3 Contributors
  • 2 Replies
  • 261 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by Reverend Jim

Recommended Answers

All 2 Replies

Member Avatar for G_Waddell

Hi,

Run the code in debug mode and find the exact line that gives the error.. That's alot of code to go through!

I'd say it is likely to be somewhere you are running a SELECT statement based on a WHERE clause and an empty or null value is being passed in e.g.

sql="SELECT * FROM MyTable WHERE (ID =" & MyID &")"

but the MyID value is not populated.

Member Avatar for Reverend Jim

Try replacing Date with [Date] in your queries. Date is a reserved word. It is a function that returns the current date. I am assuming that you are using it as a field name rather than calling the Date function.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.