Member Avatar for pete08

I have developed VB.NET 2005 application with .sdf database, but I have problem with inserting new records into databse from application.
I have tried with MSDN example code, but failed.
Can someone provide me with code for this one. Please...

Pete

  • 3 Contributors
  • 2 Replies
  • 118 Views
  • 4 Years Discussion Span
  • Latest Post Latest Post by Ailton Junior

Dani AI

Generated

reported inserts failing against a VB.NET 2005 .sdf (SQL Server Compact) file. Replies from and point to examples, but the usual causes are environmental rather than a single missing line of code. The checklist below addresses the most common reasons an insert appears to fail and gives a minimal, working VB.NET pattern to use.

Common causes and quick checks

  • Use the SQL Server Compact classes (System.Data.SqlServerCe.SqlCeConnection/SqlCeCommand), not SqlClient or OleDb.
  • Typical connection string: Data Source=|DataDirectory|\MyDatabase.sdf;Persist Security Info=False; — remember |DataDirectory| resolves to the app runtime folder (App_Data for ASP.NET, application base for desktop apps).
  • Visual Studio file copy: if the .sdf file property "Copy to Output Directory" is set to "Copy always", the runtime changes are overwritten on each run. Use "Copy if newer" or "Do not copy" so the runtime copy is not replaced.
  • Ensure the correct SQL CE runtime version (3.5 vs 4.0) is present on the machine or deployed with the app.
  • File permissions matter: the account running the app (ASP.NET app pool identity, service account, or logged user) needs write permission on the .sdf file.
  • Use parameterized commands to avoid SQL errors and type mismatches; verify NOT NULL columns and constraints.

Minimal VB.NET pattern

Imports System.Data.SqlServerCe

Dim cs As String = "Data Source=|DataDirectory|\MyDatabase.sdf;Persist Security Info=False;"
Using cn As New SqlCeConnection(cs)
    cn.Open()
    Using cmd As New SqlCeCommand("INSERT INTO MyTable (Col1, Col2) VALUES (@c1, @c2)", cn)
        cmd.Parameters.AddWithValue("@c1", "value1")
        cmd.Parameters.AddWithValue("@c2", 123)
        cmd.ExecuteNonQuery()
    End Using
End Using

Troubleshooting tips

Log or print the resolved DataDirectory (Debug.WriteLine(AppDomain.CurrentDomain.GetData("DataDirectory"))) to confirm which .sdf file is being used at runtime. Inspect the same file with Visual Studio Server Explorer or a SQL CE viewer to confirm inserted rows. If there is still no insert and no exception, the runtime is likely writing to a different .sdf copy (file-copy or path issue) or the connection string/provider is incorrect.

Recommended Answers

All 2 Replies

Member Avatar for Ailton Junior

Study this code: ;D

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.