Setting Up Connection and Parameters for SQL Stored Procedure

[camromere]

I have a stored procedure in SQL Server that accepts 6 parameters and will duplicate a quote and quote details. Depending upon the values checked, it makes an exact duplicate of an existing quote/quote details or changes the price by either a dollar amount or percentage amount. The field type in SQL Server is currency. The field type for the amount is a decimal(6,4) (I selected decimal because it could be a dollar amount or a percentage amount). When I run the stored procedure from SQL, it runs without errors. I am setting up an Access application that connects to this SQL Server. This is my SQL stored proc code:

ALTER PROCEDURE [dbo].[usp_DuplicateQuote]
@QuoteID INT, --quoteID
@quoteDate date, --quoteDate
@percent bit, --percent
@amount decimal(10,4), --priceChgAmt
@futureprice bit, --futurePrice
@pricechange bit --priceChange

AS
BEGIN
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#newQuote') IS NOT NULL
    DROP TABLE #newQuote

SELECT q.[Quote Date], q.QuoteID, q.[Future Pricing], q.[Special Price], q.CustomerID,
q.Freight, q.Notes, q.[Nat'l Accounts], q.Nestle, q.Invalid as quote_invalid, d.[Div ID],
d.[Product ID], d.[$/lb], d.[$/roll], d.[Roll Width], d.[Roll Length], d.[Core ID], d.CustPartNumber,
d.ItemNotes, d.InternalNotes, d.EndUserID, d.Invalid as detail_invalid, d.[Cust Rebate], d.[EU Rebate],
d.[Nestle Alt Price $/lb]
INTO #newQuote
FROM dbo.Quotes q LEFT OUTER JOIN dbo.[Quotes Detail] d
ON q.QuoteID=d.QuoteID
WHERE q.QuoteID = @QuoteID

INSERT INTO Quotes ([Quote Date], [Future Pricing], [Special Price], CustomerID, Freight, Notes,
[Nat'l Accounts], Nestle, Invalid)
SELECT DISTINCT @quoteDate, q.[Future Pricing], q.[Special Price], q.CustomerID, q.Freight,
q.Notes, q.[Nat'l Accounts], q.Nestle, q.quote_invalid
FROM #newQuote q;

DECLARE @new_identity INT = @@IDENTITY;

IF @percent = 1
BEGIN
    INSERT INTO [dbo].[Quotes Detail] ([QuoteID], [Div ID], [Product ID], [$/lb], [$/roll], [Roll Width],
    [Roll Length], [Core ID], [CustPartNumber], [ItemNotes], [InternalNotes], [EndUserID], [Invalid],
    [Cust Rebate], [EU Rebate], [Nestle Alt Price $/lb])

    SELECT DISTINCT @new_identity, [Div ID], [Product ID], [$/lb]+([$/lb]*@amount), [$/roll], [Roll Width],
    [Roll Length], [Core ID], [CustPartNumber], [ItemNotes], [InternalNotes], [EndUserID], [detail_invalid],
    [Cust Rebate], [EU Rebate], [Nestle Alt Price $/lb]
    FROM #newQuote
END

IF @percent = 0
BEGIN
    INSERT INTO [dbo].[Quotes Detail] ([QuoteID], [Div ID], [Product ID], [$/lb], [$/roll], [Roll Width],
    [Roll Length], [Core ID], [CustPartNumber], [ItemNotes], [InternalNotes], [EndUserID], [Invalid],
    [Cust Rebate], [EU Rebate], [Nestle Alt Price $/lb])

    SELECT DISTINCT @new_identity, [Div ID], [Product ID], [$/lb]+@amount, [$/roll], [Roll Width],
    [Roll Length], [Core ID], [CustPartNumber], [ItemNotes], [InternalNotes], [EndUserID], [detail_invalid],
    [Cust Rebate], [EU Rebate], [Nestle Alt Price $/lb]
    FROM #newQuote
END
END

Then in Access, I am taking values from text boxes and checkboxes to populate the parameters. This is my code from Access:

Private Sub cmdDuplicateQuote_Click()

  Dim quoteID               As Integer
  Dim quoteDate             As Date
  Dim percent               As Byte
  Dim priceChgAmt           As Double
  Dim futurePrice           As Byte
  Dim priceChange           As Byte

  If Me.NewRecord = True Then Exit Sub

quoteID = Form_frmDuplicateQuote.QuoteID_Textbox.Value

If Me!cboxFutureDate = True Then
    quoteDate = Form_frmDuplicateQuote.txtFutureDAte.Value
    futurePrice = True
Else
    quoteDate = Date
    futurePrice = False
End If

If Me!cboxPriceChange = True Then
    priceChgAmt = Form_frmDuplicateQuote.txtPriceChange.Value
Else
    priceChgAmt = 0
End If

If Me!cboxPercent = True Then
    percent = True
Else
    percent = False
End If

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=MSOLEDBSQL;" _
         & "Server=CAMROMERE\SQLEXPRESS;" _
         & "Database=innoviaSales;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;"

conn.Open


Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "[dbo].[usp_DuplicateQuote]"

cmd.Parameters.Append cmd.CreateParameter("@quoteID", adInteger, adParamInput, 0, quoteID)
cmd.Parameters.Append cmd.CreateParameter("@quoteDate", adDate, adParamInput, 0, quoteDate)
cmd.Parameters.Append cmd.CreateParameter("@percent", adBoolean, adParamInput, 0, percent)
cmd.Parameters.Append cmd.CreateParameter("@amount", adDecimal, adParamInput, 0, priceChgAmt)
cmd.Parameters.Append cmd.CreateParameter("@futureprice", adBoolean, adParamInput, 0, futurePrice)
cmd.Parameters.Append cmd.CreateParameter("@pricechange", adBoolean, adParamInput, 0, priceChange)
cmd.Execute
conn.Close

The problem I'm having is this error when I try to run the code from Access.

"The precision is invalid" and when I debug that error, it's on the cmd.Execute line. I think somewhere on the Parameters.Append for the @amount parameter, I need to add the Precision and Scale but I haven't figure out how to add that.

 

[Minty]

A decimal is an (18,0) precision by default in access. I suspect that is where your problem lies.

You need to set its precision, but I don't know how you do that, and a quick google isn't helping...
cmd.Parameters.Append cmd.CreateParameter("@amount", adDecimal, adParamInput, 0, priceChgAmt)

 

[camromere]

That's exactly what I've run into. I tried adding it in the parameter definition, but that didn't work at all.

 

[Minty]

Actually hee is some code I found;

 Set param1 = Comm.CreateParameter("@OCR_Freq", adDecimal, adParamInput, ,
OCR_Freq)
    param1.NumericScale = 1
    param1.Precision = 3
    Comm.Parameters.Append param1

Hopefully that will sort it out.

 

[camromere]

I kept googling too and found this which worked.
cmd.Parameters.Append cmd.CreateParameter("@amount", adDecimal, adParamInput, 0, priceChgAmt)
cmd.Parameters("@amount").Precision = 6
cmd.Parameters("@amount").NumericScale = 4


It is now inserting to the database

 

[Isaac]

I kept googling too and found this which worked.
cmd.Parameters.Append cmd.CreateParameter("@amount", adDecimal, adParamInput, 0, priceChgAmt)
cmd.Parameters("@amount").Precision = 6
cmd.Parameters("@amount").NumericScale = 4


It is now inserting to the database

I see this kind of code pretty often, but just to let you know, it's much easier (compared to ado, parameters, and all that) to just set up a pass through query in your database and then populate it at run-time with the exact (very short & simple) t-sql that you would use to execute the proc from ssms:

Currentdb.querydefs("Nameofquery").sql="exec [dbo].[spname] 'par1value','par2value', ...........etc
and execute that query.

To me that is....like...a million times easier, because you can test your app straight through without worry about ado and paramters.
Once you know your t-sql exec procname 'par', 'par' then your next and only task to finish the job is to execute exact same code in access in pt query.

Only time I use ADO for executing sql server is from Excel.