multiple record inserts

Discussion in 'ASP .Net' started by Jim, Jan 18, 2008.

  1. Jim

    Jim Guest

    Using aspx and vb with sql2005 server.
    I have an insert form/query that will insert a record. What I want to do is
    on the page have a dropdown list with numbers. From the dropdown list, the
    user will select how many records to insert the date (each insert is a
    seperate record).

    For example, if a user has 20 parts to enter, instead of entering the same
    information 20 times, they can select the number 20 from the dropdown list
    and then fillout the information in the form and when the "Insert" button is
    clicked, the sql insert query will run 20 times.

    Is this possible? If so, can you point me to a place that either has an
    example or talks about it?

    Thanks.
     
    Jim, Jan 18, 2008
    #1
    1. Advertising

  2. Jim

    sloan Guest

    One idea.

    Create a strong dataset.

    EmployeeDS

    with 1 table.

    Employee (table)

    with some columns

    EmployeeID, LastName, FirstName
    .............

    After your user selects "I want to insert 10 new employees"....
    on the code behind, create a "dummy" EmployeeDS, and then bind your GridView
    or Repeater to it.


    dim ds as EmployeeDS = new EmployeeDS
    --excuse any syntax errors with my vb.net skillzz
    For i as int32 = 0 to 9

    EmployeeDS.EmployeeRow newRow = ds.Employee.NewEmployeeRow

    newRow.EmpID = i '' obviously this won't be the actual empid, but
    will give you some kind of uniqueidentifier until you ship it to the
    database

    ds.Employee.AddNewEmployeeRow = newRow

    next i

    Then bind your GridView to
    gv1.DataSource = ds.Employee
    gv1.DataBind


    That'll give you the entries you need.


    THEN!!

    You can dual use the EmployeeDS....
    http://support.microsoft.com/kb/315968

    and insert all 10 new employees into the db in a single shot.

    Sweet!






    "Jim" <> wrote in message
    news:%234jda$...
    > Using aspx and vb with sql2005 server.
    > I have an insert form/query that will insert a record. What I want to do
    > is on the page have a dropdown list with numbers. From the dropdown list,
    > the user will select how many records to insert the date (each insert is a
    > seperate record).
    >
    > For example, if a user has 20 parts to enter, instead of entering the same
    > information 20 times, they can select the number 20 from the dropdown list
    > and then fillout the information in the form and when the "Insert" button
    > is clicked, the sql insert query will run 20 times.
    >
    > Is this possible? If so, can you point me to a place that either has an
    > example or talks about it?
    >
    > Thanks.
    >
     
    sloan, Jan 18, 2008
    #2
    1. Advertising

  3. Jim

    Jim Guest

    Thanks for the reply. If I understand your message, this would make a form
    appear with 10 rows in a gridview that I would fillout 10 different
    firstnames and lastnames then click "insert" and it would put all 10
    recoreds in.

    What I'm actually after is inserting identical data 10 times. Basically have
    the follwing query run 10 times over.
    InsertCommand="INSERT INTO [1TestPart] ([part_name]) VALUES (@part_name)">

    -- start database table --
    CREATE TABLE [dbo].[1TestPart](
    [part_id] [int] IDENTITY(1,1) NOT NULL,
    [part_name] [nvarchar](50) NULL,
    [part_serial] [int] NULL,
    CONSTRAINT [PK_1TestPart] PRIMARY KEY CLUSTERED
    (
    [part_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
    OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    -- end database table --


    Part Name:
    "sloan" <> wrote in message
    news:%...
    >
    >
    > One idea.
    >
    > Create a strong dataset.
    >
    > EmployeeDS
    >
    > with 1 table.
    >
    > Employee (table)
    >
    > with some columns
    >
    > EmployeeID, LastName, FirstName
    > ............
    >
    > After your user selects "I want to insert 10 new employees"....
    > on the code behind, create a "dummy" EmployeeDS, and then bind your
    > GridView or Repeater to it.
    >
    >
    > dim ds as EmployeeDS = new EmployeeDS
    > --excuse any syntax errors with my vb.net skillzz
    > For i as int32 = 0 to 9
    >
    > EmployeeDS.EmployeeRow newRow = ds.Employee.NewEmployeeRow
    >
    > newRow.EmpID = i '' obviously this won't be the actual empid, but
    > will give you some kind of uniqueidentifier until you ship it to the
    > database
    >
    > ds.Employee.AddNewEmployeeRow = newRow
    >
    > next i
    >
    > Then bind your GridView to
    > gv1.DataSource = ds.Employee
    > gv1.DataBind
    >
    >
    > That'll give you the entries you need.
    >
    >
    > THEN!!
    >
    > You can dual use the EmployeeDS....
    > http://support.microsoft.com/kb/315968
    >
    > and insert all 10 new employees into the db in a single shot.
    >
    > Sweet!
    >
    >
    >
    >
    >
    >
    > "Jim" <> wrote in message
    > news:%234jda$...
    >> Using aspx and vb with sql2005 server.
    >> I have an insert form/query that will insert a record. What I want to do
    >> is on the page have a dropdown list with numbers. From the dropdown list,
    >> the user will select how many records to insert the date (each insert is
    >> a seperate record).
    >>
    >> For example, if a user has 20 parts to enter, instead of entering the
    >> same information 20 times, they can select the number 20 from the
    >> dropdown list and then fillout the information in the form and when the
    >> "Insert" button is clicked, the sql insert query will run 20 times.
    >>
    >> Is this possible? If so, can you point me to a place that either has an
    >> example or talks about it?
    >>
    >> Thanks.
    >>

    >
    >
     
    Jim, Jan 18, 2008
    #3
  4. Jim

    sloan Guest

    Then just deviate my example to suit your needs.

    When the user clicks 'Go', then create the strong ds, instert X records, and
    then ship it off.

    If you do the BULK INSERT, you'll save time. Though its slightly harder to
    code.

    Or you can run the same usp or inline-sql statement 10 times.

    Either way.

    BULK INSERT method gives you the advantage of a "all or nothing" ... without
    coding up transactions in DotNet.

    See my cleaned up and nicer version of the MS KB here:
    http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/0bd13f38f82bec77





    "Jim" <> wrote in message
    news:eIFLQ$...
    > Thanks for the reply. If I understand your message, this would make a form
    > appear with 10 rows in a gridview that I would fillout 10 different
    > firstnames and lastnames then click "insert" and it would put all 10
    > recoreds in.
    >
    > What I'm actually after is inserting identical data 10 times. Basically
    > have the follwing query run 10 times over.
    > InsertCommand="INSERT INTO [1TestPart] ([part_name]) VALUES (@part_name)">
    >
    > -- start database table --
    > CREATE TABLE [dbo].[1TestPart](
    > [part_id] [int] IDENTITY(1,1) NOT NULL,
    > [part_name] [nvarchar](50) NULL,
    > [part_serial] [int] NULL,
    > CONSTRAINT [PK_1TestPart] PRIMARY KEY CLUSTERED
    > (
    > [part_id] ASC
    > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
    > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    > ) ON [PRIMARY]
    > -- end database table --
    >
    >
    > Part Name:
    > "sloan" <> wrote in message
    > news:%...
    >>
    >>
    >> One idea.
    >>
    >> Create a strong dataset.
    >>
    >> EmployeeDS
    >>
    >> with 1 table.
    >>
    >> Employee (table)
    >>
    >> with some columns
    >>
    >> EmployeeID, LastName, FirstName
    >> ............
    >>
    >> After your user selects "I want to insert 10 new employees"....
    >> on the code behind, create a "dummy" EmployeeDS, and then bind your
    >> GridView or Repeater to it.
    >>
    >>
    >> dim ds as EmployeeDS = new EmployeeDS
    >> --excuse any syntax errors with my vb.net skillzz
    >> For i as int32 = 0 to 9
    >>
    >> EmployeeDS.EmployeeRow newRow = ds.Employee.NewEmployeeRow
    >>
    >> newRow.EmpID = i '' obviously this won't be the actual empid, but
    >> will give you some kind of uniqueidentifier until you ship it to the
    >> database
    >>
    >> ds.Employee.AddNewEmployeeRow = newRow
    >>
    >> next i
    >>
    >> Then bind your GridView to
    >> gv1.DataSource = ds.Employee
    >> gv1.DataBind
    >>
    >>
    >> That'll give you the entries you need.
    >>
    >>
    >> THEN!!
    >>
    >> You can dual use the EmployeeDS....
    >> http://support.microsoft.com/kb/315968
    >>
    >> and insert all 10 new employees into the db in a single shot.
    >>
    >> Sweet!
    >>
    >>
    >>
    >>
    >>
    >>
    >> "Jim" <> wrote in message
    >> news:%234jda$...
    >>> Using aspx and vb with sql2005 server.
    >>> I have an insert form/query that will insert a record. What I want to
    >>> do is on the page have a dropdown list with numbers. From the dropdown
    >>> list, the user will select how many records to insert the date (each
    >>> insert is a seperate record).
    >>>
    >>> For example, if a user has 20 parts to enter, instead of entering the
    >>> same information 20 times, they can select the number 20 from the
    >>> dropdown list and then fillout the information in the form and when the
    >>> "Insert" button is clicked, the sql insert query will run 20 times.
    >>>
    >>> Is this possible? If so, can you point me to a place that either has an
    >>> example or talks about it?
    >>>
    >>> Thanks.
    >>>

    >>
    >>

    >
    >
     
    sloan, Jan 18, 2008
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?SnVzdGlu?=

    Retrieving Record Key while creating the record.

    =?Utf-8?B?SnVzdGlu?=, Oct 4, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    648
    =?Utf-8?B?SnVzdGlu?=
    Oct 5, 2004
  2. André
    Replies:
    0
    Views:
    3,761
    André
    Jun 25, 2006
  3. Steve C. Orr [MCSD, MVP, CSM, ASP Insider]

    Re: Multiple inserts question

    Steve C. Orr [MCSD, MVP, CSM, ASP Insider], Jan 22, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    339
    Steve C. Orr [MCSD, MVP, CSM, ASP Insider]
    Jan 22, 2007
  4. MRW
    Replies:
    0
    Views:
    391
  5. Tomasz Chmielewski

    DBI:mysql how to do multiple inserts/modification?

    Tomasz Chmielewski, Feb 13, 2008, in forum: Perl Misc
    Replies:
    3
    Views:
    126
    Tomasz Chmielewski
    Feb 13, 2008
Loading...

Share This Page