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 always 0.

I am trying to find out why my return from my ASP.Net page is always 0.
I have the following code:
****************************************
************
Dim objCmd as New SqlCommand("AddNewResumeCoverTemplate",objConn)
objCmd.CommandType = CommandType.StoredProcedure
objCmd.parameters.add("@.ClientID",SqldbType.VarChar,20).value =
session("ClientID")
objCmd.parameters.add("@.Email",SqlDbType.VarChar).value = session("Email")
objCmd.parameters.add("@.ResumeTitle",SqlDbType.VarChar,45).value =
ResumeTitle.Text
objCmd.parameters.add("@.Resume",SqlDbType.text).value = ResumeText.Text
objCmd.parameters.add("@.CoverLetterTitle",SqlDbType.VarChar,45).value =
ResumeTitle.Text
objCmd.parameters.add("@.CoverLetter",SqlDbType.text).value =
CoverLetter.Text
objCmd.Parameters.Add("@.errorCode", SqlDbType.Int).Direction =
ParameterDirection.Output
objConn.Open()
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("@.errorCode").Value))
****************************************
************************************
*
The stored procedure essentially looks like:
****************************************
************************************
*
CREATE PROCEDURE AddNewResumeCoverTemplate
(
@.ClientID varChar(20),@.Email varChar(45),@.ResumeTitle varChar(45),@.Resume
text,@.CoverLetterTitle varChar(45),@.CoverLetter text,@.errorCode int Output
)
AS
...
select @.errorCode = 1
return @.errorCode
GO
****************************************
************************************
**
I put the select statement there just to force @.errorCode to be 1.
But my pages trace.warn is showing it as 0 (always).
Do I have it set up correctly?
Thanks,
Tomtshad wrote:
> I am trying to find out why my return from my ASP.Net page is always
> 0.
> I have the following code:
> ****************************************
************
> Dim objCmd as New SqlCommand("AddNewResumeCoverTemplate",objConn)
> objCmd.CommandType = CommandType.StoredProcedure
> objCmd.parameters.add("@.ClientID",SqldbType.VarChar,20).value =
> session("ClientID")
> objCmd.parameters.add("@.Email",SqlDbType.VarChar).value =
> session("Email")
> objCmd.parameters.add("@.ResumeTitle",SqlDbType.VarChar,45).value =
> ResumeTitle.Text
> objCmd.parameters.add("@.Resume",SqlDbType.text).value =
> ResumeText.Text
> objCmd.parameters.add("@.CoverLetterTitle",SqlDbType.VarChar,45).value
> = ResumeTitle.Text
> objCmd.parameters.add("@.CoverLetter",SqlDbType.text).value =
> CoverLetter.Text objCmd.Parameters.Add("@.errorCode",
> SqlDbType.Int).Direction = ParameterDirection.Output
> objConn.Open()
> trace.warn("Error return = " &
> Convert.ToInt32(objCmd.Parameters("@.errorCode").Value))
> ****************************************
**********************************
***
> The stored procedure essentially looks like:
> ****************************************
**********************************
***
> CREATE PROCEDURE AddNewResumeCoverTemplate
> (
> @.ClientID varChar(20),@.Email varChar(45),@.ResumeTitle
> varChar(45),@.Resume text,@.CoverLetterTitle varChar(45),@.CoverLetter
> text,@.errorCode int Output )
> AS
> ...
> select @.errorCode = 1
> return @.errorCode
> GO
> ****************************************
**********************************
****
> I put the select statement there just to force @.errorCode to be 1.
> But my pages trace.warn is showing it as 0 (always).
> Do I have it set up correctly?
> Thanks,
> Tom
Return types and parameters are two different things. You are not
declaring a return type from your .Net code. However, I would think the
output parameter, as you defined it, should be coming back correctly. In
order to use the @.errorCode as a return value, you don't want to declare
it in the procedure as a parameter. From the ADO.Net code, you define
the return value using the ParameterDirection = ReturnValue.
For example from MSDN:
Dim PubsConn As SqlConnection = New SqlConnection & _
("Data Source=server;integrated security=sspi;" & _
"initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand & _
("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add ("RetValue",
SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add ("@.au_idIN",
SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add
("@.numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eW6TjTvYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> tshad wrote:
> Return types and parameters are two different things. You are not
> declaring a return type from your .Net code. However, I would think the
> output parameter, as you defined it, should be coming back correctly. In
> order to use the @.errorCode as a return value, you don't want to declare
> it in the procedure as a parameter. From the ADO.Net code, you define the
> return value using the ParameterDirection = ReturnValue.
> For example from MSDN:
> Dim PubsConn As SqlConnection = New SqlConnection & _
> ("Data Source=server;integrated security=sspi;" & _
> "initial Catalog=pubs;")
> Dim testCMD As SqlCommand = New SqlCommand & _
> ("TestProcedure", PubsConn)
> testCMD.CommandType = CommandType.StoredProcedure
> Dim RetValue As SqlParameter = testCMD.Parameters.Add ("RetValue",
> SqlDbType.Int)
> RetValue.Direction = ParameterDirection.ReturnValue
> Dim auIDIN As SqlParameter = testCMD.Parameters.Add ("@.au_idIN",
> SqlDbType.VarChar, 11)
> auIDIN.Direction = ParameterDirection.Input
> Dim NumTitles As SqlParameter = testCMD.Parameters.Add ("@.numtitlesout",
> SqlDbType.Int)
> NumTitles.Direction = ParameterDirection.Output
> auIDIN.Value = "213-46-8915"
> PubsConn.Open()
> Dim myReader As SqlDataReader = testCMD.ExecuteReader()
> Console.WriteLine("Book Titles for this Author:")
> Do While myReader.Read
> Console.WriteLine("{0}", myReader.GetString(2))
> Loop
> myReader.Close()
> Console.WriteLine("Return Value: " & (RetValue.Value))
> Console.WriteLine("Number of Records: " & (NumTitles.Value))
>
Still doesn't seem to work.
I changed the asp.net code as so:
objCmd.parameters.add("@.CoverLetter",SqlDbType.text).value =
CoverLetter.Text
objCmd.Parameters.Add("@.errorCode", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue
and the Stored Procedure as:
CREATE PROCEDURE AddNewResumeCoverTemplate
(
@.ClientID varChar(20),@.Email varChar(45),@.ResumeTitle varChar(45),@.Resume
text,@.CoverLetterTitle varChar(45),@.CoverLetter text
)
AS
declare @.errorCode int
...
select @.errorCode = 1
return @.errorCode
I am still getting back a value of 0.
Tom|||tshad wrote:
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:eW6TjTvYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> Still doesn't seem to work.
> I changed the asp.net code as so:
> objCmd.parameters.add("@.CoverLetter",SqlDbType.text).value =
> CoverLetter.Text
> objCmd.Parameters.Add("@.errorCode", SqlDbType.Int).Direction =
> ParameterDirection.ReturnValue
> and the Stored Procedure as:
> CREATE PROCEDURE AddNewResumeCoverTemplate
> (
> @.ClientID varChar(20),@.Email varChar(45),@.ResumeTitle
> varChar(45),@.Resume text,@.CoverLetterTitle varChar(45),@.CoverLetter
> text )
> AS
> declare @.errorCode int
> ...
> select @.errorCode = 1
> return @.errorCode
> I am still getting back a value of 0.
> Tom
Try removing the @. prefix on the return value in the code and see what
happens.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23Om7HqwYFHA.3032@.TK2MSFTNGP10.phx.gbl...
> tshad wrote:
> Try removing the @. prefix on the return value in the code and see what
> happens.
>
I changed it to:
objCmd.Parameters.Add("errorCode", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue
objConn.Open()
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("errorCode").Value))
But still get 0 back.
Tom
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23iFl1vwYFHA.2076@.TK2MSFTNGP15.phx.gbl...
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23Om7HqwYFHA.3032@.TK2MSFTNGP10.phx.gbl...
Your solution is exactly as shown in my asp.net book
I even tried to use the "ReturnValue", as they show and did the following:
objCmd.Parameters.Add("ReturnValue", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue
objConn.Open()
Dim applicantReader = objCmd.ExecuteReader
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
if applicantReader.Read then
if applicantReader("ResumeID") is DBNull.Value then
trace.warn("ResumeID = nothing")
else
trace.warn("ResumeID <> nothing")
end if
end if
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
I realized that the trace.warn was in the wrong place and moved it after the
applicantReader.Read if statement.
But I still got a 0.
Very confusing.
Tom
> I changed it to:
> objCmd.Parameters.Add("errorCode", SqlDbType.Int).Direction =
> ParameterDirection.ReturnValue
> objConn.Open()
> trace.warn("Error return = " &
> Convert.ToInt32(objCmd.Parameters("errorCode").Value))
> But still get 0 back.
> Tom
>|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23iFl1vwYFHA.2076@.TK2MSFTNGP15.phx.gbl...
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23Om7HqwYFHA.3032@.TK2MSFTNGP10.phx.gbl...
> I changed it to:
> objCmd.Parameters.Add("errorCode", SqlDbType.Int).Direction =
> ParameterDirection.ReturnValue
> objConn.Open()
> trace.warn("Error return = " &
> Convert.ToInt32(objCmd.Parameters("errorCode").Value))
> But still get 0 back.
Ok.
I got it to work, bu changing it from a Reader to ExecuteNonQuery.
objCmd.Parameters.Add("ReturnValue", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue
objConn.Open()
objCmd.ExecuteNonQuery()
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
Now I am getting a 1 back.
In this case, I am not passing back any data (just my return value).
But even with a DataReader, I still need to get the return value if there is
no Data passed back (as in this case). So how do I get the Return value if
this is the case?
Thanks,
Tom|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ezmd28wYFHA.2756@.tk2msftngp13.phx.gbl...
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23iFl1vwYFHA.2076@.TK2MSFTNGP15.phx.gbl...
> Ok.
> I got it to work, bu changing it from a Reader to ExecuteNonQuery.
> objCmd.Parameters.Add("ReturnValue", SqlDbType.Int).Direction =
> ParameterDirection.ReturnValue
> objConn.Open()
> objCmd.ExecuteNonQuery()
> trace.warn("Error return = " &
> Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
> Now I am getting a 1 back.
> In this case, I am not passing back any data (just my return value).
> But even with a DataReader, I still need to get the return value if there
> is no Data passed back (as in this case). So how do I get the Return
> value if this is the case?
>
I remember someone mentioning before that a DataReader had to read to the
end of the data before it got the return value. So I changed the DataReader
code to:
while applicantReader.Read()
if applicantReader("ResumeID") is DBNull.Value then
trace.warn("ResumeID = nothing")
trace.warn("ResumeID = " & applicantReader("ResumeID") & "
CoverLetterID = " & applicantReader("CoverLetterID"))
else
trace.warn("ResumeID <> nothing")
end if
trace.warn("inside read ResumeID = " & applicantReader("ResumeID") & "
CoverLetterID = " & applicantReader("CoverLetterID"))
end while
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
I just changed the "if" to a "while", but I am still getting 0 back.
I know it is sending a 1 back since I do get that if I use an
"ExecuteNonQuery".
Tom

> Thanks,
> Tom
>|||Before retrieving output parameters or the return code, you need to invoke
the NextResult method after retrieving resultset(s):
applicantReader.NextResult()
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
Hope this helps.
Dan Guzman
SQL Server MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OFH9QKxYFHA.3572@.TK2MSFTNGP12.phx.gbl...
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ezmd28wYFHA.2756@.tk2msftngp13.phx.gbl...
> I remember someone mentioning before that a DataReader had to read to the
> end of the data before it got the return value. So I changed the
> DataReader code to:
> while applicantReader.Read()
> if applicantReader("ResumeID") is DBNull.Value then
> trace.warn("ResumeID = nothing")
> trace.warn("ResumeID = " & applicantReader("ResumeID") & "
> CoverLetterID = " & applicantReader("CoverLetterID"))
> else
> trace.warn("ResumeID <> nothing")
> end if
> trace.warn("inside read ResumeID = " & applicantReader("ResumeID") & "
> CoverLetterID = " & applicantReader("CoverLetterID"))
> end while
> trace.warn("Error return = " &
> Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
> I just changed the "if" to a "while", but I am still getting 0 back.
> I know it is sending a 1 back since I do get that if I use an
> "ExecuteNonQuery".
> Tom
>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eQ0WxXyYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> Before retrieving output parameters or the return code, you need to invoke
> the NextResult method after retrieving resultset(s):
> applicantReader.NextResult()
> trace.warn("Error return = " &
> Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
I'm here.
I thought that NextResult() gets you the next set if you are doing multiple
selects and expecting multiple results?
Does this mean that you really need to always do a NextResult after getting
your results to make sure you get the return value (if there was one)?
What about if you just to a databind()? Would you still need to do a
NextResult() to get the return value?
Also, just want to make sure I understand, if there is no more result sets
and no return value, wouldn't a NextResult() give you an error?
Thanks,
Tom
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:OFH9QKxYFHA.3572@.TK2MSFTNGP12.phx.gbl...
>

return a value from SQL server SP back to .net

I have a SP code:
select 'nothing' from tableA where userID = '123'
if @.@.rowcount = 0
return 0
else
return 1

.net code:
Dim myConnection As New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")
Dim myCommand As New SqlCommand("StoreP", myConnection)

myCommand.Connection.Open()
Returnvalue = myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

Returnvalue shows -1, it doesnt show the return value from SP. how do I fix this problem? thanksHi,

The return value from the ExecuteNonQuery returns the number of rows affected by the query, not the return value from the stored procedure. For a SELECT statement like you're using, it always returns -1.

To get the return value, you have to add a parameter to the ADO.NET command object, and set its Direction property to ReturnValue. Something like this:

myCommand.Parameters.Add("@.RetVal", SqlType.Integer).Direction = _
 ParameterDirection.ReturnValue

Then you can read the value after you run the query:

Dim i as Int32 = myCommand.Parameters("@.RetVal").Value

I've typed the code from memory, so it may need some tweaking.

Don|||I still have two questions
1. If I want to return 2 value from SP to asp.net, how do I do?
2. when I want to insert same value into PK twice in SP, it will give me a error message 2627, and the code break. How do I do to let SP return me an error message WITHOUT hanging the code. In other word, I am trying to let SP return the error message, but I dont want the asp.net web page to stop.

Thank you|||Hi,

1. If I want to return 2 value from SP to asp.net, how do I do?

Then use output parameters. You can define as many of those as you want. For them, use ParameterDirection.Output for the Direction property.

2. when I want to insert same value into PK twice in SP, it will give me a error message 2627, and the code break. How do I do to let SP return me an error message WITHOUT hanging the code. In other word, I am trying to let SP return the error message, but I dont want the asp.net web page to stop.

Probably the best way is to raise an error from the SP using the RAISERROR statement. That will generate a SqlException that you can catch and handle in your page.

Another way is to return one or more output parameters from the SP, one for an error number and another for a message. I don't like this option because it's more work and doesn't hook into the natural exception infrastructure of .NET.

Don|||Can you tell me how exactly you do it? I try parameterdirection.output it gives me an error "too many argument specified." how do I code in SP to return 2 values? thank you|||It sounds like you haven't added the output parameters to the stored procedure, right? You have to do that as well as add the ADO.NET code.

Post the complete sp definition and we'll help you make the changes.

Don|||the SP code is

CREATE PROCEDURE test @.aaa as varchar(10) output, @.bbb as varchar(10) output AS
select @.aaa = '111'
select @.bbb = '222'
return
GO

asp.net code is

Dim objCOmmand As New SqlCommand(strSQL, objConnection)
objConnection.Open()
objCOmmand.CommandType = CommandType.StoredProcedure
objCOmmand.Parameters.Add(New SqlParameter("@.aaa", SqlDbType.VarChar))
objCOmmand.Parameters.Add("@.aaa", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
objCOmmand.Parameters("@.aaa").Value = "aaa"
objCOmmand.Parameters.Add(New SqlParameter("@.bbb", SqlDbType.VarChar))
objCOmmand.Parameters.Add("@.bbb", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
objCOmmand.Parameters("@.bbb").Value = "bbb"
objCOmmand.ExecuteNonQuery()
Label1.Text = objCOmmand.Parameters("@.aaa").Value
Label2.Text = objCOmmand.Parameters("@.bbb").Value
objConnection.Close()

after SP I should get 111 in stead of aaa in @.aaa and 222 instead of bbb in @.bbb, but I get aaa and bbb in the result. how do I get the value return from SP?|||Your are not using the correct ParameterDirection. ReturnValue is solely to return the value that appears after the RETURN keyword in your stored procedure. Valid ParameterDirection values for stored procedure parameters are:
Input
Output
InputOutput

In your case, you should be using InputOutput for @.aaa and @.bbb since you are supplying data to the stored procedure (input) and are new receiving data back from the stored procedure (output).

Terri|||I try:

objCOmmand.Parameters.Add("@.bbb", SqlDbType.VarChar).Direction = ParameterDirection.InputOutput

but it gives me an error:

Parameter 1: '@.aaa' of type: String, the property Size has an invalid size: 0

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Parameter 1: '@.aaa' of type: String, the property Size has an invalid size: 0|||Since you are using a VarChar datatype, you need to specify the length. And I don't know if it matters, but I usually take 2 lines to add a parameter and set the direction:

objCommand.Parameters.Add("@.aaa", SqlDbType.VarChar, 10)
objCommand.Parameters("@.aaa").Direction=ParameterDirection.InputOutput
objCommand.Parameters.Add("@.bbb", SqlDbType.VarChar, 10)
objCommand.Parameters("@.bbb").Direction=ParameterDirection.InputOutput

Terri

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 UDT data in non .NET languages. ?

I have been thinking of using SQL server 2005 as i would like the flexibility i get through UDT. Retrieving the UDT data in managed could is ok but i would like to retrieve it in non .NET languages too.

For example lets say i create a UDT "Point". I insert data in a table that has some columns of type point.

Now is there a way i can get the data of the type point in a point object in non .NET languages like perl, python...

Hi,

There's no special magic to doing this. If the UDT is defined using user-defined serialization, then you know the serialization format. You could pull the bytes out of the database and write a deserializer to a Python class, a Perl class, or whatever else you want. Obviously, you'll have to be careful to make sure the types align. E.g., ints shouldn't be too problematic, but I'm not sure how Python stores a datetime.

If the UDT is using native serialization, you could try to reverse engineer the serialization format. This is not supported, and you'll have to be a bit careful: SQL Server does some funky bit twiddling to preserve binary ordering (see here).

Hope this helps,
-Isaac

retrieving SQL Server roles and permissions

Hi,

I am developping an application using Windows forms(C#.net) and SQL Server 2005 Express edition. I would like to use SQL Server authentication. This is what I would like my application to do:

When a user logs in and is authenticated by SQL Server, the application to be able read the user's permissions/rights from SQL Server and use them to restrict to access what the user can do in the application. I have gone through lots of articles but all articles talk either of security in the .net environment or SQL Server security. None talks about integrating database security with application security.

Any leads will be appreciated.

Jakiiki

Hi Jakiiki,

Applications connect to SQL Server either via SQL Authentication or Windows Authentication. Once Autheticated the Applications identity in SQL Server is determined and access to resources is determined by the permissions the identity possess.

You can retrieve permission and role information about a user from sql server's catalog views.

sys.server_permissions and sys.database_permissions will list out all of the permissions granted to sql logins and sql user respectively.

sys.server_principals and sys.database_principals will list the sql logins for the instance and the sql users for the current database.

database and server roles will be listed in sys.database_principals and sys.server_principals

membership in these roles is tracked in the catalog view sys.database_role_members and sys.server_role_members

HTH,

-Steven Gott

SDE/T

SQL Server

sql

Retrieving Return value from stored procedure declaratively

Hi.

I have a stored procedure "sp1" which returns a value (with the sql statement Return @.ReturnValue).

Is it possible for my asp.net page to retrieve this return value, and to do it declaratively (meaning without writing code to connect to the database in the code behind). If it is possible to do it like this please tell me how, and if not please tell me how to do it with code.

Thanks in advance .

i do not know what will you sp return but i suppose that it is and INT

so you write this way;

int retrunvalue=sqlcommad.excutenonequery();

so the returned value will be passed to you int.

hope this will help

|||

this is sample code, it can help you:

Here is a sample sproc that populates output parameters
from the Northwind Products table:

CREATE PROCEDURE CustOrderOne
@.CustomerID nchar(5),
@.ProductName varchar(50) output,
@.Quantity int output

AS
SELECT TOP 1 @.ProductName=PRODUCTNAME, @.Quantity =quantity
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @.CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

And here is an example of some C# code to return and display the output parameters:

using System;
using System.Data;
using System.Data.SqlClient;
namespace OutPutParms
{
class OutputParams
{
[STAThread]
static void Main(string[] args)
{
using(SqlConnection cn = new SqlConnection("server=(local);Database=Northwind;user id=sa;password=;"))
{
SqlCommand cmd = new SqlCommand("CustOrderOne", cn);
cmd.CommandType=CommandType.StoredProcedure ;
SqlParameter parm=new SqlParameter("@.CustomerID",SqlDbType.NChar) ;
parm.Value="ALFKI";
parm.Direction =ParameterDirection.Input ;
cmd.Parameters.Add(parm);
SqlParameter parm2=new SqlParameter("@.ProductName",SqlDbType.VarChar);
parm2.Size=50;
parm2.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm2);
SqlParameter parm3=new SqlParameter("@.Quantity",SqlDbType.Int);
parm3.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm3);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine(cmd.Parameters["@.ProductName"].Value);
Console.WriteLine(cmd.Parameters["@.Quantity"].Value.ToString());
Console.ReadLine();
}
}
}
}

|||

The above 2 replies does not actually get the return value, which is a special parameter.

The first reply returns the row affected count and the second reply just gets the value out output parameters.

I am afraid I do not know how to retrieve the return value declaratively using controls like object data sources.

However of you are familiar with using SqlCommands then the following code shows you how to get the return values from stored procedures assuming your stored procedure is returning values which is different to result sets, row counts, and output parameters.

SqlCommand cmd =new SqlCommand("this is the query", connection);//create a parameter for the return valueSqlParameter param =new SqlParameter();param.Direction = ParameterDirection.ReturnValue;param.ParameterName ="returnValue";//add to parameter to collectioncmd.Parameters.Add(param);//execute commandcmd.ExecuteNonQuery();//get the return valueint retVal =int.Parse(cmd.Parameters["returnValue"].Value.ToString);

Retrieving OLAP Cube Names from AM2000

Hello,
I am trying to retrieve the Cube names from Analysis Manager 2000 by using DSO objects in VS2005.NET C# 2.0 ( framework 2.0)

The code is something like that.
DSO.Server srv = new DSO.Server();
srv.Connect("localhost");

after that i do not what to do in order to get the cube names from AM2000.
when i do the following

srv.MDStore.Count

i can get the number of the cubes but i cant get the names.
I tried to use the following method but did not work out.

srv.MDStore.Item(object vntIndexKey)

May be i do not know how to use the above method to get the cube names.

for(int i = 0; i < srv.MDStore.Count; i++)
combobox1.Item.Add(srv.MDStore.Item(i).ToString());

the above code does not add the cube names into combobox, either. (

Please somebody help me with this problem.
I need to get the cube names from the AM2000 to let the user choose what cube he/she wants to work with!?

thanks in advance

best regards

Tunc OVACIK

DSO is the wrong API to use for something ordinary users need to run. DSO is the admin API and will only work for OLAP Administrators.

You should use the ADOMD or ADOMD.NET api, the MDX Sample app has code that does this using the older ADOMD API.

There is a sample in BOL for using ADOMD.NET to get a list of cubes which I have copied out below, the original page is available here

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/adodw9/html/0183dcdc-f2ea-4246-ad00-6e8ccc9d8217.htm

Code Snippet

private string RetrieveCubesAndDimensions()
{
System.Text.StringBuilder result = new System.Text.StringBuilder();

//Connect to the local server
using (AdomdConnection conn = new AdomdConnection("Data Source=localhost;"))
{
conn.Open();

//Loop through every cube
foreach (CubeDef cube in conn.Cubes)
{
//Skip hidden cubes.
if (cube.Name.StartsWith("$"))
continue;

//Write the cube name
result.AppendLine(cube.Name);

//Write out all dimensions, indented by a tab.
foreach (Dimension dim in cube.Dimensions)
{
result.Append("\t");
result.AppendLine(dim.Name);
}
}

//Close the connection
conn.Close();
}

//Return the results
return result.ToString();
}

|||

Hello again,

I have succedded to get the cube names from AM2000 by using DSO API. To do this job with DSO API is very easy.

For further information for the others who may need it I will give the sample code.

string[] cubeNames;

DSO.Server dsoServer = new DSO.Server();

dsoServer.Connect("localhost");

// Count will return the number of cubes on AM2000

cubeNames = new string[dsoServer.MDStore.Count];

int i = 0;

foreach( DSO.MDStore cube in dsoServer.MDStore)

{

cubeNames = cube.Name;

i++;

}

Before going through the code you should add the relevant .dll file into your project from the "Add Reference" menu.

Actually it is possible to get the cube names by using ADOMD classes as well as Darren said. I thank you for your help which was really usefull. So, the next step for me is to go through the cube and get the neccesarry data I need to make report for the user.

thanks for everything

Tunc OVACIK

|||The DSO code will only work for administrators, you can run it because you are an admin, normal users will not be able to run it. Hence the reason I suggested using Adomd.

Retrieving OLAP Cube Names from AM2000

Hello,
I am trying to retrieve the Cube names from Analysis Manager 2000 by using DSO objects in VS2005.NET C# 2.0 ( framework 2.0)

The code is something like that.
DSO.Server srv = new DSO.Server();
srv.Connect("localhost");

after that i do not what to do in order to get the cube names from AM2000.
when i do the following

srv.MDStore.Count

i can get the number of the cubes but i cant get the names.
I tried to use the following method but did not work out.

srv.MDStore.Item(object vntIndexKey)

May be i do not know how to use the above method to get the cube names.

for(int i = 0; i < srv.MDStore.Count; i++)
combobox1.Item.Add(srv.MDStore.Item(i).ToString());

the above code does not add the cube names into combobox, either. (

Please somebody help me with this problem.
I need to get the cube names from the AM2000 to let the user choose what cube he/she wants to work with!?

thanks in advance

best regards

Tunc OVACIK

DSO is the wrong API to use for something ordinary users need to run. DSO is the admin API and will only work for OLAP Administrators.

You should use the ADOMD or ADOMD.NET api, the MDX Sample app has code that does this using the older ADOMD API.

There is a sample in BOL for using ADOMD.NET to get a list of cubes which I have copied out below, the original page is available here

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/adodw9/html/0183dcdc-f2ea-4246-ad00-6e8ccc9d8217.htm

Code Snippet

private string RetrieveCubesAndDimensions()
{
System.Text.StringBuilder result = new System.Text.StringBuilder();

//Connect to the local server
using (AdomdConnection conn = new AdomdConnection("Data Source=localhost;"))
{
conn.Open();

//Loop through every cube
foreach (CubeDef cube in conn.Cubes)
{
//Skip hidden cubes.
if (cube.Name.StartsWith("$"))
continue;

//Write the cube name
result.AppendLine(cube.Name);

//Write out all dimensions, indented by a tab.
foreach (Dimension dim in cube.Dimensions)
{
result.Append("\t");
result.AppendLine(dim.Name);
}
}

//Close the connection
conn.Close();
}

//Return the results
return result.ToString();
}

|||

Hello again,

I have succedded to get the cube names from AM2000 by using DSO API. To do this job with DSO API is very easy.

For further information for the others who may need it I will give the sample code.

string[] cubeNames;

DSO.Server dsoServer = new DSO.Server();

dsoServer.Connect("localhost");

// Count will return the number of cubes on AM2000

cubeNames = new string[dsoServer.MDStore.Count];

int i = 0;

foreach( DSO.MDStore cube in dsoServer.MDStore)

{

cubeNames = cube.Name;

i++;

}

Before going through the code you should add the relevant .dll file into your project from the "Add Reference" menu.

Actually it is possible to get the cube names by using ADOMD classes as well as Darren said. I thank you for your help which was really usefull. So, the next step for me is to go through the cube and get the neccesarry data I need to make report for the user.

thanks for everything

Tunc OVACIK

|||The DSO code will only work for administrators, you can run it because you are an admin, normal users will not be able to run it. Hence the reason I suggested using Adomd.

Retrieving image from SQL database

Ok, again, I'm reasonably new to this. I've been trying to display an image stored in SQL in a ASP.NET page. Pretty simple stuff I would have thought. I've read countless examples of how to do this online, and many of them use the same method of displaying the image, but none seem to work for me. The problem seems to lie in the following line of code:

Dim imageDataAsByte() =CByte(command.ExecuteScalar())

Which always returns the error: Value of type 'Byte' cannot be converted to '1-dimensional array of Byte'.

Here's the rest of my code, hope someone can help. It's doing my head in!

Imports System.Data.SqlClient

Imports System.Data

Imports System.Drawing

Imports System.IO

PartialClass _ProfileEditor

Inherits System.Web.UI.Page

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

'Get the UserID of the currently logged on user

Dim NTUserIDAsString = HttpContext.Current.User.Identity.Name.ToString

Session("UserID") = NTUserID

Dim PhotoAs Image =Nothing

Dim connectionAsNew SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)

Dim commandAs SqlCommand = connection.CreateCommand()

command.CommandText ="SELECT Photograph, ImageType FROM Users WHERE UserID = @.UserID"

command.Parameters.AddWithValue("@.UserID", NTUserID)

connection.Open()

Dim imageDataAsByte() =CByte(command.ExecuteScalar())Dim memStreamAsNew MemoryStream(Buffer)

Photo = Image.FromStream(memStream)

EndSub

EndClass

Onwww.SingingEels.com we use images (and other files like zip files, source code etc) in our database, and we show them through an ASP.NET page just like you're trying to do.

There are a few things though with the above that are an issue:

scottishfruit:

Dim imageDataAsByte() =CByte(command.ExecuteScalar())

The problem here (as your compiler is trying to tell you) is that you are trying to assign a BYTE to a BYTE_ARRAY object... to put it in human terms... a BYTE is a pair of shoes... and a BYTE_ARRAY is a shoe store... so when your friend asks you where the nearest shoe store is, and you pointed at your shoes, he yells at you :)

That's what the compiler is doing... so the long and the short of it is... you need toCAST the results from the ExecuteScalar function to a BYTE_ARRAY... like this:

Dim imageData As Byte() = CType(command.ExecuteScalar(), Byte()) <-- (I haven't done VB in a very long time, but I think that's right).

Ok, to "read" that in human speak you would say: "Create a variable named 'imageData' which happens to be an array of bytes and assign it the value of whatever comes from the fuction 'command.ExecuteScalar()' which I know is also a byte array."

I'm sure 100 people have probably posted quick answers already, but if not... let me know if this solves your problem. (or if I lost you all together)

|||

'hey buddy chk out these link>>>

http://aspalliance.com/articleViewer.aspx?aId=140

http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp

i hope it will help u>>>

have a great day!

mark the post as answer if it helped u>>

|||

Awesome! That did the trick! (and your shoe analogy was pretty cool too)

Alas I now have another problem. This line:

Photo = Image.FromStream(memStream)

Is giving me this error: System.ArgumentException: Parameter is not valid.

Once again, I've googled this to pieces and there are heaps of solutions, but none that actually work!

Here's my code again:

Imports System.Data.SqlClient
Imports System.Data
Imports System.Drawing
Imports System.IO

Partial Class _ProfileEditor

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim NTUserID As String = HttpContext.Current.User.Identity.Name.ToString

Session("UserID") = NTUserID

Dim Photo As Image = Nothing

Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("PeopleConnectionString").ConnectionString)

Dim command As SqlCommand = connection.CreateCommand()

command.CommandText = "SELECT Photograph, ImageType FROM Users WHERE UserID = @.UserID"

command.Parameters.AddWithValue("@.UserID", NTUserID)

connection.Open()

Dim imageData As Byte() = CType(command.ExecuteScalar(), Byte())

Dim memStream As New MemoryStream(imageData)

Photo = Image.FromStream(memStream)

End Sub

End Class

|||

Well, this isn't really an answer to your "what's with the argument exception" error... but I think you're almost done... there's no need to create a MemoryStream, or an Image... at this point, you already have the image data (as you so nicely named your variable)... so all that remains is for you to send that data out to the user!

scottishfruit:

Dim imageData As Byte() = CType(command.ExecuteScalar(), Byte())

Dim memStream As New MemoryStream(imageData)

Photo = Image.FromStream(memStream)

End Sub

End Class

Change the above... to this:

Dim imageData As Byte() = CType(command.ExecuteScalar(), Byte())

Response.OutputStream.Write(imageData, 0, imageData.Length)

Response.AddHeader("content-type", "image/jpeg")

Response.End()

End Sub

End Class

I know the image might not always be a JPEG... so you can leave that part out, but it's fine to use even if the image is a GIF, BMP or whatever... but if you choose to leave it out, some browsers may not appreciate that you are trying to send a picture of some kind :)

Enjoy (and when you're done... mark one of these posts as the "answer" so the thread is closed)

Retrieving image from database

Dear Friends,

I have read many solution over the net, but since I am unable to utilize anyone according to my needs I am seeking help from you people.

I have a table imagedata in sql server 2005 having fields name and imageperson. Name is string and imageperson is Image field. I have successfully stored name of the person and his image in database.

I have populated the dataset from codebehind and bind it to the repeater.

By writing <%#DataBinder.Eval(Container.DataItem,"name")%>

I am a able to retrieve the name of the person. But when I pass photodata as

<%#photogen((DataBinder.Eval(Container.DataItem,"imageperson")))%>

where photogen is function in code behind having structure

public void photogen(byte[] dataretrieved)

{

Response.BinaryWrite(datarerieved)

}

But it is giving error at <%#photogen((DataBinder.Eval(Container.DataItem,"imageperson")))%>

The best overloaded method match for '_Default.photogen(byte[])' has some invalid arguments

AND

Cannot convert object to byte[].

Can anyone please provide me working solution with code for aspx page and code behind.

Thanks and regards

Have a look at the sample projects at:

http://www.codeproject.com/aspnet/EasyThumbs.asp

Retrieving Database Names via C#

Hello,
I am trying to develop a desktop application by using C#.Net. I am working with .NET Framewrok 2.0.
I need to list the database names which are taking place in Anlaysis Services.
Actually, my application will work on the OLAP Cube which is going to be chosen by the user.
In order to do this, I have to retrieve the Cube names so the user can choose what cube he/she wants to work with!
any help appritiated.

thanks in advance.

best regards

Tunc OVACIK

Check out Analysis Management Objects (AMO).

http://msdn2.microsoft.com/en-us/library/ms124924.aspx

|||Thanks for the link which is very usefull and has good informations about the whole programming stuff of OLAP technology but I guess those classes are for Analysis Manager 2005.
I am using Analysis Manager 2000 and those classes do not support AM 2000 as far as I understand. Because I have tried to implement the sample codes given in the link but
it did not work out.
Do you have any documents or any other side which is explaining how to get database names and such stuff from Analysis Manager 2000.

thanks for your time
best regards

Tunc OVACIK|||

In that case, check out Decision Support Objects (DSO)

http://msdn2.microsoft.com/en-us/library/aa902639(sql.80).aspx

http://msdn2.microsoft.com/en-us/library/ms133828.aspx

Retrieving Binary Image from SQL

i am trying to retrieve an image blob stored in SQL to a web matrix form (using VB.NET). I am totaly new to this and i got the code below from a site but it doesn't see to work....

<%@. Page Language="VB" %>

<%@. import Namespace="System.Data" %>

<%@. import Namespace="System.IO" %>

<%@. import Namespace="System.Drawing.Image" %>

<%@. import Namespace="System.Drawing.Bitmap" %>

<%@. import Namespace="System.Drawing.Graphics" %>

<%@. import Namespace="System.Web.UI.WebControls" %>

<%@. import Namespace="System.Data.Odbc" %>

<%@. import Namespace="System.Data.SqlClient" %>

<script runat="server">

Private Function GetImageFromDB(ByVal ImageID As Integer) As System.Drawing.Image

' Get the Connection string

Dim strConnection As String

strConnection = "server='(local)'; trusted_connection=true; database='mydatabasename'"

Dim conn As New SqlConnection(strConnection)

Dim sqlCommand As String

Dim strImage As String

Dim image As System.Drawing.Image

Try

sqlCommand = "SELECT ImageField FROM ImageTable WHERE MyImageId = " + ImageID.ToString

Dim cmd As New SqlCommand(sqlCommand)

cmd.Connection = conn

conn.Open()

Dim dr As SqlDataReader

dr = cmd.ExecuteReader()

While (dr.Read())

Dim byt As Byte()

byt = dr.Item(strImage)

Dim bmp As New System.Drawing.Bitmap(New System.IO.MemoryStream(byt))

image = bmp

End While

Catch ex As Exception

' Log Error

Response.Write(ex.Message)

Finally

If conn.State = ConnectionState.Open Then

conn.Close()

End If

End Try

Return image

End Function

Private Sub Page_Load (ByVal sender As System.Object, ByVal e As System.EventArgs)

If Not IsPostBack Then

GetImageFromDB(2111)

End If

End Sub

</script>

<html>

<head>

</head>

<body>

<form enctype="multipart/form-data" runat="server">

<asp:Image id="BrandSignImg" runat="server"></asp:Image>

</form></body></html>

You can not send two different content types with a single http request. ie Html and Image data.
You will need a HttpHandler for serving images out, or you can use a special .aspx page, here is a link to get your started.
http://www.sadeveloper.net/Forums/ShowPost.aspx?PostID=52636
bill

Retrieve XML file from MSSQL

Hi Everyone,

How to retrieve a XML file from MSSQL using a stored procedure in asp.net ?

Thanks,
may

Check this:

http://www.sqldbatips.com/showarticle.asp?ID=23

this will help to generate a xml file than leater from your asp.net app u can read.

Or u can use the power of SQL to generate xml than from asp.net page to manipulate that xml (save edit...) examle:

create procedure usp_getXMLasselect *from ordersfor xml auto

i hope this will help u..

Sorry for my bad english

Retrieve size of db

Hi all,
given a certain SQL Server database (2000 or 2005), I need to execute
queries/commands from a .NET app in order to retrieve the following
information:
1) Database size
2) List of tables
3) Each table size
4) List of indexes
5) Each index size
I'd appreciate any advice or pointer.
Many thanks in advance,
TonyHi Tony,
1) sp_helpdb (List all databases) or sp_helpdb DBName (DBNAme information)
2) sp_tables
3) Look in the SQL Server Books online, "Estimating the Size of a Table".
There, you can see some calculations to determine the table size
4) sp_helpindex Table_Name
5) See the point 3), too.
I hope that this helps you.
Cordially,
Richard_SQL
"Tony" wrote:

> Hi all,
> given a certain SQL Server database (2000 or 2005), I need to execute
> queries/commands from a .NET app in order to retrieve the following
> information:
> 1) Database size
> 2) List of tables
> 3) Each table size
> 4) List of indexes
> 5) Each index size
> I'd appreciate any advice or pointer.
> Many thanks in advance,
> Tony
>|||Tony (acangiano@.gmail.com) writes:
> given a certain SQL Server database (2000 or 2005), I need to execute
> queries/commands from a .NET app in order to retrieve the following
> information:
> 1) Database size
sp_helpdb, the first result set.

> 2) List of tables
SELECT name FROM sysobjects WHERE type = 'U' (SQL 2000)
SELECT name FROM sys.tables (SQL 2005)

> 3) Each table size
SELECT object_name(id), reserved *8192/1000000
FROM sysindexes
WHERE indid (0, 1)
ORDER BY 1
This is for SQL 2000. It will run on SQL 2005, but may not give
accurate vales. (I'm not really up to shape on the new metadata
views in SQL 2005.)

> 4) List of indexes
> 5) Each index size
SELECT object_name(id), name, reserved *8192/1000000
FROM sysindexes
WHERE indid > and indid < 250
AND indexproperty(id, name, 'IsHypothetical') = 0
AND indexproperty(id, name, 'IsStatistics') = 0
AND indexproperty(id, name, 'IsAutoStatistics') = 0
ORDER BY 1, name
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> sp_helpdb, the first result set. [cut]
Thank you.
Erland and Richard you have been very helpful.
Thanks,
Tony

retrieve record from database problem

i m using sql server 2000 with asp.net with c#

i hv 4 customer records in the customer table starting from C1001 to C1004, i wanna ask is that when a new record is add to the table, the record will be placed at the bottom of the table. For example,
CustomerID

Customer IDC1001C1002C1003C1004C1005

When i add a new record which is C1005, is it the record will be placed as shown in the table?

if so, that's mean can straight away use the datarow to retrieve the largest number which is C1005, right?

Thx

In most cases the answer is yes, even the new record is C1000, it till be append to the end of the table. When you select data from the table withoutORDER BY clause, the order of returned rows wil be the same as the they had been inserted. To ensure you get the row with max id, please use TOP..ORDER BY DESC:

SELECT top 1* FROM yourTable ORDER BY CustomerID DESC

Or use the MAX function if you only want the max id:

SELECT MAX(CustomerID) FROM yourTable

Retrieve Images from SQL db: Code Problem.

Hi,
I want to get an image from a sql server database and display it with an asp:image control. I use C# in MS Visual Studio .Net 2005 and Sql server 2005.
All I've done is:

// and Page Display_image.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
try
{

SqlConnection Con = new SqlConnection(
"server=localhost;" +
"database=;" +
"uid=;" +
"password=;");

System.String SqlCmd = "SELECT img_data FROM Image WHERE business_id = 2";

System.Data.SqlClient.SqlCommand SqlCmdObj = new System.Data.SqlClient.SqlCommand(SqlCmd, Con);

Con.Open();

System.Data.SqlClient.SqlDataReader SqlReader = SqlCmdObj.ExecuteReader(CommandBehavior.CloseConnection);

SqlReader.Read();

System.Web.HttpContext.Current.Response.ContentType = "image/jpeg";

// I write this:
System.Web.HttpContext.Current.Response.BinaryWrite((byte[])SqlReader["img_data"]);

// Or this:
//System.Drawing.Image _image = System.Drawing.Image.FromStream(new System.IO.MemoryStream((byte[])SqlReader["img_data"]));

//System.Drawing.Image _newimage = _image.GetThumbnailImage(100, 100, null, new System.IntPtr());

//_newimage.Save(System.Web.HttpContext.Current.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);

Con.Close();

}
catch (System.Exception Ex)
{

System.Web.HttpContext.Current.Trace.Write(Ex.Message.ToString());
}
}

Both work the same way. I mean the image is displayed well but when I view code of the web page I see this:

...
GIF89aå o ÷ó å݉97)HS ¢ ?x L£0¬B´ç¬D(áâK? ?ô² –I€8`È® û l1ã?#K?L( ?*\X±‰ Î"«Mè± ??
...

TOO MUCH characters before the html tag. And any code of the master page used for this page does not work as well!

Can anyone help me with this? I've tried for 2 days but I still fail.

Thanks.

This should help:

Dim

drAs SqlDataReader

dr = cmd.ExecuteReader

dr.Read()

Response.Clear()

Response.AddHeader(

"Content-type", dr("MimeType"))

Response.AddHeader(

"Content-Disposition","inline; filename=""" & dr("Filename") &"""")Dim buffer()AsByte = dr("Data")Dim blenAsInteger =CType(dr("Data"),Byte()).Length

Response.OutputStream.Write(buffer, 0, blen)

Response.End()

|||Hello Motley,

I've tried your comment, but it stays the same. The image is displayed, but plenty of charaters in the page source still appears.

I guess it's because of the 'response.outputstream.write()' command, so all the image's byte data has been writen to the page's code.

Can you find any way to replace 'outputstream.write()' or some changes to stop those disgusting characters?

Thank you for your help,
maivangho.

Retrieve file stored in SQL database ...

Hi there

I'm using VS2005 (VB.net) and SQL 2005.

We've uploaded files from the web application to the SQL database.
The next thing I want to be able to do is to retrieve this uploaded file(s) from the database and attach it in the email when the user click on the Submit button on the web form.

How can this be done ?

Any help would be greatly appreciated.

TIA

Maybe you can use xp_sendmail, which allows sending SQL mail with query result as attachment. For example:

EXEC xp_sendmail @.recipients = 't-leijie',
@.query = 'SELECT pr_info FROM pubs..pub_info',
@.subject = 'SQL Server Report',
@.message = 'Test attachment',
@.attach_results = 'TRUE', @.width = 250

For more information, you can refer to:

Configuring Mail Profiles

xp_sendmail

Retrieve data from a SQLDatasource object.

I'm an "old" programmer but new to ASP.NET.

I want to get a value from the SQL Dataset.

What I would normally do in other environments is iterate through the dataset to get the value I would be intrested in, but I can't figure out how to do this without using a visual data display object like a Grid view.

Typically I want to get a value from the database that I then after manipulating it, like multply by 5, use to format something on the page.

thanks in advance,

Thommie

if you already have created the dataset then its easy to retrieve and iterate through rows.

for each row as datarow in dataset.tables(table index or name, 0 if its only table).rows

dim str as string=row(column name or index).tostring

Next

or

for i as integer=0 to dataset.tables(0).rows.count

var=dataset.tables(0).rows(i)(columnname or index)

next

hope this helps.

Eric

|||

Do you have a compelling reason to use a DataSet? If you are needing to return a single value, I'd look into using ExecuteScalar() off of the SqlCommand object or using an output parameter and getting your value like that. If you need some more explicit examples, let me know.

|||

Hi,

You can go through the DataSet using foreach, like

foreach(DataRow dr in DataSet.Table[0].Rows)
{
//use dr[0] to get the first column data.
}

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!