-
Feb 11th, 2016, 10:23 AM
#1
Thread Starter
Fanatic Member
data type not compatible
Hi
I want you to help me understand the difference between "Like" and "=".
All the fields are numeric type, except for date field which is date format.
Code:
RS.Open "select * from Table1. " & _
" where ID like '" & Text1.Text & "' " & _
" and datt like '" & Text2.Text & "' " & _
" and age like '" & Text11.Text & "' " & _
" and phone like '" & Text19.Text & "'", _
DB, adOpenDynamic, adLockOptimistic
Code:
RS.Open "select * from Table1. " & _
" where ID ='" & Text1.Text & "' " & _
" and datt ='" & Text2.Text & "' " & _
" and age ='" & Text11.Text & "' " & _
" and phone ='" & Text19.Text & "'", _
DB, adOpenDynamic, adLockOptimistic
in The second query, an error of incompability pops up.
-
Feb 11th, 2016, 11:11 AM
#2
Re: data type not compatible
I am assuming (and probably a good assumptions!) that your ID field in your table is a NUMERIC value.
You will want something like this:
RS.Open "Select * from Table1 where ID = " & Cstr(Text1.Text) & ....etc
-
Feb 11th, 2016, 11:34 AM
#3
Thread Starter
Fanatic Member
Re: data type not compatible
All the fields are numeric type, except for date field which is date format.
Originally Posted by SamOscarBrown
I am assuming (and probably a good assumptions!) that your ID field in your table is a NUMERIC value.
You will want something like this:
RS.Open "Select * from Table1 where ID = " & Cstr(Text1.Text) & ....etc
Thanks for the quick reply
In fact all the fields are numeric type
Last edited by newbie2; Feb 11th, 2016 at 11:37 AM.
-
Feb 11th, 2016, 11:35 AM
#4
Re: data type not compatible
Then use CStr() to convert when using the = sign
-
Feb 11th, 2016, 12:15 PM
#5
Re: data type not compatible
You do not actually need to use CSTR() there. The text from a textbox is a string already so the call to CSTR() is pointless.
The thing is the Like is meant to be used with wildcards and this applies to text fields so you can get records that are a partial match to whatever text you search for like all where last name starts with A or BR or whatever.
When you are looking for something specific you use = and when dealing with numeric fields then = < > all come into play but not like
-
Feb 11th, 2016, 12:25 PM
#6
Re: data type not compatible
Arghhh...yup...TOO much caffeine!
-
Feb 11th, 2016, 01:24 PM
#7
Thread Starter
Fanatic Member
Re: data type not compatible
Originally Posted by DataMiser
You do not actually need to use CSTR() there. The text from a textbox is a string already so the call to CSTR() is pointless.
The thing is the Like is meant to be used with wildcards and this applies to text fields so you can get records that are a partial match to whatever text you search for like all where last name starts with A or BR or whatever.
When you are looking for something specific you use = and when dealing with numeric fields then = < > all come into play but not like
Thank you for these interesting info
But in my code there is no wildcards and the filds are numeric so am I getting this error with the operator =
-
Feb 11th, 2016, 01:28 PM
#8
Re: data type not compatible
That is because you are using the ' character, which you should not use for numeric fields (and only for Date values when using certain database systems).
For more info, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)
Note that your phone field should probably not be numeric, as it wont be able to store 0 at the start of a number. Instead you should use a text (or char) based data type.
-
Feb 11th, 2016, 01:53 PM
#9
Re: data type not compatible
you would want:
Code:
RS.Open "select * from Table1" & _
" where ID = " & Text1.Text & _
" and datt = " & Text2.Text & _
" and age = " & Text11.Text & _
" and phone = " & Text19.Text, _
DB, adOpenDynamic, adLockOptimistic
BUT...what si said.....bad choice for phone 'numbers'
-
Feb 11th, 2016, 03:24 PM
#10
Thread Starter
Fanatic Member
Re: data type not compatible
Code:
That is because you are using the ' character, which you should not use for numeric fields (and only for Date values when using certain database systems).
No I'm not using any character. I'm just using numerics.
However I discovered now that the message pops up when I leave one of the textboxes empty.
I tried to modify Sam's code: but with no success.
Code:
RS.Open "select * from Table1" & _
" where ID = " & Text1.Text & "" & _
" and datt = " & Text2.Text & "" & _
" and age = " & Text11.Text & "" & _
" and phone = " & Text19.Text & "", _
DB, adOpenDynamic, adLockOptimistic
-
Feb 11th, 2016, 04:00 PM
#11
Re: data type not compatible
1-what are those additional double quotes ("") in there for? Not in what I posted.
2-Of course you will hiccup if one of the fields is blank...as a matter of a fact, you have to make sure that the contents of each of those text boxes are NUMERIC. Anything else, it'll not work. Use IsNumeric() and Len() to make sure you have what you want in the textboxes, otherwise inform the user to correct her error(s).
Sammi
-
Feb 11th, 2016, 04:03 PM
#12
Re: data type not compatible
Originally Posted by newbie2
No I'm not using any character. I'm just using numerics.
I was referring to this kind of thing in your code:
Originally Posted by newbie2
Code:
" where ID like '" & Text1.Text & "' " & _
Originally Posted by newbie2
However I discovered now that the message pops up when I leave one of the textboxes empty.
What message?
I suspect an error of some sort, because the SQL will no longer be valid.
If you want to be able to search based on whatever values have been entered (and not need all of them to be entered), then you will need to do more work to build the SQL statement appropriately. There is an article in the Database Development FAQs showing how to do it.
I tried to modify Sam's code: but with no success.
The modifications you showed would not alter the behaviour at all, because you have just added code that doesn't do actually do anything (appending an empty string to a string with a value in will result in the same value).
-
Feb 11th, 2016, 04:36 PM
#13
Re: data type not compatible
It's all sort of moot anyway. Two words: SQL Injection.
Why this hazardous use of dynamically cobbled together SQL continues so late in the game puzzles me. I can only conclude there are tons and tons of poor examples dating back to the early ASP script days in the 1990s, with these being transported into the present by repeated copy/paste programming.
It takes very little additional effort to use parameter queries instead. For example:
Code:
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
With RS
.CursorLocation = adUseServer
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End With
With New ADODB.Command
.CommandType = adCmdText
.CommandText = "SELECT * FROM [Customers] " _
& "WHERE [Number] BETWEEN ? AND ? ORDER BY [Number]"
.Name = "BetweenQuery"
.ActiveConnection = DB
DB.BetweenQuery CLng(Text(0).Text), CLng(Text(1).Text), RS
End With
The Command becomes an extended method of the Connection. You can do all necessary editing of inputs to ensure proper characters are in them, the proper range of values have been provided, etc. And then you can convert the String data to the appropriate strong types when you make the method call.
It also generally means far fewer silly mistakes in forming the SQL statement because you do not have to deal with inline concatenation or decorating values according to data type.
This is just win, win, win all around.
If the SQL is more complicated you also have the option of using named parameters instead, which gets around some of the limitations of positional parameters (as designated by "?" placeholders in SQL).
The "?" symbols don't get replace by text, instead the query uses them to know where to insert strongly typed parameter data from the parameter list.
Last edited by dilettante; Feb 11th, 2016 at 04:45 PM.
-
Feb 11th, 2016, 04:43 PM
#14
Thread Starter
Fanatic Member
Re: data type not compatible
what are those additional double quotes ("") in there for? Not in what I posted.
I added these qotes hoping to be able to add data even some textboxes are empty.
Of course you will hiccup if one of the fields is blank
So you think there is no way to leave any of the textboxes empty even by adding if text...... <> "" then...
Type not compatible.
There is an article in the Database Development FAQs showing how to do it
Thanks I'll have a look.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|