how not to pass an optional parameter from access visual basic to stored procedure ?

[andy.vladimirsky]

dear friends !

could you please help me in resolving my issue (if any) !

i have a tiny sql server database.

it contains one table (nothing special - just first name, last name, phone, mail. [phone] and [mail] are optional):

CREATE TABLE [dbo].[tbl_contact](
[id] [int] IDENTITY (1,1) NOT NULL,
[name_last] [nvarchar] (32) NOT NULL,
[name_first] [nvarchar] (32) NOT NULL,
[phone] [nvarchar] (32) NULL,
[mail] [nvarchar] (32) NULL,
CONSTRAINT [PK_tbl_contact] PRIMARY KEY CLUSTERED ( [id] ASC ) ON [PRIMARY]

the database contains one stored procedure (nothing special - just insert a single record in the table. @phone and @mail are optional):

ALTER PROCEDURE [dbo].[prc_add_contact]
@name_last nvarchar ( 32 ),
@name_first nvarchar ( 32 ),
@phone nvarchar ( 32 ) = null,
@mail nvarchar ( 32 ) = null
AS
BEGIN
SET NOCOUNT ON;
insert [tbl_contact] ( [name_last], [name_first], [phone], [mail] )
values ( @name_last, @name_first, @phone, @mail )
end

i have a tiny ms access project file and a visual basic code within it.

this piece of code works fine (the way i expected) :

CurrentProject.Connection.Execute "[prc_add_contact] @name_last = 'smith', @name_first = 'robert', @mail = 'robert.smith ...' ;"

this one works fine (from technical point of view) but insert value for mail address into [phone] field of the table:

Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "[prc_add_contact]"
cmd.CommandType = adCmdStoredProc

Set prm_namelast = cmd.CreateParameter("@name_last", adVarWChar, adParamInput, Len("smith"), "smith")
Set prm_namefirst = cmd.CreateParameter("@name_first", adVarWChar, adParamInput, Len("robert"), "robert")
Set prm_mail = cmd.CreateParameter("@mail", adVarWChar, adParamInput, Len("robert.smith ..."), "robert.smith ...")

cmd.Parameters.Append prm_namelast
cmd.Parameters.Append prm_namefirst
cmd.Parameters.Append prm_mail

cmd.Execute

Set cmd = Nothing

so my first question is how to pass the first, the second and the fourth parameters and nothing else. (i do really believe '= NULL' means i may not to pass it at all when calling stored procedure.)

(please help me - i almost broke my brain.)

thank you in advance !

av

 

[VilaRestal]

Have you tried creating the parameter but without giving it a value?:

Set prm_phone = cmd.CreateParameter("@phone", adVarWChar, adParamInput)

cmd.Parameters.Append prm_phone

 

[mdlueck]

For NULL'able columns I have come up with the following standard practice to handle those:

1) For NULL'able columns in the table, define a default value of NULL in the Stored Procedure (SP) working with said table:

CREATE PROCEDURE [dbo].[$(SPNAME)] (
  -- Add the parameters for the stored procedure here
  @authid AS smallint,
  @prodteamid AS smallint = NULL,
  @subsystypeid AS smallint = NULL,
  @buyercodetypeid AS smallint = NULL,
  @stocktypeid AS smallint = NULL,
  @mfgmethtypeid AS smallint = NULL,
  @cmdtycodetypeid AS smallint = NULL,
  @quoteid AS int = NULL,
  @partnumber AS varchar(25),
  @title AS varchar(50),
  @rev AS smallint = NULL,
  @ver AS smallint = NULL,
  @econumber AS varchar(15) = NULL,
  @ecoreleasedate AS date = NULL,
  @eau AS integer = NULL,
  @stopworkflg AS bit,
  @bomloadedflg AS bit,
  @rtrcompflg AS bit,
  @salableflg AS bit,
  @supplierchampion AS varchar(50) = NULL,
  @comments AS varchar(8000) = NULL
  )
AS

2) In the VBA code driving the SP, special handling is needed to handle the NULL'able fields:

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.getADODBConnectionObj()
    .CommandText = "clsObjPartsTbl_Update"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@id").Value = Me.id
    .Parameters("@authid").Value = Me.authid
    .Parameters("@logtimestamp").Value = Me.logtimestamp

    If Me.prodteamid = 0 Then
      .Parameters("@prodteamid").Value = Empty
    Else
      .Parameters("@prodteamid").Value = Me.prodteamid
    End If

    If Me.subsystypeid = 0 Then
      .Parameters("@subsystypeid").Value = Empty
    Else
      .Parameters("@subsystypeid").Value = Me.subsystypeid
    End If

    If Me.buyercodetypeid = 0 Then
      .Parameters("@buyercodetypeid").Value = Empty
    Else
      .Parameters("@buyercodetypeid").Value = Me.buyercodetypeid
    End If

    If Me.stocktypeid = 0 Then
      .Parameters("@stocktypeid").Value = Empty
    Else
      .Parameters("@stocktypeid").Value = Me.stocktypeid
    End If

    If Me.mfgmethtypeid = 0 Then
      .Parameters("@mfgmethtypeid").Value = Empty
    Else
      .Parameters("@mfgmethtypeid").Value = Me.mfgmethtypeid
    End If

    If Me.cmdtycodetypeid = 0 Then
      .Parameters("@cmdtycodetypeid").Value = Empty
    Else
      .Parameters("@cmdtycodetypeid").Value = Me.cmdtycodetypeid
    End If

    If (Me.quoteid = 0) Or (Me.quoteid = -1) Then
      .Parameters("@quoteid").Value = Empty
    Else
      .Parameters("@quoteid").Value = Me.quoteid
    End If

    .Parameters("@partnumber").Value = Me.partnumber
    .Parameters("@title").Value = Me.title

    If Me.rev = -1 Then
      .Parameters("@rev").Value = Empty
    Else
      .Parameters("@rev").Value = Me.rev
    End If

    If (Me.ver = "-") Or (Me.ver = vbNullString) Then
      .Parameters("@ver").Value = Empty
    Else
      .Parameters("@ver").Value = Me.ver
    End If

    If Me.econumber = vbNullString Then
      .Parameters("@econumber").Value = Empty
    Else
      .Parameters("@econumber").Value = Me.econumber
    End If

    .Parameters("@ecoreleasedate").Type = adDBTimeStamp
    If Me.ecoreleasedate = vbNullString Then
      .Parameters("@ecoreleasedate").Value = Empty
    Else
      .Parameters("@ecoreleasedate").Value = Me.ecoreleasedate
    End If

    If Me.eau = 0 Then
      .Parameters("@eau").Value = Empty
    Else
      .Parameters("@eau").Value = Me.eau
    End If

    .Parameters("@stopworkflg").Value = Me.stopworkflg
    .Parameters("@bomloadedflg").Value = Me.bomloadedflg
    .Parameters("@rtrcompflg").Value = Me.rtrcompflg
    .Parameters("@salableflg").Value = Me.salableflg

    If Me.supplierchampion = vbNullString Then
      .Parameters("@supplierchampion").Value = Empty
    Else
      .Parameters("@supplierchampion").Value = Me.supplierchampion
    End If

    If Me.comments = vbNullString Then
      .Parameters("@comments").Value = Empty
    Else
      .Parameters("@comments").Value = Me.comments
    End If

    Set adoRS = .Execute(lRecordsAffected)
  End With

Step through and check the VBA variable for the NULL indicator. (Sometimes I have used -1 to indicate NULL, sometimes 0, sometimes vbNullString, etc...) If it is found, set the ADO.Parameters object to a Value of Empty, else set the value of the ADO.Parameters object.

Plop plop fizz fizz NULL values end up showing up in the SQL table!

 

[VilaRestal]

So it is an empty value to get the default.

Set prm_phone = cmd.CreateParameter("@phone", adVarWChar, adParamInput,, adEmpty)

would do it then

 

[mdlueck]

would do it then

If the SP has been coded to supply the default value to the optional column. If not you will get an error that a required field value was not supplied. CoMpLiCaTeD!

And I forgot to mention that the table schema needs to have the field in question not set to be a required field as well, thus three parts to the puzzle.

 

[VilaRestal]

Indeed and I see from OP that those three are the case,

 

[kleky]

Hello folks, can I just enquire as to why you use this ADODB.Command, as I use a pass through query, which seems far simpler.

In the stores procedure I set the parameter default: = NULL, then in access I call the SP with:

If gPassThrough("siNewOrganisation 1," & _
                    "0," & _
                    IIf(IsNull(txtOrgName.Value), "Null", "'" & txtOrgName.Value & "'"), "Organisations", False) = False Then Exit Sub