Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
| Search | Today's Posts | Mark Forums Read
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 12th, 2005, 12:12 PM
Authorized User
Join Date: Apr 2005
Location: , , .
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need to connect to SQL DB in VB app

I'm trying to create this SIMPLE VB application that essentially opens an excel file and row by row it sends pieces of the data to a SQL database to execute a stored proc. But its not working . I can loop through the data but once it gets to the adoConn.open it exits the function.

Here's my code. Can anyone see what I'm doing wrong or perhaps missing? Do you need any additional info?

Dim FileToOpen As String

Private Sub Form_Load()

    Dim MyXL As Excel.Application 'create MyXL
    Set MyXL = New Excel.Application
    Dim WkbkName As String

    On Error Resume Next
        Set MyXL = GetObject(, "Excel.Application")

        If Err.Number = 429 Then
            Set appExcel = CreateObject("Excel.Application")
        End If

    FileToOpen = Application.GetOpenFilename 'Opens up explorer

    If FileToOpen = "False" Or FileToOpen = "" Then
        MsgBox "Ensure file is chosen correctly"
        Workbooks.Open FileName:=FileToOpen 'Opens up the file selected
        'Places the portion of the filename I want into a string variable
        WkbkName = Mid$(FileToOpen, InStr(FileToOpen, "_") + 1, InStr(FileToOpen, "[") - InStr(FileToOpen, "_") - 1)
        Application.Visible = True 'in Excel
    End If

    'Re-Saves the workbook using the new filename (withOUT the characters that Excel doesn't accept)
    Application.ActiveWorkbook.SaveAs " " + WkbkName + " "

    Call CrossingFiles 'Calls function

    Application.DisplayAlerts = False

    Set MyXL = Nothing

    Unload OpenFile

End Sub

Sub CrossingFiles()

    Dim wsData As Worksheet
    Dim fund, trans_type, security_id, shares As String
    Dim strSQL As String
    Dim adoConn As ADODB.Connection
    Set adoConn = New ADODB.Connection
    Set wsData = ActiveSheet

    'This will use the new worksheet as the 'trading ground' and will create all of the values
    'which will then be copied over to the original D column
    Dim MyColumns_Range As Range
    Set MyColumns_Range = Range(wsData.Cells(1, "A"), wsData.Cells(1, "A").End(xlDown))
    For Each c In MyColumns_Range
        fund = Range("A1").Value
        trans_type = Range("B1").Value
        security_id = Range("C1").Value
        shares = Range("E1").Value

        strSQL = "EXECUTE ssga_import_residuals " & fund & ", " & trans_type & ", " & security_id & ", " & shares & ", ,"

        With adoConn
            .ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=user;Data Source=odbcuser;Initial Catalog=main"
            .Execute strSQL, , adCmdText

        End With

    fund = " "
    trans_type = " "
    security_id = " "
    shares = " "
    Next c

    Set wsData = Nothing

    'deletes the newly created worksheet and sets everything to original status
    'yet with new data
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True

End Sub
Old April 12th, 2005, 03:49 PM
Friend of Wrox
Join Date: Jun 2003
Location: Alameda, ca, USA.
Posts: 627
Thanks: 0
Thanked 0 Times in 0 Posts

The problem is that you have a 'on error resume next' in the caller method (form_load). When ado.open fails, the program flow returns to the caller.
Put a 'on error goto errhandler' in CrossingFiles, and print the error in errhandler, so you know what is going on. As an alternative, Tools->Options->General tab->Break on all errors. Or stop the debugger in the .open line, step by F10 and get the error in the Immediate window.

Old April 13th, 2005, 11:17 AM
Authorized User
Join Date: Apr 2005
Location: , , .
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts

Ok it does make sense now. I changed my code a bit to display the error description. I also took the password out of my connection string so that it would used trusted connection (using my ODBC). I am now connecting. However my new problem deals with the string I am passing to the SQL server.

This is my sql string:

strSQL = "EXECUTE ssga_import_residuals " & fund & ", '" & trans_type & "', '" & security_id & "', " & shares & ", ,"

To pass it:

adoConn.Execute strSQL, , adCmdText

Yet I am getting an error:
"Incorrect syntax near ','"

Any ideas?
Old April 21st, 2005, 12:03 AM
Authorized User
Join Date: Mar 2005
Location: Pune, Maharashtra, India.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to dadahonde Send a message via Yahoo to dadahonde

I have gone through your code. The One thing I found in your code is you need to check the Provider value in the connection string.

For your reference the connection string in your code is as follows.

.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=user;Data Source=odbcuser;Initial Catalog=main"
Actually the syntax of connection string is right but
SomeTimes The DOT (.) you are using within strings might be creating problems as in VB it is teated as very special char, go through the provider details first. Then you can try with string handling functions.

Similar Threads
Thread Thread Starter Forum Replies Last Post
I can't connect to the sql 2005 developer DB gulfpaddler BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 16 August 21st, 2008 04:17 AM
How to connect to Access db thru VB.NET? am_kuthus Visual Studio 2008 1 June 3rd, 2008 04:48 AM
HELP: trying to connect to SQL DB Newbie19 VB Databases Basics 1 June 21st, 2007 05:34 PM
connect to sql db jopay Visual C++ 0 January 12th, 2005 05:17 PM
how i can connect to remote sql server db yoord ADO.NET 16 June 16th, 2004 01:40 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.