Need help with DataSet in C#

vlado_036's Avatar, Join Date: Aug 2006
Go4Expert Member
I have C# application which manage SQL database and there is no problem when i read multiple tables from dataset, but when i need to insert new rows in tables i have problem. It works only if i use one table and not with more tables.

I have one form where i collect the data and data should be inserted in 3 different database tables.

Here is the code :

Code:
System.Data.SqlClient.SqlConnection conn;
System.Data.SqlClient.SqlDataAdapter da;
DataSet ds;

string strConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\myDatabase.mdf;Integrated Security=True;User Instance=True";

conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = strConnectionString;

try
{
	conn.Open();                    

	da = new SqlDataAdapter("SELECT * FROM Employee", conn);
	SqlCommandBuilder cb;
	cb = new SqlCommandBuilder(da);
	DataTable TEmployee = ds.Tables["Employee"];

	DataRow dRow = TEmployee.NewRow();
	dRow[1] = textBox1.Text);
	dRow[2] = textBox2.Text;
	dRow[3] = textBox3.text;
	dRow[4] = Convert.ToInt16(textBox4.text);
	TEmployee.Rows.Add(dRow);
	da.Update(ds, "Employee");

	da = new SqlDataAdapter("SELECT * FROM Job", conn);
	cb = new SqlCommandBuilder(da);
	DataTable TJob = ds.Tables["Job"];
	DataRow dRow1 = TJob.NewRow(); // error is in this line
	dRow1[1] = cbSelection.Text;
	dRow1[2] = textBox5.Text;
	dRow1[3] = textBox6.Text;
	TJob.Rows.Add(dRow1);
	da.Update(ds, "Job");
                  
            int nCount = listView1.Items.Count;
	if (nCount > 0)
	{
		for (int i = 0; i < nCount; i++)
        	{
        		int nContact = i + 1;
	                da = new SqlDataAdapter("SELECT * FROM Contact", conn);
			cb = new SqlCommandBuilder(da);
                	DataTable TContact = ds.Tables["Contact"];
                                                        
	                DataRow dRow2 = TContact.NewRow();
        	        dRow2[1] = nContact;
                	dRow2[2] = textBox7.Text;
	                dRow2[3] = listView1.Items[i].SubItems[1].Text;
			dRow2[4] = listView1.Items[i].SubItems[1].Text;
        	        TContact.Rows.Add(dRow2);
                	da.Update(ds, "Contact");                            
		}                        
	}
	conn.Close();
	conn.Dispose();
	ds.Dispose();
	ds.Dispose();
}
catch (Exception ex)
{
	MessageBox.Show("Error : \n" + ex, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
The error is : Object reference not set to an instance of an object
0
xpi0t0s's Avatar, Join Date: Aug 2004
Mentor
Does it work OK if you use different variables for the different tables, e.g.
Code:
da1 = new SqlDataAdapter("SELECT * FROM Employee", conn);
da2 = new SqlDataAdapter("SELECT * FROM Job", conn);
If so then the problem with the initial code is most likely that you have to do something with the variable before you can reuse it, maybe Close() it, although I'm not certain.
0
vlado_036's Avatar, Join Date: Aug 2006
Go4Expert Member
No i try to use different variables for the different tables but have the same error again.

I found the solution :
For each table i create different function

private void newEmployee();
private void newJob();
private void newContact();

and than separate the code in these three functions.
After that i call them one by one and it works as i need.

Thanks anyway.