I keep getting errors when I run my project

Question

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

You have a difference in field names between the SELECt and the INSERT query. Do you have an `Amount Paid` or your field is named `AmountPaid` (The same clarification is necessary for the other fields names in the SELECT) — Steve, Jan 03 '15 at 17:30
check your query.in the refreshdata() you have specified column names with spaces between them and in commandtext you have it without spaces(eg:mode of payment).what is actually right? — akhil kumar, Jan 03 '15 at 17:30
Your whole method of calling SQL is wrong; you need to use [parameters](http://stackoverflow.com/q/11139791/22437). — Dour High Arch, Jan 03 '15 at 17:34
I just saw this code from a tutorial in youtube. I changed the codes that I think will suit my project. She had different names for the SELECT and INSERT query. So I thought if I did the same to mine it would be okay. Should I just use one kind of name per field? — Paula Angela Cua, Jan 03 '15 at 17:38
You should look at your database table `Transactions` and use the field names declared there — Steve, Jan 03 '15 at 17:39
Leave the database without spaces. It is better for you when you need to use that db. For your display requirements there are better options (Changing the header in grids, creating labels for your textboxes) For parameters [read about them here](http://www.4guysfromrolla.com/webtech/092601-1.shtml) — Steve, Jan 03 '15 at 17:53

score 2 · Accepted Answer · answered Jan 03 '15 at 17:36

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?)

I keep getting errors when I run my project

1 Answers1