CODEWITHSUNDEEP
c#database
jlodenius
jlodenius

Reputation: 829

C# Database Compact connection error

Im testing out some database compact code using a simple form to add data to a database. When I click the AddBtn, all I want is to insert some values from text fields into my database but instead I get this error "Format of the initialization string does not conform to specification starting at index 0." And the problem seems to be within the SqlCeConnection row.

Complete code sample:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Data.SqlServerCe;

    namespace WindowsFormsApplication1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
        InitializeComponent();
            }

            private void AddBtn_Click(object sender, EventArgs e)
            {
                SqlCeConnection con = new SqlCeConnection(@"C:\Users\Name\Documents\Visual Studio         2012\Projects\DataBaseTest\MyDatabase#1.sdf");
                try {
                    con.Open();

                    SqlCeCommand cmd = con.CreateCommand();

                    cmd.CommandText = "insert into test ([ID], [OrderID], [KundID]) values('"+textBox1.Text+"','"+textBox2.Text+"','"+textBox3.Text+"')";

                    try {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex) {
                        MessageBox.Show(ex.Message);
                    }


                }
                catch (Exception ex) {
                    MessageBox.Show(ex.Message);
                }
            }
        }
    }

Upvotes: 1

Views: 398

Answers (2)

Eli Algranti
Eli Algranti

Reputation: 9007

You are not using a valid connection string (add 'Data Source=' before the path to the SDF. See the MSDN documentation for SqlCeConnection.

SqlCeConnection con = new SqlCeConnection(@"Data Source=C:\Users\Name\Documents\Visual Studio         2012\Projects\DataBaseTest\MyDatabase#1.sdf"

Other than that do not use the text of the buttons directly in the query (leaves you open to SQL injection attack) use SQL parameters instead (again MSDN is your friend.)

Upvotes: 3

Steve
Steve

Reputation: 216358

Yes your connection string is wrong.
Look here http://www.connectionstrings.com/sql-server-2005-ce

The corrected one should be 

 string pathSDF = @"C:\Users\Name\Documents\Visual Studio 2012" + 
                   "\Projects\DataBaseTest\MyDatabase#1.sdf";
 SqlCeConnection con = new SqlCeConnection("Data Source=" + pathSDF + 
                                           ";Persist Security Info=False;");

Aside from that, your SQL command is really dangerous. You should never write sql command concatenating strings taken directly from the user input. This leads to Sql Injection Attacks
Change your code to: (I assume that your ID fields are of numeric type and not varchars)

  cmd.CommandText = "insert into test ([ID], [OrderID], [KundID]) values " + 
                    "(@userID, @orderID, @kundID)";  

  cmd.Parameters.AddWithValue("@userID", Convert.ToInt32(textBox1.Text));
  cmd.Parameters.AddWithValue("@orderID", Convert.ToInt32(textBox2.Text));
  cmd.Parameters.AddWithValue("@kundID", Convert.ToInt32(textBox3.Text));

Upvotes: 5

Related Questions