ADP SPROC Help

[TR21Mark]

Using Access 2007 ADP front end / SQL 2005 BE / Win7 x64

I have a form with unbound controls used for data entry. I have a stored procedure that is to append all of the fields into tblProjects.

What my code below does runs validation that each textbox is not null and if it passes then it runs a private sub that passes the parameters to the stored procedure. Right now I am having 2 issues with the code. The first is I am getting no error messages, the second is the data is not getting input into the table.

Could someone review my code and steer me in the right direction please? Thank in advance and appreciate any help that is given.

Private Sub cmdSaveProj_Click()
    ' Comments:
    ' Params  :
    ' Modified: MWJ 07192011
    
    'This checks all controls on the form with the word "ACE" in the Tag property of the control
    'If any of the controls are null or empty then the user can not proceed
    
    On Error GoTo PROC_ERR
    
    Dim objCtl As Object
    
    For Each objCtl In Me.Controls
        If (objCtl.ControlType = acTextBox Or objCtl.ControlType = acComboBox) And objCtl.Tag = "ACE" Then
            
            If Nz(objCtl, "") = "" Then
                MsgBox ("You can't exit this form until ALL data is provided!" & vbCrLf & "Enter the missing data and try again . . . "), , "MISSING DATA"
                objCtl.SetFocus
                
                Exit For
            End If
            
        End If
        'Insert below this line the commands you want if validation is met
        'Me.cmdCreateFC.Visible = True
        Call AddProject
        
    Next objCtl
    

    
PROC_EXIT:
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Description, vbCritical, Me.Name & ".cmdSaveProj_Click"
    Resume PROC_EXIT




End Sub

Private Sub Form_AfterUpdate()

End Sub


Private Sub AddProject()
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    
    Set cmd = New ADODB.Command
    Set cn = New ADODB.Connection
    cn.Open "my conn left out on purpose"
    
    On Error GoTo PROC_ERR
    
    With cmd
            Set .ActiveConnection = cn
            .CommandText = "mjspAddNewProject"
            .CommandType = adCmdStoredProc
            
           
            .Parameters.Append .CreateParameter("@ProjName", adVarChar, adParamInput, 100, Me.txtJobName)
            .Parameters.Append .CreateParameter("@ProjNo", adVarChar, adParamInput, 20, Me.txtJobNo)
            .Parameters.Append .CreateParameter("@ProjS1", adVarChar, adParamInput, 60, Me.txtStreet1)
            .Parameters.Append .CreateParameter("@ProjS2", adVarChar, adParamInput, 60, Me.txtStreet2)
            .Parameters.Append .CreateParameter("@ProjCity", adVarChar, adParamInput, 50, Me.txtCity)
            .Parameters.Append .CreateParameter("@ProjState", adVarChar, adParamInput, 5, Me.txtState)
            .Parameters.Append .CreateParameter("@ProjZip", adVarChar, adParamInput, 15, Me.txtZip)
            .Parameters.Append .CreateParameter("@ProjGC", adVarChar, adParamInput, 85, Me.cboGCName)
            .Parameters.Append .CreateParameter("@ProjGCID", adVarChar, adParamInput, 20, Me.txtGCID)
            
            'keep adding lines above for each parameter/textbox or control
            
            .Execute
            Set .ActiveConnection = Nothing
            End With
            
    
    
PROC_EXIT:
    cn.Close
    Set cmd = Nothing
    Set cn = Nothing
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Number, Err.Description, vbCritical, Me.Name & ".AddProject"
    Resume PROC_EXIT


End Sub

 

[TR21Mark]

I forgot to add my stored procedure

ALTER PROCEDURE dbo.mjspAddNewProject


(@ProjName varchar(100),
@ProjNo varchar(20),
@ProjS1 varchar(60),
@ProjS2 varchar(50),
@ProjCity varchar(50),
@ProjState varchar(5),
@ProjZip varchar(15),
@ProjGC varchar(85),
@ProjGCID varchar(20))
AS 
 SET NOCOUNT ON 
 SET IDENTITY_INSERT dbo.tblProjects ON

INSERT INTO dbo.tblProjects
                        (ProjName, ProjNo, ProjStreet, ProjST2, ProjCity, ProjState, ProjZip, ProjGC, ProjGCID, ProjID)
SELECT      ProjName, ProjNo, ProjStreet, ProjST2, ProjCity, ProjState, ProjZip, ProjGC, ProjGCID, ProjID
FROM          dbo.tblProjects
WHERE      (ProjName = @ProjName) AND (ProjNo = @ProjNo) AND (ProjStreet = @ProjS1) AND (ProjST2 = @ProjS2) AND (ProjCity = @ProjCity) AND 
                        (ProjState = @ProjState) AND (ProjZip = @ProjZip) AND (ProjGC = @ProjGC) AND (ProjGCID = @ProjGCID)

 

[xenou]

This seems odd. You are inserting into a table records selected from the same table?

Also btw your Stored Procedure has a size of 50 for Street2, and your ADO parameter uses a size of 60 - not sure if it matters.

INSERT INTO 
    dbo.tblProjects
    (ProjName, ProjNo, ProjStreet, 
     ProjST2, ProjCity, ProjState, 
     ProjZip, ProjGC, ProjGCID, ProjID)
SELECT
    ProjName, ProjNo, ProjStreet, 
    ProjST2, ProjCity, ProjState, 
    ProjZip, ProjGC, ProjGCID, ProjID
FROM 
    dbo.tblProjects
WHERE
    (ProjName = @ProjName) 
    AND 
    (ProjNo = @ProjNo) 
    AND 
    (ProjStreet = @ProjS1) 
    AND 
    (ProjST2 = @ProjS2) 
    AND 
    (ProjCity = @ProjCity) 
    AND 
    (ProjState = @ProjState) 
    AND 
    (ProjZip = @ProjZip) 
    AND 
    (ProjGC = @ProjGC) 
    AND 
    (ProjGCID = @ProjGCID)

 

[TR21Mark]

I see that now. I am only trying to append a new row into the table by the passed parameters which are from the data the user inputs.

The sp was created in Access not on the SQL Server side.

 

[xenou]

Okay.
The stored procedure mjspAddNewProject is not right then.

It should be:

INSERT INTO
    dbo.tblProjects
        (ProjName, ProjNo, ProjStreet,
        ProjST2, ProjCity, ProjState,
        ProjZip, ProjGC, ProjGCID, ProjID)

    VALUES
        (@ProjName, @ProjNo, @ProjS1,
        @ProjS2, @ProjCity, @ProjState,
        @ProjZip, @ProjGC, @ProjGCID)
    ;

ProjID is missing though - is that an autoincrement field that the server generates?

 

[TR21Mark]

yes ProjID is the identity seed. I had the SET Identity_Insert ON in the sproc

 

[TR21Mark]

I have it figured it out and will post the updated code.

The stored procedure looks like this now, I had to take out the " Identity_Insert" line and make the changes you had listed.

ALTER PROCEDURE dbo.mjspAddNewProject


(@ProjName varchar(100),
@ProjNo varchar(20),
@ProjS1 varchar(60),
@ProjS2 varchar(50),
@ProjCity varchar(50),
@ProjState varchar(5),
@ProjZip varchar(15),
@ProjGC varchar(85),
@ProjGCID varchar(20))
AS 
 SET NOCOUNT ON 
 
INSERT INTO dbo.tblProjects  (ProjName, ProjNo, ProjStreet, ProjST2, ProjCity, ProjState, ProjZip, ProjGC, ProjGCID)

VALUES   (@ProjName, @ProjNo, @ProjS1, @ProjS2, @ProjCity, @ProjState, @ProjZip, @ProjGC, @ProjGCID)

Thanks for the help. Many more questions on their way.

 

[TR21Mark]

One other question. On the last line of code below

Private Sub AddProject()
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    
    Set cmd = New ADODB.Command
    Set cn = New ADODB.Connection
    cn.Open "my conn left out on purpose"

Is there a way to place my connection string in one place and reference it in the code above? Once I have the development done, I will have to go through each page of code and change this. Is there an easier way to do this? Is there a better approved way you suggest?

Thanks in advance.

 

[xenou]

Normally, I will add a standard module to my project and name it modConstants (or whatever you like to call it).

This module will be for your Public Constants (which are all caps by convention):

So in modConstants I would put:

Public Const [COLOR="RoyalBlue"]CONNECTION_STRING_DB1[/COLOR] As String = "my connection string here"

Then in your module with the code:

cn.Open sSQL, [COLOR="RoyalBlue"]CONNECTION_STRING_1[/COLOR]

If the connection string changes you can update the constant in one place this way.

There's many variations on this theme. You could declare the constant in the same module as the code, if that is the only module in which this connection string is used. Note that constant declarations don't allow for line breaks so if its a long string its also a long line.

 

[TR21Mark]

I am getting the 'Variable Not Defined' error on the sSQL

cn.Open sSQL, CONNECTION_STRING_1