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\B RANCH00\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("h ibdb"))
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
