Trying to check if a record was inserted into the database

[jakeir]

The code that I have here to insert a record works fine, but I tried to put a check in here to see if the record was entered and send back to a label on the page if it was entered or not.
but when I run this with the lines that are commented out here, it stops at the line with "Object result = cmd2.ExecuteScalar()
Stating incorrect sysntext.
Any ideas I would appreciate
thank you

My code:
private void Button1_Click(object sender, EventArgs e)
{

System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection("Data Source=(local)\\SQLEXPRESS;Initial Catalog=NotthWind;Integrated Security=True");

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("INSERT INTO Shippers(CompanyName, Phone) VALUES(@CompName,@Phone)", con);

cmd.Parameters.AddWithValue("@CompName", TextBox1.Text).SqlDbType = System.Data.SqlDbType.NVarChar;
cmd.Parameters.AddWithValue("@Phone", TextBox2.Text).SqlDbType = System.Data.SqlDbType.NVarChar;


using (con)
{
con.Open();
cmd.ExecuteNonQuery();
}

con.Close();

//System.Data.SqlClient.SqlConnection con2 = new System.Data.SqlClient.SqlConnection("Data Source=(local)\\SQLEXPRESS;Initial Catalog=NotthWind;Integrated Security=True");
//System.Data.SqlClient.SqlCommand cmd2 = new System.Data.SqlClient.SqlCommand("Select CompanyName From Shippers Where CompanyName = @CompName2)", con2);

//cmd2.Parameters.AddWithValue("@CompName2", TextBox1.Text).SqlDbType = System.Data.SqlDbType.NVarChar;
//lblResult.Visible = true;

//using (con2)
//{
// con2.Open();
// Object result = cmd2.ExecuteScalar();

// if (result != null)
// lblResult.Text = string.Format("{0:c}", result);
// else
// lblResult.Text = "was not inserted";
//}



}

 

[jmeckley]

error is red

System.Data.SqlClient.SqlCommand cmd2 = new System.Data.SqlClient.SqlCommand("Select CompanyName From Shippers Where CompanyName = @CompName2[COLOR=red])[/color]", con2);

some other issues with your code.
1. ExecuteScalar() will never return null. if the value is null it returns DbNull.Value, not null.
2. the result is a string so

string.Format("{0:c}", result)

is pointless. "c" is the currency formatter
3. the usages of connections/commands is poor.
3.1. why use 2 connections back to back? using one connection with 2 commands is a better usage of the connection object.
3.2. if you are going to manage the connections and commands within a method, then wrap everything in the using block, not just the opening and execution of the command.
3.3. there is no need for the 2nd command at all. ExecuteNonQuery() will return the number of rows affected. you could accomplish the sames thing like this

int affected = cmd1.ExecuteNonQuery();
if(affected == 0)
{
   // record not inserted
}
else
{
   //record inserted
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[jakeir]

Tnaks Jason that did it, and thanks for the other advise I really appreciate it. I am new to ASP, so thank you for the additional information.

 

[jmeckley]

asp and asp.net are totally different.
asp is a server scripting language.
asp.net is an http framework built on top of the .net framework.
webforms is an html engine that runs atop asp.net.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190