CODEWITHSUNDEEP
c#
Rookie
Rookie

Reputation: 113

Null Reference Exception in database handling

in c sharp in win forms i am encountering an error, something like null reference exception this is my code...and also I don't find any entries in the database table...

public partial class Form1 : Form
{
    string strCon, strQry;
    SqlConnection con;
    SqlCommand cmd;
    int rowsaffected;

    public Form1()
    {
        InitializeComponent();
    }

    box s2 = new box();
    class box
    {
        protected string fname;
        protected string lname;
        public void name(string s1, string s2)
        {
            fname = s1;
            lname = s2;

        }
    }

    void func(string x, string y)
    {
        s2.name(x, y);
    }

    private void btnClick_Click(object sender, EventArgs e)
    {
        string first = txtFname.Text;
        string last = txtLname.Text;
        func(first, last);

        strQry = "Insert Into Practice Values(" + first + "," + last + " )";

        cmd = new SqlCommand(strQry, con);

        cmd.Connection.Open();

        rowsaffected = cmd.ExecuteNonQuery();

        cmd.Connection.Close();

        MessageBox.Show(+rowsaffected + "  row(s) affected"); 
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        strCon = " Data Source = (local); Initial Catalog = Student; User Id= sa; Password=sa;";
        con = new SqlConnection(strCon);
    }

alt text http://img682.imageshack.us/img682/6017/hjki.jpg

sorry i didnt mention initialize it u mean to say con = new SqlConnection(strCon); i have done dat in dat case error is {"The name 'xyz' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."}

Upvotes: 0

Views: 2835

Answers (7)

fremis
fremis

Reputation: 584

I bet your problem is with the connection string, and the connection object is null. Here is a quick way to generate and test a connection string:

  1. Right click the windows desktop or inside a folder in windows explorer,
  2. Click New -> Text Document
  3. Rename the new file to Test.udl (.udl stands for Universal Data Link)
  4. Create and test your connection with the UDL Dialog and click OK
  5. Rename Test.udl to Test.txt and open the text file.

The text file will have a valid connection string that you can use in your code.

Also for your reference, I have simplified your code. The following should be much easier to debug:

    private const string dbConnection = "USE THE UDL STRING HERE";

    private void btnClick_Click(object sender, EventArgs e)
    {
        string first = txtFname.Text;
        string last = txtLname.Text;
        //I think the orig code was missing the single quotes
        string query = string.Format("INSERT INTO Practice ('{0}','{1}')", first, last);

        int rowsAffected = 0;

        //Using statement will automatically close the connection for you
        //Using a const for connection string ensures .NET Connection Pooling
        using (SqlConnection conn = new SqlConnection(dbConnection))
        {
            //Creates a command associated with the SqlConnection
            SqlCommand cmd = conn.CreateCommand();

            //Set your sql statement
            cmd.CommandText = query;

            //open the connection
            cmd.Connection.Open();

            //Execute the connection
            rowsAffected = cmd.ExecuteNonQuery();
        }
        MessageBox.Show(rowsAffected + " rows Affected");
    }

Upvotes: 2

JeffH
JeffH

Reputation: 10484

Any time you get a null reference on a line that has multiple calls linked together like:

    something.somethingElse.somethingElse

break it apart and check each piece. For example, in your case this code:

    cmd = new SqlCommand(strQry, con);
    SqlConnection sc = cmd.Connection;
    System.Diagnostics.Debug.Assert(sc != null, "You got a null connection from the SqlCommand.");
    sc.Open();

may demonstrate what the problem is.

Upvotes: 0

msarchet
msarchet

Reputation: 15242

You are actually creating you connection correctly. What is happening is if you look at your connection string

strCon = " Data Source = (local); Initial Catalog = Student; User Id= sa; Password=sa;";

when it tries to connect it reads this as:

Data Source: " (local)" Initial Catalog: " Student" User Id= " sa" Password - "sa"

So the spaces that you have after the equals signs are getting passed to the SQL server. What your string should look like is this

strCon = "Data Source =(local); Initial Catalog=Student;User Id=sa;Password=sa;"

I am pretty sure that you are never getting an actual connection to your database, but it's failing silently.

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33476

I don't think you need cmd.Connection.Open and cmd.Connection.Close.
cmd will open the connection & close it, in order to execute the query/stored procedure.

Upvotes: 0

Larry Hipp
Larry Hipp

Reputation: 6255

Are you setting a connection string? It appears you are accessing the Connection object without telling it where to insert the data.

cmd.Connection.ConnectionString = "some string";

Upvotes: 0

sergiom
sergiom

Reputation: 4877

I suppose the error happens because you use con, that is not initialized.

I don't see a SqlConnection con = new SqlConnection(strCon);

Upvotes: 2

Rafa Castaneda
Rafa Castaneda

Reputation: 832

You are not instantiating the con variable, for example:

SqlConnection con = new SqlConnection(connectionString);

Upvotes: 3

Related Questions