• Hello and welcome to our new forums. We upgraded our forum sites to a more robust and modern system which we hope you will enjoy. Be sure to check out your profile by clicking the button on the top right and configure your preferences, signature, time zone, avatar, etc. as you wish. If you need help with using this new forum'ware try the help link on the bottom right.

    Click here to review your account now.

Can't Insert into SQL CE Table

jhepfner

New member
Joined
May 15, 2010
Messages
2
Programming Experience
1-3
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,031
Location
Sydney, Australia
Programming Experience
10+
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?
 
Top Bottom