DynDeveloper.com

DynDeveloper.com
Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Guest Log Out
Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
You are accessing the Members Only area for DynDeveloper.com using guest access. The member experience is slightly different
stevegray
4Penny.net
Points: 55409
1/24/2014 11:23:54 AM

VBA ADODB Paramaterized Query Example

* This article, and all our great .NET Development documentation, Is available on the .NET Development menu

This is a sample of a piece of code that executes a SQL statement in VBA and uses an ADODB parameter.

Why go to all the trouble? Google 'SQL Injection Attack' and you'll find out. Using an ADO Parameter object will protect you.

We're frequently called upon to write simple validations behind forms in Dynamics GP, this piece of code is boiler plate for us - cut and paste, edit the SQL, done.

For the record, we're a 'stored procedure only' shop... but for things as small as this the direct SQL make sense. The  query is small, and the customer doesn't have to maintain the stored procedure.                        

Note that there are no quotes around the parameter question mark... that took me a while to get. Using this method, you don't use quotes even for string params.

Along the way we caused a few errors:

Run-time error '3708': Parameter object is improperly defined. Inconsistent or incomplete information was provided.

and

Run-time error '1006': Unsafe Operation. This operation cannot be performed in the target field's AfterUserChange, BeforeLostFocus, or AfterLostFocus Events. This operation could compromise the integrity of the application.

 

 
4Penny.net
Version: GP 9,GP 10,GP 2010,GP 2013
Section: VBA

BTW, that last one was caused from the BeforeUserChange event... but we had popped up a message box before the field assignment. Moving the field assignment before the message box fixed the error.

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strItemClass As String
  
Sub openConnection()
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
End Sub
  
Private Sub Window_AfterOpen()
    openConnection
End Sub
 
Private Sub zUDString20_AfterGotFocus()
 
End Sub
 
Private Sub zUDString20_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
    If cn.State = 0 Then
        openConnection
    End If
 
    Dim strPaymentTerm As String
    strPaymentTerm = Me.zUDString20
     
    'get an item
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "select pymtrmid from sy03300 where pymtrmid = ?"
    cmd.Parameters.Append cmd.CreateParameter("@pymtrmid", adVarChar, adParamInput, 21, strPaymentTerm)
    Set rst = cmd.Execute
 
    If rst.EOF Then
        Me.zUDString20 = ""
        MsgBox ("Invalid Payment Term")
        Exit Sub
    Else
        strPaymentTerm = rst("pymtrmid")
        Me.zUDString20 = strPaymentTerm
    End If
 
End Sub
 
 
Private Sub zUDString20_Changed()
 
End Sub
Please leave a comment

Add a Comment



Not Subscribed. You will not receive emails on article changes or comment additions

Comments

body header
No records to display.
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
Archives | Table Listing | eConnect Error Database | eConnect Schema | Anchored Marriage
3