Need help fixing code that retrieves data from SQL CE Server databaseto populate textbox and combobo

[Loogie]

Hello

Using VB.Net 2005 compact framework and SQL CE Server

I have code that is supposed to retrieve data from an SQL CE Server
database and populate a textbox and a select a value in a combobox.

I have verified that the code driving data to the tables works using
Query Analyzer so the problem must be in syntax in my SQL.

The error I am getting is 'The column name is not valid. [ Node name (if
any) = ,Column name = X ]' and it is thrown in the line Dim myReaderU As
SqlCeDataReader = cmdU.ExecuteReader()

Here is the code...any help appreciated.

Thanks

:L

Dim ssceconnU As New SqlCeConnection("Data Source = \Program
Files\data\products\" & clsGlobals.Form4.txtName.Text & ".sdf")
ssceconnU.Open()

Dim X As Integer
X = CInt(clsGlobals.Form4.lsvProds.FocusedItem.Text) '
verified that this is giving me the correct value

Dim cmdU As New
System.Data.SqlServerCe.SqlCeCommand("SELECT * FROM product WHERE p_item
= X", ssceconnU)
Dim myReaderU As SqlCeDataReader = cmdU.ExecuteReader() '
error thrown here

cboCode.SelectedItem = myReaderU.Item("p_code")
txtName.Text = myReaderU.Item("p_name")

Dim cmdU2 As New
System.Data.SqlServerCe.SqlCeCommand("SELECT * FROM prodspecies WHERE
s_link = '" & cboCode.SelectedItem.ToString & "'", ssceconnU)

Dim myReaderU2 As SqlCeDataReader = cmdU2.ExecuteReader()

Do While myReaderU2.Read()
lstIn.Items.Add(myReaderU2.Item("s_code"))
Loop

myReaderU.Close()
myReaderU.Dispose()
myReaderU = Nothing


myReaderU2.Close()
myReaderU2.Dispose()
myReaderU2 = Nothing

ssceconnU.Close()
ssceconnU.Dispose()
ssceconnU = Nothing

 

[Guest]

Sure, you have invalid syntax. The X must be in single quotes.


--

Chris Tacke, Embedded MVP
OpenNETCF Consulting
Managed Code in an Embedded World
www.OpenNETCF.com

 

[Loogie]

Sure, you have invalid syntax. The X must be in single quotes.

I tried that and it still does not work. I even took it one further and
made it a basic SQL

Dim cmdU As New System.Data.SqlServerCe.SqlCeCommand("SELECT * FROM
product", ssceconnU)

Still does not work.

Went one farther and tried just one field

Dim cmdU As New System.Data.SqlServerCe.SqlCeCommand("SELECT p_item FROM
product", ssceconnU)

Still does not work.

Using query analyzer I ran a query on the db to make sure I had data
there and sure enough it is.

I am confused as to why it will not work

:L

 

[Loogie]

I tried that and it still does not work. I even took it one further and
made it a basic SQL

Dim cmdU As New System.Data.SqlServerCe.SqlCeCommand("SELECT * FROM
product", ssceconnU)

Still does not work.

Went one farther and tried just one field

Dim cmdU As New System.Data.SqlServerCe.SqlCeCommand("SELECT p_item FROM
product", ssceconnU)

Still does not work.

Using query analyzer I ran a query on the db to make sure I had data
there and sure enough it is.

I am confused as to why it will not work

:L

I ran this to let me know if records were being selected from the SQL


Dim cmdU As New System.Data.SqlServerCe.SqlCeCommand("SELECT p_item FROM
product", ssceconnU)


ssceconnU.Open()


Dim myReaderU As SqlCeDataReader = cmdU.ExecuteReader()

MsgBox(myReaderU.RecordsAffected.ToString) - returns a value of -1.

:L

 

[Loogie]

Sure, you have invalid syntax. The X must be in single quotes.

Hi again

The problem was that I forgot to include MyReader.Read

DOH

:L