开发者

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


You are not instantiating the con variable, for example:

SqlConnection con = new SqlConnection(connectionString);


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

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


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


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


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.


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.


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜