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 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)

retrieve popular search items from table - problems

I've been using SQL for a while but I'm kinda stumped as to where to start with this one. I have a search function on my ecommerce site and whatever anyone searches for is being stored in a database.

I need a script that will look at my search entries table and return a list of the most popular search terms.

So go to the table and produce result like

Search Term (count)
Harry Potter (6)
Sherlock Holmes (4)
Garfield (2)

But like I say, I'm a little stumped as to where to even begin with this one.

You can use a GROUP BY and ORDER BY clause in your SQL statement e,g,

DECLARE @.MYTABLETABLE (idint IDENTITY(1,1), SearchTermvarchar(10))INSERT @.MYTABLEVALUES('ASP.NET')INSERT @.MYTABLEVALUES('ASP.NET')INSERT @.MYTABLEVALUES('Something')INSERT @.MYTABLEVALUES('Nothing')INSERT @.MYTABLEVALUES('Anything')INSERT @.MYTABLEVALUES('Other')SELECT SearchTerm,COUNT(id)AS SearchesFROM @.MyTableGROUP BY SearchTermORDER BYCOUNT(id)DESC