How to pass Numeric Value???

Results 1 to 2 of 2

Thread: How to pass Numeric Value???

  1. #1
    Join Date
    Dec 1969

    Default How to pass Numeric Value???

    In our SQL DB, we have a view that has a column called OrdYrMth that is a Numeric type. I am using a Cmd parameter to pass the value to a Stored Proc. I know how to pass VChar and Integers but not sure on Numeric values.<BR><BR>Stored Proc:<BR>CREATE PROCEDURE StatementSelectCurrentBal<BR>(<BR> @GroupKey char(5),<BR> @OrderYrMth numeric<BR>)<BR><BR>Command Parameter:<BR>With objCmd<BR> .ActiveConnection = objConn<BR> .CommandText = "StatementSelectCurrentBal"<BR> .CommandType = adCmdStoredProc <BR> .Parameters.Append .CreateParameter("@GroupKey",adChar,adParamInput,5 ,AGrp) <BR> .Parameters.Append .CreateParameter("@OrderYrMth",adNumeric,adParamIn put,,CurYrMth) <BR>End With<BR><BR>I created the CurYrMth with the following:<BR>CurYrMth = Year(Date()) & "." & Month(Date()).<BR><BR>I can&#039;t get this to run. I&#039;m assuming it is the Numeric piece.<BR>Do I need to convert CurYrMth to a different data type??<BR>Is the Numeric parameter correct??

  2. #2
    Join Date
    Dec 1969

    Default But you are passing a STRING!

    CurYrMth will become "2002.12" or "2003.1" -- STRINGS.<BR><BR>You *could* convert those to numbers by doing<BR> CDBL( CurYrMth )<BR>but now they won&#039;t "sort" correctly. That is, for example, <BR> 2002.12<BR>will be *less than* <BR> 2002.9<BR>Clearly nonsense when it comes to dates!<BR><BR>You SHOULD be doing something like<BR> CurYrMth = Year(Date()) + ( Month(Date())/ 100 )<BR>And *now* you *will* get numbers. And now you will get<BR> 2002.09<BR>instead of the incorrect 2002.9, yes?<BR><BR>And you might, then, be better off declaring that the parameter is DOUBLE instead of simply adNumeric. When you specify adNumeric, you must also specify a precision and scaling factor, as I recall.<BR><BR>INCIDENTALLY...<BR><BR>You can easily get the current year and month in SQL code, inside the SP. If you will always pass in the current year and month, then why bother?<BR><BR>FINALLY...<BR><BR>It would surely be simpler to pass two separate INTEGER parameters, CurYr and CurMth, wouldn&#039;t it? <BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts