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 all ESNID one time, the most recent

This is more ASP SElect .

I need to return all the rows. Where the ESNnumber only returns the most recent one that is associsted with the Asset.

Basically, I need the info most current ESN number only.
They are 19,00 rows of each ESN number but it returns 40,000. Duplicates.

SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetCustomAttributeDef.Name AS [Custom Asset], dbo.ESN.EsnNumber AS [ESN #],
dbo.AssetAttribute.AssetDescription AS [Description Detail], dbo.Asset.Barcode, dbo.Asset.SKU,
dbo.InventoryOrigin.WarehouseDescription AS [Inventory (W/H)], dbo.ESN.DateImplemented, dbo.ESNTracking.TraceTime,
dbo.ESNTracking.PreviousTraceTime, dbo.ESNTracking.HasMoved, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.Direction,
dbo.ESNTracking.Landmark, dbo.ESNTracking.FemaLocation AS Fema, dbo.ESNTracking.ReportTime AS [Report Time],
dbo.ESNTracking.CurrLocStreet AS Address, dbo.ESNTracking.CurrLocCity AS City, dbo.ESNTracking.CurrLocState AS State,
dbo.ESNTracking.CurrLocZip AS Zipcode, dbo.ESNTracking.CurrLocCounty AS County, dbo.ESNTracking.MapUrl AS [Map Link],
dbo.ESNTracking.ReplaceByDate AS [Replace Batt.], dbo.ESNTracking.CurrMileFromStratix AS [From Stratix Now],
dbo.ESNTracking.PrevMileFromStratix AS [From STratix Then]
FROM dbo.AssetType INNER JOIN
dbo.Asset ON dbo.AssetType.AssetTypeId = dbo.Asset.AssetTypeId INNER JOIN
dbo.InventoryOrigin ON dbo.Asset.WarehouseId = dbo.InventoryOrigin.WarehouseId INNER JOIN
dbo.AssetAttribute ON dbo.Asset.AssetAttributeId = dbo.AssetAttribute.AssetAttributeId INNER JOIN
dbo.EsnAsset ON dbo.Asset.AssetId = dbo.EsnAsset.AssetId INNER JOIN
dbo.ESN ON dbo.EsnAsset.EsnId = dbo.ESN.EsnId LEFT OUTER JOIN
dbo.ESNTracking ON dbo.EsnAsset.EsnId = dbo.ESNTracking.EsnId LEFT OUTER JOIN
dbo.AssetVehicle ON dbo.EsnAsset.AssetId = dbo.AssetVehicle.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttribute ON dbo.EsnAsset.AssetId = dbo.AssetCustomAttribute.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttributeDef ON dbo.AssetCustomAttribute.AssetTypeId = dbo.AssetCustomAttributeDef.AssetTypeId

ORDER BY dbo.AssetType.Description

If I have understood this correctly...

1. Drop a MULTICAST into your flow.

2. On one output, use AGGREGATE to work out the max ESN Number per asset.

3. Join that back to the other output using MERGE JOIN, joining on ESN Number and Asset

Does that work?

-Jamie

|||

Could you help me please.

I need help.

writiing the query with thos parameters

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 Images from SQL Database

Hello everyone!

I have an SQL Database that originally we had an image filename in the "Image" field, and using ASP, concantenated a loaction with it to retrieve an image on our website. We just converted that field to where we can actually place the actual image in the field. My question is, how can I use ASP to retireve and show the image?

Thanks for your help in advance!

Matt

Use the following link for a good example

http://authors.aspalliance.com/stevesmith/articles/imagequery.asp

Retrieving Images from SQL Database

Hello everyone!

I have an SQL Database that originally we had an image filename in the "Image" field, and using ASP, concantenated a loaction with it to retrieve an image on our website. We just converted that field to where we can actually place the actual image in the field. My question is, how can I use ASP to retireve and show the image?

Thanks for your help in advance!

Matt

Use the following link for a good example

http://authors.aspalliance.com/stevesmith/articles/imagequery.asp

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