Why I can't search SQL database with WHERE with a text variable?
-
I am using VB.net and I have a SQL database table GuestInfo with 1 column Col1 defined as Text with allow NULL & another column Col2 defined as Int with allow NULL If I use command:
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col2 = 12345"
Dim lrd As SqlDataReader = cmd.ExecuteReader()- There is no error happen But if I use:
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col1 = 'ABC'"
Dim lrd As SqlDataReader = cmd.ExecuteReader()- There is an error pop-up said that text and varchar are incompatible operator Then I tried the following:
Dim str As String
str = "ABC"
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col1 = '" & str & "'"
Dim lrd As SqlDataReader = cmd.ExecuteReader()- There is a same error display What can I do to search a Text value in column 1?
-
I am using VB.net and I have a SQL database table GuestInfo with 1 column Col1 defined as Text with allow NULL & another column Col2 defined as Int with allow NULL If I use command:
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col2 = 12345"
Dim lrd As SqlDataReader = cmd.ExecuteReader()- There is no error happen But if I use:
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col1 = 'ABC'"
Dim lrd As SqlDataReader = cmd.ExecuteReader()- There is an error pop-up said that text and varchar are incompatible operator Then I tried the following:
Dim str As String
str = "ABC"
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col1 = '" & str & "'"
Dim lrd As SqlDataReader = cmd.ExecuteReader()- There is a same error display What can I do to search a Text value in column 1?
I rarely use TEXT data type so have not run across this problem but you might try
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col1 = Convert(text,'ABC')"
It is probable that the command object passes the string data type as varchar, as it is the default I think. Do some research on stored procedures and parameters, that will solve all these problems and move your sql development and testing to Management Studio which will speed up your delivery time.
Never underestimate the power of human stupidity RAH
-
I rarely use TEXT data type so have not run across this problem but you might try
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col1 = Convert(text,'ABC')"
It is probable that the command object passes the string data type as varchar, as it is the default I think. Do some research on stored procedures and parameters, that will solve all these problems and move your sql development and testing to Management Studio which will speed up your delivery time.
Never underestimate the power of human stupidity RAH
I did try as you suggested but now it is weird, the error now shows: Connect to SQL Server, data types text and text are incompatible in the equal to operator - Might be in SQL database, I shouldn't defined Col1 as Text? If YES, what I should define to with
Dim str as Stringin VB.net (str = "ABC"): . varchar(50) . varchar(MAX) . char(10) . nchar(10) . ntext Any suggestion? Meanwhile, I will also search for more solutions :doh: -
I did try as you suggested but now it is weird, the error now shows: Connect to SQL Server, data types text and text are incompatible in the equal to operator - Might be in SQL database, I shouldn't defined Col1 as Text? If YES, what I should define to with
Dim str as Stringin VB.net (str = "ABC"): . varchar(50) . varchar(MAX) . char(10) . nchar(10) . ntext Any suggestion? Meanwhile, I will also search for more solutions :doh:The default of nvarchar is for unicode and should not be used unless you intend to store non english characters. I would recommend using varchar(##), the length should be slightly larger than the max characters you expect. varchar can be used for up to 8000 characters, this generally meets most requirements. varchar(max) I would only use if you need to store a serious amount of text in a field, ie. a book or at least a story.
Never underestimate the power of human stupidity RAH
-
The default of nvarchar is for unicode and should not be used unless you intend to store non english characters. I would recommend using varchar(##), the length should be slightly larger than the max characters you expect. varchar can be used for up to 8000 characters, this generally meets most requirements. varchar(max) I would only use if you need to store a serious amount of text in a field, ie. a book or at least a story.
Never underestimate the power of human stupidity RAH
I re-define Col1 to varchar(50) then use: cmd3.CommandText = "SELECT * FROM GuestInfo WHERE Trial = 'ABC' " Dim lrd As SqlDataReader = cmd3.ExecuteReader() Working as a charm! Thanks :) :) :)