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:
- 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");
}
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.
精彩评论