Incompatible data types in SQL EXP 2005

I'm trying to read a data table with all text fields using C# in ado.net. However, when my query runs, it always returns this error:

"The data types text and varchar are incompatible in the equal to operator. "

The SQL statment is "SELECT field1, field2 FROM table WHERE field1 = 'value'"

If I leave out the WHERE clause, it runs fine. There are NO varchar fields in my entire table (It's a test table). I've tried using both parameter objects and Convert (See commented section) with no luck. A packet trace shows the SQL string is being delivered in tact and the SQL server is returning the error. I don't know if it's a C# client issue, or a SQL Server 2005 Exp issue. The table was created using MS SQL Server Management Studio Express I'm currently using the SqlClient object like this:

<script runat="server">
DataSet dsData = new DataSet();
DataTable dtData = new DataTable();

void Page_Load()
{
string strConn = ConfigurationSettings.AppSettings["authstr"];
string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";

//string strSQL = "SELECT password_fld, pharmacy_fld FROM Esker_Tbl WHERE CONVERT(text, username_fld ) = '@.prmuser'";
//SqlParameter spuser = new SqlParameter("@.prmuser", SqlDbType.Text);

//string struser = "bobhope";
//spuser.Value = struser;
try
{
SqlConnection Conn = new SqlConnection(strConn);
SqlCommand Cmd = new SqlCommand(strSQL, Conn);
SqlDataAdapter Adp = new SqlDataAdapter(Cmd);

Conn.Open();
Adp.Fill(dsData, "USERS1");
dtData = dsData.Tables["USERS1"];
dgData.DataSource = dtData;
dgData.DataBind();
dgData.Visible = true;

}
catch (Exception ex)
{
string strmessage = "";
strmessage = "Unable to access database: " + ex.Message;
lblMessage.Text = strmessage;
}
return;
}

Have you pulled out the query and tried to run it in Management Studio to break out the conversion type differences? I don't have your data structure so I'm not sure what the issue is.

If you're trying to compare two text data types, you should use the CONTAINS or other search parameters. In fact, the TEXT data type is being phased out in favor of nvarchar(max), which can hold gigs of data per row. Check out this article for more:

http://msdn2.microsoft.com/en-us/library/ms187993.aspx

|||

Hi Mark:

Not sure if this is an error caused by copy-paste the code into the forum, but if the code sample you provided here is correct, then it seems like you should've seen a compile error here:

string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";

There is an extra " .

if that's an copy-paste typo, could you please provide what is the data type declared for User_ID in User_ID table?

thanks