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 @@rowcount from stored proc

Hi

I'm using an sqldatasource control in my aspx page, and then executing it from my code behind page (SqlDataSource1.Insert()), how do i retrieve the number of rows (@.@.rowcount) which have been inserted into the database and display it in my aspx page. I am using a stored procedure.

thanks

Hello Mattock,

Have a look at the following article about using stored procedure to update data:http://msdn2.microsoft.com/en-us/library/59x02y99(VS.80).aspx

Jeroen Molenaar.

sql

retrieving specific page(number of rows) form table

hi,

i need SP that receive 2 integers ,@.NUM_ROWS and @.PAGE_NUMBER,
and return the rows in that page.
for example:

SP(4,2) will return 4 rows in page number 2 .

So if i have table with 9 rows i will get rows 5-8,
the first page is rows 1-4 the second page is 5-8 and the 3 page is row 9.

i have to assume that rows can be deleted form that table.
thanksYou have to have a (preferably unique) column or set of columns to order the data consistently each call. Do you have an incrementing identity field or datetime stamp?|||i have the PK of the table, but i have to assume some records have been deleted.
so i can not assume i have perfectly order column|||Not necessary.
declare @.NUM_ROWS int
declare @.PAGE_NUMBER int

select [YourTable].*
from [YourTable]
inner join --PageRows
(select [PKey],
count(*) as RowNum
from [YourTable]
inner join [YourTable] Ordinal on [YourTable].[PKey] >= Ordinal.[PKey]
having count(*) between (@.PAGE_NUMBER * @.NUM_ROWS) + 1 and (@.PAGE_NUMBER + 1) * @.NUM_ROWS) PageRows
on [YourTable].[Pkey] = PageRows.Pkey

Retrieving scanned images

Hi all
I have a scenario where I need to retrieve scanned images for a report. The
plan is to have one page showing items submitted from a table and the
remaining pages producing the linked scanned images. the scanned images will
probably be PDF or data held in XML format. I am thinking the jmp to URL
function or something similar will be used here
thank you
Mickeyyou can use the Image item from the toolbox, drag it onto the report and
follow the wizard.
"Mickey N" <MickeyN@.discussions.microsoft.com> wrote in message
news:46EB0E8F-C0D7-498F-82FB-F3AF9C36270E@.microsoft.com...
> Hi all
> I have a scenario where I need to retrieve scanned images for a report.
> The
> plan is to have one page showing items submitted from a table and the
> remaining pages producing the linked scanned images. the scanned images
> will
> probably be PDF or data held in XML format. I am thinking the jmp to URL
> function or something similar will be used here
> thank you
> Mickey

Retrieving Scalar or Calculated Values from Stored Procedures with C#

I am trying to build an Sql page hit provider. I am having trouble getting a count back from the database. If I use ExecuteScalar it doesn't see any value in the returned R1C1. If I use ExecuteNonQuery with a @.ReturnValue, the return value parameter value is always zero. Ideally I would like to use a dynamic stored proceudre if there are any suggestions for using them with C#. My table has rvPathName, userName and a date. I have the AddWebPageHit method working so I know data connection and sql support code in provider is working. I think the problem is either in how I am writing the stored procedures or how I am trying to retrieve the data in C#. Any help with this will be greatly appreciated.

We're not going to be able to help without seeing the code, both the C# code and the stored procedure code.|||

Here you go. I worked on this for more than 8 hours so know that you help is greatly appreciated!!!

CREATE PROCEDURE dbo.WebPageHits_CountByWebPageVPathName @.webPageVPathName nvarchar(256)

AS

DECLARE @.count int

SELECT @.count = COUNT(WebPageHitId)

FROM dbo.WebPageHits

WHERE WebPageVPathName = @.webPageVPathName

RETURN (@.count)

GO

publicoverrideint GetWebPageHitCount(string webPageVPathName)

{

SecUtility.CheckParameter(ref webPageVPathName,true,false,true, 256,"webPageVPathName");

SqlConnectionHolder connectionHolder =null;

SqlConnection connection =null;

int webPageHitCount = 0;

try

{

try

{

connectionHolder =SqlConnectionHelper.GetConnection(_sqlConnectionString,true);

connection = connectionHolder.Connection;

CheckSchemaVersion(connectionHolder.Connection);

SqlCommand cmd =newSqlCommand("dbo.WebPageHits_CountByWebPageVPathName", connection);

cmd.CommandType =CommandType.StoredProcedure;

cmd.CommandTimeout = CommandTimeout;

SqlParameter p =newSqlParameter("@.ReturnValue",SqlDbType.Int);

p.Direction =ParameterDirection.ReturnValue;

cmd.Parameters.Add(p);

cmd.Parameters.Add(CreateInputParam("@.webPageVPathName",SqlDbType.VarChar, webPageVPathName));

cmd.ExecuteNonQuery();

webPageHitCount = GetReturnValue(cmd);

}

finally

{

if (connectionHolder !=null)

{

connectionHolder.Close();

connectionHolder =null;

}

}

}

catch

{

throw;

}

return webPageHitCount;

}

|||

It's a little tough, as you are using custom methods such as GetReturnValue and CreateInputParam.

My gut instinct is that you are having a problem because youare both using the wrong data type for the @.webPageVPathName as well asnot specifying the length. The lack of length specification is mostlikely causing the trouble; if you don't specify it then a length of 1is used. Use the nvarchar datatype and a length of 256 in your C#code, and I think you will have better luck.

I will tell you that it's a better practice to use Output parameters to return values from a stored procedure rather than a ReturnValue. Return values are typically used to communicate success or failure; using them in the manner you are attempting just because the value you want to communicate back to the calling code happens to be an integer can be seen as "cheating". Use an Output parameter instead.

|||

You are right about my cheating. Sometimes you have to hear things from somebody else to actually realize it even though it's right in front of your face.

I got this to work by following the aspnet profile provider code. The provider uses this stored procedure.

aspnet_Profile_GetCountOf...

If just does SELECT COUNT(*) FROM ... with no RETURN statement. Then the C# uses ExecuteScalar then dim o as object = cmd then if o <> null return cmd. My function returns an int so somehow the value gets from the cmd object to the function's return value. I don't know but it's easier.

|||

mtsonic:

If just does SELECT COUNT(*) FROM ... with no RETURN statement. Then the C# uses ExecuteScalar then dim o as object = cmd then if o <> null return cmd. My function returns an int so somehow the value gets from the cmd object to the function's return value. I don't know but it's easier.

ExecuteScalar is an OK way to accomplish what you need. Glad you got it working :-)

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 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 data as xml from sql server

I am trying to retrieve SQL data from multiple tables (SQl Server 2000) and present it in XML in the browser (result.aspx page). I am using "for xml explicit" to retrieve the data. But I am having trouble in displaying the data since I would have to valid
ate the xml against the dtd.
could anyone tell me how to validate the XML document and present it in the browser after I have retrieved the data ?
thanks!
- garfieldinstore
the dtd is as follows:
<!ELEMENT WebOrders(WebOrder*)>
<!ATTLIST WebOrders
NumberOfOrders CDATA #REQUIRED
>
<!ELEMENTWebOrder(Order, WebComment)>
<!ELEMENTWebComment (#CDATA)>
<!ELEMENT Order (
AddressInfo*,
Shipping?,
CreditCard?,
Comments?,
Item*,
Total,
)>
<!ATTLIST Order
id CDATA #REQUIRED
currency CDATA #REQUIRED
>
<!ELEMENT AddressInfo (Name,
Address1?,
Address2?,
City?,
State?,
Country?,
Zip?,
Phone?,
Email?,
Custom*
)>
<!ATTLIST AddressInfo
type (ship|bill) #REQUIRED
>
<!ELEMENT Name (First,
Last,
Full
)>
<!ELEMENT First (#PCDATA)>
<!ELEMENT Last (#PCDATA)>
<!ELEMENT Full (#PCDATA)>
<!ELEMENT Address1 (#PCDATA)>
<!ELEMENT Address2 (#PCDATA)>
<!ELEMENT City (#PCDATA)>
<!ELEMENT State (#PCDATA)>
<!ELEMENT Country (#PCDATA)>
<!ELEMENT Zip (#PCDATA)>
<!ELEMENT Phone (#PCDATA)>
<!ELEMENT Email (#PCDATA)>
<!ELEMENT Custom (#PCDATA)>
<!ATTLIST Custom
name CDATA #REQUIRED
>
<!ELEMENT Shipping (#PCDATA)>
<!ELEMENT CreditCard (#PCDATA)>
<!ATTLIST CreditCard
type CDATA #REQUIRED
expiration CDATA #REQUIRED
>
<!ELEMENT Comments (#PCDATA)>
<!ELEMENT Item (
Code,
Quantity,
Unit-Price,
Description,
Option*,
)>
<!ATTLIST Item
num CDATA #IMPLIED
>
<!ELEMENT Code (#PCDATA)>
<!ELEMENT Quantity (#PCDATA)>
<!ELEMENT Unit-Price (#PCDATA)>
<!ELEMENT Description (#PCDATA)>
<!ELEMENT Option (#PCDATA)>
<!ATTLIST Option
name CDATA #REQUIRED
>
<!--
In an effort to programmatically access the Line items, we provide a canonical type attribute which will be one of the enumerated values listed below.
-->
<!ELEMENT Total (Line*)>
<!ELEMENT Line (#PCDATA)>
<!ATTLIST Line
type (GiftWrap|Discount|MiscAdjustment|
Coupon|GiftCertificate|Subtotal|
Shipping|Tax|Credit|Total) #IMPLIED
name CDATA #REQUIRED
notes CDATA #IMPLIED
>
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
You could read the XML into an XmlValidatingReader object and use that to
validate it before rendering it. See
http://msdn.microsoft.com/library/de...tingReader.asp
for more info.
Alternatively, you could use an XML schema instead of a DTD and add
annotations to the schema to map it to the data - SQLXML would then
automatically generate the appropriate FOR XML EXPLICIT query at runtime.
See
http://msdn.microsoft.com/library/de...tions_0gqb.asp
for more info.
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OB2NPFuNEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am trying to retrieve SQL data from multiple tables (SQl Server 2000)
and present it in XML in the browser (result.aspx page). I am using "for xml
explicit" to retrieve the data. But I am having trouble in displaying the
data since I would have to validate the xml against the dtd.
> could anyone tell me how to validate the XML document and present it in
the browser after I have retrieved the data ?
> thanks!
> - garfieldinstore
> the dtd is as follows:
> <!ELEMENT WebOrders(WebOrder*)>
> <!ATTLIST WebOrders
> NumberOfOrders CDATA #REQUIRED
> <!ELEMENT WebOrder(Order, WebComment)>
> <!ELEMENT WebComment (#CDATA)>
> <!ELEMENT Order (
> AddressInfo*,
> Shipping?,
> CreditCard?,
> Comments?,
> Item*,
> Total,
> )>
> <!ATTLIST Order
> id CDATA #REQUIRED
> currency CDATA #REQUIRED
> <!ELEMENT AddressInfo (Name,
> Address1?,
> Address2?,
> City?,
> State?,
> Country?,
> Zip?,
> Phone?,
> Email?,
> Custom*
> )>
> <!ATTLIST AddressInfo
> type (ship|bill) #REQUIRED
> <!ELEMENT Name (First,
> Last,
> Full
> )>
> <!ELEMENT First (#PCDATA)>
> <!ELEMENT Last (#PCDATA)>
> <!ELEMENT Full (#PCDATA)>
> <!ELEMENT Address1 (#PCDATA)>
> <!ELEMENT Address2 (#PCDATA)>
> <!ELEMENT City (#PCDATA)>
> <!ELEMENT State (#PCDATA)>
> <!ELEMENT Country (#PCDATA)>
> <!ELEMENT Zip (#PCDATA)>
> <!ELEMENT Phone (#PCDATA)>
> <!ELEMENT Email (#PCDATA)>
> <!ELEMENT Custom (#PCDATA)>
> <!ATTLIST Custom
> name CDATA #REQUIRED
> <!ELEMENT Shipping (#PCDATA)>
> <!ELEMENT CreditCard (#PCDATA)>
> <!ATTLIST CreditCard
> type CDATA #REQUIRED
> expiration CDATA #REQUIRED
> <!ELEMENT Comments (#PCDATA)>
> <!ELEMENT Item (
> Code,
> Quantity,
> Unit-Price,
> Description,
> Option*,
> )>
> <!ATTLIST Item
> num CDATA #IMPLIED
> <!ELEMENT Code (#PCDATA)>
> <!ELEMENT Quantity (#PCDATA)>
> <!ELEMENT Unit-Price (#PCDATA)>
> <!ELEMENT Description (#PCDATA)>
> <!ELEMENT Option (#PCDATA)>
> <!ATTLIST Option
> name CDATA #REQUIRED
> <!--
> In an effort to programmatically access the Line items, we provide a
canonical type attribute which will be one of the enumerated values listed
below.
> -->
> <!ELEMENT Total (Line*)>
> <!ELEMENT Line (#PCDATA)>
> <!ATTLIST Line
> type (GiftWrap|Discount|MiscAdjustment|
> Coupon|GiftCertificate|Subtotal|
> Shipping|Tax|Credit|Total) #IMPLIED
> name CDATA #REQUIRED
> notes CDATA #IMPLIED
>
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Retrieving an image from SQL, test for null

I have an employee directory application that displays employees in a gridview. When a record is selected, a new page opens and displays all info about the employee, including their photo. I have the code working that displays the photos, however, when no photo is present an exception is thrown that "Unable to cast object of type System.DbNull to System.Byte[]". I'm not sure how to test for no photo before trying to write it out.

My code is as follows (with no error trapping):

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load,Me.Load

Dim tempAsString

Dim connPhotoAs System.Data.SqlClient.SqlConnection

Dim connstringAsString

connstring = Web.Configuration.WebConfigurationManager.ConnectionStrings("connPhoto").ConnectionString

connPhoto =New System.Data.SqlClient.SqlConnection(connstring)

temp = Request.QueryString("id")

Dim SqlSelectCommand2As System.Data.SqlClient.SqlCommand

Dim sqlstringAsString

sqlstring ="Select * from dbo.PhotoDir WHERE (CMS_ID = " + temp +")"

SqlSelectCommand2 =New System.Data.SqlClient.SqlCommand(sqlstring, connPhoto)

Try

connPhoto.Open()

Dim myDataReaderAs System.Data.SqlClient.SqlDataReader

myDataReader = SqlSelectCommand2.ExecuteReader

DoWhile (myDataReader.Read())

Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))

Loop

connPhoto.Close()

Catch SQLexecAs System.Data.SqlClient.SqlException

Response.Write("Read Failed : " & SQLexec.ToString())

EndTry

EndSub

EndClass

If you could point me in the right direction I would appreciate it.

lwhalen618:

when no photo is present an exception isthrown that "Unable to cast object of type System.DbNull toSystem.Byte[]

lwhalen618:

DoWhile (myDataReader.Read())

Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))

Loop

did you try to check for nulls ??

DoWhile (myDataReader.Read())
if Not IsDBNull(myDataReader.Item("ImportedPhoto")) then
Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))
End if
Loop

hope it works... pls let me know

Good Luck./.

|||

I did try testing for null but was doing it incorrectly. Your code worked fine. Thanks!

Retrieving an Answer from Mulitple ResultSet statements

I have several ResultSet Querys/Statements within my page. An example
of the code looks like this:

ResultSet rs1 = stmt1.executeQuery("SELECT right('
' + '$' + convert(varchar,SUM(ActivePrim),1), 15) AS 'ActivePrim',
right(' ' + '$' + convert(varchar,SUM(KGAP),1), 15) AS
'KGAP', right(' ' + '$' +
convert(varchar,SUM(PrimaryRepo),1), 15) AS 'PrimaryRepo', right('
' + '$' + convert(varchar,SUM(WeeklyTotal),1), 15) AS
'WeeklyTotal' FROM Intranet..InsuranceStats WHERE EmployeeName =
'Jamie' and Date BETWEEN '01/01/04' and '01/31/04'");

What would be the correct way to retrieve each result set? I
currently have it as the example below. But this doesn't allow for
each result set to be displayed separately.

<td valign=top><b>Active Primary:</b><%= ActivePrim %></td
Any help would be greatly appreciated.

Catherineclequieu@.nuvell.com (Catherine) wrote in message news:<a0edee50.0404051011.1235b95c@.posting.google.com>...
> I have several ResultSet Querys/Statements within my page. An example
> of the code looks like this:
> ResultSet rs1 = stmt1.executeQuery("SELECT right('
> ' + '$' + convert(varchar,SUM(ActivePrim),1), 15) AS 'ActivePrim',
> right(' ' + '$' + convert(varchar,SUM(KGAP),1), 15) AS
> 'KGAP', right(' ' + '$' +
> convert(varchar,SUM(PrimaryRepo),1), 15) AS 'PrimaryRepo', right('
> ' + '$' + convert(varchar,SUM(WeeklyTotal),1), 15) AS
> 'WeeklyTotal' FROM Intranet..InsuranceStats WHERE EmployeeName =
> 'Jamie' and Date BETWEEN '01/01/04' and '01/31/04'");
> What would be the correct way to retrieve each result set? I
> currently have it as the example below. But this doesn't allow for
> each result set to be displayed separately.
> <td valign=top><b>Active Primary:</b><%= ActivePrim %></td>
> Any help would be greatly appreciated.
> Catherine

You'll probably get a better answer to this in an ASP forum.

Simon

Retrieving a Value from a SQL Database in Code Behind

VWD 2005 Express. I need to retrieve a value from a SQL database from the code behind a page and assign it to a variable. In Microsoft Access I can do this using the DLookup function. What I need to do is get the data that results from the following query into a variable:

SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = @.Username)

The name of the data source is SqlDataSource2

Also, in Access I can create a recordset from a query and then process through the recordset. Can that be done in VB code in VWD 2005 Express?

I wouldn't use a SqlDataSource for this. I would use ADO.NET code and ExecuteScalar to obtain one value.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

Their are two potential equivalents to a RecordSet. One is using a DataReader, for forward only, read only access, and the other is a DataSet, in which you can move forwards and backwards. DataReader is the more common approach, but it depends on what you want to do.

http://msdn2.microsoft.com/EN-US/library/system.data.sqlclient.sqldatareader.aspx

|||

Thanks Mike. I looked at the link. The MSDN info is so cryptic to me that I cannot discern what to do. Could you provide a real VB code example of what you are talking about? Thanks for the help.

|||

Dim SystemUserId As Int32 = 0
Dim sql As String = "SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = @.Username)"
Using conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@.UserName", TxtUserName.Text)
Try
conn.Open()
SystemUserId = Convert.ToInt32(cmd.ExecuteScalar())
Catch ex As Exception
'Do whatever with Exceptions
End Try
End Using

In the line that starts cmd.Parameters.AddWithValue, I have assumed that the value for the @.Username will come from a TextBox called TxtUserName. Of course, you would need to adjust this to reflect the actual source. Also, connString is a variable of type String that contains your connection string. You would obviously have to suuply this value as well.

|||

Mike. I tried the following using a code example from the link you provided. I get errors saying that types SqlConnection and SqlCommand are not defined. What do I need to do here? Thanks.

ProtectedFunction GetSystemUserId(ByVal UsernameAsString)AsString

Dim connStringAsString ="<%$ ConnectionStrings:GoodNews_IntranetConnectionString %>"

Dim UserIDAsString

Dim sqlAsString ="SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = '" + Username +"')"

Using connAsNew SqlConnection(connString)

Dim cmdAsNew SqlCommand(sql, conn)

Try

conn.Open()

UserID = Str(cmd.ExecuteScalar())

Catch exAs Exception

Console.WriteLine(ex.Message)

EndTry

EndUsing

Return UserID

EndFunction

|||

You need to add

Imports System.Data.SqlClient

at the top of the page. That makes the classes relating to connections and commands within the System.Data.SqlClient available to that page. The alternative is to use the full reference:

Using conn as new System.Data.SqlClient.SqlConnection

etc. Imports statements save a lot of typing in the long run.

|||

Thanks loads Mike!!! After I added the "Imports" at the top, all I had to do was modify my connection string (just copied from my web.config file) and she worked. You have now moved me from novice level 1 to novice level 1.1. Thanks and God bless.

|||

Novice level 1.1's will use:

Dim Conn as new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString)

and keep the connection string in the connection string section of the web.config file.

|||

I would be interested on the information on what you called "DataSet." Can you provide a link?

|||

Public Function GetDataSet(ByVal SQLString As String) As DataSet
Dim cmd As New SqlCommand(SQLString)
Return GetDataSet(cmd)
End Function

Public Function GetDataSet(ByVal cmd As SqlCommand) As DataSet
NullifyParameters(cmd)
Dim DS As New DataSet
Dim MyCommand As SqlDataAdapter
OpenConn(cmd)
cmd.CommandTimeout = m_CommandTimeout
MyCommand = New SqlDataAdapter(cmd)
'MyCommand.SelectCommand.CommandTimeout = m_CommandTimeout -- Change if you want
MyCommand.Fill(DS, "DS")
CloseConn(cmd)
Return DS
End Function

Private Sub NullifyParameters(ByVal cmd As SqlCommand)
For Each p As SqlParameter In cmd.Parameters
If p.Value Is Nothing OrElse (TypeOf p.Value Is String AndAlso p.Value.Length = 0 AndAlso (p.SqlDbType = SqlDbType.DateTime OrElse p.SqlDbType = SqlDbType.Int OrElse p.SqlDbType = SqlDbType.Money OrElse p.SqlDbType = SqlDbType.Real OrElse p.SqlDbType = SqlDbType.Float OrElse p.SqlDbType = SqlDbType.Decimal OrElse p.SqlDbType = SqlDbType.BigInt OrElse p.SqlDbType = SqlDbType.UniqueIdentifier OrElse p.SqlDbType = SqlDbType.TinyInt)) Then
p.Value = DBNull.Value
End If
Next
End Sub

I can't really provide the OpenConn/CloseConn functions, but they do what you would expect them to basically. They just have transaction support in them, which then needs more functions, etc. So here are some ones that will do what you want:

Private OpenConn(cmd as SqlCommand)

dim conn as new SqlConnection(System.Configuration.ConfigurationManager("ConnectionString").ToString)

conn.open

cmd.Connection=conn

end Sub

Private CloseConn(cmd as SqlCommand)

cmd.Connection.Close

end sub

|||

Add those functions to any code file you want to use them in (or make them part of a class library), and then you can do things like:

Dim MyDataSet as dataset = GetDataSet("SELECT * FROM MyTable")

then you can iterate through the dataset rows like

For each dr as datarow in MyDataSet.tables(0).Rows

if dr("Column1")= ... then

' Do something here

end if

next

|||

I placed your code in a class module. I got the following errors. Any help in clearing these up would be appreciated. Thanks.

The following code generates the error, "Type 'DataSet not defined."

PublicFunction GetDataSet(ByVal SQLStringAsString)As DataSet

Dim cmdAsNew SqlCommand(SQLString)

Return GetDataSet(cmd)

EndFunction

The following code generates the error, "m_CommandTimeout not declared."

PublicFunction GetDataSet(ByVal cmdAs SqlCommand)As DataSet

NullifyParameters(cmd)

Dim DSAsNew DataSet

Dim MyCommandAs SqlDataAdapter

OpenConn(cmd)

cmd.CommandTimeout = m_CommandTimeout

MyCommand =New SqlDataAdapter(cmd)

'MyCommand.SelectCommand.CommandTimeout = m_CommandTimeout -- Change if you want

MyCommand.Fill(DS,"DS")

CloseConn(cmd)

Return DS

EndFunction

The following code generates the error, "SqlDbType is not declared."

PrivateSub NullifyParameters(ByVal cmdAs SqlCommand)

ForEach pAs SqlParameterIn cmd.Parameters

If p.ValueIsNothingOrElse (TypeOf p.ValueIsStringAndAlso p.Value.Length = 0AndAlso (p.SqlDbType = SqlDbType.DateTimeOrElse p.SqlDbType = SqlDbType.IntOrElse p.SqlDbType = SqlDbType.MoneyOrElse p.SqlDbType = SqlDbType.RealOrElse p.SqlDbType = SqlDbType.FloatOrElse p.SqlDbType = SqlDbType.DecimalOrElse p.SqlDbType = SqlDbType.BigIntOrElse p.SqlDbType = SqlDbType.UniqueIdentifierOrElse p.SqlDbType = SqlDbType.TinyInt))Then

p.Value = DBNull.Value

EndIf

Next

EndSub

The following code generates the error, "Error 14 'ConfigurationManager' is a type in 'Configuration' and cannot be used as an expression."

Dim connAsNew SqlConnection(System.Configuration.ConfigurationManager("ConnectionString").ToString)

|||

Add

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

to the top of the file containing these methods.

Comment out the Timout line. The default value is good for most scenarios. You will know if you have to increase it, because you get Timeout errors.

|||

The changes that mike suggested should fix the errors. If they don't, please post again.

|||

Mike:

I put the routines you gave me into a class module and I defined them as Shared so that I may call them from other modules. However, when I change the code as shown below (adding the Shared) I get the error that follows. Also I am confused as to how you can have two routines called "GetDataSet." How does the code know which one you are calling?

Cannot refer to an instance member of a class from within a shared method or shared member initializer without an explicit instance of the class.

Public SharedFunction GetDataSet(ByVal SQLStringAsString)As DataSet

Dim cmdAsNew SqlCommand(SQLString)

Return GetDataSet(cmd)

EndFunction

PrivateFunction GetDataSet(ByVal cmdAs SqlCommand)As DataSet

NullifyParameters(cmd)

Dim DSAsNew DataSet

Dim MyCommandAs SqlDataAdapter

OpenConn(cmd)

'cmd.CommandTimeout = m_CommandTimeout

MyCommand =New SqlDataAdapter(cmd)

'MyCommand.SelectCommand.CommandTimeout = m_CommandTimeout -- Change if you want

MyCommand.Fill(DS,"DS")

CloseConn(cmd)

Return DS

EndFunction

Retrieve Bit type value

I am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code

Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)

I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?You're not selecting what you think you're selecting. You will get a 1 or 0 back, not S00817. Do any of the fields in the record have that value? If you miss a comma in your field list, you can get goofy-looking, unexpected data. Also, when the bit comes back, you can feed it right into boolean properties. You don't have to change 1 into true, for example. Pretty handy. I'd run your SQL statement in Query Analyzer, some where outside of ASP. Actually, ADO might convert the 1 to -1. Might want to check that also. Check your query; something's up there I would suspect.|||Yes you were quite right. I was trying to read data from another field. I should have using Mydataset.tables(0).Rows(0)(13)

Thank you for your help