CODEWITHSUNDEEP
sqldatabasevb6
Tom Davis
Tom Davis

Reputation: 11

VB6 SQL Type Mismatch issues with Databases

For my project, I have integrated a calculator which converts CM to FT. To make it worth more marks, I am trying to make it so that it will get the values and compare them against a Database to see which size Garage Door would be most suitable for the job

The SQL which purely searches for the value works. through and ADO which then only shows the found value on a data grid. But when I try to use the BETWEEN version, I get a Type Mismatch error which I have tried to fix by changing the variables to Integers and Reals, but it doesn't work. If anyone could help with this I would be really grateful!

Option Explicit
Dim sql As String
Dim sizeFindH As String
Dim sizeFindW As String  

Dim sizeFindHUp As Double
Dim sizeFindHDown As Double
Dim sizeFindWUp As Double
Dim sizeFindWDown As Double

feet = 30.48
heightCm = txtHeightCm.Text
widthCm = txtWidthCm.Text

txtHeight.Text = heightCm / feet
txtWidth.Text = widthCm / feet

heightFt = txtHeight.Text
widthFt = txtWidth.Text

sizeFindH = txtHeight.Text
sizeFindW = txtWidth.Text

sizeFindHUp = sizeFindH + 1
sizeFindHDown = sizeFindH - 1

sizeFindWUp = sizeFindW + 1
sizeFindWDown = sizeFindW - 1

sql = "SELECT * FROM  garageDoorSize WHERE ((garagedoorSize.height) BETWEEN '" + "%" + sizeFindHDown + "%" + "') AND '" + "%" + sizeFindHUp + "%" + "');"
sql = "SELECT * FROM  garageDoorSize WHERE ((garagedoorSize.width) BETWEEN '" + "%" + sizeFindHDown + "%" + "') AND '" + "%" + sizeFindHUp + "%" + "');"

The error Type Mismatch highlights this line of code, as I'm sure it would the next if I could fix it.

sql = "SELECT * FROM  garageDoorSize WHERE ((garagedoorSize.height) BETWEEN '" + "%" + sizeFindHDown + "%" + "') AND '" + "%" + sizeFindHUp + "%" + "');"
sql = "SELECT * FROM  garageDoorSize WHERE ((garagedoorSize.width) BETWEEN '" + "%" + sizeFindHDown + "%" + "') AND '" + "%" + sizeFindHUp + "%" + "');"

Upvotes: 0

Views: 319

Answers (2)

Mohamed Gharib
Mohamed Gharib

Reputation: 2587

The problem with your code in the SQL statement is that you are using an extra parentheses before the AND operator. It's not a logical operator here combining 2 conditions.. It's part of the BETWEEN clause and shouldn't have parentheses before. Also the % shouldn't be used here as others said. Also the single quote is only used with strings.. So as long as your fields are numbers you shouldn't use it.

Oh.. And there's an extra parentheses without an opening one at the end of the query.

So your SQL should look like this:

sql = "SELECT * FROM garageDoorSize WHERE (garagedoorSize.width) BETWEEN " & sizeFindHDown & " AND " &  sizeFindHUp & ";"

Upvotes: 0

Jack
Jack

Reputation: 435

You are trying to concatenate a number and a string, hence the Type mismatch exception . Use CStr to convert the numbers to strings.

Also, I think you don't need the % signs, which are used with LIKE comparisons.

Also, as @jac correctly pointed out, use & to concatenate strings, not +. Edited my example to reflect for future readers of the post..

Try

sql = "SELECT * FROM garageDoorSize WHERE ((garagedoorSize.height) BETWEEN '" & CStr(sizeFindHDown) & "') AND '" & CStr(sizeFindHUp) & "');"

Note: Your code style of adding values direct to sql strings is vulnerable to sql injection (although not in this case as they are numbers). If these were strings, and came from user input your database and application is not secure. Best practice is to us sql parameter objects for your values... See this post, which shows usage. Note, first post I found, sure there are better guides out there :)

Upvotes: 0

Related Questions