Insert........ Records

[mca.mdebnath]

Hope for a fruitful reply....................

Enter a number into a textbox(like 5 or 10 or something like that.

Then that number of rows are inserted into the table ..."Master"( id,serial_no,password) .

Like if i enter 10 into that textbox and submit then inserted 10 number of rows into a table ...

Plz Help me ... i have to done it by asp.net using c#.

[mail2saion]

You can follow the below way:

//open your connection here
for(int i=0; i<Convert.ToInt(TextBox1.Text);i++)
{
// insert HERE
}
//close your connection here

[mca.mdebnath]

Thanks 4 ur fruitful Reply But it gives some error when it insert more than 1 times ..................in a table.
Code:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;

public partial class PinGeneration : System.Web.UI.Page
{
    ADOALL aa;
    private SqlConnection sconn;
    private SqlCommand scomm;
    private SqlDataReader sdr;
    MemoryStream ms = new MemoryStream();
    SqlParameter spfull_no,spserial_no,sppassword;
    string strCommText = "insert into Pin_Generate (id_no, serial_no, password) values(@pfull_no, @pserial_no, @ppassword)";
    string strCommText2 = "select last_id from Temp_ID";
    public PinGeneration()
    {
        sconn = new SqlConnection();
        scomm = new SqlCommand();

        sconn.ConnectionString = ConfigurationManager.ConnectionStrings["MLMSqlServer"].ConnectionString;
        scomm.CommandType = CommandType.Text;
        //  scomm.CommandText = strCommText1.Trim();
        scomm.CommandText = strCommText2.Trim();
        scomm.Connection = sconn;

        spfull_no = new SqlParameter();
        spfull_no.SqlDbType = SqlDbType.VarChar;
        spfull_no.ParameterName = "pfull_no";
        spfull_no.Direction = ParameterDirection.Input;

        spserial_no = new SqlParameter();
        spserial_no.SqlDbType = SqlDbType.VarChar;
        spserial_no.ParameterName = "pserial_no";
        spserial_no.Direction = ParameterDirection.Input;

        sppassword = new SqlParameter();
        sppassword.SqlDbType = SqlDbType.VarChar;
        sppassword.ParameterName = "ppassword";
        sppassword.Direction = ParameterDirection.Input;


    }

    protected void Page_Load(object sender, EventArgs e)
    {
        lbldate.Text = DateTime.Now.ToString();

        try
        {
            if (sconn.State == ConnectionState.Closed)
            {
                sconn.Open();

                sdr = scomm.ExecuteReader();
                sdr.Read();

                string a = sdr["last_id"].ToString(); 
                int b = Int32.Parse(a);
                int c = b + 1;
                lblstatement.Text = "FMS00".ToString() + b;
                Label3.Text = c.ToString();


                lblprint_id.Text = "FMS00".ToString() + c;
                sconn.Close();

            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }


    }
    private int Randam_Number(int min,int max)
    {
        Random randam = new Random();
        return randam.Next(min,max);
    }

    protected void btnsubmit_Click(object sender, EventArgs z)
    {


       Randam_Number(14,100);
       int  number = 3490219;
       lblserial_no.Text=number.ToString();
       lblpassword.Text = number.ToString();


        int i;
        for (i = 1; i <= Convert.ToInt32(txtpin_no.Text); i++)
        {
            getNum();

            spfull_no.Value = lblprint_id.Text;
            spserial_no.Value = lblserial_no.Text;
            sppassword.Value = lblpassword.Text;

             aa = new ADOALL(strCommText, spfull_no, spserial_no, sppassword);



            int x = 0;
            try
            {
                x = aa.ExecuteStatements();
            }
            catch (SqlException sqlex)
            {
                Response.Write(sqlex.ToString());
                x = 0;
            }
            if (x > 0)
            {
                lblmsg.Text = "Successfully Created";

            }
            else
            {
                lblmsg.Text = "Please Try Again";
            }
            update();

        }

       }


    private void getNum()
    {
        if (sconn.State == ConnectionState.Closed)
        {

            sconn.Open();

            scomm.CommandType = CommandType.Text;
            string strCommText2 = "select last_id from Temp_ID";
            scomm.CommandText = strCommText2.Trim();
            scomm.Connection = sconn;
            sdr = scomm.ExecuteReader();
            sdr.Read();
            string a = sdr["last_id"].ToString();
            int b = Int32.Parse(a);
            int c = b + 1;
            lblstatement.Text = "FMS00".ToString() + b;
            Label3.Text = c.ToString();
            lblprint_id.Text = "FMS00".ToString() + c;
            sconn.Close();
        }
    }


    private void update()
    {
        try
        {
            if (sconn.State == ConnectionState.Closed)
            {
                sconn.Open();
                scomm.CommandText = "UPDATE Temp_ID SET last_id='" + Label3.Text + "'";  // WHERE last_id=@last_id";


                scomm.Connection = sconn;
                scomm.ExecuteNonQuery();

                sconn.Close();
            }
        }
        catch (SqlException e)
        {
            Response.Write(e.ToString());
        }
    }



    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
    protected void lblogout_Click(object sender, EventArgs e)
    {
        FormsAuthentication.SignOut();
        Response.Redirect("super_admin_login");
    }



}

ADOALL CLASS:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ADOALL
/// </summary>
public class ADOALL
{
    private SqlConnection sconn;
    private SqlCommand scomm;
    private SqlDataReader sdr;

    public ADOALL(string strCommText, params SqlParameter[] spCollection)
    {
        //
        // TODO: Add constructor logic here
        //
        sconn = new SqlConnection();
        scomm = new SqlCommand();

        sconn.ConnectionString = ConfigurationManager.ConnectionStrings["MLMSqlServer"].ConnectionString;
        scomm.CommandType = CommandType.Text;
        scomm.CommandText = strCommText.Trim();

        scomm.Connection = sconn;
       scomm.Parameters.AddRange(spCollection); // Error occurs here... It   



    }
    public bool ConnOpen()
    {
        if (sconn.State == ConnectionState.Closed)
        {
            try
            {
                sconn.Open();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        else
        {
            return true;
        }
    }

    public bool ConnClose()
    {
        if (sconn.State == ConnectionState.Open)
        {
            try
            {
                sconn.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        else
        {
            return true;
        }
    }

    public bool GetReader()
    {
        if (ConnOpen())
        {
            sdr = scomm.ExecuteReader();
            if (sdr.Read())
            {
                ConnClose();
                return true;
            }
            else
            {
                ConnClose();
                return false;
            }
        }
        else
        {
            return false;
        }

    }

    public int ExecuteStatements()
    {
        if (ConnOpen())
        {
            int x = scomm.ExecuteNonQuery();
            ConnClose();
            return x;
        }
        return 0;
    }


    public SqlDataReader getSqlReader()
    {
        if (ConnOpen())
        {
            sdr = scomm.ExecuteReader(CommandBehavior.CloseConnection);
            return sdr;
        }
        else
        {
            return null;
        }
    }
    public object getSqlScaler()
    {
        if (ConnOpen())
        {
            object ser = scomm.ExecuteScalar();
            sconn.Close();
            return ser;

        }
        else
        {
            return null;
        }
    }


}

Plz Sir, Give me a Solution... [:)]

[mail2saion]

Its a bit time consuming to read your code. But i can give you one tricks & hope it will resolve your problem.

Wrap all insert related code under loop. I mean within the loop open connection execute statement then close connection. So that for 2nd iteration connection will open again then execute & close so on.

If you can can insert multiple statement in this way then you can optimize your code later.