CODEWITHSUNDEEP
vb.net
Paula Angela Cua
Paula Angela Cua

Reputation: 19

I keep getting errors when I run my project

When I run my project it keeps on giving me errors. The first exception is at da.Fill(dt). It says that IErrorInfo.GetDescription Failed With E_FAIL(0x80004005). The second exception is at cmd.ExecuteNonQuery() and says syntax error in INSERT INTO statement. Please help me. I'm quite new to VB so I couldn't really see the problem even if I looked at it all day.

Public Class Telephone_Bill

Dim cnn As New OleDb.OleDbConnection
Private Sub RefreshData()
    If Not cnn.State = ConnectionState.Open Then
        cnn.Open()
    End If

    Dim da As New OleDb.OleDbDataAdapter("SELECT Month as [Month], " & _
                                         "Day as [Day], Year, Amount Paid, Amount Due, Mode of Payment, Company Name " & _
                                         " FROM Transactions ORDER BY Month", cnn)

    Dim dt As New DataTable

    Transaction_Log.dgvTransaction.DataSource = dt

    da.Fill(dt)



    cnn.Close()
End Sub

Private Sub btnProceed_Click(sender As System.Object, e As System.EventArgs) Handles btnProceed.Click


    Dim cmd As New OleDb.OleDbCommand
    If Not cnn.State = ConnectionState.Open Then
        cnn.Open()
    End If

    cmd.Connection = cnn
    cmd.CommandText = "INSERT INTO Transactions([Month], [Day], [Year], AmountPaid, AmountDue, ModeofPayment, CompanyName) " & _
                        " VALUES(" & cboMonth1.SelectedItem & ",'" & cboDay1.SelectedItem & "','" & _
                        cboYear1.SelectedItem & "','" & txtAmount.Text & "','" & _
                        txtTotalCharges.Text & "','" & cboMonetary.SelectedItem & "','" & _
                        txtCompName.Text & "')"
    cmd.ExecuteNonQuery()

    RefreshData()

    cnn.Close()
End Sub

Private Sub Telephone_Bill_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    cnn = New OleDb.OleDbConnection
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Accounts for Bill Payment Center.accdb"

    Me.RefreshData()

End Sub

End Class

Upvotes: 0

Views: 93

Answers (1)

Steve
Steve

Reputation: 216302

The first error in Fill is probably due to the use of reserved keywords and the missing square brackets around field names with spaces (Month, Day, Year are all reserved keywords for many database systems and need square brackets around them to be used in a query text)

Dim da As New OleDb.OleDbDataAdapter("SELECT [Month], " & _
                                     "[Day], [Year], [Amount Paid], [Amount Due], " & _ 
                                     "[Mode of Payment], [Company Name] " & _
                                     " FROM Transactions ORDER BY [Month]", cnn)

However, it is not clear if your fields names contains effectively spaces or not.
In your INSERT statement the same fields appear to be without spaces. So, if they have a space, you need to encapsulate them in square brackets, if not, you could leave them as in the INSERT query.

The error in the INSERT INTO should be fixed using the appropriate parameterized approach

cmd.CommandText = "INSERT INTO Transactions([Month], [Day], [Year], " & _ 
                  "AmountPaid, AmountDue, ModeofPayment, CompanyName) " & _
                   " VALUES(?,?,?,?,?,?,?)"
cmd.Parameters.AddWithValue("@p1", Convert.ToInt32(cboMonth1.SelectedItem))
cmd.Parameters.AddWithValue("@p2", cboDay1.SelectedItem.ToString)
... and so on for the other fields....

Note that when using parameters and the AddWithValue method you should be very precise on the kind of value that you pass to the parameter. If the underlying field expects a numeric value you should convert the value to the appropriate datatype as expected. For example AmountPaid and AmountDue seems to be numeric fields and you should convert the user input to the appropriate datatype (decimal?)

Upvotes: 2

Related Questions