none
ExecuteNonQuery(); converting... INSERT at Visual RRS feed

  • Question

  • Hey I'm new at this forum, so I hope I am asking at the right place... My code looks like this..

    namespace Exwterika_Iatreia
    {
        public partial class patient_file : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
            }
            public string GetConnectionString()
            {
                return System.Configuration.ConfigurationManager.ConnectionStrings["ekswterika_iatreiaConnectionString"].ConnectionString;
            }
            private void ExecuteInsert(string id_number, string patient_name, string patient_lastname, string patient_sex, string father_name, string mother_name, string  marital_status, string patient_number, string occupation, string insurance_fund, string patient_mail, string date_birth, string place_birth, string amka, string medical_history)
            {
                SqlConnection conn = new SqlConnection(GetConnectionString());
                string sql = "INSERT INTO patient_file (id_number, patient_name, patient_lastname, patient_sex, father_name, mother_name, marital_status, patient_number, occupation, insurance_fund, patient_mail, date_birth, place_birth, amka, medical_history) VALUES " + "(@id_number, @patient_name, @patient_lastname, , @patient_sex, @father_name, @mother_name, @marital_status, @patient_number, @occupation, @insurance_fund, @patient_mail, @date_birth, @place_birth, @amka, @medical_history)";
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlParameter[] param = new SqlParameter[15];
                    param[0] = new SqlParameter("@id_number", SqlDbType.BigInt, 15);
                    param[1] = new SqlParameter("@patient_name", SqlDbType.Char, 10);
                    param[2] = new SqlParameter("@patient_lastname", SqlDbType.Char, 20);
                    param[3] = new SqlParameter("@patient_sex", SqlDbType.Char, 6);
                    param[4] = new SqlParameter("@father_name", SqlDbType.Char, 10);
                    param[5] = new SqlParameter("@mother_name", SqlDbType.Char, 10);
                    param[6] = new SqlParameter("@marital_status", SqlDbType.Char, 7);
                    param[7] = new SqlParameter("@patient_number", SqlDbType.BigInt, 20);
                    param[8] = new SqlParameter("@occupation", SqlDbType.Char, 20);
                    param[9] = new SqlParameter("@insuarnce_fund", SqlDbType.Char, 10);
                    param[10] = new SqlParameter("@patient_mail", SqlDbType.VarChar, 50);
                    param[11] = new SqlParameter("@date_birth", SqlDbType.DateTime, 50);
                    param[12] = new SqlParameter("@place_birth", SqlDbType.Char, 50);
                    param[13] = new SqlParameter("@amka", SqlDbType.BigInt, 50);
                    param[14] = new SqlParameter("@medical_history", SqlDbType.Text, 50);




                    param[0].Value = id_number;
                    param[1].Value = patient_name;
                    param[2].Value = patient_lastname;
                    param[3].Value = patient_sex;
                    param[4].Value = father_name;
                    param[5].Value = mother_name;
                    param[6].Value = marital_status;
                    param[7].Value = patient_number;
                    param[8].Value = occupation;
                    param[9].Value = insurance_fund;
                    param[10].Value = patient_mail;
                    param[11].Value = date_birth;
                    param[12].Value = place_birth;
                    param[13].Value = amka;
                    param[14].Value = medical_history;


                    for (int i = 0; i < param.Length; i++)
                    {
                        cmd.Parameters.Add(param[i]);
                    }


                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    string msg = "Insert Error";
                    msg += ex.Message;
                    throw new Exception(msg);
                }
                finally
                {
                    conn.Close();
                }
            }
            public static void ClearControls(Control Parent)
            {


                if (Parent is TextBox)
                { (Parent as TextBox).Text = string.Empty; }
                else
                {
                    foreach (Control c in Parent.Controls)
                        ClearControls(c);
                }
            }


            protected void Button1_Click1(object sender, EventArgs e)
            {
                if (patient_mail.Text == patient_reemail.Text)
                {
                    ExecuteInsert(id_number.Text, patient_name.Text, patient_lastname.Text, patient_sex.SelectedItem.Text, father_name.Text, mother_name.Text, marital_status.SelectedItem.Text, patient_number.Text, occupation.Text, insurance_fund.Text, patient_mail.Text, date_birth.Text, place_birth.Text, amka.Text, medical_history.Text);
                    Response.Write("Record was successfully added!");
                    ClearControls(Page);
                }
                else
                {
                    Response.Write("E-mail uncorrect");
                    patient_mail.Focus();
                }
            }
        }
    }

    When I start Debugging the form to insert appears correctly at my browser screen but when I click on the button nothing happens.. Instead a mistake appears on visual at the order "cmd.ExecuteNonQuery();" and it says that it can not be converted from String to Int64... I can't what i have to fix and my error list is empty... :/

    P.S Could you gine some similar code for SELECTING items from database? Thank youuuu so much!!!!

    Thursday, February 9, 2012 12:12 PM

Answers

  • Hi,

    the datatypes of the parameters in your function ExecuteInsert are all string, while the parameters of the command expect different types.; e.g. id_number should be integer or int64.


    Regards, Nico

    Thursday, February 9, 2012 12:48 PM
  • To add to Nico's response, below is an example of how you can parse the strings for the non-string parameter types:

    param[0].Value = Int64.Parse(id_number); param[7].Value = Int64.Parse(patient_number); param[11].Value = DateTime.Parse(date_birth); param[12].Value = DateTime.Parse(place_birth); param[13].Value = Int64.Parse(amka);


    You can similarly build a parameterized SELECT query and invoke the command methods ExecuteScalar (single row and column result of type object) or ExecuteReader (SqlDataReader result) and process as desired.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Thursday, February 9, 2012 1:20 PM
  • look in your query: there's two comma's with nothing inbetween:

    @patient_lastname, , @patient_sex,


    Regards, Nico

    Thursday, February 9, 2012 3:58 PM

All replies

  • Hi,

    the datatypes of the parameters in your function ExecuteInsert are all string, while the parameters of the command expect different types.; e.g. id_number should be integer or int64.


    Regards, Nico

    Thursday, February 9, 2012 12:48 PM
  • Hi,

    the datatypes of the parameters in your function ExecuteInsert are all string, while the parameters of the command expect different types.; e.g. id_number should be integer or int64.


    Regards, Nico

    Hi! 

    I changed it but then it shows errors.. "Cannot convert from String to Int" I also tried "double" but the same mistake.. I think it's the ConnectionString that doesn't let it.. But how could I write my code?


    Thursday, February 9, 2012 1:10 PM
  • To add to Nico's response, below is an example of how you can parse the strings for the non-string parameter types:

    param[0].Value = Int64.Parse(id_number); param[7].Value = Int64.Parse(patient_number); param[11].Value = DateTime.Parse(date_birth); param[12].Value = DateTime.Parse(place_birth); param[13].Value = Int64.Parse(amka);


    You can similarly build a parameterized SELECT query and invoke the command methods ExecuteScalar (single row and column result of type object) or ExecuteReader (SqlDataReader result) and process as desired.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Thursday, February 9, 2012 1:20 PM
  • Hi,

    can you show some code please, and the exact error message

    thank you


    Regards, Nico

    Thursday, February 9, 2012 1:34 PM
  • That's the error that is shown to me now... thnks both of you very muchhh!! I may still be confused but you were at least very helpful!

    Thursday, February 9, 2012 1:59 PM
  • look in your query: there's two comma's with nothing inbetween:

    @patient_lastname, , @patient_sex,


    Regards, Nico

    Thursday, February 9, 2012 3:58 PM