CODEWITHSUNDEEP
c#
Brock Robichaux
Brock Robichaux

Reputation: 1

Can't connect with database c#

cmd.executenonquery showing (can't have null values) ERROR!

Here is the code behind

I am trying to insert data into textboxes and sent to a table in a database, but it keeps showing NULL.

SqlConnection con = new SqlConnection("Data Source=*******;Initial Catalog=MaleFemale;Integrated Security=True");

public MainWindow()
{
    InitializeComponent();
}

private void Button_Click(object sender, RoutedEventArgs e)
{
    if (con.State == System.Data.ConnectionState.Closed)
    {
        con.Open();

        SqlCommand cmd = new SqlCommand("insert into TableMaleFemale(Name,EiD,Gender) values ('" + NametextBox.Text + "', '" + EiDtextBox.Text + "', '" + GendertextBox.Text + "')", con);


        cmd.ExecuteNonQuery();
        cmd.Dispose();

        con.Close();
    }
}

Upvotes: 0

Views: 57

Answers (1)

D Stanley
D Stanley

Reputation: 152644

There are three things that you should change with your code:

  1. Do not create one connection object and re-use it - connections are pooled by .NET, so creating them generally isn't en expensive process, plus you don't have to worry about checking the current state anymore.
  2. Dispose of connections and commands immediately after you're done with them - this is convenient to do by using using statement blocks
  3. Use parameters instead of concatenating SQL strings (expecially when dealing with user input) - using concatenation opens you up to SQL injection attacks and to characters that will foul up the SQL (e.g. an apostrophe in a name). It also makes null values easier to deal with.

If I make those changes, your code is not more like this:

String connectionString = "Data Source=*******;Initial Catalog=MaleFemale;Integrated Security=True";

public MainWindow()
{
    InitializeComponent();
}

private void Button_Click(object sender, RoutedEventArgs e)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string sql = "insert into TableMaleFemale(Name,EiD,Gender) values (@Name, @EiD, @Gender)"
        using(SqlCommand cmd = new SqlCommand(sql, connection))
        {
            cmd.Parameters.Add("@Name").Value   = NametextBox.Text == null   ? DBNull.Value : NametextBox.Text;
            cmd.Parameters.Add("@EiD").Value    = EiDtextBox.Text == null    ? DBNull.Value : EiDtextBox.Text;
            cmd.Parameters.Add("@Gender").Value = GendertextBox.Text == null ? DBNull.Value : GendertextBox.Text;
            connection.Open();
            cmd.ExecuteNonQuery();
        }
        con.Close();
    }
}

None of these three things may fix your stated problem, but it will solve other problems that you don't have yet.

Upvotes: 2

Related Questions