HELP: "text and varchar are incompatible" agrivation!

I am trying to do a simple select statement on my db but keep getting the fallowing exception being thrown ...

System.Web.Services.Protocols.SoapException was unhandled
Actor=""
Lang=""
Message="System.Web.Services.Protocols.SoapException: Server was unable to process request. --> System.Data.SqlClient.SqlException: The data types text and varchar are incompatible in the equal to operator.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at RelayService.ValidAccessID(String ID) in C:\Documents and Settings\Dean\My Documents\Visual Studio 2005\Projects\MsgSRX\MsgSRXService\App_Code\RelayService.vb:line 193
at RelayService.SendMessage(String msg, String AccessID, Int64& MsgID) in C:\Documents and Settings\Dean\My Documents\Visual Studio 2005\Projects\MsgSRX\MsgSRXService\App_Code\RelayService.vb:line 377
-- End of inner exception stack trace --"
Node=""
Role=""
Source="System.Web.Services"
StackTrace:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ChatClient.MsgSRX.RelayService.SendMessage(String msg, String AccessID, Int64& MsgID) in C:\Documents and Settings\Dean\My Documents\Visual Studio 2005\Projects\MsgSRX\ChatClient\Web References\MsgSRX\Reference.vb:line 342
at ChatClient.Form1.btnSend_Click(Object sender, EventArgs e) in C:\Documents and Settings\Dean\My Documents\Visual Studio 2005\Projects\MsgSRX\ChatClient\Form1.vb:line 13
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ChatClient.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

... Here is the source code to the method I am using ...

Private Function ValidAccessID(ByVal ID As String) As Boolean
Dim conn As New SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbConnect").ConnectionString

Dim cmd As String
cmd = "SELECT AccessID, ExpireTime "
cmd &= "FROM AccessIDNumbers "
cmd &= "WHERE AccessID IN ('" & ID & "')"
' cmd &= "WHERE AccessID ='@.ID'"
Dim C As New SqlCommand(cmd, conn)
' C.Parameters.AddWithValue("@.ID", New SqlTypes.SqlString(ID))
' C.Parameters.Item("@.ID").SqlDbType = SqlDbType.Text

conn.Open()

Dim Count As Integer = 0
Dim reader As SqlDataReader = C.ExecuteReader
Dim expired As Boolean = False
If reader.HasRows Then
While reader.Read
Count += 1
Dim et As SqlTypes.SqlDateTime
et = SqlTypes.SqlDateTime.Parse(reader("ExpireTime"))
Dim ct As New SqlTypes.SqlDateTime(Now)
If ct > et AndAlso expired = False Then expired = True
End While
End If

conn.Close()
C.Dispose()
C = Nothing
conn.Dispose()
conn = Nothing

Return expired = False
End Function

... the problem is with the cmd &= "WHERE AccessID IN ('" & ID & "')" statement. How Can I get this to work properly!!! My data types for the AccessIDNumbers table are as fallows ..

UserName -> text -> nulls not allowedAccessID -> text -> nulls not allowedCreationTime -> datatime -> nulls not allowedExpireTime -> datatime -> nulls not allowedCurrentTable -> text -> nulls allowed

I don't understand why I'm getting this error!!!

Are you actually using 'text' columns, or 'varchar' columns?

|||I'm using text columns. What is the difference between text and varchar? They seem like they should be basically the same thing and as such the db engine should be able to compare them?|||

The 'text' datatype is a BLOB, which in short means that it is stored in a separate location than the rest of the data in the record. Instead of storing the actual value, the record stores a 16-bit pointer that directs SQL to the actual file that stores this data (by default, anyway. this can be changed). The max for this type of field is 2 GB.

'varchar,' on the other hand, is a standard type, which means it is stored with the actual record data. The limit for this type is 8,000 characters (=8 KB). This is actually preferable to a 'text' column if you are using 8,000 or fewer characters.

If you still want to use the text datatype, you may need to do conversions in your SQL statements (I haven't tried this--just a suggestion to see if you can get it to work).

' instead of thiscmd &="WHERE AccessID IN ('" & ID & "')"' try thiscmd &= "WHERE CAST(AccessID AS VARCHAR) ='" & ID & "'"