How do I declare a numeric parameter?  
Author Message
BeeCreative





PostPosted: Mon May 31 16:23:28 CDT 2004 Top

SQL Server Developer >> How do I declare a numeric parameter?

Hi
I'm trying to run a stored procedure from VB6, using ADO. So I've
declared several parameters, for example

Set objParm = New ADODB.Parameter
objParm.Name = "pLIN_T_BAT"
objParm.Type = adInteger
objParm.Direction = adParamInput
objParm.Value = 0
objCmd.Parameters.Append objParm

But there is one paramter I can't figure out - pLIN_QTY is numeric(18,6).
i've tried
Set objParm = New ADODB.Parameter
objParm.Name = "pLIN_QTY"
objParm.Type = adNumeric
objParm.Direction = adParamInput
objParm.Value = sQuantity
objCmd.Parameters.Append objParm

But I'm getting problems. Any ideas?
Thanks
Diarmuid

SQL Server278  
 
 
John





PostPosted: Mon May 31 16:23:28 CDT 2004 Top

SQL Server Developer >> How do I declare a numeric parameter? Hi

I am no expert on this but it would help to know what error you are getting!

From:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcreateparam.asp

If you specify a numeric data type (adNumeric or adDecimal) in the Type
argument, then you must also set the NumericScale and Precision properties.

John



> Hi
> I'm trying to run a stored procedure from VB6, using ADO. So I've
> declared several parameters, for example
>
> Set objParm = New ADODB.Parameter
> objParm.Name = "pLIN_T_BAT"
> objParm.Type = adInteger
> objParm.Direction = adParamInput
> objParm.Value = 0
> objCmd.Parameters.Append objParm
>
> But there is one paramter I can't figure out - pLIN_QTY is numeric(18,6).
> i've tried
> Set objParm = New ADODB.Parameter
> objParm.Name = "pLIN_QTY"
> objParm.Type = adNumeric
> objParm.Direction = adParamInput
> objParm.Value = sQuantity
> objCmd.Parameters.Append objParm
>
> But I'm getting problems. Any ideas?
> Thanks
> Diarmuid
>
>


 
 
v-mingqc





PostPosted: Mon May 31 21:20:52 CDT 2004 Top

SQL Server Developer >> How do I declare a numeric parameter? Hi Diarmuid,

From your descriptions, I understood that you would like to know how to
call an numeric parameter stored procedures from VB ADO. Have I understood
you? If there is anything I misunderstood, please feel free to let me know
:)

First and foremost, I would like to strongly recommand you have a look at
the following documents

HOWTO: Retrieve Values in SQL Server Stored Procedures with ADO
http://support.microsoft.com/?id=194792

We usually do this kind of declaration as listed in the documents above like
---
sParmName = "E1Num" 'Number of RAISERROR statements in Resultset
'1.
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
adParamInput)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = 0
---

Additional information are for calling stored procedures by ADO.NET

HOW TO: Return Errors and Warnings from a SQL Server Stored Procedure in
ADO.NET
http://support.microsoft.com/?id=321903

Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!

Sincerely yours,

Mingqing Cheng
Microsoft Online Support
---------------------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

 
 
Diarmuid





PostPosted: Tue Jun 01 03:22:16 CDT 2004 Top

SQL Server Developer >> How do I declare a numeric parameter? Thanks. I looked at the link, and used
Set objParm = New ADODB.Parameter
objParm.Name = "pLIN_QTY"
objParm.Type = adNumeric
objParm.NumericScale = 18
objParm.Precision = 6
objParm.Direction = adParamInput
objParm.Value = sQuantity
objCmd.Parameters.Append objParm

But I'm still getting the same error is "Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work was
done."




> Hi
>
> I am no expert on this but it would help to know what error you are
getting!
>
> From:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcreateparam.asp
>
> If you specify a numeric data type (adNumeric or adDecimal) in the Type
> argument, then you must also set the NumericScale and Precision
properties.
>
> John
>


> > Hi
> > I'm trying to run a stored procedure from VB6, using ADO. So I've
> > declared several parameters, for example
> >
> > Set objParm = New ADODB.Parameter
> > objParm.Name = "pLIN_T_BAT"
> > objParm.Type = adInteger
> > objParm.Direction = adParamInput
> > objParm.Value = 0
> > objCmd.Parameters.Append objParm
> >
> > But there is one paramter I can't figure out - pLIN_QTY is
numeric(18,6).
> > i've tried
> > Set objParm = New ADODB.Parameter
> > objParm.Name = "pLIN_QTY"
> > objParm.Type = adNumeric
> > objParm.Direction = adParamInput
> > objParm.Value = sQuantity
> > objCmd.Parameters.Append objParm
> >
> > But I'm getting problems. Any ideas?
> > Thanks
> > Diarmuid
> >
> >
>
>


 
 
John





PostPosted: Tue Jun 01 03:51:12 CDT 2004 Top

SQL Server Developer >> How do I declare a numeric parameter? Hi

Looking through Google for "Multiple-step OLE DB operation" gives loads of
hits:

http://tinyurl.com/3gpdq

This could be a mismatch between the data and you parameter type. What is in
sQuantity and this does not seem to be numeric!

John



> Thanks. I looked at the link, and used
> Set objParm = New ADODB.Parameter
> objParm.Name = "pLIN_QTY"
> objParm.Type = adNumeric
> objParm.NumericScale = 18
> objParm.Precision = 6
> objParm.Direction = adParamInput
> objParm.Value = sQuantity
> objCmd.Parameters.Append objParm
>
> But I'm still getting the same error is "Multiple-step OLE DB operation
> generated errors. Check each OLE DB status value, if available. No work
was
> done."
>
>


> > Hi
> >
> > I am no expert on this but it would help to know what error you are
> getting!
> >
> > From:
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcreateparam.asp
> >
> > If you specify a numeric data type (adNumeric or adDecimal) in the Type
> > argument, then you must also set the NumericScale and Precision
> properties.
> >
> > John
> >


> > > Hi
> > > I'm trying to run a stored procedure from VB6, using ADO. So I've
> > > declared several parameters, for example
> > >
> > > Set objParm = New ADODB.Parameter
> > > objParm.Name = "pLIN_T_BAT"
> > > objParm.Type = adInteger
> > > objParm.Direction = adParamInput
> > > objParm.Value = 0
> > > objCmd.Parameters.Append objParm
> > >
> > > But there is one paramter I can't figure out - pLIN_QTY is
> numeric(18,6).
> > > i've tried
> > > Set objParm = New ADODB.Parameter
> > > objParm.Name = "pLIN_QTY"
> > > objParm.Type = adNumeric
> > > objParm.Direction = adParamInput
> > > objParm.Value = sQuantity
> > > objCmd.Parameters.Append objParm
> > >
> > > But I'm getting problems. Any ideas?
> > > Thanks
> > > Diarmuid
> > >
> > >
> >
> >
>
>


 
 
Diarmuid





PostPosted: Tue Jun 01 04:28:36 CDT 2004 Top

SQL Server Developer >> How do I declare a numeric parameter? sQuantity =2. I've tried using 2ObjParm.Value = 2 and still get the same
error. Theres several other parameters, so it may be that one of them is the
incorrect data type.




> Hi
>
> Looking through Google for "Multiple-step OLE DB operation" gives loads of
> hits:
>
> http://tinyurl.com/3gpdq
>
> This could be a mismatch between the data and you parameter type. What is
in
> sQuantity and this does not seem to be numeric!
>
> John
>


> > Thanks. I looked at the link, and used
> > Set objParm = New ADODB.Parameter
> > objParm.Name = "pLIN_QTY"
> > objParm.Type = adNumeric
> > objParm.NumericScale = 18
> > objParm.Precision = 6
> > objParm.Direction = adParamInput
> > objParm.Value = sQuantity
> > objCmd.Parameters.Append objParm
> >
> > But I'm still getting the same error is "Multiple-step OLE DB operation
> > generated errors. Check each OLE DB status value, if available. No work
> was
> > done."
> >
> >


> > > Hi
> > >
> > > I am no expert on this but it would help to know what error you are
> > getting!
> > >
> > > From:
> > >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcreateparam.asp
> > >
> > > If you specify a numeric data type (adNumeric or adDecimal) in the
Type
> > > argument, then you must also set the NumericScale and Precision
> > properties.
> > >
> > > John
> > >


> > > > Hi
> > > > I'm trying to run a stored procedure from VB6, using ADO. So
I've
> > > > declared several parameters, for example
> > > >
> > > > Set objParm = New ADODB.Parameter
> > > > objParm.Name = "pLIN_T_BAT"
> > > > objParm.Type = adInteger
> > > > objParm.Direction = adParamInput
> > > > objParm.Value = 0
> > > > objCmd.Parameters.Append objParm
> > > >
> > > > But there is one paramter I can't figure out - pLIN_QTY is
> > numeric(18,6).
> > > > i've tried
> > > > Set objParm = New ADODB.Parameter
> > > > objParm.Name = "pLIN_QTY"
> > > > objParm.Type = adNumeric
> > > > objParm.Direction = adParamInput
> > > > objParm.Value = sQuantity
> > > > objCmd.Parameters.Append objParm
> > > >
> > > > But I'm getting problems. Any ideas?
> > > > Thanks
> > > > Diarmuid
> > > >
> > > >
> > >
> > >
> >
> >
>
>


 
 
v-mingqc





PostPosted: Thu Jun 03 01:36:49 CDT 2004 Top

SQL Server Developer >> How do I declare a numeric parameter? Hi Diarmuid,

From your descriptions, I understood Error "Multiple-step OLE DB operation"
occurs in your codes. Have I understood you? If there is anything I
misunderstood, please feel free to let me know :)

Based on my testing, the error is in your codes: objParm.NumericScale = 18

We use the NumericScale property to determine how many digits to the right
of the decimal point will be used to represent values for a numeric
Parameter or Field object. I am afraid 18 is too big to be under control
and that's why we will get an error here. Checking your Database Table
design or change the codes.

The following docuemnts contains an example using NumericScale, you could
have a look at it
PRB: Decimal Values Passed to a Stored Procedure Get Truncated
http://support.microsoft.com/default.aspx?scid=kb;en-us;188574

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Online Support
------------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!




 
 
Diarmuid





PostPosted: Thu Jun 03 03:37:52 CDT 2004 Top

SQL Server Developer >> How do I declare a numeric parameter? Thanks, it works fine when I have
objParm.NumericScale = 6
objParm.Precision = 18

i had them the wrong way around!
Regards
Diarmuid



> Hi Diarmuid,
>
> From your descriptions, I understood Error "Multiple-step OLE DB
operation"
> occurs in your codes. Have I understood you? If there is anything I
> misunderstood, please feel free to let me know :)
>
> Based on my testing, the error is in your codes: objParm.NumericScale = 18
>
> We use the NumericScale property to determine how many digits to the right
> of the decimal point will be used to represent values for a numeric
> Parameter or Field object. I am afraid 18 is too big to be under control
> and that's why we will get an error here. Checking your Database Table
> design or change the codes.
>
> The following docuemnts contains an example using NumericScale, you could
> have a look at it
> PRB: Decimal Values Passed to a Stored Procedure Get Truncated
> http://support.microsoft.com/default.aspx?scid=kb;en-us;188574
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
>
> Sincerely yours,
>
> Mingqing Cheng
> Microsoft Online Support
> ------------------------------------------------------
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>
>