Return BIGINT OUTPUT param to VB!?

Please help me on this one.

I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc

CREATE PROCEDURE dbo.uspCalcWeightedAverage
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.WeightedAverage bigint OUTPUT
AS
...
...
SELECT @.WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)
FROM
TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR]
INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,DBI.DATE) = [HHF].[Month]
WHERE
PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND [PDF].[WD-WE] = @.InWeekDay
AND HHF.MARKETID = @.InMarketID
AND PDF.FLAG = 1
GROUP BY FLAG

When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null

I try to return it with the following code (got the code from a friend)

Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.Recordset
Dim objAdoRecordset As ADODB.Recordset
Dim objAdoCommand As ADODB.Command
Dim lngCtr As Long
On Error GoTo RunCommand_Error

' Create cmd object
Set objAdoCommand = New ADODB.Command
Set objAdoCommand.ActiveConnection = m_oAdoConnection
objAdoCommand.ActiveConnection = m_oAdoConnection
objAdoCommand.CommandText = v_strStoredProcName
objAdoCommand.CommandType = adCmdStoredProc
Call objAdoCommand.Parameters.Refresh
'Stop
For lngCtr = 0 To UBound(r_varParamValues)
If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then
objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr)
End If
Next

Set objAdoRecordset = New ADODB.Recordset
objAdoRecordset.CursorLocation = adUseClient

Set objAdoRecordset = objAdoCommand.Execute
'Stop
For lngCtr = 0 To objAdoCommand.Parameters.Count - 1
If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then
r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value
End If
Next
Set RunProcedure = objAdoRecordset
RunCommand_Exit:
' Collect your garbage here
Exit Function
RunCommand_Error:
' Collect your garbage here
Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")
End Function

PLEASE HELP.

Regards,

Sander

Grrrr...If I add the following line (before the DROP) to the sp it returns the value!

SET @.WeightedAverage = 9587558855

?! Que Pasa ?!

Return an unique identifier to an ASP.NET page to send it as a parameter into another stor

Hi !

I have a problem with the unique identifier and don't know how to solve it.

I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values.

I tried withSCOPE_IDENTITYbut i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable.

Dim

cmdAsNew SqlCommand

cmd.CommandText ="Insertar_Contacto"

cmd.CommandType = CommandType.StoredProcedure

cmd.Connection = connect

Thanks!!

Create a parameter of type OUTPUT in your stored proc, assign the value of SCOPE_IDENTITY() to it after your insert, create the same parameter on your application layer, set its direction to OUTPU and retrieve the value. Search my posts here for some sample code as I posted some code for someone, in the last few days.|||

Have a look at

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.usp_Test_Insert
(
@.IsAdmin bit,
@.JobTitle nvarchar (50),
@.Name nvarchar (50),
@.DateCreated datetime,
@.RETURN INT OUTPUT,
@.IDENTITY INT OUTPUT
) AS
-- Purpose:
-- Insert record into Test table
-- Parameters:
-- IsAdmin -
-- JobTitle -
-- Name -
-- DateCreated -
-- RETURN - Zero or Error Code
-- IDENTITY - Identity of inserted row
-- History:
-- 11Jan2006 ACERXP\Administrator Original coding
SET NOCOUNT ON
INSERT INTO Test( IsAdmin, JobTitle, Name, DateCreated)
VALUES (
@.IsAdmin,
@.JobTitle,
@.Name,
@.DateCreated)
SELECT @.RETURN = @.@.error, @.IDENTITY = SCOPE_IDENTITY()
RETURN
----- this is the end ------
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

return all rows

Hello,
Here is my query for my report. Can you make this query fetch all rows if
@.myID parameter is null?
SELECT FName, MName, LName, ID
FROM MyTable
WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
Thanks,
Jim.write a stored procedure and use an if condition
"JIM.H." wrote:
> Hello,
> Here is my query for my report. Can you make this query fetch all rows if
> @.myID parameter is null?
> SELECT FName, MName, LName, ID
> FROM MyTable
> WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> Thanks,
> Jim.
>|||is it possible without stored procedure?
"NI" wrote:
> write a stored procedure and use an if condition
> "JIM.H." wrote:
> > Hello,
> > Here is my query for my report. Can you make this query fetch all rows if
> > @.myID parameter is null?
> >
> > SELECT FName, MName, LName, ID
> > FROM MyTable
> > WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> >
> > Thanks,
> > Jim.
> >|||Hello Jim
Try to set your "is null" clause in the report settings itself
Ruud Boots
Holland
> SELECT FName, MName, LName, ID
> FROM MyTable
> WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
"JIM.H." wrote:
> Hello,
> Here is my query for my report. Can you make this query fetch all rows if
> @.myID parameter is null?
> SELECT FName, MName, LName, ID
> FROM MyTable
> WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> Thanks,
> Jim.
>|||Here is a technique I like for the WHERE clause so that null means "all":
SELECT FName, MName, LName, ID
FROM MyTable
WHERE (ID = @.myID or @.myID is null)
AND myDate BETWEEN @.StartDate AND @.EndDate
I am currently trying to find how to pass a null parameter to a report. If
you know how to pass a null in RS, I would appreciate the feedback.
Thanks.
Randy Howie
--
"Ruud" wrote:
> Hello Jim
> Try to set your "is null" clause in the report settings itself
> Ruud Boots
> Holland
> > SELECT FName, MName, LName, ID
> > FROM MyTable
> > WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> "JIM.H." wrote:
> > Hello,
> > Here is my query for my report. Can you make this query fetch all rows if
> > @.myID parameter is null?
> >
> > SELECT FName, MName, LName, ID
> > FROM MyTable
> > WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> >
> > Thanks,
> > Jim.
> >sql

RETURING XML AS A PARAMETER .NET

Dear programmers,
I'm having real issues with returning data from an output parameter
from a SQL 2005 stored procedure. I've checked the result by applying
the input directly to the stored procedure and all seems to look fine.
Its simply that when the data returns it looks to be in the wrong
format. I'm getting forward slashes and bits and bobs that do not
deserialize because they don't correspond to the original document
stored in the database.
The sp simply takes in an xml document and returns a response xml in
the output parameter. This is returned the xml such as the
following ...
<result provider_reference=\"iTunes\"><items> ...
when this should be ...
"<result provider_reference="iTunes"><items> ...
this might just be because the result encoding is changed when i look
at the result in the immediate window ... but I'm sure I'm not reading
the result out correctly ... I'd really appreciate it if anyone knows
how to correctly read xml from an output parameters. Any advice most
warmly welcomed.
The code fails when I get to the Deserialize section and it returns
with error ""There is an error in XML document (1, 2)."
My code is as below ...
internal ResponseResult PostRequestToDatabase(ServiceRequest
Request, UserCredentials User)
{
////////////////////////////////////////////////////////////////////////////
//////////////////
/// Description: This method posts a request to the
database for verification
/// Created Date: 28th November 2007
/// Created By: T.O'Donnell
////////////////////////////////////////////////////////////////////////////
///////////////////
// create a local string variable to pass in the xml
string xml_posted;
string xml_returned;
// create a new connection to the database
this.DataAccess_Connection = new SqlConnection();
this.DataAccess_Connection.ConnectionString =
GetConnectionString(User);
try
{
// create a new instance of the serialiser and
textwritter objects
XmlSerializer ser = new
XmlSerializer(typeof(ServiceRequest));
StringWriter swriter = new StringWriter();
// write the xml formated classes to the xml
variable
ser.Serialize(swriter, Request);
xml_posted = swriter.ToString();
// create a command object for the storedprocedure
this.DataAccess_Command = new SqlCommand();
this.DataAccess_Command.CommandType =
System.Data.CommandType.StoredProcedure;
this.DataAccess_Command.Connection =
this.DataAccess_Connection;
this.DataAccess_Command.CommandText =
"symin_get_response_result";
// set return parameter
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.xml_request", SqlDbType.Xml)).Value =
xml_posted;
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.xml_result", SqlDbType.Xml, 1)).Direction =
ParameterDirection.Output;
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.return_value", SqlDbType.Int)).Direction =
ParameterDirection.ReturnValue;
// check to see if the connection is still open
if (this.DataAccess_Connection.State ==
ConnectionState.Closed)
{
// open the connection and submit the query
this.DataAccess_Connection.Open();
}
// execute the query
this.DataAccess_Command.ExecuteNonQuery();
// get the return value to check for errors
if
((Int32)this.DataAccess_Command.Parameters["@.return_value"].Value ==
0)
{
// check that the returning xml is not null
if
(this.DataAccess_Command.Parameters["@.xml_result"].Value !=
DBNull.Value)
{
// get the returning xml object
xml_returned =
(string)this.DataAccess_Command.Parameters["@.xml_result"].SqlValue;
// deserialize the results to a class
structure
XmlSerializer Serializer = new
XmlSerializer(typeof(ResponseResult));
StringReader xmlstream = new
StringReader(xml_returned);
XmlTextReader xmlreader = new
XmlTextReader(xmlstream);
// ***** THIS IS WHERE THE ERROR IS
RETURNED
return
(ResponseResult)Serializer.Deserialize(xmlreader);
}
}
// return nothing to the calling party
return null;
}
catch (Exception ee)
{
Console.WriteLine(ee.Message);
return null;
}
finally
{
// check to see if the connection object has been
initialised
if (this.DataAccess_Connection != null)
{
// check to see if the connection is still
open
if (this.DataAccess_Connection.State ==
ConnectionState.Open)
{
// close the Connection
this.DataAccess_Connection.Close();
}
}
}
}The \" is an escaped quotation mark in C#. VS is probably adding that to
the display when you view it, assuming it's enclosing the entire string in
double quotes. I'd think your problem is probably unrelated to this. Try
printing the results directly to Console and see if the \ still appears
before the ".
"Caspian" <timothy.odonnell@.hotmail.com> wrote in message
news:56c69ddc-ef93-4aad-856c-22cf7392e3d4@.u10g2000prn.googlegroups.com...
> Dear programmers,
> I'm having real issues with returning data from an output parameter
> from a SQL 2005 stored procedure. I've checked the result by applying
> the input directly to the stored procedure and all seems to look fine.
> Its simply that when the data returns it looks to be in the wrong
> format. I'm getting forward slashes and bits and bobs that do not
> deserialize because they don't correspond to the original document
> stored in the database.
> The sp simply takes in an xml document and returns a response xml in
> the output parameter. This is returned the xml such as the
> following ...
> <result provider_reference=\"iTunes\"><items> ...
> when this should be ...
> "<result provider_reference="iTunes"><items> ...
> this might just be because the result encoding is changed when i look
> at the result in the immediate window ... but I'm sure I'm not reading
> the result out correctly ... I'd really appreciate it if anyone knows
> how to correctly read xml from an output parameters. Any advice most
> warmly welcomed.
> The code fails when I get to the Deserialize section and it returns
> with error ""There is an error in XML document (1, 2)."
> My code is as below ...
> internal ResponseResult PostRequestToDatabase(ServiceRequest
> Request, UserCredentials User)
> {
> //////////////////////////////////////////////////////////////////////////
////////////////////
> /// Description: This method posts a request to the
> database for verification
> /// Created Date: 28th November 2007
> /// Created By: T.O'Donnell
> //////////////////////////////////////////////////////////////////////////
/////////////////////
> // create a local string variable to pass in the xml
> string xml_posted;
> string xml_returned;
> // create a new connection to the database
> this.DataAccess_Connection = new SqlConnection();
> this.DataAccess_Connection.ConnectionString =
> GetConnectionString(User);
> try
> {
> // create a new instance of the serialiser and
> textwritter objects
> XmlSerializer ser = new
> XmlSerializer(typeof(ServiceRequest));
> StringWriter swriter = new StringWriter();
> // write the xml formated classes to the xml
> variable
> ser.Serialize(swriter, Request);
> xml_posted = swriter.ToString();
> // create a command object for the storedprocedure
> this.DataAccess_Command = new SqlCommand();
> this.DataAccess_Command.CommandType =
> System.Data.CommandType.StoredProcedure;
> this.DataAccess_Command.Connection =
> this.DataAccess_Connection;
> this.DataAccess_Command.CommandText =
> "symin_get_response_result";
> // set return parameter
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.xml_request", SqlDbType.Xml)).Value =
> xml_posted;
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.xml_result", SqlDbType.Xml, 1)).Direction =
> ParameterDirection.Output;
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.return_value", SqlDbType.Int)).Direction =
> ParameterDirection.ReturnValue;
> // check to see if the connection is still open
> if (this.DataAccess_Connection.State ==
> ConnectionState.Closed)
> {
> // open the connection and submit the query
> this.DataAccess_Connection.Open();
> }
> // execute the query
> this.DataAccess_Command.ExecuteNonQuery();
> // get the return value to check for errors
> if
> ((Int32)this.DataAccess_Command.Parameters["@.return_value"].Value ==
> 0)
> {
> // check that the returning xml is not null
> if
> (this.DataAccess_Command.Parameters["@.xml_result"].Value !=
> DBNull.Value)
> {
> // get the returning xml object
> xml_returned =
> (string)this.DataAccess_Command.Parameters["@.xml_result"].SqlValue;
>
> // deserialize the results to a class
> structure
> XmlSerializer Serializer = new
> XmlSerializer(typeof(ResponseResult));
> StringReader xmlstream = new
> StringReader(xml_returned);
> XmlTextReader xmlreader = new
> XmlTextReader(xmlstream);
> // ***** THIS IS WHERE THE ERROR IS
> RETURNED
> return
> (ResponseResult)Serializer.Deserialize(xmlreader);
> }
> }
> // return nothing to the calling party
> return null;
> }
> catch (Exception ee)
> {
> Console.WriteLine(ee.Message);
> return null;
> }
> finally
> {
> // check to see if the connection object has been
> initialised
> if (this.DataAccess_Connection != null)
> {
> // check to see if the connection is still
> open
> if (this.DataAccess_Connection.State ==
> ConnectionState.Open)
> {
> // close the Connection
> this.DataAccess_Connection.Close();
> }
> }
> }
> }
>|||Thanks for your help Mike ... I'd spent so long looking into this
problem that I couldn't see the wood through the trees. As it happens
for future readers, it would appear that the returning XML read just
fine by looking at it from the console. Additionally, this following
line is incorrect ...
xml_returned =3D
(string)this.DataAccess_Command.Parameters["@.xml_result"].SqlValue;
this should read ...
xml_returned =3D
(string)this.DataAccess_Command.Parameters["@.xml_result"].Value;
The problem was quite simply because I'd removed the
[XmlType("result")] above the class that I was trying to deserialize
against (which incidentally used a different name).
Hope others benefit from this code.
Kind regards,
Tim
On Jan 14, 11:54=A0pm, "Mike C#" <x...@.xyz.com> wrote:
> The \" is an escaped quotation mark in C#. =A0VS is probably adding that t=[/color
]
o
> the display when you view it, assuming it's enclosing the entire string in=[/color
]
> double quotes. =A0I'd think your problem is probably unrelated to this. =
=A0Try
> printing the results directly to Console and see if the \ still appears
> before the ".
> "Caspian" <timothy.odonn...@.hotmail.com> wrote in message
> news:56c69ddc-ef93-4aad-856c-22cf7392e3d4@.u10g2000prn.googlegroups.com...
>sql

RETURING XML AS A PARAMETER .NET

Dear programmers,
I'm having real issues with returning data from an output parameter
from a SQL 2005 stored procedure. I've checked the result by applying
the input directly to the stored procedure and all seems to look fine.
Its simply that when the data returns it looks to be in the wrong
format. I'm getting forward slashes and bits and bobs that do not
deserialize because they don't correspond to the original document
stored in the database.
The sp simply takes in an xml document and returns a response xml in
the output parameter. This is returned the xml such as the
following ...
<result provider_reference=\"iTunes\"><items> ...
when this should be ...
"<result provider_reference="iTunes"><items> ...
this might just be because the result encoding is changed when i look
at the result in the immediate window ... but I'm sure I'm not reading
the result out correctly ... I'd really appreciate it if anyone knows
how to correctly read xml from an output parameters. Any advice most
warmly welcomed.
The code fails when I get to the Deserialize section and it returns
with error ""There is an error in XML document (1, 2)."
My code is as below ...
internal ResponseResult PostRequestToDatabase(ServiceRequest
Request, UserCredentials User)
{
//////////////////////////////////////////////////////////////////////////////////////////////
/// Description: This method posts a request to the
database for verification
/// Created Date: 28th November 2007
/// Created By: T.O'Donnell
///////////////////////////////////////////////////////////////////////////////////////////////
// create a local string variable to pass in the xml
string xml_posted;
string xml_returned;
// create a new connection to the database
this.DataAccess_Connection = new SqlConnection();
this.DataAccess_Connection.ConnectionString =
GetConnectionString(User);
try
{
// create a new instance of the serialiser and
textwritter objects
XmlSerializer ser = new
XmlSerializer(typeof(ServiceRequest));
StringWriter swriter = new StringWriter();
// write the xml formated classes to the xml
variable
ser.Serialize(swriter, Request);
xml_posted = swriter.ToString();
// create a command object for the storedprocedure
this.DataAccess_Command = new SqlCommand();
this.DataAccess_Command.CommandType =
System.Data.CommandType.StoredProcedure;
this.DataAccess_Command.Connection =
this.DataAccess_Connection;
this.DataAccess_Command.CommandText =
"sysadmin_get_response_result";
// set return parameter
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.xml_request", SqlDbType.Xml)).Value =
xml_posted;
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.xml_result", SqlDbType.Xml, 1)).Direction =
ParameterDirection.Output;
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.return_value", SqlDbType.Int)).Direction =
ParameterDirection.ReturnValue;
// check to see if the connection is still open
if (this.DataAccess_Connection.State ==
ConnectionState.Closed)
{
// open the connection and submit the query
this.DataAccess_Connection.Open();
}
// execute the query
this.DataAccess_Command.ExecuteNonQuery();
// get the return value to check for errors
if
((Int32)this.DataAccess_Command.Parameters["@.retur n_value"].Value ==
0)
{
// check that the returning xml is not null
if
(this.DataAccess_Command.Parameters["@.xml_result"].Value !=
DBNull.Value)
{
// get the returning xml object
xml_returned =
(string)this.DataAccess_Command.Parameters["@.xml_r esult"].SqlValue;
// deserialize the results to a class
structure
XmlSerializer Serializer = new
XmlSerializer(typeof(ResponseResult));
StringReader xmlstream = new
StringReader(xml_returned);
XmlTextReader xmlreader = new
XmlTextReader(xmlstream);
// ***** THIS IS WHERE THE ERROR IS
RETURNED
return
(ResponseResult)Serializer.Deserialize(xmlreader);
}
}
// return nothing to the calling party
return null;
}
catch (Exception ee)
{
Console.WriteLine(ee.Message);
return null;
}
finally
{
// check to see if the connection object has been
initialised
if (this.DataAccess_Connection != null)
{
// check to see if the connection is still
open
if (this.DataAccess_Connection.State ==
ConnectionState.Open)
{
// close the Connection
this.DataAccess_Connection.Close();
}
}
}
}
The \" is an escaped quotation mark in C#. VS is probably adding that to
the display when you view it, assuming it's enclosing the entire string in
double quotes. I'd think your problem is probably unrelated to this. Try
printing the results directly to Console and see if the \ still appears
before the ".
"Caspian" <timothy.odonnell@.hotmail.com> wrote in message
news:56c69ddc-ef93-4aad-856c-22cf7392e3d4@.u10g2000prn.googlegroups.com...
> Dear programmers,
> I'm having real issues with returning data from an output parameter
> from a SQL 2005 stored procedure. I've checked the result by applying
> the input directly to the stored procedure and all seems to look fine.
> Its simply that when the data returns it looks to be in the wrong
> format. I'm getting forward slashes and bits and bobs that do not
> deserialize because they don't correspond to the original document
> stored in the database.
> The sp simply takes in an xml document and returns a response xml in
> the output parameter. This is returned the xml such as the
> following ...
> <result provider_reference=\"iTunes\"><items> ...
> when this should be ...
> "<result provider_reference="iTunes"><items> ...
> this might just be because the result encoding is changed when i look
> at the result in the immediate window ... but I'm sure I'm not reading
> the result out correctly ... I'd really appreciate it if anyone knows
> how to correctly read xml from an output parameters. Any advice most
> warmly welcomed.
> The code fails when I get to the Deserialize section and it returns
> with error ""There is an error in XML document (1, 2)."
> My code is as below ...
> internal ResponseResult PostRequestToDatabase(ServiceRequest
> Request, UserCredentials User)
> {
> //////////////////////////////////////////////////////////////////////////////////////////////
> /// Description: This method posts a request to the
> database for verification
> /// Created Date: 28th November 2007
> /// Created By: T.O'Donnell
> ///////////////////////////////////////////////////////////////////////////////////////////////
> // create a local string variable to pass in the xml
> string xml_posted;
> string xml_returned;
> // create a new connection to the database
> this.DataAccess_Connection = new SqlConnection();
> this.DataAccess_Connection.ConnectionString =
> GetConnectionString(User);
> try
> {
> // create a new instance of the serialiser and
> textwritter objects
> XmlSerializer ser = new
> XmlSerializer(typeof(ServiceRequest));
> StringWriter swriter = new StringWriter();
> // write the xml formated classes to the xml
> variable
> ser.Serialize(swriter, Request);
> xml_posted = swriter.ToString();
> // create a command object for the storedprocedure
> this.DataAccess_Command = new SqlCommand();
> this.DataAccess_Command.CommandType =
> System.Data.CommandType.StoredProcedure;
> this.DataAccess_Command.Connection =
> this.DataAccess_Connection;
> this.DataAccess_Command.CommandText =
> "sysadmin_get_response_result";
> // set return parameter
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.xml_request", SqlDbType.Xml)).Value =
> xml_posted;
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.xml_result", SqlDbType.Xml, 1)).Direction =
> ParameterDirection.Output;
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.return_value", SqlDbType.Int)).Direction =
> ParameterDirection.ReturnValue;
> // check to see if the connection is still open
> if (this.DataAccess_Connection.State ==
> ConnectionState.Closed)
> {
> // open the connection and submit the query
> this.DataAccess_Connection.Open();
> }
> // execute the query
> this.DataAccess_Command.ExecuteNonQuery();
> // get the return value to check for errors
> if
> ((Int32)this.DataAccess_Command.Parameters["@.retur n_value"].Value ==
> 0)
> {
> // check that the returning xml is not null
> if
> (this.DataAccess_Command.Parameters["@.xml_result"].Value !=
> DBNull.Value)
> {
> // get the returning xml object
> xml_returned =
> (string)this.DataAccess_Command.Parameters["@.xml_r esult"].SqlValue;
>
> // deserialize the results to a class
> structure
> XmlSerializer Serializer = new
> XmlSerializer(typeof(ResponseResult));
> StringReader xmlstream = new
> StringReader(xml_returned);
> XmlTextReader xmlreader = new
> XmlTextReader(xmlstream);
> // ***** THIS IS WHERE THE ERROR IS
> RETURNED
> return
> (ResponseResult)Serializer.Deserialize(xmlreader);
> }
> }
> // return nothing to the calling party
> return null;
> }
> catch (Exception ee)
> {
> Console.WriteLine(ee.Message);
> return null;
> }
> finally
> {
> // check to see if the connection object has been
> initialised
> if (this.DataAccess_Connection != null)
> {
> // check to see if the connection is still
> open
> if (this.DataAccess_Connection.State ==
> ConnectionState.Open)
> {
> // close the Connection
> this.DataAccess_Connection.Close();
> }
> }
> }
> }
>
|||Thanks for your help Mike ... I'd spent so long looking into this
problem that I couldn't see the wood through the trees. As it happens
for future readers, it would appear that the returning XML read just
fine by looking at it from the console. Additionally, this following
line is incorrect ...
xml_returned =
(string)this.DataAccess_Command.Parameters["@.xml_r esult"].SqlValue;
this should read ...
xml_returned =
(string)this.DataAccess_Command.Parameters["@.xml_r esult"].Value;
The problem was quite simply because I'd removed the
[XmlType("result")] above the class that I was trying to deserialize
against (which incidentally used a different name).
Hope others benefit from this code.
Kind regards,
Tim
On Jan 14, 11:54Xpm, "Mike C#" <x...@.xyz.com> wrote:
> The \" is an escaped quotation mark in C#. XVS is probably adding that to
> the display when you view it, assuming it's enclosing the entire string in
> double quotes. XI'd think your problem is probably unrelated to this. XTry
> printing the results directly to Console and see if the \ still appears
> before the ".
> "Caspian" <timothy.odonn...@.hotmail.com> wrote in message
> news:56c69ddc-ef93-4aad-856c-22cf7392e3d4@.u10g2000prn.googlegroups.com...
>

Retrieving user defined Role name

Is there a System stored procedure that gives me the Role in which a user is in. For example I execute this procedure, give the user as parameter an that gives me back the Role the user is in. It has to be said that this is a user defined role, I got three of them, HR, Employee, Approver.

Greetings,
GodofredoIs it not clear? I just want to retrieve a user defined Role

Greets,
geoff|||I'd use sp_helpuser (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_help_45o2.asp).

-PatP

Retrieving url parameters

I have a 2 reports - Product Master and Product Details. I have the ProductID parameter being passed properly from the Master via the URL to the Details Report, but the Details report doesn't seem to be picking up the paramater from the URL and keeps prompting for the ProductID.

What settings should I be configuring in my Details report to say - "Get the parameter from the URL"?

I've tried multiple configs within the Report Parameters dialog box but I seem to be missing something...

?

In the first report, on the navigation tab for the textbox:

Choose the Report action, choose a report or enter a path to the report and then click the parameters button. Provide the values you want. you can use an expression here which is something like Parameters!ParameterName.Value which will pass to the drill-through report the same value as was passed to the original report.

Hope that helps,

-Lukasz

Retrieving selected gridview column values for SQLDatasource asp:controlparameters

Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@." at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005):

<

asp:GridViewID="gvLoadEditProductPrices"runat="server"AutoGenerateColumns="False"AllowSorting="True"DataSourceID="SqlDataSource1"DataKeyNames="ProductID"><Columns><asp:CommandFieldShowEditButton="True"/><asp:BoundFieldDataField="ProductID"HeaderText="ProductID"HeaderStyle-BackColor="white"InsertVisible="False"ReadOnly="True"SortExpression="ProductID"/><asp:BoundFieldDataField="Product"HeaderText="Product"SortExpression="Product"ReadOnly="True"/><asp:BoundFieldDataField="ProductCat"HeaderText="ProductCat"SortExpression="ProductCat"ReadOnly="True"/><asp:BoundFieldDataField="VarRate"HeaderText="VarRate"SortExpression="VarRate"/><asp:BoundFieldDataField="loadid"HeaderText="loadid"InsertVisible="False"ReadOnly="True"SortExpression="loadid"/><asp:BoundFieldDataField="loadamount"HeaderText="loadamount"SortExpression="loadamount"ReadOnly="True"/><asp:BoundFieldDataField="ProductCol"HeaderText="ProductCol"SortExpression="ProductCol"ReadOnly="True"/><asp:BoundFieldDataField="PageID"HeaderText="PageID"SortExpression="PageID"ReadOnly="True"/></Columns></asp:GridView>

and the sqldatasource's info:

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>"ProviderName="System.Data.SqlClient"SelectCommand="spGetLoadEditProductPrices"SelectCommandType="StoredProcedure"UpdateCommand="spUpdateProductPrices"UpdateCommandType="StoredProcedure"><UpdateParameters><asp:ControlParameterName="ProductID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter><asp:ControlParameterName="LoadID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter><asp:ControlParameterName="PageID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter><asp:ControlParameterName="ProductCol"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter><asp:ControlParameterName="NewRate"Type="Double"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter></UpdateParameters><SelectParameters><asp:ControlParameterControlID="ddlEstLoadsPerAcre"Name="LoadID"PropertyName="SelectedValue"Type="Int32"/><asp:ControlParameterControlID="txtEditType"Name="PageName"PropertyName="Text"Type="String"/></SelectParameters></asp:SqlDataSource>

TIA,

John

Nevermind...after hours of testing many different combinations and scenarios, I found that I had to add each field that the control parameter needs to reference in the gridview to the "DataKeyNames" property in the gridview. But, any field I wanted to be updated thru the gridview edit had to use the PropertyName="SelectedValue" as opposed to the PropertyName="SelectedDataKey.Values('fieldname')". I also had to use single quotes for the field name.

John

Retrieving Parameter List for a Stored Procedure

Is there a way to retrieve the parameter list for a given stored procedure?

I am trying to create a program that will autogenerate a list of stored procedures and their parameters so that changes to the database can be accurately reflected in code.

Thanks,
Allen K.sp_sproc_columns @.Procedure_Name='procedurename'

Column_Name : Type_Name : Precision : Length : Scale : Is_Nullable

Retrieving output parameter from stored proc

I have difficulty reading back the value of an output parameter that I use in a stored procedure. I searched through other posts and found that this is quite a common problem but couldn't find an answer to it. Maybe now there is a knowledgeable person who could help out many people with a good answer.

The problem is that

cmd.Parameters["@.UserExists"].Value evaluates to null. If I call the stored procedure externally from the Server Management Studio Express everything works fine.

Here is my code:

using (SqlConnection cn =new SqlConnection(this.ConnectionString)){ SqlCommand cmd =new SqlCommand("mys_ExistsPersonWithUserName", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@.UserName", SqlDbType.VarChar).Value = userName; cmd.Parameters.Add("@.UserExists", SqlDbType.Int); cmd.Parameters["@.UserExists"].Direction = ParameterDirection.Output; cn.Open();int x = (int)cmd.Parameters["@.UserExists"].Value; cn.Close();return (x>1);}

And the corresponding stored procedure:

ALTER PROCEDURE dbo.mys_Spieler_ExistsPersonWithUserName (@.UserNamevarchar(16),@.UserExistsint OUTPUT)ASSET NOCOUNT ONSELECT @.UserExists =count(*)FROM mys_ProfilesWHERE UserName = @.UserNameRETURN

Hey,

Not sure what the problem is, but as an alternative, you can use a return method and define it as return value, or you can just select the result, and in your code, use ExecuteScalar().

sql

Retrieving multiple values from stored procedure: parameter and recordset

Hello,

The problem below appears to be a very common problem, but despite having tried every solution I can find offered online, the solution continues to elude me. Any tips you could offer would bemuchappreciated.

Problem: I'm successfully retrieving a recordset from a SQL Server 2000 stored procedure, but don't know how to retrieve an Output Parameter as well.

Components:

a simple Web Form with a Text Box (txtOutput) and a GridView (dgOutput). (Visual Web Developer Express 2005)
a simple stored procedure which - when executed in the Studio Output window - returns the correct output.Running [dbo].[usp_Test] ( @.Region = <NULL>, @.StartDate = <NULL> ).

State Total
-------- ----
ACT 27
NSW 26
NT 6
QLD 20
SA 44
TAS 17
VIC 28
WA 48
No rows affected.
(8 row(s) returned)
@.StartDate = 27/11/2006
@.RETURN_VALUE = 0
Finished running [dbo].[usp_Test].
the code behind the form, to call the stored proc and bind the grid to the output of the stored proc. 'Already declared and populated strConn and strSelect
Dim conn As New SqlConnection(strConn)
Dim storedProc As New SqlCommand
With storedProc
.CommandType = CommandType.StoredProcedure
.CommandText = strSelect
.Connection = conn
End With
Dim returnParam As SqlParameter
returnParam = New SqlParameter("@.StartDate", SqlDbType.DateTime, 30)
returnParam.Direction = ParameterDirection.Output
storedProc.Parameters.Add(returnParam)

Dim dr As SqlClient.SqlDataReader
conn.Open()
dr = storedProc.ExecuteReader
dgOutput.DataSource = dr
dgOutput.DataBind()
dr.Close()
conn.Close()

Situation:

the GridView successfully displays the 8-row recordsetI don't know how to display the Output Parameter ("@.StartDate") in the Text Box. Without success I've tried variations on:
txtOutput.Text = storedProc.Parameters("@.StartDate").Value.ToStringtxtOutput.Text = returnParam.Value.ToStringusing dr.NextResultIf possible, I would like to be able to display the 'number of row(s) returned', which is already being returned (as printed above).

Any assistance would be sincerely appreciated - it's been a long day of trying. Thank you for your time.

- Sarah

You need to declare the @.startDate as OUTPUT parameter in the stored proc. You could also return the rowcount but that would have to be another OUTPUT parameter.

CREATE PROC dbo.usp_test

@.StartDate datetime OUTPUT,

@.rowcount int OUTPUT

AS

BEGIN

--Your SELECT statement here

SELECT ....

END

Here's how you could call an OUTPUT parameter:

'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.StartDate"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.datetime
mycommand.Parameters.Add(myParam)

Dim startDate as DateTime

startDate = mycommand.Parameters("@.StartDate").Value)

|||

Hi Dinakar,

Thank you for looking at my problem Unfortunately the same problem is still happening.

The stored proc is already returning the output parameter with the correct value of27/11/2006(as shown in the output in my first post), but any time I try to retrieve it, it seems to have a null value.

In my VB code, I've changed my parameter creation to match your suggested code.

When I use the new DateTime variable as you suggest, and set it tostoredProc.Parameters("@.StartDate").Value, it's value is set to 1/01/0001 12:00:00 AM .

Any suggestions?

Thank you again for your time.

|||Can you post your stored proc code?|||

CREATE PROCEDURE usp_Test(
@.Region AS VARCHAR(8) = NULL, --Optional
@.StartDate DATETIME OUTPUT
)
AS

BEGIN
SET @.StartDate =DATEADD(Week, DATEDIFF(Week,0,GETDATE()), 0) --Monday of current week

SELECT Loc.LocationState AS State,
COUNT(Appt.ApartmentID) AS Total
FROM Apartment AS Appt
LEFT JOIN ReportLocation AS Loc ON Appt.LocationID = Loc.LocationID
WHERE (( Appt.InsertedOn >= @.StartDate) AND
(Appt.InsertedOn <= GETDATE()) AND
((@.Region IS NULL) OR (Loc.LocationState LIKE @.Region)) AND
(Appt.isBreak = 0) AND
(Appt.isBlock = 0) AND
(Appt.isCancelled = 0)
)
GROUP BY Loc.LocationState
ORDER BY Loc.LocationState
END
GO

|||Is there anything else it might be? I'm happy to post any of my code, which might expose the cause.|||

Allright, after a long long time, I wrote up a quick windows app. I used a datagridview and called a stored proc which returns a result set through a SELECT statement. The stored proc also has an OUTPUT parameter. I was able to retrieve both.

alter proc dbo.test_dk @.fileidint, @.rowcountint output as begin select fileid, orderid, creationdatefrom servicefilewhere fileid = @.fileidselect @.rowcount =@.@.rowcountend

And the code for the windows app is:

Dim objconAs SqlConnection objcon =New SqlConnection("server=...")Try If objcon.State = 0Then objcon.Open()Dim dsAs New DataSetDim daAs SqlDataAdapter =New SqlDataAdapter da.SelectCommand =New SqlCommand da.SelectCommand.CommandType = CommandType.StoredProcedure da.SelectCommand.CommandText ="test_dk"Dim sqlparamAs New SqlParameter("@.fileid", SqlDbType.Int) sqlparam.Value = 7035833Dim sqlparam2As New SqlParameter("@.rowcount", SqlDbType.Int) sqlparam2.Direction = ParameterDirection.Output da.SelectCommand.Parameters.Add(sqlparam) da.SelectCommand.Parameters.Add(sqlparam2) da.SelectCommand.Connection = objcon da.Fill(ds,"servicefile") DataGridView1.DataSource = ds DataGridView1.DataMember ="servicefile" TextBox1.Text = da.SelectCommand.Parameters("@.rowcount").ValueCatch excAs Exception MsgBox(exc)Finally If objcon.State = ConnectionState.OpenThen objcon.Close()End If'objCon.Dispose()End Try

|||

That worked beautifully - you're a champion, Dinakar! Some day they'll sing songs about you.

Thank you very much for your time and help.

|||You are welcome.|||

ndinakar:

You need to declare the @.startDate as OUTPUT parameter in the stored proc. You could also return the rowcount but that would have to be another OUTPUT parameter.

CREATE PROC dbo.usp_test

@.StartDate datetime OUTPUT,

@.rowcount int OUTPUT

AS

BEGIN

--Your SELECT statement here

SELECT ....

END

Here's how you could call an OUTPUT parameter:

'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.StartDate"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.datetime
mycommand.Parameters.Add(myParam)

Dim startDate as DateTime

startDate = mycommand.Parameters("@.StartDate").Value)

|||

I'm trying to follow your example but on this line..

Your line

TextBox1.Text = da.SelectCommand.Parameters("@.rowcount").Value

My line

lbl_ERROR.Text = da.SelectCommand.Parameters("@.RETURNError").Value;

I get a build error.. Error 1 'System.Data.SqlClient.SqlCommand.Parameters' is a 'property' but is used like a 'method'

sql

Retrieve output value from store procedure

I'm trying to use a store procedure to add an item into the database and retrieve the id of such item within and output parameter but this is not happening.

The item is added into the db but the output param is not modified.

Here is the code:

SP

CREATE procedure dbo.AddItem
(
@.Desc nvarchar(100),
@.intItemID int output
)
as
insert into RR_Item (
desc
)
values (
@.Desc
)

select @.intItemID = SCOPE_IDENTITY()
GO

I have tried in the last line of the SP

select @.intItemID = SCOPE_IDENTITY()

select @.intItemID = @.@.IDENTITY

select @.intItemID = max(itemid) from RR_Item

but nothing seems to work.

I'm calling the store procedure as follows from asp.net:

Dim intItemIDAsNew SqlParameter("@.intItemID", SqlDbType.Int)

intItemID.Direction = ParameterDirection.Output

SqlHelper.ExecuteDataset(objConn.ConnectionString,"AddItem", desc, intItemID)

MsgBox(intItemID.Value.ToString)

Any help would be appreciated.

Thanks

(1) Use SCOPE_IDENTITY().

(2) check the 2nd portion ofthis articleto retrieve the OUTPUT value.

Retrieve output parameter from stored procedure

Hi,

I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error

An SqlParameter with ParameterName '@.slideshowid' is not contained by this SqlParameterCollection

code as follows:

int publicationId = (int) Session["PublicationId"];
string title = txtTitle.Text;
int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);
SqlConnection myConnection =new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand =new SqlCommand("sp_be_addSlideshow", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add(new SqlParameter("@.publicationid", SqlDbType.Int));
myCommand.Parameters["@.publicationid"].Value = publicationId;
myCommand.Parameters.Add(new SqlParameter("@.title", SqlDbType.NVarChar));
myCommand.Parameters["@.title"].Value = title;
myCommand.Parameters.Add(new SqlParameter("@.categoryid", SqlDbType.Int));
myCommand.Parameters["@.categoryid"].Value = categoryid;
myConnection.Open();
myCommand.ExecuteNonQuery();
string slideshowId = myCommand.Parameters["@.slideshowid"].Value.ToString();
myConnection.Close();

my stored procedure:

CREATE PROCEDURE sp_be_addSlideshow
(
@.publicationid int,
@.title nvarchar(50),
@.categoryid int,
@.slideshowid int = NULL OUTPUT
)
AS

INSERT INTO
Slideshow(publicationid,title,slideshowcategoryid,deleted)

VALUES
(@.publicationid,@.title,@.categoryid,0)

SELECT @.slideshowid = SCOPE_IDENTITY()
GO

Supergirl_gem,

You need to specify to your command that that parameter is an output param. Try this.

myCommand.Parameters.Add("@.slideshowid", SqlDbType.Int);
myCommand.Parameters["@.slideshowid"].Direction = ParameterDirection.Output;

Then after the execute, you can set the id

string slideshowId = myCommand.Parameters["@.slideshowid"].Value.ToString();

Hope this helps. If you are still having problems. Please let me know.

Regards.