Selecting an integer from an Access Database using SQL

Trying to select an integer from an Access Database using an SQL statement in VB

Dim cmdAutoTypes As New OleDbCommand
Dim AutoTypesReader As OleDbDataReader

cmdAutoTypes.CommandText = "SELECT * FROM AutoTypes WHERE TypeId = '" & cboTypeIds.Text & "'"

AutoTypesReader = cmdAutoTypes.ExecuteReader

Error message sa开发者_StackOverflowys: "OleDbException was unhandled: Data type mismatch in criteria expression." and points to the AutoTypesReader = cmdAutoTypes.ExecuteReader line

---

Rather make use of OleDbParameter Class

This will also avoid Sql Injection.

---

You don't need the quotes in the query string. You're searching for a number, not a string.

cmdAutoTypes.CommandText = "SELECT * FROM AutoTypes WHERE TypeId = " & cboTypeIds.Text

---

Hi In access SQL you can't use single quote around your Integer type.

so command text will be.. "SELECT * FROM AutoTypes WHERE TypeId = " & cboTypeIds.Text & " and .... "

---

In Access SQL, don't quote numeric constants.

And test whether IsNull(cboTypeIds). You can't do what you were planning to do until a value has been chosen.

---

Do not use string concatenation when you build your SQL query, use parameters instead.

Dim cmd As OledbCommand = Nothing
Dim reader as OleDbDataReader = Nothing
Try
    Dim query As String = "SELECT * FROM AutoTypes WHERE TypeId = @Id"
    cmd = New OledbCommand(query, connection)
    //adding parameter implicitly
    cmd.Parameters.AddWithValue("@Id", cboTypeIds.Text)
    reader = cmd.ExecuteReader()
Catch ex As Exception
    Messagebox.Show(ex.Message, MsgBoxStyle.Critical)
End Try

You can also explicitly state the parameter data type.

cmd.Parameters.Add("@Id", OleDbType.Integer).Value = cboTypeIds.Text

Hope this helps.