Results 1 to 2 of 2

Thread: Can't Insert into SQL CE Table

  1. #1
    jhepfner is offline VB.NET Forum Newbie
    .NET Framework
    .NET 3.5
    Join Date
    May 2010
    Posts
    2
    Reputation
    0

    Can't Insert into SQL CE Table

    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

  2. #2
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,801
    Reputation
    1705
    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?

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •