Database woes in compact framework. Attempting to add contents oflistview to database.

[Loogie]

I am attempting to add contents of a listview to a sqlceserver database
for a pocket pc using VB.net 2005 compact framework. There are 2
subitems in the listview

Item is to go into field 'species'
SubItem1 is to go into field 'code'
SubItem2 is to go into field 'type'

My code with error being thrown below that:

Dim engine As New SqlCeEngine("Data Source = \Program Files\data\" &
txtName.Text & ".sdf")
engine.CreateDatabase()

' Create and open connection.
Dim ssceconn As New SqlCeConnection("Data Source = \Program
Files\data\" & txtName.Text & ".sdf")
ssceconn.Open()

' Create table
Dim sqlCreateTable As SqlCeCommand = ssceconn.CreateCommand()
sqlCreateTable.CommandText = "CREATE TABLE species(spec_id
int IDENTITY(0,1) PRIMARY KEY, species NVARCHAR(100), code NVARCHAR(10),
type NVARCHAR(10))"
sqlCreateTable.ExecuteNonQuery()

' Add records
Dim sqlInsertRow As SqlCeCommand = ssceconn.CreateCommand()
Dim lsvItem As ListViewItem
For Each lsvItem In lsvSpec.Items
sqlInsertRow.CommandText = "INSERT INTO
species(species, code, type) VALUES(lsvSpec.Item, lsvItem.subitem(1),
lsvItem.subitem(2))"
sqlInsertRow.ExecuteNonQuery()
Next

It is throwing an error as follows:

System.Data.SqlServerCe.SqlCeException was unhandled
HResult=-2147217900
Message="There was an error parsing the query. [ Token line number =
1,Token line offset = 78,Token in error = ( ]"
NativeError=25501
Source="SQL Server 2005 Mobile Edition ADO.NET Data Provider"
StackTrace:
at System.Data.SqlServerCe.SqlCeCommand.ProcessResults()
at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
at Cruise.Form2.MenuItem1_Click()
at System.Windows.Forms.MenuItem.OnClick()
at System.Windows.Forms.Menu.ProcessMnuProc()
at System.Windows.Forms.Form.WnProc()
at System.Windows.Forms.Control._InternalWnProc()
at Microsoft.AGL.Forms.EVL.EnterMainLoop()
at System.Windows.Forms.Application.Run()
at Cruise.Form2.Main()

Any ideas on how to fix this?

Thanks

:L

 

[Pete Vickers [MVP]]

Hi,
should sqlInsertRow.CommandText = "INSERT INTO
species(species, code, type) VALUES(lsvSpec.Item, lsvItem.subitem(1),
lsvItem.subitem(2))"

not read

sqlInsertRow.CommandText = "INSERT INTO
species(species, code, type) VALUES('" & lsvSpec.Item, & "','" &
lsvItem.subitem(1), & "','" & lsvItem.subitem(2)) & "')"

Or similar. You need to specify values. As you have it at the moment, you
are trying to set species to 'lvspec.item'

The message actually tells you the line and character in error, which I
guess is the ( in (1)

Pete