Best way to Insert multiple records into a table?

Discussion in 'ASP .Net' started by Ian Macey, Jan 25, 2005.

  1. Ian Macey

    Ian Macey Guest

    I am wanting to Insert multiple records into a table, and although I have some working code I feel that there must be a better way. The purpose of the code is to do a directory listing of a sub-directory and for each file listed insert a record into a temporary table.

    Can anyone help?

    My code is as follows....

    SQL Stored Procedure
    dbo.sp_bmwrInsSpool @username VarChar(8), @spoolName VarChar(12), @spoolDate VarChar(6) AS

    DECLARE @uid Int

    SET rowcount 1
    SELECT @uid=userid.uid
    FROM userid
    WHERE username = @username
    SET rowcount 0

    INSERT INTO bmwrTmp (uid, spoolName, spoolDate)
    VALUES (@uid, @spoolName, @spoolDate)

    RETURN

    ASP.NET Code (VB.NET)
    Sub GetSpoolFileListing
    Dim FilSysObj = Server.CreateObject("Scripting.FileSystemObject")
    Dim Folder = FilSysObj.GetFolder("\\huksrv03\bmliv$\bmpc\DATA\BRANCH00\Spool")
    Dim FolderContents = Folder.Files
    Dim File

    For Each File in FolderContents
    InsertSpoolFile(File.Name)
    Next
    End Sub

    Sub InsertSpoolFile(sFileName As String)
    Dim SqlConn As New SqlConnection(ConfigurationSettings.AppSettings("hibdb"))
    Dim CmdTxt As String = "sp_bmwrInsSpool"
    Dim SqlCmd As New SqlCommand(CmdTxt, SqlConn)
    Dim rowsAffected As Integer = 0
    sqlCmd.CommandType = CommandType.StoredProcedure

    SqlCmd.Parameters.Add(New SQLParameter("@username", Context.User.Identity.Name))
    SqlCmd.Parameters.Add(New SQLParameter("@spoolName", sFileName))
    SqlCmd.Parameters.Add(New SQLParameter("@spoolDate", ""))

    SqlConn.Open
    Try
    rowsaffected=SqlCmd.ExecuteNonQuery
    Catch
    ' lblStatus.Text = "Insert not successful ! Possible dupilcate reference."
    Finally
    SqlConn.Close
    End Try
    End Sub
    Ian Macey, Jan 25, 2005
    #1
    1. Advertising

  2. It looks good to me. There are ways to insert multiple rows with one stored
    procedure call but they are hacks and kludges.
    =?Utf-8?B?U2NvdHQgU2ltb25z?=, Jan 25, 2005
    #2
    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. Andrew Bourne

    Insert records into database based on query

    Andrew Bourne, Oct 17, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    516
    Andrew Bourne
    Oct 19, 2003
  2. Mike Hnatt
    Replies:
    4
    Views:
    428
    Steve C. Orr [MVP, MCSD]
    Nov 3, 2003
  3. Replies:
    1
    Views:
    1,781
    Albert Hopkins
    Dec 6, 2008
  4. Sans Spam
    Replies:
    8
    Views:
    248
    Sans Spam
    May 24, 2004
  5. Roy Adams

    insert multiple records to table

    Roy Adams, May 26, 2004, in forum: ASP General
    Replies:
    3
    Views:
    194
    Bob Barrows [MVP]
    May 26, 2004
Loading...

Share This Page