Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
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 15th, 2005, 08:16 AM
Authorized User
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default VB Error: Syntax Error or Access Violation

I am getting the Syntax Error or Access Violation when I attempt to run my program. I have an idea as to what the problem is but can't figure out how to fix it.

I am using the command object to execute a stored procedure that has 4 input parameters and 2 output parameters.

Here is my code:

    Call CrossingFiles 'Calls function

    Application.DisplayAlerts = False

    Set MyXL = Nothing

    Unload OpenFile

ErrorHandler: ' Error-handling routine.
Dim StrErr As String
StrErr = Err.Number & " - " & Err.Description
    If Err = 364 Then
      Exit Sub
    End If
    MsgBox (StrErr), vbOKOnly, Error

End Sub

Sub CrossingFiles()

    Dim wsData As Worksheet
    Dim fund As String
    Dim trans_type As String
    Dim security_id As String
    Dim shares As String
    Dim strRangeA As String
    Dim strRangeB As String
    Dim strRangeC As String
    Dim strRangeE As String
    Dim prmfund As ADODB.Parameter
    Dim prmtrans As ADODB.Parameter
    Dim prmsec As ADODB.Parameter
    Dim prmshare As ADODB.Parameter
    Dim prmFlg As ADODB.Parameter
    Dim prmErr As ADODB.Parameter
    Dim strSQL As String
    Dim strDesc As String
    Dim cmd As Command
    Dim i As Integer
    Dim adoConn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim txtLog As String
    Dim flag As String
    Dim desc As String
    Set wsData = ActiveSheet
    Set adoConn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command
    i = 1
    txtLog = " "
    flag = " "
    desc = " "


    adoConn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=ODBCsrc;Initial Catalog=main"

    'This will go through the worksheet, row by row, and send the necessary data to the DB
    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
        strRangeA = "A" & i
        strRangeB = "B" & i
        strRangeC = "C" & i
        strRangeE = "E" & i

        fund = Range(strRangeA).Value
        trans_type = Range(strRangeB).Value
        security_id = Range(strRangeC).Value
        shares = Range(strRangeE).Value

        Set cmd.ActiveConnection = adoConn
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "Execute stored_proc '" & fund & "', '" & trans_type & "', '" & security_id & "', '" & shares & "', "

        'Declare the output parameters
        Set prmFlg = cmd.CreateParameter("flg", adVarChar, adParamOutput, 1, "")
        cmd.Parameters.Append prmFlg

        Set prmErr = cmd.CreateParameter("Err", adVarChar, adParamOutput, 255, "")
        cmd.Parameters.Append prmErr
        'adoConn.Execute strSQL, , adCmdText

        'rst.Open strSQL, adoConn, adOpenStatic, adLockReadOnly
        Set rst = cmd.Execute
        'strDesc = rst.Fields("flag") & " " & rst.Fields("desc")
        strDesc = prmFlg.Value + " " + prmErr.Value
        txtLog = txtLog + strDesc

        i = i + 1

        fund = " "
        trans_type = " "
        security_id = " "
        shares = " "
        strDesc = " "
        cmd.CommandText = " "
        Set prmFlg = Nothing
        Set prmErr = Nothing
    Next c

    Set wsData = Nothing

    'saves any changes made to the workbook and turns off the prompts ('are you sure')
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True

End Sub

I have a watch set for the cmd.CommandText to see what it is set for (before I get the error). This is what it is set for:

"{ call Execute stored_proc '520150472', 'BUY', '36144810', '700', (?, ?) }"

Note the (?,?). Why are there parantheses and why are there question marks? Even when I type this into SQL I get the same error. Why? What can I do to fix it?
Old April 15th, 2005, 12:50 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 627
Thanks: 0
Thanked 0 Times in 0 Posts

You have
       strRangeA = "A" & i
        strRangeB = "B" & i
        strRangeC = "C" & i
        strRangeE = "E" & i

why are you skipping "D"? (?,?) means that the range is not valid
Old April 15th, 2005, 12:54 PM
Authorized User
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts

"D" is the D column in the excel spreadsheet that I am reading the data from.
Before I introduced the command object, this worked (with the skipping of the "D"). As I step through my code, I can see that each variable populates, it just doesn't run. Even when I take the

call Execute stored_proc '520150472', 'BUY', '36144810', '700', (?, ?)

I get the same error in SQL...
Old April 15th, 2005, 03:14 PM
Friend of Wrox
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts

When you are executing a stored procedure through a command object whose CommandType is adCmdStoredProc, the CommandText should just be the name of the stored procedure.
If the name of your stored procedure is "stored_proc" then you should have
    cmd.CommandText = "stored_proc"
The question marks represent parameters in the definition that the cmd is holding.
    Set prmFlg = cmd.CreateParameter("flg", adVarChar, adParamOutput, 1, "")
creates the first question mark that you see (the name of that ? is "flg") and [code] Creates the other. Because these parameter creations are inside the loop, you will get 2 more for each loop, but that will generate an error, because the names of a command object's parameters must be unique.

You should put the assignation of the CommandType and ActiveConnection outside the For / Next, as those properties are not going to change.

It looks like maybe fund holds the stored procedure name, so that needs to be inside the loop.

I don't know if setting the CommandText clears the parameter collection or not, you should experiment on that one.

If it clears the list, the creation of the parameters can stay where it is. otherwise, it should be outside the loop.

Does that clarify some of the things that you need to rearrange?
Old April 15th, 2005, 03:41 PM
Authorized User
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts

Well I did change things a bit so that the cmd.CommandText ONLY specifies the stored procedure. I then created the parameters (all 6) as such:

Set prm_fund = cmd.CreateParameter("@acct_str", adVarChar, adParamInput, 20, fund)
        cmd.Parameters.Append prm_fund

        Set prm_trans = cmd.CreateParameter("@trans_type", adVarChar, adParamInput, 10, trans_type)
        cmd.Parameters.Append prm_trans

        Set prm_sec = cmd.CreateParameter("@security", adVarChar, adParamInput, 8, security_id)
        cmd.Parameters.Append prm_sec

        Set prm_shares = cmd.CreateParameter("@qty_str", adVarChar, adParamInput, 20, shares)
        cmd.Parameters.Append prm_shares

        'Declare the output parameters
        Set prmFlg = cmd.CreateParameter("@error_flag", adVarChar, adParamOutput, 1, " ")
        cmd.Parameters.Append prmFlg

        Set prmErr = cmd.CreateParameter("@msg_desc", adVarChar, adParamOutput, 255, " ")
        cmd.Parameters.Append prmErr

I am still looking for ways to refresh the parameters. Yet I am not getting the error message, however the application does not work...as my watched CommandText is equal to:

call stored_proc (?, ?, ?, ?, ?, ?)

How can I be sure that the stored proc knows what to do with the ?'s or that the ?'s are just place holders?

Also to retrieve the error codes, how can I do that? They are not coming back to me.
Old April 18th, 2005, 03:09 PM
Friend of Wrox
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts

The ?s are placeholders in the watch window.

The parameters from the parameters collection will be linked with the ?s, 1st ? to Parameter(0), 2nd ? with parameter(1), etc.

The order must be in the order that the parameter list is found in the declaration of the stored procedure in the DB. It is unnerving to just trust Oracle and VB to play well together, but it does work. The parameter's order in VB and Oracle must be the same, but when they are, it works.

Any errors will be in the Errors collection of the connection object.
Old April 19th, 2005, 08:53 AM
Authorized User
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts

Ok this makes sense. So I'm no longer getting errors BUT I am not able to return the output parameters. Could this be a problem with the stored procedure or is this syntax incorrect?

strDesc = Trim(cmd.Parameters("@error_flag").Value)
strDesc = strDesc + Trim(cmd.Parameters("@msg_desc").Value)
Old October 3rd, 2007, 05:41 PM
Registered User
Join Date: Oct 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

Hi People, it may be in appropriate to post this here.

I have the following stored procedure in sql server 2000

CREATE PROCEDURE dbo.[usp_SampleProc]
@ErrorStmt varchar(200) ='' output
    set @ErrorStmt = ' Error while updating'
    SELECT 1/0

    IF (@@ERROR != 0 )
             set @ErrorStmt = ' Error while updating 123456'


set @ErrorStmt = ' No Errors!'

When I execute this through vb 6.0 using following providers it yields the following results for the output parameter @ErrorStmt

MSDASQL.1 --> This returns always null value in the Commnad parameter.

SQLOLEDB.1 --> This returns the correct result as ' Error while updating 123456'.

Is there anything to do with my code or MSDASQL.1 settings. Awaiting for your reply... :-)

Similar Threads
Thread Thread Starter Forum Replies Last Post
Ch 4: Parse error: syntax error, unexpected T_SL hanizar77 BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 0 June 23rd, 2008 09:17 PM
Parse error: syntax error, unexpected T_STRING ginost7 Beginning PHP 1 November 9th, 2007 02:51 AM
Create table statement syntax error in access 2000 method Access 1 August 8th, 2005 02:10 AM
Primary Key Violation error code hasanali00 BOOK: ASP.NET Website Programming Problem-Design-Solution 1 April 19th, 2005 07:41 PM
sntax error or acces violation shoakat Classic ASP Databases 0 November 9th, 2004 07:07 PM

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