cannot make insert into database

zzchixo

SOLVED CLOSED ACTIVE ANSWERED
User: zzchixo
Posted: on Aug 11, 2014 09:30 AM
Forum: Data Controls
Answer: 1
Views: 4269

Hi Azim,

I am trying to input some data into the database and it just won't make INSERT. Before, I am checking for duplicate entry and then I am trying to insert the data. Please advise on what I am doing wrong. Here is the code:

In the end I have the warning that con.Close() - unreacheable code detected.

Thank you.

 protected void Button1_Click(object sender, EventArgs e)
    {
        bool UserExist;
        UserExist = this.UserExit();

        if (UserExist == false)
        {

            SqlConnection conn;
            SqlCommand comm;
            string connectionString =
            ConfigurationManager.ConnectionStrings["CompanyInfoEventsConnectionString"].ConnectionString;
            conn = new SqlConnection(connectionString);
            comm = new SqlCommand(
            "INSERT INTO EmployeeCity (Name,Position, " +
                   "Cell, Email, Extention, WhyWork, Hobby, FileLink, FotoLink) " +
                   "VALUES (@Name, @Position, @Cell, " +
                   "@Email, @Extention, @WhyWork,@Hobby,@FileLink, @FotoLink", conn);

            comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 200);
            comm.Parameters["@Name"].Value = cmbName.Text;

            comm.Parameters.Add("@Position", System.Data.SqlDbType.NVarChar, 300);
            comm.Parameters["@Position"].Value = cmbPosition.Text;

            comm.Parameters.Add("@Cell", System.Data.SqlDbType.NVarChar, 150);
            comm.Parameters["@Cell"].Value = cmbMobile.Text;

            comm.Parameters.Add("@Email", System.Data.SqlDbType.NVarChar, 150);
            comm.Parameters["@Email"].Value = cmbPosition.Text;

            comm.Parameters.Add("@Email", System.Data.SqlDbType.NVarChar, 150);
            comm.Parameters["@Email"].Value = cmbEmail.SelectedItem.Text;

            comm.Parameters.Add("@Extention", System.Data.SqlDbType.Int);
            comm.Parameters["@Extention"].Value = cmbExtention.SelectedItem.Value;


            comm.Parameters.Add("@WhyWork", System.Data.SqlDbType.NVarChar, 2000);
            comm.Parameters["@WhyWork"].Value = txtWhyWork.Text.Trim();

            comm.Parameters.Add("@txtHobby", System.Data.SqlDbType.NVarChar, 1500);
            comm.Parameters["@txtHobby"].Value = txtHobby.Text.Trim();

            comm.Parameters.Add("@FileLink", System.Data.SqlDbType.NVarChar, 500);
            comm.Parameters["@FileLink"].Value = hdnName.Value;

            comm.Parameters.Add("@FotoLink", System.Data.SqlDbType.NVarChar, 500);
            comm.Parameters["@FotoLink"].Value = hndFoto.Value;

            try
            {
                conn.Open();
                Response.Redirect("UploadInfo.aspx");
            }

            catch { 
            
            
            } 

            finally
            {
                conn.Close();
            }
        }

        else
        {
            lblMessage.Text = "Duplicate Entry Detected!!!";
        }
    }



    private bool UserExit()
    {
        string strConnString = ConfigurationManager.ConnectionStrings["CompanyInfoEventsConnectionString"].ConnectionString;
        SqlConnection con = new SqlConnection(strConnString);

        SqlCommand cmd = new SqlCommand("Select * From EmployeeCity Where Email =@Email ");
        cmd.Parameters.AddWithValue("@Email", cmbEmail.Text);

        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        con.Open();

        SqlDataReader rdr;
        rdr = cmd.ExecuteReader();
        if (rdr.HasRows)
        {
            return true;
        }
        else
        {
            return false;
        }
        con.Close();
    }
}

zzchixo

0 2

Reply
User: zzchixo
Replied: on Aug 11, 2014 09:37 AM
Report

it gives this error:

The variable name '@Email' has already been declared. Variable names must be unique within a query batch or stored procedure. Must declare the scalar variable "@Extention".

Azim

0 3

Reply
ANSWER
User: Azim
Replied: on Aug 11, 2014 09:45 AM
Modified: on Aug 11, 2014 10:03 AM
Report

You have not used comm.ExceuteNonQuery to insert the values and you are returning the value before con.Close() in UserExist finction

Please refer this code.

protected void Button1_Click(object sender, EventArgs e)
{
    bool UserExist;
    UserExist = this.UserExit();
    string query = "INSERT INTO EmployeeCity (Name,Position, " +
                "Cell, Email, Extention, WhyWork, Hobby, FileLink, FotoLink) " +
                "VALUES (@Name, @Position, @Cell, " +
                "@Email, @Extention, @WhyWork,@Hobby,@FileLink, @FotoLink)";
    if (UserExist == false)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["CompanyInfoEventsConnectionString"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand comm = new SqlCommand(query, conn))
            {
                comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 200);
                comm.Parameters["@Name"].Value = cmbName.Text;
                comm.Parameters.Add("@Position", System.Data.SqlDbType.NVarChar, 300);
                comm.Parameters["@Position"].Value = cmbPosition.Text;

                comm.Parameters.Add("@Cell", System.Data.SqlDbType.NVarChar, 150);
                comm.Parameters["@Cell"].Value = cmbMobile.Text;

                comm.Parameters.Add("@Email", System.Data.SqlDbType.NVarChar, 150);
                comm.Parameters["@Email"].Value = cmbPosition.Text;                

                comm.Parameters.Add("@Extention", System.Data.SqlDbType.Int);
                comm.Parameters["@Extention"].Value = cmbExtention.SelectedItem.Value;


                comm.Parameters.Add("@WhyWork", System.Data.SqlDbType.NVarChar, 2000);
                comm.Parameters["@WhyWork"].Value = txtWhyWork.Text.Trim();

                comm.Parameters.Add("@Hobby", System.Data.SqlDbType.NVarChar, 1500);
                comm.Parameters["@Hobby"].Value = txtHobby.Text.Trim();

                comm.Parameters.Add("@FileLink", System.Data.SqlDbType.NVarChar, 500);
                comm.Parameters["@FileLink"].Value = hdnName.Value;

                comm.Parameters.Add("@FotoLink", System.Data.SqlDbType.NVarChar, 500);
                comm.Parameters["@FotoLink"].Value = hndFoto.Value;

                conn.Open();
                //Response.Redirect("UploadInfo.aspx");
                comm.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

    else
    {
        lblMessage.Text = "Duplicate Entry Detected!!!";
    }
}
private bool UserExit()
{
    string strConnString = ConfigurationManager.ConnectionStrings["CompanyInfoEventsConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand("Select * From EmployeeCity Where Email =@Email "))
        {
            cmd.Parameters.AddWithValue("@Email", cmbEmail.Text);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            SqlDataReader rdr;
            rdr = cmd.ExecuteReader();
            bool isAvailable = false;
            if (rdr.HasRows)
            {
                isAvailable = true;
            }
            else
            {
                isAvailable = false;
            }
            con.Close();
            return isAvailable;
        }
    }
}

zzchixo

0 4

Reply
User: zzchixo
Replied: on Aug 11, 2014 09:53 AM
Report

gives error:

Azim

0 5

Reply
User: Azim
Replied: on Aug 11, 2014 09:59 AM
Report

Add this in the code

comm.Parameters.Add("@Hobby", System.Data.SqlDbType.NVarChar, 1500);
comm.Parameters["@Hobby"].Value = txtHobby.Text.Trim();

zzchixo

0 6

Reply
User: zzchixo
Replied: on Aug 11, 2014 10:11 AM
Report

Super thank so much!!!!

cannot make insert into database

The variable name '@Email' has already been declared. Variable names must be unique within a query batch or stored procedure. Must declare the scalar variable "@Extention".

Company

Explore

Follow us

Contact Us