CODEWITHSUNDEEP
databasevb.net
wwe9112
wwe9112

Reputation: 43

Data Type Mismatch Vb.net Access

enter image description hereI am trying to insert data into my Access database using vb for an application I am building. I have been BEATING my had off of an invisible wall trying to get the data to enter into the database. Below is the code, below that I will explain it more. 

 Private Sub btnSubmitExpense_Click(sender As Object, e As EventArgs) Handles btnSubmitExpense.Click
    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
    dataFile = "C:\PFADatabase.accdb"
    connString = provider & dataFile
    myConnection.ConnectionString = connString
    myConnection.Open()
    Dim str As String
    str = "Insert into Expenses ([ExpenseTypeID], [DateOfExpense],[AmountOfExpense]) Values(?,?,?)"
    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
    cmd.Parameters.Add(New OleDbParameter("ExpenseTypeID", CType(cboCategoryOfExpense.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("DateOfExpense", CType(dateOfExpense.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("AmountOfExpense", CType(txtAmountOfExpense.Text, String)))

    Try
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        myConnection.Close()
        'clear the form below
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

[ExpenseTypeID] is actually a lookup. The object is a combobox in vb that I have programmed above the code I posted here. [DateOfExpense] is also a Date object in VB. I just dragged it over. I want that to be associated with everything in that table. [AmountOfExpense] is again, tied into the db.

Basically a user enters the date using the tool, then chooses a category of their expense and then enters the amount. This is all one table but the expenseType is a Lookup actually within Access to another table so we could have the dropdown. Everything works well until I click the save button. Then whenever i do that I get the exception the type is mismatched. How do I fix that? I'm GUESSING it is the text string being wrong, but I've tried other things so I am thinking it is a bit more than that which is why I came here to you professionals to get input.

Thank you.

EDIT: The error is coming in at

 cmd.ExecuteNonQuery()

and says "Data type mismatch in criteria expression. "

'START THE POPULATE THE COMBO BOXES

    'Gets the database and makes the connection

    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="

    dataFile = "C:\PFADatabase.accdb"

    connString = provider & dataFile
    myConnection.ConnectionString = connString


    'Generates a query command
    Dim cmd As OleDbCommand = New OleDbCommand("SELECT [ExpenseType] FROM [ExpenseType]", myConnection)
    myConnection.Open() 'Opens connection
    Dim dr As OleDbDataReader = cmd.ExecuteReader


    While dr.Read
        cboCategoryOfExpense.Items.Add(dr.Item(0))
    End While
    myConnection.Close()


    'END THE POPULATE OF COMBO BOXES

As you can see in the comments, it is saying how the combo boxes are populated.

EDIT: In the picture under Expenses is the main table. The circled value is the FK and it's leading to the PK of the ExpenseType. The data we are pulling the drop down from is the ExpenseType under ExpenseType. From what I understand, you guys are saying, and I agree, it's writing to the PK of ExpenseType and not the data. ALL I want it to do is keep the data categorized for a graph we will be using.

Upvotes: 0

Views: 1361

Answers (1)

Steve
Steve

Reputation: 216323

Convert your input to the appropriate datatype as required by your columns. If you don't specify the datatype of your parameters and pass always strings then you are easy target of this kind of errors

   ...
   Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)

   Dim expID As Integer
   if Not Int32.TryParse(cboCategoryOfExpense.Text, expID) Then
        MessageBox.Show("Invalid integer")
        return 
   End If
   cmd.Parameters.Add("ExpenseTypeID", OleDbType.Integer).Value = expID

   Dim dtExp as DateTime
   if Not DateTime.TryParse(dateOfExpense.Text, dtExp) Then 
        MessageBox.Show("Invalid Date")
        return 
   End If
   cmd.Parameters.Add("DateOfExpense", OleDbType.Date).Value = dtExp

   Dim amount As Decimal
   if Not Decimal.TryParse(txtAmountOfExpense.Text, amount) Then 
        MessageBox.Show("Invalid Decimal")
        return 
   End If
   cmd.Parameters.Add("AmountOfExpense", OleDbType.Currency).Value = amount
   ....

Looking at your edit and comments, perhaps this is what you need

' Be sure to have a combobox with DropDownList style to avoid
' user entering an unexpected value then....

'Load the combobox from the table ExpenseType 
 Dim cmd As OleDbCommand = New OleDbCommand("SELECT [ExpenseTypeID], " & _
                           "[ExpenseType] FROM [ExpenseType]", myConnection)
myConnection.Open() 'Opens connection
Dim dr As OleDbDataReader = cmd.ExecuteReader
Dim dt = new DataTable()
dt.Load(dr)

cboCategoryOfExpense.DataSource = dt

' The visible part of the combo contains value from ExpenseType field
cboCategoryOfExpense.DisplayMember = "ExpenseType"

' The hidden part refers to the ExpenseTypeID field
cboCategoryOfExpense.ValueMember = "ExpenseTypeID"
...

and now when converting the combobox value you coud write

   Dim expID As Integer

   ' Sanity check in case the user want to insert but has not 
   ' selected anything from the combobox 
   if cboCategoryOfExpense.SelectedValue Is Nothing Then
        MessageBox.Show("Select an Expense")
        return 
   End if
   cmd.Parameters.Add("ExpenseTypeID", OleDbType.Integer).Value = Convert.ToInt32(cboCategoryOfExpense.SelectedValue)
   ....

Upvotes: 1

Related Questions