Hello..

I am making a program where i use an sql database to storage user information, but i cant figure out how to add a new record that actually stays in the database, i have found some different codes but they only add the record "locally", so when i close my program the new records is gone.. =s

If anybody can help me i will really appreciate it. =)

My database contains(in this order): User_ID(primary key), firstname, lastname, title

Another thing is that the new record automatically should get a new User_ID.

thanks in advance. =)

Recommended Answers

Show your code and name your database product. You must know-how ADO.NET to accomplish database operation.

Jump to Post

You may use SqlCommand class and its method to execute SQL statements.

con = new System.Data.SqlClient.SqlConnection();
           
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Users.mdf;Integrated Security=True;User Instance=True";


           System.Data.SqlClient.SqlCommand cmd;
           cmd = new System.Data.SqlClient.SqlCommand();

           cmd.Connection=con;
           cmd.CommandText="INSERT INTO [Users] VALUES (@p1,@p2,@p3)"; 
 
          cmd.Parameters.AddWithValue("@p1",textBox1.Text);
          cmd.Parameters.AddWithValue("@p2",textBox2.Text);
          cmd.Parameters.AddWithValue("@p3",textBox3.Text);
      
         con.Open();
         cmd.ExecuteNonQuery();
         con.Close();
Jump to Post

>What does "@p1" mean? row 1?

Parameter.

Jump to Post

>This does not save anything to my database?

Do you get any errors? If no them check your database located at Bin\Debug folder.

Jump to Post

All 13 Replies

Show your code and name your database product. You must know-how ADO.NET to accomplish database operation.

My database is called Users.mdf, and the table in it is called tblUsers.

I am not sure what code you want but here is my loading of the database(in my form1_load event):

con = new System.Data.SqlClient.SqlConnection();
            ds1 = new DataSet();
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Users.mdf;Integrated Security=True;User Instance=True";


            try
            {
                con.Open();
                string sql = "SELECT * From tblUsers";
                da = new System.Data.SqlClient.SqlDataAdapter(sql, con);
                da.Fill(ds1, "Users");

                MaxRows = ds1.Tables["Users"].Rows.Count;

                con.Close();
            }
            catch (Exception g)
            {
                MessageBox.Show(g.ToString());
            }

The code i have at the moment for saving a new record is this:

System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(da);

            DataRow dRow = ds1.Tables["Users"].NewRow();

            dRow[1] = textBox1.Text;
            dRow[2] = textBox2.Text;
            dRow[3] = textBox3.Text;

            ds1.Tables["Users"].Rows.Add(dRow);
            MaxRows = MaxRows + 1;

            da.Update(ds1, "Users");

You may use SqlCommand class and its method to execute SQL statements.

con = new System.Data.SqlClient.SqlConnection();
           
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Users.mdf;Integrated Security=True;User Instance=True";


           System.Data.SqlClient.SqlCommand cmd;
           cmd = new System.Data.SqlClient.SqlCommand();

           cmd.Connection=con;
           cmd.CommandText="INSERT INTO [Users] VALUES (@p1,@p2,@p3)"; 
 
          cmd.Parameters.AddWithValue("@p1",textBox1.Text);
          cmd.Parameters.AddWithValue("@p2",textBox2.Text);
          cmd.Parameters.AddWithValue("@p3",textBox3.Text);
      
         con.Open();
         cmd.ExecuteNonQuery();
         con.Close();

You may use SqlCommand class and its method to execute SQL statements.

con = new System.Data.SqlClient.SqlConnection();
           
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Users.mdf;Integrated Security=True;User Instance=True";


           System.Data.SqlClient.SqlCommand cmd;
           cmd = new System.Data.SqlClient.SqlCommand();

           cmd.Connection=con;
           cmd.CommandText="INSERT INTO [Users] VALUES (@p1,@p2,@p3)"; 
 
          cmd.Parameters.AddWithValue("@p1",textBox1.Text);
          cmd.Parameters.AddWithValue("@p2",textBox2.Text);
          cmd.Parameters.AddWithValue("@p3",textBox3.Text);
      
         con.Open();
         cmd.ExecuteNonQuery();
         con.Close();

What does "@p1" mean? row 1?

>What does "@p1" mean? row 1?

Parameter.

This does not save anything to my database?

>This does not save anything to my database?

Do you get any errors? If no them check your database located at Bin\Debug folder.

I don't get any errors.. But it doesn't save to the database.. =s

Hi..
There have u set the Identity for Primary key in database means is it auto generated r not..if it is autogenerated no need to give..
and if u using Dataset for inserting record..
use this for changes.. hope it wil work..
da.Update(ds1, "Users");
ds1.AcceptChanges();

I don't need to change anything? I need to add a new row in the root DB and i am asking how i can make it work. So i ask you.. Is it possible to do with dataset? or is it just temporary like all the other stuff i have tried? =s

double post

I don't need to change anything? I need to add a new row in the root DB and i am asking how i can make it work. So i ask you.. Is it possible to do with dataset? or is it just temporary like all the other stuff i have tried? =s

///////////////////////////////////
This is your code..right
////////////////////////

System.Data.SqlClient.SqlCommandBuilder cb;
      cb = new System.Data.SqlClient.SqlCommandBuilder(da);
      DataRow dRow = ds1.Tables["Users"].NewRow();
      dRow[1] = textBox1.Text;
      dRow[2] = textBox2.Text;
      dRow[3] = textBox3.Text;
       
      ds1.Tables["Users"].Rows.Add(dRow);
      MaxRows = MaxRows + 1;
      da.Update(ds1, "Users");

You suppose to write this code too
ds1.AcceptChanges();
so that The new record will updated in database..

Or u can write the following code..
u have 4 feilds in database
Id,FirstName,LastName and title

First Set the primary Key as Autogenrated in Database creation or alter it..
then in C#.. use..

sqlconnection conn=new SqlConnection(connectionString);
string StrCmd="insert into TableName(FirstName,LastName,Title) values('"+TextBox.Text.Trim()+"','"+TextBox2.Text.Trim()+"','"+TextBox3.Text.Trim()+"')";
Sqlcommand cmd=new Sqlcommand(StrCmd,conn);
int CheckRecord=0;
conn.open();
CheckRecord=cmd.ExecuteNonQuery();
if(CheckRecord>0)
{
   //confirmation Message..
}
cmd,dispose();
cmd=null;
conn.close();

i hope this will work for u.. thanks

Be a part of the DaniWeb community

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