Declaring an nvarchar in VB/Matching an nvarchar in a SQL query

Question

I'm working on an ASP.NET application that uses VB. I'm using a SQLReader/SQLCommand/SQLConnection within the VB file to try to pull data values.

I was mystified to find out that the reason why the query wasn't returning any values, and someone here showed me how to troubleshoot the query to verify things were being returned, which they weren't.

I talked to a co-worker, and he asked if it would match because I was feeding a string, and the Text field for the database is an nvarchar. How can I declare the variable in VB so that when fed in as a parameter for the query, it can match?

The types of data it looks to match are just basic things like "2", "2a", "1a", and so on.

If there's no way to just declare the nvarchar, does anyone have a suggestion on how I might workaround that?

Thanks a bunch.

Side note: This is a continuation, of sorts, of this question: http://stackoverflow.com/questions/768052/sql-reader-saying-no-values-exist-query-seems-fine

Edit: Thanks for all the help. I set things up like you guys said, but now I'm getting an error that "No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type. "

I have the parameter as a form-wide variable like this:

Private travelParameter As New SqlParameter("@trip", SqlDbType.NVarChar)

And then inside the main form,

travelQuery.CommandText = "SELECT [StartLoc], [EndLoc],[TravelTime], [AvgSpeed], [Distance]  FROM [TravelTimes] WHERE [TripNum] = @trip"

travelQuery.Parameters.AddWithValue("@trip", travelParameter)

Later on, a function like this will be called.

FillWithTime("2", travelReader, time, newCell)

Defined as:

Private Sub FillWithTime(ByVal TripNum As Char, ByRef travelReader As SqlDataReader, ByRef TimeData() As Object, ByRef Cell As System.Web.UI.WebControls.TableCell)

    travelParameter.Value = TripNum
    travelReader = travelQuery.ExecuteReader()
    If (travelReader.Read()) Then

        travelReader.GetValues(TimeData)

        'stuff
    End If

End Sub

Thanks again.

P.S. - Adam and Cerebus, I'd accept both answers if I could. They both really brought the idea together for me, thanks big time.

Answer 1

The string should work given that it's length is less than the field size of the nvarchar in the database.

Answer 2

Your data type in .NET should be a string. You just need to make sure that your parameter instance is using NVarChar (if it's a DbParameter, then then that's DBType.String. If it's a SqlDbParameter, then that's SqlDbType.NVarChar).

Answer 3

The nvarchar SqlDbType maps to the .NET string datatype. Personally, I like to create SqlParameters by making them as specific as possible :

'This overload of the constructor takes the Parameter name, ...
' its SqlDbType and size.
Dim param as New SqlParameter("@trip", SqlDbType.NVarChar, 2)
param.Value = "1a"
travelQuery.CommandParameters.Add(param)

Answer 4

It's just text encoding as far as .net is concerned.

Find out what encoding it is, probaly UTF8

byte[] barr = String.GetBytes( string )

System.Text.Encoding.UTF8.GetString( byte[] )