Adding new records to sql database

[pepsy11]

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. =)

[kvprajapati]

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

[pepsy11]

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");

[kvprajapati]

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();

[pepsy11]

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?

[kvprajapati]

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

Parameter.

[pepsy11]

This does not save anything to my database?

[kvprajapati]

>This does not save anything to my database?

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

[pepsy11]

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

[amitshrivas]

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();

[pepsy11]

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

[pepsy11]

double post

[amitshrivas]

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..

[amitshrivas]

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