Vb to T-SQL conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Vb to T-SQL conversion

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-15 : 08:48:39

hassan writes "Hi I have the Following code in VB, it uses FSO , goes and read a folder and insert all info into a table. I need to know if I can write a Stored Proc to do same function for me? Also if I could where should I start?
Thanks

Option Compare Database
Option Explicit

Public Const strServerPath As String = "\\Atlas\CAPA\"
Public fso As New FileSystemObject
Public dblCountFolder As Double, dblCountFile As Double
Public Const conAppName = "CAPA DB"
Public strLastErrorWsUpdateDynamicLink As String


Public Function wsUpdateDynamicLink() As Boolean
    On Error Resume Next
    
    Dim db As Database
    Dim rsDynamicLink As Recordset
    Dim f As Folder
    Dim sf As Folder, sf2 As Folder
    Dim fl As File
    
    Dim lngFolder As Long
    lngFolder = 0
    strLastErrorWsUpdateDynamicLink = ""
    
    Set db = CurrentDb()
    Set rsDynamicLink = db.OpenRecordset("CPA_tdta_LinkDynamic", dbOpenDynaset, dbSeeChanges)
    
    If Not fso.FolderExists(strServerPath) Then
        MsgBox "ServerPath not found (" & strServerPath & ")", vbCritical, conAppName
        wsUpdateDynamicLink = False
        Exit Function
    End If
    
    Set f = fso.GetFolder(strServerPath)
    dblCountFolder = 0
    dblCountFile = 0
    
    DoCmd.RunSQL ("DELETE * FROM CPA_tdta_LinkDynamic")
    
    With rsDynamicLink
        fso.CreateTextFile(f.Path & "\capa.ini", True).WriteLine ("Last scanned @ " & Now() & " - SMSOCSG, e.Solutions")
        For Each fl In f.Files
                dblCountFile = dblCountFile + 1
                .AddNew
                !dt_FolderLevel = 1
                !dt_LinkDir = fl.ParentFolder
                !dt_LinkDirOnly = wsGetBaseName(fl.ParentFolder)
                !dt_FolderId = "0"
                !dt_FolderParentId = "foldersTree"
                !dt_LinkFile = fl.Name
                !dt_LinkPath = fl.Path
                !dt_LinkType = fl.Type
                !dt_LinkDateCreated = fl.DateCreated
                !dt_LinkDateModified = fl.DateLastModified
                !dt_LinkDateLastAccessed = fl.DateLastAccessed
                !dt_LinkFileSize = fl.Size
                !dt_LinkDescription = "n.a."
                .Update
        Next fl
        
        For Each sf In f.SubFolders
            lngFolder = lngFolder + 1
            dblCountFolder = dblCountFolder + 1
            fso.CreateTextFile(sf.Path & "\capa.ini", True).WriteLine ("Last scanned @ " & Now() & " - SMSOCSG, e.Solutions")
            For Each fl In sf.Files
                dblCountFile = dblCountFile + 1
                .AddNew
                !dt_FolderLevel = 2
                !dt_LinkDir = fl.ParentFolder
                !dt_LinkDirOnly = wsGetBaseName(fl.ParentFolder)
                !dt_FolderId = "sf" & lngFolder
                !dt_FolderParentId = "foldersTree"
                !dt_LinkFile = fl.Name
                !dt_LinkPath = fl.Path
                !dt_LinkType = fl.Type
                !dt_LinkDateCreated = fl.DateCreated
                !dt_LinkDateModified = fl.DateLastModified
                !dt_LinkDateLastAccessed = fl.DateLastAccessed
                !dt_LinkFileSize = fl.Size
                !dt_LinkDescription = "n.a."
                .Update
            Next fl
                
            For Each sf2 In sf.SubFolders
                dblCountFolder = dblCountFolder + 1
                fso.CreateTextFile(sf2.Path & "\capa.ini", True).WriteLine ("Last scanned @ " & Now() & " - SMSOCSG, e.Solutions")
                For Each fl In sf2.Files
                    dblCountFile = dblCountFile + 1
                    .AddNew
                    !dt_FolderLevel = 3
                    !dt_LinkDir = fl.ParentFolder
                    !dt_LinkD

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-15 : 16:57:50

In general

, the answer is yes, you can write a stored procedure to insert data from an external file into a SQL Server table. The place to start is to research the topics BULK INSERT and BCP. BCP is a command-line utility which can be called from within SQL or from the outside.

Depending on your needs to validate the data being inserted, or to modify it, you may need to do some pre- or post-processing. For example, you could write a VB app to pre-process the file and check for data errors. Then you could BCP the data into a holding table in SQL Server and then do some post-processing with lookups to other tables to get the data into its final storage places.

------------------------
GENERAL-ly speaking...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources