CODEWITHSUNDEEP
sqlvb.net
Mike Stearney
Mike Stearney

Reputation: 1

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 says: "OleDbException was unhandled: Data type mismatch in criteria expression." and points to the AutoTypesReader = cmdAutoTypes.ExecuteReader line

Upvotes: 0

Views: 2489

Answers (5)

cubski
cubski

Reputation: 3248

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.

Upvotes: 0

Narveson
Narveson

Reputation: 1111

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.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166356

Rather make use of OleDbParameter Class

This will also avoid Sql Injection.

Upvotes: 1

Vkalal
Vkalal

Reputation: 36

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 .... "

Upvotes: 0

Andrew Cooper
Andrew Cooper

Reputation: 32576

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

Upvotes: 0

Related Questions