asked on

C# Databinding

Everytime i hit the save button (btnSaveContacts_Click) i get a 'NullReference Exception was Unhandled' Why? the controls load up fine but falls over when i select a row, delete then hit save.
It seems to be picking up the table when i put a break point against btnSaveContacts yet the error is saying its NULL! Help!

private void btnCustomerSearch_Click(object sender, EventArgs e)
        {
            
            using (frmSearchCustomer form = new frmSearchCustomer())
            {
                if (form.ShowDialog() == DialogResult.OK)
                {
                    {
                        
                        // Setup DB-Connection
                        ConnectionString = @"Data Source=<SQLInstance>;Initial Catalog=<Database>;Integrated Security=True";
                        SqlConnection cn = new SqlConnection(ConnectionString);
 
                        // Create the DataSet
                        ds = new DataSet("Contact");
 
                        // Fill the Dataset with Customers, map Default Tablename
                        // "Table" to "Customers".
                        SqlDataAdapter da1 = new SqlDataAdapter("SELECT * FROM Customer WHERE Counter = " + form.Counter + "", cn);
                        da1.TableMappings.Add("Table", "Customer");                        
                        da1.Fill(ds);
 
                        // Fill the Dataset with Contacts, map Default Tablename
                        // "Table" to "Contacts".
                        SqlDataAdapter da2 = new SqlDataAdapter("SELECT * FROM Contact WHERE CustomerCounter = " + form.Counter + "", cn);
                        da2.TableMappings.Add("Table", "Contact");
                        SqlCommand cmdDelete = cn.CreateCommand();
                        cmdDelete.CommandType = CommandType.Text;
                        cmdDelete.CommandText = ("DELETE FROM Contact WHERE Counter =@Counter");
                        cmdDelete.Parameters.AddWithValue("@Counter", "Counter");
                        cmdDelete.Parameters["@Counter"].SourceVersion = DataRowVersion.Original;
                        da2.DeleteCommand = cmdDelete;
                        da2.Fill(ds);                                            
 
                       
 
                        // Establish the Relationship "RelCustOrd"
                        // between Customers and Contacts
                        System.Data.DataRelation relCustCon;
                        System.Data.DataColumn colMaster1;
                        System.Data.DataColumn colDetail1;
                        colMaster1 = ds.Tables["Customer"].Columns["Counter"];
                        colDetail1 = ds.Tables["Contact"].Columns["CustomerCounter"];
                        relCustCon = new System.Data.DataRelation("RelCustCon", colMaster1, colDetail1);
                        ds.Relations.Add(relCustCon);
 
                                               
                        // DataTable in the DataSet.
                        dsView = ds.DefaultViewManager;
 
                        // Grid Databinding
                        datagridview1.DataSource = dsView;
                        datagridview1.DataMember = "Customer.RelCustCon";
 
                                             
 
                        // Text Columns Databinding
                        txtCustomerName.DataBindings.Add("Text", dsView, "Customer.CustomerName");
                        txtAddress.DataBindings.Add("Text", dsView, "Customer.Address");
                        txtPostCode.DataBindings.Add("Text", dsView, "Customer.PostCode");
                        txtCustomerTelephone.DataBindings.Add("Text", dsView, "Customer.MainTelephone");
                        txtProduct.DataBindings.Add("Text", dsView, "Customer.Product");
                                                                       
                    }
                }
            }
        }
 
private void btnSaveContacts_Click(object sender, EventArgs e)
        {
            
            da2.Update(ds, "Contact");  //Errors...NullReference Exception, yet spy glass shows table when debugging!
        }

Open in new window

nkhelashvili

Try to declare DataAdapters out ot the methods :)

wilko100

ASKER

you mean something like

Public SqlDataAdaptor da2;?

Anurag Thakur

private will also do inside the class
private SqlDataAdaptor da2;?

you are trying to access the da2 defined in btnCustomerSearch_Click method from save method and its not accessible
its surprising why u didnt get a compile time error

wilko100

ASKER

Yes i see whats happening, though its already declared (See code) as private SqlDataAdaptor da2; da2 is NULL in the btnSaveContacts click, where should i delcare it?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
 
namespace CustomersAndContacts
{
    public partial class frmMain : Form
    {
        private String ConnectionString;
        private DataViewManager dsView;
        private DataSet ds;
        private SqlDataAdapter da1;
        private SqlDataAdapter da2;     
 
etc
etc

Open in new window

nkhelashvili

You`re probebly using da1 and da2 object before instantiating

wilko100

ASKER

Yes your right, da2 is not exposed to anything other than btnCustomerSearch_Click, therefore
btnSavecontacts cannot see da2 method, thats why its showing as NULL. So how can i expose da2 to all the controls without ripping out the code set in btnCustomerSearch and starting again?

nkhelashvili

try changing

private SqlDataAdapter da2;

to

private SqlDataAdapter da2= new SqlDataAdapter ();

besides of it, make breakpoint at the line
da2.Update(ds, "Contact");
and check da2 and ds objects , and then tell us what`s happening

ASKER CERTIFIED SOLUTION

nkhelashvili

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wilko100

ASKER

Yes it works! upto a point which is great, da2 is now not NULL, and ds is picking up Contact table (as well as Customer) i.e i click on the spy glass and it displays customer table then a drop down box with the contact table. Should return just contact? but...i get an error...
{"Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."}
but i have one?! taken straight the book! see code sample.

 SqlDataAdapter da2 = new SqlDataAdapter("SELECT * FROM Contact WHERE CustomerCounter = " + form.Counter + "", cn);
                        da2.TableMappings.Add("Table", "Contact");
                        SqlCommand cmdDelete = cn.CreateCommand();
                        cmdDelete.CommandType = CommandType.Text;
                        cmdDelete.CommandText = ("DELETE FROM Contact WHERE Counter =@Counter");
                        cmdDelete.Parameters.AddWithValue("@Counter", "Counter");
                        cmdDelete.Parameters["@Counter"].SourceVersion = DataRowVersion.Original;
                        da2.DeleteCommand = cmdDelete;
                        da2.Fill(ds);

Open in new window

wilko100

ASKER

to add, i checked the table and it has a primary key set as i read that this can sometimes cause the error. Any other ideas?

wilko100

ASKER

After playing around abit with the code, i found adding cmdDelete.executeNonQuery then changing sql statement to ("DELETE FROM Contact WHERE Counter = 2") worked, so it must be something to do with the sql statement being to ambiguous? anyone help, i cant belive how difficult this is to do a friggin simple delete!!! (Is there something where i can grab the Counter ID from the row selected and say if DELETE FROM Contact WHERE Counter in selected Row is = to Counter ?????

nkhelashvili

I think
cmdDelete.Parameters.AddWithValue("@Counter", "Counter");

this line is incorrect. What is the column type of Counter?

wilko100

ASKER

int set as primary key

nkhelashvili

and it`s error :)

if there was for example cmdDelete.Parameters.AddWithValue("@Counter", 16);

it would be right

wilko100

ASKER

It does not error, never has. And that example works but i dont want that, that will delete everything on the grid cos all the rows will have the same counter. i just simply want to to know how to highlight the row seleceted and delete it both from the grid and in the db if the counter in the row is = to the customer counter. Why is this so frustrating!! i have never had such a problem with a control!!

wilko100

ASKER

Thanks, your answer to my first question worked, though opened up another problem but will ask a new question on this new problem and award you 500 points for this question, cheers nkhelashvill