1

I am trying to create a template in Excel for customers to bulk update a SQL Server database.

The tricky part is with a few numeric columns, for example, the one I am testing is Estimated_Amt. The value could be null, and I tried and still cannot figure out if when it is null, how to pass the value to fee an update stored procedure.

Here's How I set up the parameter:

Estimated_Amt  = .Cells(iRowNo,8)

If (.Cells(iRowNo, 8) = "") Then
    cmd.Parameters.Append cmd.CreateParameter("@Estimated_Amt", adVarChar, adParamInput, DBNULL.Value)
Else
    cmd.Parameters.Append cmd.CreateParameter("@Estimated_Amt", adVarChar, adParamInput, CStr(Estimated_Amt))
End If

I tried AdDouble and AdDecimal respectively, and never succeeded in catching without error the null or empty value, and with AdVarchar, still didn't work out correctly

And the calling procedure part is like:

cmd.CommandText = "SP_RIBulkyUpdate @CaseNumber = " &     CaseNumber & ", " & _
"@Estimated_Amt = " & Estimated_Amt

Any suggestion on setting the input parameter correctly? Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Viviannne
  • 121
  • 1
  • 1
  • 8

1 Answers1

0

Instead of DBNULL.Value, you could try vbNullString or Null (https://msdn.microsoft.com/en-us/library/microsoft.visualbasic.constants.vbnullstring(v=vs.110).aspx) i.e.

...
cmd.Parameters.Append cmd.CreateParameter("@Estimated_Amt", adVarChar, adParamInput, Null)
...
Janis S.
  • 2,526
  • 22
  • 32
  • Thank you. I guessed vbNullString could work and still got Type mismatch erro – Viviannne Apr 15 '16 at 23:18
  • Have a look [here](http://stackoverflow.com/questions/24248870/calling-stored-procedure-while-passing-parameters-from-access-module-in-vba) where the `CommandType` is set `cmd.CommandType = adCmdStoredProc` and `CommandText` is set to the stored procedure name, i.e., `cmd.CommandText = "SP_RIBulkyUpdate"`. – Janis S. Apr 15 '16 at 23:38
  • I see what you meant, I just tried to specify cmd.CommandType = adCmdStoreProc, but some how the CreateParameter part didn't work out, parameters were not fed properly. Do you have some example about calling an update stored proc? – Viviannne Apr 20 '16 at 16:41
  • Have you tried to resemble this example? http://stackoverflow.com/a/24249331/5251960 – Janis S. Apr 21 '16 at 23:19