Data Type ?

Hi Forum, Im new to SQL db and am receiving this error when updating Detailview.

System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.

DB Table PK Customer_ID is set Int also Mobile and PIN columns, all others are Text. Could it be that a combination of text and a number ie 7a 3 MyStreet, be cause?

Detailview Update Parameters as below

<asp:ParameterName="Mobile"Type="Int32"/>

<asp:ParameterName="PIN"Type="Int32"/>

<asp:ParameterName="Street"Type="String"/>

<asp:ParameterName="original_Customer_ID"Type="Int32"/>

Select fills Detailview with Table values OK, its on UPDATE things go wrong! much thanks Paul

Hi pl,

Your trouble is SQL level. You would have to change Text datatype to NVarchar datatype in your columns.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

Don't use the text data type. Change them to varchar(8000) if you must, or use a more realistic number for it's maximum length. If you are using SQL Server 2005 or SQL Express, you can also use varchar(max), which is pretty close to the same thing as text with quite a few less restrictions.

As a side note, it's the sqldatasource that has the problem. Although we don't really need to see it, we can guess what your update statement looks like. It's trying to compare a text field to an original value, and you can't do that. Using text columns for comparision within a WHERE clause isn't allowed.

|||

Thanks for both your replies, changing to column to varchar did the trick!

Something else you could help me with is SQL connection string. Im used to using Access DB, OLEDB

publicString str;

publicstring strAccessConn ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\\inetpub\\vhosts\\mtaxi.co.nz\\httpdocs\\data\\mtaxidb1.mdb";

OleDbConnection myAccessConn =newOleDbConnection(strAccessConn);

How to change this toSystem.Data.SqlClient ?

I have connection in web.config

connectionStrings>

<addname="MT_ConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MTaxidb1.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

SqlConnection myConnection = new SqlConnection("****THIS STRING IM UNSURE OF****");

Really appreciate advice Paul

|||

I'm going to be close, but I don't have the exact code in front of me, but what you want is something similiar to:

SqlConnection myConnection=new SqlConnection(ConfigurationManager.ConnectionStrings("MT_ConnectionString").ConnectionString);

Or you could use this (but obviously it's not configurable then):
SqlConnection myConnection = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MTaxidb1.mdf;Integrated Security=True;User Instance=True");

|||

Thanks Motely, This has all been good information! helped me out big time cheers P

string incase anyone is interested

strSqlConn = System.Configuration.ConfigurationManager.ConnectionStrings["MT_ConnectionString"].ToString();