data type synchronization

[Alex]

Hi,
I have three different data types coming out of my SQL tables that I
need to align with my VB code and the data set properties. I'm
getting an error message that reads "SQL exception unhandled. The
data types text and nvarchar are incompatible in the equal to
operator." Here is the relevant code.

Dim da As New SqlDataAdapter(strSQL, cn)
da.SelectCommand.Parameters.AddWithValue("@OrderID",
tbOrderID.Text)


Dim tbl As New DataTable("Orders")
With tbl
.Columns.Add("OrderID", GetType(String))
.Columns.Add("Price", GetType(Decimal))
.Columns.Add("Item", GetType(Integer))
End With
da.Fill(tbl)


Previously, I had all of the fields set to text in SQL, String in the
VB code and System.String in the data set properties. Everything
executed fine. However, when I tightened up my data types,
attempting
to change them to their correct types, the app crashed. That was the
only thing that I changed between successful runs and the run-time
error.


So I suspect that I just have some sort of contradiction in how I
have
set the data types in the various settings (sql, vb code, data set).
I have attempted to synchronize these data types using:
Text (sql), String (vb code), System.String (data set
properties);
Money, Decimal, System.Decimal;
Int, Integer, System.Int16.
No matter what I try, I get the exact same error.


Am I right or is the error message coming from somewhere else? Also,
does anybody know of a reference that shows what data types are
"legal" matches between these different settings?


I appreciate any help.
Alex

 

[Miha Markic]

Do you have any computed columns in DataTable?

 

[Alex]

No, it's very basic. Three types of fields: text, money, and integer.

 

[Miha Markic]

What type is OrderID in the database?
How does your strSql look like?

 

[Alex]

What type is OrderID in the database?

text

How does your strSql look like?

strSQL = "SELECT * FROM Orders WHERE OrderID = @OrderID"

Thanks Miha. I appreciate you looking at this for me.

 

[Miha Markic]

Hi Alex,

I see. Text is for storing large text fields and operators are not supported
on it.
You should change OrderID's text type to a varchar or nvarchar.
I think this should do.

 

[Alex]

Got it. That solved the problem. I'm pretty new to this, so I'm
working my way through these painful little roadblocks. Do you know
of a web link that lists the various data types and what they can be
used for?

Thank you very much for your help!
Alex

 

[Miha Markic]

SQL Server's books online is a good place to start
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
(same help file is included with SQL Server)
Other than that you should really pick a book on Sql Server...