CODEWITHSUNDEEP
c#sqlsql-serverwinforms
user3442765
user3442765

Reputation: 61

C# is not saving data to SQL Server

When I replaced my concatenated insert with parameters, INSERT stopped saving to database.

I use:

Int32 rowsAffected = cmd.ExecuteNonQuery();                          
var totalRecords = (new SqlCommand("SELECT COUNT(*) FROM dbo.EmpInfo", cmd.Connection)).ExecuteNonQuery();

MessageBox.Show("RowsAffected: " + rowsAffected.ToString());
MessageBox.Show("Total Records: " + totalRecords);

but then, the return value is 1 for RowsAffected, and -1 for totalRecords.

if (Status == RegistrationStatus.r_OK)
{
    String query = "INSERT INTO dbo.EmpInfo(EmpYear, EmpStatus, LName, FName, JobTitle, EmpPay, EmpDoB, EmpSex, EmpAddr, EmpCity, EmpState, EmpZIP, EmpCountry, EmpEAddr, EmpTelNo, EmpMobileNo, EmpDate) VALUES (@EmpYear, @EmpStatus, @LName, @FName, @JobTitle, @EmpPay, @EmpDoB, @EmpSex, @EmpAddr, @EmpCity, @EmpState, @EmpZIP, @EmpCountry, @EmpEAddr, @EmpTelNo, @EmpMobileNo, getdate())";

    using (SqlConnection conn = new SqlConnection("Data Source=RB-DESKTOP;Initial Catalog=TimeDB;Persist Security Info=True;User ID=sa;Password=bautista7"))
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }

        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            using (SqlTransaction transaction = conn.BeginTransaction())
            {
                cmd.Connection = conn;
                cmd.Transaction = transaction;

                ParameterName = "@EmpYear", Value = EmpYear });
                cmd.Parameters.AddWithValue("@EmpYear", EmpYear);
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpStatus", Value = "Active" });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@LName", Value =   regLname_text.Text });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@FName", Value =   regFname_text.Text });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@JobTitle", Value = "NULL" });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpPay", Value =   PayType_cb.SelectedItem.ToString()});
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpDoB", Value =   regDob_dtp.Value.Date  });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpSex", Value =   gender });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpAddr", Value =   regAddr_text.Text });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpCity", Value =   regCity_text.Text  });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpState", Value =   regState_text.Text  });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpZIP", Value =  regZip_text.Text });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpCountry", Value = regCountry_text.Text });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpEAddr", Value = regEmail_text.Text  });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpTelNo", Value =   regTel_text.Text });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpMobileNo", Value =   regMob_text.Text});

                cmd.ExecuteNonQuery();
                transaction.Commit();

Upvotes: 0

Views: 293

Answers (3)

Ash
Ash

Reputation: 6035

You're not using the ExecuteNonQuery in the right place and you need to use ExecuteScalar in the other place. Also rowsAffected will always be 1 since you're inserting just one record.

if (Status == RegistrationStatus.r_OK)
{
    String query = "INSERT INTO dbo.EmpInfo(EmpYear,EmpStatus,LName,FName,JobTitle,EmpPay,EmpDoB,EmpSex,EmpAddr,EmpCity,EmpState,EmpZIP,EmpCountry,EmpEAddr,EmpTelNo,EmpMobileNo,EmpDate) values(@EmpYear,@EmpStatus,@LName,@FName,@JobTitle,@EmpPay,@EmpDoB,@EmpSex,@EmpAddr,@EmpCity,@EmpState,@EmpZIP,@EmpCountry,@EmpEAddr,@EmpTelNo,@EmpMobileNo,getdate())";
    using (SqlConnection conn = new SqlConnection("Data Source=RB-DESKTOP;Initial Catalog=TimeDB;Persist Security Info=True;User ID=sa;Password=bautista7"))
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }

        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            using (SqlTransaction transaction = conn.BeginTransaction())
            {
                cmd.Connection = conn;
                cmd.Transaction = transaction;
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpYear", Value = EmpYear });
                cmd.Parameters.AddWithValue("@EmpYear", EmpYear);
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpStatus", Value = "Active" });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@LName", Value =   regLname_text.Text });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@FName", Value =   regFname_text.Text });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@JobTitle", Value = "NULL" });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpPay", Value =   PayType_cb.SelectedItem.ToString()});
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpDoB", Value =   regDob_dtp.Value.Date  });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpSex", Value =   gender });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpAddr", Value =   regAddr_text.Text });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpCity", Value =   regCity_text.Text  });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpState", Value =   regState_text.Text  });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpZIP", Value =  regZip_text.Text });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpCountry", Value = regCountry_text.Text });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpEAddr", Value = regEmail_text.Text  });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpTelNo", Value =   regTel_text.Text });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmpMobileNo", Value =   regMob_text.Text});

                Int32 rowsAffected = cmd.ExecuteNonQuery();
                transaction.Commit();                        
                var totalRecords = (new SqlCommand("SELECT COUNT(*) FROM dbo.EmpInfo", cmd.Connection)).ExecuteScalar();
                MessageBox.Show("RowsAffected: " + rowsAffected.ToString());
                MessageBox.Show("Total Records: " + totalRecords);
           }
        }
   }
}

Upvotes: 0

bbsimonbb
bbsimonbb

Reputation: 29020

You could always cut n paste your sql into its own file and let QueryFirst generate all the parameter code. Your sql syntax will be validated, you won't have to worry about the types of parameters. Your maintenance will be simplified, and if ever your db schema changes, you'll see straight away if your sql is broken.

Upvotes: 0

Hari Prasad
Hari Prasad

Reputation: 16966

SqlCommand.ExecuteNonQuery returns number of rows affected, what you need is SqlCommand.ExecuteScalar which executes the query, and returns the first column of the first row

Upvotes: 1

Related Questions