C# - SQL Server 2008 - how do I add a row?

[liam0014]

Hello,

I am quite stuck. I have my database running on a seperate machine from where I am writing my application. The connection between Visual Studio and the database seems to be fine as I can display data in various controls on the GUI.

My problem arises when I try to add something to the database, I get no error messages just the data isn't added to the database.

Here is my code:

MyDataSet.categoryRow newRow = myDataSet.category.NewcategoryRow();

newRow.CategoryID = Convert.ToInt32(tbxID.Text);
newRow.CategoryName = tbxCategory.Text;
newRow.Description = tbxDescription.Text;


myDataSet.category.Rows.Add(newRow);
myDataSet.category.AcceptChanges();
categoryDA.Update(fYPDataSet); //categoryDA is the table adapter generated by visual studio

I have tried many variations on this in attempt to get it to work.
Including the following:

System.Data.SqlClient.SqlConnection con;
System.Data.SqlClient.SqlDataAdapter da;

con = new System.Data.SqlClient.SqlConnection();

con.ConnectionString = "Data Source=FYPSERVER;Initial Catalog=FYP;Integrated Security=True";

con.Open();

string sql = "SELECT * from category";
da = new System.Data.SqlClient.SqlDataAdapter(sql, con);

con.Close();
            
System.Data.SqlClient.SqlCommandBuilder cb;
cb = new System.Data.SqlClient.SqlCommandBuilder(da);
            
MyDataSet.categoryRow newRow = myDataSet.category.NewcategoryRow();

newRow[1] = tbxCategory.Text;
newRow[2] = tbxDescription.Text;

myDataSet.Tables["category"].Rows.Add(newRow);
//fYPDataSet.category.Rows.Add(newRow);
//fYPDataSet.category.AcceptChanges();
da.Update(myDataSet, "catagory");

I also added a dataGridView control and linked that to the deisred table, using the design window, not through code.
Even when I add, edit or remove any of the values none of it is happening to the actual database or when I close and re-open that particular form.

I apologise if there has been some disucssion of this before but I have been working on this one aspect for a week and I am beginning to loose the will to live. I have done a search for this issue but all the results I have come across are due to error messages.

I am writing this in Visual Studio 2010 ultimate and the database is running under SQL Server 2008 R2 Enterprise

Thank you very much

[prvnkmr194]

Here is my simple code to save the record in table.

cnn.ConnectionString = "your connection string";
cnn.Open();
cmd.CommandText = "select * from <tablename>";
cmd.Connection = cnn;
da.SelectCommand = cmd;
da.Fill(ds, "<tablename>");

OleDbCommandBuilder cmb = new OleDbCommandBuilder(da);
DataRow drow;
drow = ds.Tables["table1"].NewRow();

drow["column1"] = textBox1.Text;
drow["column2"] = textBox2.Text;
drow["column3"] = textBox3.Text;

ds.Tables["table1"].Rows.Add(drow);
da.Update(ds, "table1");
MessageBox.Show("ONE RECORD IS ADDED.");

ds dataset
da data adapter
table1 name of your table
column1,column2.column3 is the name of column in your table
cmd is a command object

I hope it will help you other wise feel free to ask any problem

[liam0014]

That was perfect.

Thank you very much

[prvnkmr194]

Most welcome ...............