Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Posts
    5

    Unanswered: Connecting to a SQL Server 2000 database & executing contents of using Visual Basic

    I am trying to update the local database by running a txt file with several DML statements e.g. delete, update, and insert. I need to use Visual Basic to connect to the database and execute the script (or the contents of the script). How do I go about it?

    I have put together this but I need to know how to use the 'strsql' and 'cmDB.Execute' to update and execute the file. Thanks for all your help.

    ' Update the (local) xxxx Database by running "xxx_UpdateDB.txt"

    Private Sub cmdUpdateLocalxxx_Click()
    Dim cnConnection As ADODB.Connection
    Dim cmDB As ADODB.Command
    Set cnConnection = CreateObject("ADODB.Connection")
    Set cmDB = New ADODB.Command
    cnConnection.ConnectionString = "Provider=sqloledb;Data Source='(local)';Initial Catalog='xxx';User Id='xx'; password='xxxxxx"

    ' strsql = ""

    cnConnection.Open
    'Dim cnConnection As ADODB.Connection
    cmDB.CommandType = adCmdFile

    cmDB.CommandText = strsql
    cmDB.CommandType = adCmdStoredProc

    'cmDB.Execute




    End Sub

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    First, you'll need to open the text file, parse it in to separate statements, and execute the statements.

    I'm assuming VB6 here (your code looks like it.) First, ref this File I/O Tutorial at our VB sister site. In particular, take a look at reading the entire file into a single string.

    Then, use the SPLIT function to split the file contents into a dynamic array of string. If you use a delimiter like the semicolon (';') to indicate the end of each SQL statement, then use it as the delimiter for the split function too.

    Now, your dynamic array contains one element for each SQL statement. You could just copy each array element to your sql string, or for that matter, use each array element as the parameter for the connection.execute statement.

    Code:
    For N = LBound(SQL_Array) to UBound(SQL_Array)
      connection.Execute SQL_Array(N)
    Next N
    Note: Note that you should examine each SQL statement, to see if a recordset will be generated. Select statements will generate a recordset, and can use the Recordset.Open method. All other will not, so they can use the Connection.Execute method.

    You may need to further process the individual array elements to remove commented lines. If so, declare a second dynamic array of string, SPLIT on the vbNewLine character pair, Delete the contents of each element starting with a double "-", and then use the JOIN function to rebuild your string, sans comments.

    I don't know if there's a specific reason for you using late binding for your ADO connection. (there are occasionally valid reasons to follow that approach.) I normally explicitly declare, instanciate, and open database objects like connections and recordsets.

    Code:
    Dim cn as ADODB.Connection        ' Declare
    Set cn = New ADODB.Connection     ' Instanciate
    cn.Cursorlocation = adUseServer   ' Set default cursor location to Server
    cn.Open ConnectionString          ' Open the connection
    Finally. If you're unsure about working with ADO, take a look at the sister-site's ADO Tutorial.
    Last edited by loquin; 10-27-07 at 14:58.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Oct 2007
    Posts
    5

    Connecting to a SQL Server 2000 database & executing contents of using Visual Basic

    Hi Loquin

    Thank you very much indeed for your response. I am new to VB6 programming and have been asked to get this ready for tomorrow. I will go through your response to familiarize myself with opening the text file, parse it in to separate statements, split functions, arrays and using the Connection.Execute method.

    I will keep you posted.

  4. #4
    Join Date
    Oct 2007
    Posts
    5

    Connecting to a SQL Server 2000 database & executing contents of using Visual Basic

    Loquin - Is this what you're expecting. I am still having difficulties putting together the script. Please can you show me an example for deleting the contents with double (-) comments. Help please. Thanks


    *********************
    Private Sub cmdUpdateLocalxxx_Click()

    ' Update the (local) xxxx Database by running "xxx_UpdateDB.txt"

    Dim cn As ADODB.Connection ' Declare
    Set cn = New ADODB.Connection ' Instanciate
    cn.Cursorlocation = adUseServer ' Set default cursor location to Server

    cnConnection.ConnectionString = "Provider=sqloledb;Data Source='(local)';Initial Catalog='xxx';User Id='xx'; password='xxxxxx"
    cn.Open ConnectionString ' Open the connection



    Dim strSql() As String 'dynamic array of strings
    Dim strFile As String 'Single string to contain the entire file (File Buffer)
    Dim FF As Integer
    Dim strSql2() As String ' declare a second dynamic array of string


    FF = FreeFile()
    Open "c:\UpdateDB.txt" For Binary As FF

    'For a string, do this:
    strFile = Space(LOF(FF))
    Get FF, , strFile

    Close FF

    'Read this from the file
    strFile = "ALTER TABLE dbo.tblPartner ADD PartIdentityID int NULL;ALTER TABLE dbo.tblPartner ADD PartOrgTypePrimaryID int NULL;CREATE PROCEDURE [DeleteKeywordLinkByPartnerLevelWork];CREATE PROCEDURE [DeleteKeywordLinkByPartnerLevelWork];CREATE PROCEDURE [dbo].[ListStrategyPurposeKeywords];CREATE PROCEDURE [dbo].[ListStrategyPurposeKeywordsbyProject];CREATE PROCEDURE [dbo].[DeletePurposeStrategyKWLinkByProject];ALTER PROCEDURE [dbo].[ListAdvocacyKeywordsByPartner];ALTER PROCEDURE [dbo].[ListProjectKeywords];CREATE FUNCTION [dbo].[GetProjectPurposeStrategies] (@ProjectID int);delete from tblGridDefinition where gridid=1 and columntitle='Main Function';update tblGridDefinition set columnnumber=5 where gridid=1 and columntitle='Created On';insert into tblKeywordType (KWTID, KWTName) values (11,'Project Strategy Objective')"

    'Break the string by commas
    strSql = Split(strFile, ";")

    'copy each array element to your sql string, or for that matter
    'use each array element as the parameter for the connection.execute statement

    For N = LBound(strSql) To UBound(strSql)
    Connection.Execute strSql(N)
    Next N


    ' strFile contains the entire file, split on the vbNewLine character pair
    strSql2 = Split(strFile, vbNewLine)


    ' Delete the contents of each element starting with a double "-"


    'Kill()

    ' use the JOIN function to rebuild your string without comments

    strFile = Join(strSql2, vbNewLine) ' Rebuild the entire file string


    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •