Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I am trying to time stamp entries in my access db. i get an error saying that OleDb exception was unhandled and that one less prameter was pssed . please help.
VB
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        If Electric_Cables.Checked = True Then
            If RadioButton1.Checked = True Then
                mycon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Warehouses\prjct_mntrng.mdb"
                Dim command As New OleDbCommand()
                command.CommandText = "UPDATE Electric_Cables SET Actual_Start= '" & DateTime.Now & "'WHERE Asset_ID=" + TextBox1.Text + ""
                mycon.Open()
                command.Connection = mycon
                command.ExecuteNonQuery()
                MessageBox.Show("Entry Registered Successfully.")
            ElseIf RadioButton2.Checked = True Then
                mycon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Warehouses\prjct_mntrng.mdb"
                Dim command As New OleDbCommand()
                command.CommandText = "UPDATE Electric_Cables SET Actual_Finish= '" & Date.Now & "' WHERE Asset_ID=" + TextBox1.Text + ""
                mycon.Open()
                command.Connection = mycon
                command.ExecuteNonQuery()
                MessageBox.Show("Entry Registered Successfully.")
            ElseIf RadioButton1.Checked = False AndAlso RadioButton2.Checked = False Then
                MessageBox.Show("Check Report Start or Report Finish option for successful Entry.")
            End If

            If Utility_Poles.Checked = True Then

                If RadioButton1.Checked = True Then
                    mycon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Warehouses\prjct_mntrng.mdb"
                    Dim command1 As New OleDbCommand()
                    command1.CommandText = "UPDATE Utility_Poles SET Actual_Start= '" & Date.Now & "' WHERE Asset_ID=" + TextBox1.Text + ""
                    mycon.Open()
                    command1.Connection = mycon
                    command1.ExecuteNonQuery()
                    MessageBox.Show("Entry Registered Successfully.")
                ElseIf RadioButton2.Checked = True Then
                    mycon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Warehouses\prjct_mntrng.mdb"
                    Dim command2 As New OleDbCommand()
                    command.CommandText = "UPDATE Utility_Poles SET Actual_Finish= '" & Date.Now & "' WHERE Asset_ID=" + TextBox1.Text + ""
                    mycon.Open()
                    command2.Connection = mycon
                    command2.ExecuteNonQuery()
                    MessageBox.Show("Entry Registered Successfully.")
                ElseIf RadioButton1.Checked = False AndAlso RadioButton2.Checked = False Then
                    MessageBox.Show("Check Report Start or Report Finish option for successful Entry.")
                End If
            End If
        End If

    End Sub
Posted
Comments
_Damian S_ 9-Apr-14 1:22am    
Have you tried setting a breakpoint and stepping through your code one line at a time to find out what actual line of code the error is coming from?
Member 10538358 9-Apr-14 1:24am    
yes. it comes at ExecutNonQuery()
_Damian S_ 9-Apr-14 1:37am    
So at that point, what is the value of command.CommandText?
[no name] 9-Apr-14 7:34am    
Probably because you have the same problem as http://www.codeproject.com/Questions/756696/updating-access-db-using-vb?cmt=613259#cmt2_756696

Check the date format being returned by Date.Now() is in the same format required by the DB, had a similar problem myself and used the Format() function to correct the format first.

Replace the actual format you need in the format function below this is the one I had to use for a MySQL db.

command.CommandText = "UPDATE Electric_Cables SET Actual_Finish= '" + Format(Date.Now(), "yyyy-MM-dd") + "' WHERE Asset_ID=" + TextBox1.Text 


The alternative is to use OLEDB .Parameters which will construct and convert parameters content correctly.

VB
command.CommandText= "UPDATE Electric_Cables SET Actual_Finish=? WHERE Asset_ID=" + TextBox1.Text
command.prepare
command.Parameters.Add(Date.Now)


Good Luck.
 
Share this answer
 
v2
Replace:
C#
command.CommandText = "UPDATE Utility_Poles SET Actual_Finish= '" & Date.Now & "' WHERE Asset_ID=" + TextBox1.Text + ""


With:
C#
command.CommandText = "PARAMETERS [assetid] INT; UPDATE Utility_Poles SET Actual_Finish=#Now()# WHERE Asset_ID=[assetid]"

and use OleDbCommand with named parameters[^].

Note:
I used built-in function Now() for MS JET OleDB engine and Asset_ID as numeric field.

For further information, please see:
Configuring Parameters and Parameter Data Types[^]
OleDbParameterCollection.AddWithValue Method [^]
MS Access: PARAMETERES declaration[^]
Tips and Techniques for Queries in Access 2007[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900