Null Reference Exception in database handling

Question

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

I think you're not creating 'SqlConnection con' in your code. — Jay Riggs
– Jay Riggs, Commented Jan 28, 2010 at 18:25
You never initialize it anywhere, you declare it, but never initialize. — Anthony Forloney
– Anthony Forloney, Commented Jan 28, 2010 at 18:27
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."} — Rookie
– Rookie, Commented Jan 28, 2010 at 18:30
Why are you guys posting the answer as comments? Post it as an answer so you can get the points. — Brandon
– Brandon, Commented Jan 28, 2010 at 18:30
Someone has to say it... "Little Bobby Tables" is going to hurt you... — Austin Salonen
– Austin Salonen, Commented Jan 28, 2010 at 18:52

Rafa Castaneda · Accepted Answer · 2010-01-28 18:29:37Z

3

You are not instantiating the con variable, for example:

SqlConnection con = new SqlConnection(connectionString);

answered Jan 28, 2010 at 18:29

Rafa Castaneda

8321 gold badge7 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JeffH Over a year ago

Look at Form1_Load() - OP is doing it there.

Brandon Over a year ago

@JeffH, that was edited into the question after this answer was posted. See the comments on the question.

sergiom · Accepted Answer · 2010-01-28 18:30:03Z

2

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

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

answered Jan 28, 2010 at 18:30

sergiom

4,9273 gold badges26 silver badges34 bronze badges

Comments

fremis · Accepted Answer · 2010-01-28 20:17:06Z

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:

Right click the windows desktop or inside a folder in windows explorer,
Click New -> Text Document
Rename the new file to Test.udl (.udl stands for Universal Data Link)
Create and test your connection with the UDL Dialog and click OK
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");
    }

Upvoted as this will probably fix the OP's problem in light of his edit.

Larry Hipp · Accepted Answer · 2010-01-28 18:30:38Z

0

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

answered Jan 28, 2010 at 18:30

Larry Hipp

6,2653 gold badges28 silver badges31 bronze badges

1 Comment

Rookie Over a year ago

strQry = "Insert Into Practice Values(" +"'" +first+"'," +"'" +last +"'" +")"; the problem here was while mentioning the insert query i dint put the string values in single braces!!!! i gt it ryt now!

shahkalpesh · Accepted Answer · 2010-01-28 18:54:20Z

0

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.

answered Jan 28, 2010 at 18:54

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Comments

msarchet · Accepted Answer · 2010-01-28 18:58:32Z

0

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.

answered Jan 28, 2010 at 18:58

msarchet

15.3k2 gold badges45 silver badges66 bronze badges

1 Comment

Rookie Over a year ago

still da the same problem msarchet

JeffH · Accepted Answer · 2010-01-28 19:16:55Z

0

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.

answered Jan 28, 2010 at 19:16

JeffH

10.6k2 gold badges29 silver badges48 bronze badges

Collectives™ on Stack Overflow

Null Reference Exception in database handling

7 Answers 7

2 Comments

Comments

1 Comment

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

1 Comment

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related