Can't Insert into SQL CE Table

[jhepfner]

Hi All -
I am rather new to vb.net using SQL CE.
I am trying to use a button to insert records into a table in a SQL CE database.
rowsaffected returns a value of 1, but when I preview the data, the row is never inserted.

As I am new at this, I am pretty much lost in what needs to be changed. The sql statement I am using uses hardcoded values only because I am trying to get this to intially work. Once I can get the insert statement to work, it will use variables based on user input.

Here is my code that I am using:
-----------------------------------------------------------------------
Private Sub cmdInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdInsert.Click
Dim connstr As String
Dim rowsaffected As Integer

connstr = ("Data Source =" _
+ (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.GetName.CodeBase) _
+ ("\jhepfner.sdf;")))

Dim myconn = New System.Data.SqlServerCe.SqlCeConnection(connstr)

Dim stringAdd As String = "Insert into t_acct_Taps (acct_num,acct_Tap_key) values (9999,4948)"
Dim objcommand As New Data.SqlServerCe.SqlCeCommand(stringAdd, myconn)

Try
objcommand.Connection.Open()
rowsaffected = (objcommand.ExecuteNonQuery())
MsgBox(rowsaffected)
objcommand.Connection.Close()
objcommand.Connection.Dispose()
objcommand = Nothing
Catch ex As Exception
MsgBox(ex.ToString)

End Try
End Sub
-----------------------------------------------------------------------
The original database that I am using is c:\jhepfner.sdf

When I run this, connstr shows:
Data Source =\Program Files\jhepfner\jhepfner.sdf;

In server explorer, it shows the connection string as:
Data Source=C:\Documents and Settings\Compaq_Owner\Local Settings\Application Data\Temporary Projects\jhepfner\jhepfner.sdf

Please help, and thank you,
Jeff

 

[jmcilhinney]

In ADO.NET connection strings, you use |DataDirectory| to represent the path of the default data folder. For Windows apps not deployed using ClickOnce, that is the folder from which the EXE was run, which it appears is what you're trying to use. As such, your connection string should be:

connstr = "Data Source = |DataDirectory|\jhepfner.sdf"

Much easier, right?