Error when "SELECT" statement has conditions

Hi everyone,

I just started in developing applications for PocketPC. I found an example from this webpage link and I tried it out. I have a listbox to display the results.

Dim mySelectQuery As String = "SELECT ID, Name FROM StaffData WHERE Name = 'John'"

Dim myConnection As New SqlServerCe.SqlCeConnection("Data Source = \My Documents\test.sdf")

Dim myCommand As New SqlServerCe.SqlCeCommand(mySelectQuery, myConnection)
myConnection.Open()

Dim myReader As SqlServerCe.SqlCeDataReader
myReader = myCommand.ExecuteReader()

' Always call Read before accessing data.
While myReader.Read()
ListBox1.Items.Add(myReader.GetInt32(0) & " " & myReader.GetString(1))
End While

' always call Close when done reading.
myReader.Close()
' Close the connection when done with it.
myConnection.Close()

The above code gave me an error on the line in red.
But, if I have the query statement without condition:

Dim mySelectQuery As String = "SELECT ID, Name FROM StaffData"

Everything runs fine and my listbox has the data.

Am I doing something wrong?

Regards.

What error are you getting? Since you are new to SQL CE/SQL Mobile development, wrap everything starting with "myConnection.Open()" through the While loop in a Try Catch block. Catch Exception or SqlCeException and use the following code to enumerate the exception:

Public Sub DisplaySQLCEErrors(ByVal ex As SqlCeException)

Dim errorCollection As SqlCeErrorCollection = ex.Errors
Dim bld As New StringBuilder()
Dim inner As Exception = ex.InnerException
Dim err As SqlCeError

For Each err In errorCollection

bld.Append(ControlChars.Lf + " Error Code: " + err.HResult.ToString())
bld.Append(ControlChars.Lf + " Message : " + err.Message)
bld.Append(ControlChars.Lf + " Minor Err.: " + err.NativeError.ToString())
bld.Append(ControlChars.Lf + " Source : " + err.Source)

Dim numPar As Integer
For Each numPar In err.NumericErrorParameters
If (numPar <> 0) Then
bld.Append(ControlChars.Lf + " Num. Par. : " + numPar.ToString())
End If
Next numPar
Dim errPar As String
For Each errPar In err.ErrorParameters
If (errPar <> String.Empty) Then
bld.Append(ControlChars.Lf + " Err. Par. : " + errPar)
End If
Next errPar

MessageBox.Show(bld.ToString(), "SQL Mobile Error")

Next err

End Sub

-Darren Shaffer
.NET Compact Framework MVP

|||Thanks Darren!

Your error display code is awesome!
I've an error saying "The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates."

I guess what I need to do is to change the data type.

But one thing I don't understand is why do I always have to declare like this:

Dim err As SqlServerCe.SqlCeError

instead of:

Dim err As SqlCeError

which I have no clue.

Thanks!|||Apparently your Name column is of type ntext - you should probably change it to nchar or nvarchar.

-Darren Shaffer