Return DISTINCT Values

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below?

Code Snippet

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType

FROM @.GResults r

LEFT OUTER JOIN

ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

You can apply the distinct key word after the select clause – if all the column set values are duplicate,

Code Snippet

SELECT Distinct

CommentImage AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

If column set values (CommentImage, GCustodian, GCustodianAccount, GAssetType) are not unique you can apply group functions – it may cauase some data lose.

Code Snippet

SELECT Distinct

Max(CommentImage) AS ViewComment

, r.GPositionID

, Max(GCustodian)

, Max(GCustodianAccount)

, Max(GAssetType)

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

Group BY

r.GPositionID

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

CommentImage has dataType 'Image'

Using DISTINCT with it gives the below error...

The text, ntext, or image data type cannot be selected as DISTINCT.

|||

The following query might help you,

Select

(select Top 1 CommentImage from ReconComments s where s.GPositionID=data.GPositionID),

, GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

From

(

SELECT Distinct

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

) as data

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

Get this error...

The text, ntext, and image data types are invalid in this subquery or aggregate expression

|||Yes SQL Server 2000 cause this error..let me check the solution for this.|||r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

Using SLQ Server 2005

thanks.

|||

If you really use SQL Server 2005 (check using => print @.@.version) database then the following query work fine,

MS Recommandation: Change your Image datatype to varbinary(max)

Code Snippet

SELECT Distinct

Cast(CommentImage as varbinary(max)) AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

When I click the 'Help' > 'About' link it tells me its Microsoft SQL Server 2005.

Using

print @.@.version

tells me this...

Microsoft SQL Server 2000 - 8.00.878 (Intel X86)

|||

That means you connected SQL Server 2000 server from the Management Studio (2005 Client tool).

Let me clarify where the images are stored - is it in different table (ReconComments) .

Is there any possibilty to have duplicate images for one GPositionID.

|||Sorry - it is possible for one GPositionID to have duplicate images.|||

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@.GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)

Return DISTINCT Values

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below?

Code Snippet

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType

FROM @.GResults r

LEFT OUTER JOIN

ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

You can apply the distinct key word after the select clause – if all the column set values are duplicate,

Code Snippet

SELECT Distinct

CommentImage AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

If column set values (CommentImage, GCustodian, GCustodianAccount, GAssetType) are not unique you can apply group functions – it may cauase some data lose.

Code Snippet

SELECT Distinct

Max(CommentImage) AS ViewComment

, r.GPositionID

, Max(GCustodian)

, Max(GCustodianAccount)

, Max(GAssetType)

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

Group BY

r.GPositionID

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

CommentImage has dataType 'Image'

Using DISTINCT with it gives the below error...

The text, ntext, or image data type cannot be selected as DISTINCT.

|||

The following query might help you,

Select

(select Top 1 CommentImage from ReconComments s where s.GPositionID=data.GPositionID),

, GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

From

(

SELECT Distinct

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

) as data

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

Get this error...

The text, ntext, and image data types are invalid in this subquery or aggregate expression

|||Yes SQL Server 2000 cause this error..let me check the solution for this.|||r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

Using SLQ Server 2005

thanks.

|||

If you really use SQL Server 2005 (check using => print @.@.version) database then the following query work fine,

MS Recommandation: Change your Image datatype to varbinary(max)

Code Snippet

SELECT Distinct

Cast(CommentImage as varbinary(max)) AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

When I click the 'Help' > 'About' link it tells me its Microsoft SQL Server 2005.

Using

print @.@.version

tells me this...

Microsoft SQL Server 2000 - 8.00.878 (Intel X86)

|||

That means you connected SQL Server 2000 server from the Management Studio (2005 Client tool).

Let me clarify where the images are stored - is it in different table (ReconComments) .

Is there any possibilty to have duplicate images for one GPositionID.

|||Sorry - it is possible for one GPositionID to have duplicate images.|||

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@.GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)

return current date

Hi,

Can I write a code in SQL that return the current date? If so, how?

Thanks!

WillYou can use the GETDATE() function:

SELECT GETDATE() AS CurrentDateTime

And you can use the CONVERT function to format that datetime value.

Terri|||Convert(varchar(10),GetDate(),101)

returns the date as:

01/01/2004

Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to check
for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.comTry using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html#dynamic-sql
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:
> I'm trying to apply the following code into a proc I have and wanted to check
> for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
> then tried placing a return status code variable after the execute stmt and
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com

Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to chec
k
for the success of the alter database stmt. @.@.error didn't trip to <> 0 an
d
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.comTry using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-hand...tml#dynamic-sql
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:

> I'm trying to apply the following code into a proc I have and wanted to ch
eck
> for the success of the alter database stmt. @.@.error didn't trip to <> 0
and
> then tried placing a return status code variable after the execute stmt an
d
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com

Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to check
for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.com
Try using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handl...ml#dynamic-sql
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:

> I'm trying to apply the following code into a proc I have and wanted to check
> for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
> then tried placing a return status code variable after the execute stmt and
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com
sql

Return Code from Stored Proc

I am doing an insert with a stored proc using the ExecuteNonQuery in the
DataAccess Block from Microsoft. My parameters are inserted correctly into
the database but my return code is always a -1 instead of 0. Please review
this code and tell me if you see something I am doing wrong> Thanks in
Advance!
CREATE PROCEDURE insertTrans_sp
(@.batch_id numeric,
@.cpi numeric,
@.visit numeric,
@.qty numeric,
@.gl_proc varchar(8),
@.gl_desc varchar(50),
@.charge float,
@.processed_date datetime,
@.processed_by varchar(50))
AS
SET NOCOUNT ON
DECLARE
@.err int,
@.err_desc varchar(50)
Begin Transaction
INSERT INTO Trans
(batch_id,
cpi,
visit,
qty,
gl_proc,
gl_desc,
charge,
processed_date,
processed_by)
VALUES
(@.batch_id,
@.cpi,
@.visit,
@.qty,
@.gl_proc,
@.gl_desc,
@.charge,
@.processed_date,
@.processed_by)
SET @.err = @.@.error
if @.err <> 0 GOTO ErrorHandler
COMMIT Transaction
return 0
ErrorHandler:
SET @.err_desc = 'Error occurred in insertTrans_sp'
EXEC insertErrLog_sp @.err, @.err_desc
return -100
GO
Robert HillRobert,
Exactly how are you executing this? Is this ADO, ADO.net etc? A couple of
comments here. One is that it is not necessary to wrap the Insert in an
explicit transaction. Any single sql statement is ATOMIC by itself. The
insert will either succeed or it won't. By wrapping it in a tran you now
have to commit or roll it back yourself. In this case you don't even have
any code to issue a rollback. You should always test the trancount level
before issuing a commit or rollback.
IF @.@.TRANCOUNT > 0
COMMIT TRAN
You declare a series of variables as Numeric but do not specify their
precision or scale. Always specify the size or scale of all data types.
These all look like Integers anyway. If that is the case it is more
efficient to declare them as integers than numeric.
Andrew J. Kelly SQL MVP
"Robert" <rhill938@.hotmail.com> wrote in message
news:0B27280E-35B3-4548-B928-34A0E227453F@.microsoft.com...
>I am doing an insert with a stored proc using the ExecuteNonQuery in the
> DataAccess Block from Microsoft. My parameters are inserted correctly
> into
> the database but my return code is always a -1 instead of 0. Please
> review
> this code and tell me if you see something I am doing wrong> Thanks in
> Advance!
> CREATE PROCEDURE insertTrans_sp
> (@.batch_id numeric,
> @.cpi numeric,
> @.visit numeric,
> @.qty numeric,
> @.gl_proc varchar(8),
> @.gl_desc varchar(50),
> @.charge float,
> @.processed_date datetime,
> @.processed_by varchar(50))
> AS
> SET NOCOUNT ON
> DECLARE
> @.err int,
> @.err_desc varchar(50)
> Begin Transaction
> INSERT INTO Trans
> (batch_id,
> cpi,
> visit,
> qty,
> gl_proc,
> gl_desc,
> charge,
> processed_date,
> processed_by)
> VALUES
> (@.batch_id,
> @.cpi,
> @.visit,
> @.qty,
> @.gl_proc,
> @.gl_desc,
> @.charge,
> @.processed_date,
> @.processed_by)
> SET @.err = @.@.error
> if @.err <> 0 GOTO ErrorHandler
> COMMIT Transaction
> return 0
> ErrorHandler:
> SET @.err_desc = 'Error occurred in insertTrans_sp'
> EXEC insertErrLog_sp @.err, @.err_desc
> return -100
> GO
> --
> Robert Hill
>|||in order to get a return code, I believe you need to use execute Scalar
Greg Jackson
PDX, Oregon|||"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eAujW9fHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> in order to get a return code, I believe you need to use execute Scalar
>
Yes, the return from ExecuteNonQuery is NOT the stored procedure return
code.
No ExecuteScalar won't help. To get the return code you will need to use
CommandType.Text and write a batch like:
exec @.rc=MyProc(@.p1,@.p2,@.p3)
then bind an output parameter to @.rc.
In your case it's not necessary to test the return code. From client code
if something goes wrong you will get a SqlException. A stored procedure
return code is really just for other stored procedures. When one procedure
calls another procedure the calling procedure cannot intercept the error
messages generated by the called procedure, so it must use the return code
to determine if something went wrong. From SqlClient the error message will
appear as a SqlException and you can examine it in your catch block.
David|||Thanks.
"David Browne" wrote:

> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:eAujW9fHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Yes, the return from ExecuteNonQuery is NOT the stored procedure return
> code.
> No ExecuteScalar won't help. To get the return code you will need to use
> CommandType.Text and write a batch like:
> exec @.rc=MyProc(@.p1,@.p2,@.p3)
> then bind an output parameter to @.rc.
> In your case it's not necessary to test the return code. From client code
> if something goes wrong you will get a SqlException. A stored procedure
> return code is really just for other stored procedures. When one procedur
e
> calls another procedure the calling procedure cannot intercept the error
> messages generated by the called procedure, so it must use the return code
> to determine if something went wrong. From SqlClient the error message wi
ll
> appear as a SqlException and you can examine it in your catch block.
> David
>
>|||Robert,
you dont have to set the command type to text.
you can (And in my opinion, should) leave the command type to "stored
procedure"
you can create a parameter object with direction of "Output" and get the
return value.
GAJ|||Thanks.
My solution was to use ExecuteScalar in the Application Block with the
command type "stored procedure" with an output parameter.
Robert
"pdxJaxon" wrote:

> Robert,
> you dont have to set the command type to text.
> you can (And in my opinion, should) leave the command type to "stored
> procedure"
> you can create a parameter object with direction of "Output" and get the
> return value.
> GAJ
>
>|||pdxJaxon wrote:
> Robert,
> you dont have to set the command type to text.
> you can (And in my opinion, should) leave the command type to "stored
> procedure"
> you can create a parameter object with direction of "Output" and get
> the return value.
>
You can also use a parameter with direction of "ReturnValue" to get the
return value ...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks!
I now need to return 2 values from a table. I tested my stored proc in
query analyzer and it seemed to work fine, returning the values I need. The
foloowing is the code I use in my app to call the stored proc using a
SqlParameter array. All I get back is a -1. What am I doing wrong?
SqlParameter[] oParms = new SqlParameter[3];
try
{
oParms[0] = new SqlParameter("@.cpi", sCPI);
oParms[1] = new SqlParameter("@.visit", ParameterDirection.Output);
oParms[2] = new SqlParameter("@.batch_id", ParameterDirection.Output);
ConnectString oCn = new ConnectString();
cn = oCn.GetConnection();
object oRes = new object();
oRes = SqlHelper.ExecuteNonQuery(cn,
CommandType.StoredProcedure,
"verifyCPIandBatchId_sp",
oParms);
Robert
"Bob Barrows [MVP]" wrote:

> pdxJaxon wrote:
> You can also use a parameter with direction of "ReturnValue" to get the
> return value ...
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||close the connection before attempting to read the output parms
GAJ

Return Code 62309

I'm using SQL Lightspeed for Log shipping and I keep getting the following r
eturncode when calling their xp_backup_database procedure.
The error message says it's a sql returncode. Is this a syntax returncode?
Any know?
ThanksI havent worked with sql litespeed, but given that transaction log backups
are going on, pls check the sql error logs on both the Primary and
Secondary to see if there is a problem with the tran logs.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Return Code 62309

I'm using SQL Lightspeed for Log shipping and I keep getting the following returncode when calling their xp_backup_database procedure.
The error message says it's a sql returncode. Is this a syntax returncode? Any know?
ThanksI havent worked with sql litespeed, but given that transaction log backups
are going on, pls check the sql error logs on both the Primary and
Secondary to see if there is a problem with the tran logs.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Return Code 62309

I'm using SQL Lightspeed for Log shipping and I keep getting the following returncode when calling their xp_backup_database procedure.
The error message says it's a sql returncode. Is this a syntax returncode? Any know?
Thanks
I havent worked with sql litespeed, but given that transaction log backups
are going on, pls check the sql error logs on both the Primary and
Secondary to see if there is a problem with the tran logs.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
sql

Return always 0.

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

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

Return a record set of element names

Hi,

Does anyone know how to return a list of element names from an xml document?

eg.

Code Snippet

<values>
<name>Brian</name>
<lastName>Smith</lastName>
<tel>999-123456</tel>
</values>

the result set I'm after is a table with two columns (lets say col_name and col_value)

col_name col_value
name Brian
lastName Smith
tel 999-123456

my biggest problem is extracting the element name - any ideas

Many Thanks,

Jan.

Here is an example doing that using the XQuery nodes method to shred the XML into nodes and then the local-name XQuery function:

Code Snippet

DECLARE @.x xml;

SET @.x ='<values>

<name>Brian</name>

<lastName>Smith</lastName>

<tel>999-123456</tel>

</values>';

SELECT

T.xcol.value('local-name(.)','nvarchar(20)')AScol_name

,T.xcol.value('.','nvarchar(20)')AS col_value

FROM @.x.nodes('*/*')AS T(xcol);

|||Cheers Martin,

Exactly what I was after !

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

Retriving an xml string stored in varchar(max)

I try to retrive an xml portion (<points><point><x>1</x></point></points>) stored in a varchar(max) column, this is my code

dr = cmd.ExecuteReader();_xmlFile = dr.GetSqlString(dr.GetOrdinal("XmlJoin")).ToString();Label1.Text = _xmlFile;

and this is what I get "12"
Maybe I missed something to get the whole XML StringWhat do you mean by getting "12"? It confused me...|||

mehdi_tn:

I try to retrive an xml portion (<points><point><x>1</x></point></points>) stored in a varchar(max) column, this is my code

dr = cmd.ExecuteReader();
_xmlFile = dr.GetSqlString(dr.GetOrdinal("XmlJoin")).ToString();
Label1.Text = _xmlFile;

and this is what I get "12"
Maybe I missed something to get the whole XML String

It seems to me you could do it like this:

_xmlFile = dr["XmlJoin"].ToString();

|||Thanks for answering, In fact I placed the retrived XMl in a label and the label showed "1"
When debugin I founded the whole XML in the variable. The problem was from the label try this :

Label1.Text="<;x>1</x>";// this will show 1

Bizarre this controlsql

retrive a binary field from database

hi

I have used the following code (mostly created by MSDN) to retrive a binary field from SQL database. it works but I have extra space between characters. for example if I save a text file with "Hello world" text, after retriving I have it like "H e l l o w o r l d". what is the problem??

I am really looking forward your answers

private void retrive()

{

publicvoid a()

{

SqlConnection connection =newSqlConnection("Some Connection string");SqlCommand command =newSqlCommand("Select * from temp", connection);// Writes the BLOB to a fileFileStream stream;// Streams the BLOB to the FileStream object.BinaryWriter writer;// Size of the BLOB buffer.int bufferSize = 50;// The BLOB byte[] buffer to be filled by GetBytes.byte[] outByte =newbyte[bufferSize];// The bytes returned from GetBytes.long retval;// The starting position in the BLOB output.long startIndex = 0;// Open the connection and read data into the DataReader.

connection.Open();

SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);while (reader.Read())

{

// Create a file to hold the output.

stream =

newFileStream("C:\\file.txt",FileMode.OpenOrCreate,FileAccess.Write);

writer =

newBinaryWriter(stream);// Reset the starting byte for the new BLOB.

startIndex = 0;

// Read bytes into outByte[] and retain the number of bytes returned.

retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize);

// Continue while there are bytes beyond the size of the buffer.while (retval == bufferSize)

{

writer.Write(outByte);

writer.Flush();

// Reposition start index to end of last buffer and fill buffer.

startIndex += bufferSize;

retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize);

}

// Write the remaining buffer.if (retval != 0)

writer.Write(outByte, 0, (

int)retval - 1);

writer.Flush();

// Close the output file.

writer.Close();

stream.Close();

}

// Close the reader and the connection.

reader.Close();

connection.Close();

}

}

Hi, I guess you're storing the text as NTEXT data type in your SQL Server. Since NTEXT/NVARCHAR/NCHAR data uses 2 bytes to stores a unicode character, when you retrieve the text from database using binary stream, each character is transferred as 2 bytes (with the 2nd byte empty), so that's why you found spaces between words. You?can?use?UltraEdit?to?open?the?generated?text?file?and?swith?to?hex?mode,?you'll?00s?between?normal?characters.

Retrieving Sybase data from sql server 2005

hi
i need to select a data from a sybase table into a sql server 2005 table.i dont know whts is the query to do it, let me show u my code.i get error when i do that query

select * from openrowset('MSDASQL','dsn=bodaily;uid=sa;pwd=welco me;Initial Catalog=phoenix;',
' select rim from rm_acct')

this rm_acct is a sybase table

can anyone guide me on this.
thanks

Could you elobrate on error, you are getting.

thanx and regards

Rahul Kumar

Retrieving Scope_Entity or Identity from an SQL Insert

The following code inserts a record into a table. I now wish to retrieve the IDENTITY of that entry into a variable so that I can use it again as input for other inserts. Can someone offer assistance in handling this... I tried several alternatives that I found on the internet but none seem to work...

Thanks!

Dim objConn3As SqlConnection
Dim mySettings3AsNew NameValueCollection
mySettings3 = AppSettings
Dim strConn3AsString
strConn3 = mySettings3("connString")
objConn3 =New SqlConnection(strConn3)
Dim strInsertPatientAsString
Dim cmdInsertAs SqlCommand
Dim strddlSexAsString
Dim strddlPatientStateAsString
Dim rowsAffectedAsInteger

strddlSex = ddlSex.SelectedItem.Text
strddlPatientState = ddlPatientState.SelectedItem.Text

strInsertPatient ="Insert ClinicalPatient ( UserID, Accession, FirstName, MI, " & _
"LastName, MedRecord, ddlSex, DOB, Address1, Address2, City, Suite, strddlPatientState, " & _
"ZIP, HomeTelephone, OutsideNYC, ClinicalImpression, Today_Date_Month, Today_Date_Day, " & _
"Today_Date_Year) Values (@.UserID, @.Accession, @.FirstName, @.MI, @.LastName, @.MedRecord, " & _
"'" & strddlSex &"', @.DOB, @.Address1, @.Address2, @.City, @.Suite , '" & strddlPatientState &"', " & _
"@.ZIP, @.HomeTelephone, @.OutsideNYC, @.ClinicalImpression, @.Today_Date_Month, @.Today_Date_Day, " & _
"@.Today_Date_Year)SELECT @.@.IDENTITY AS NewID SET NOCOUNT OFF"

cmdInsert =New SqlCommand(strInsertPatient, objConn3)

cmdInsert.Parameters.Add("@.UserID","Joe For Now")
cmdInsert.Parameters.Add("@.Accession", Accession.Text)
cmdInsert.Parameters.Add("@.LastName", LastName.Text)
cmdInsert.Parameters.Add("@.MI", MI.Text)
cmdInsert.Parameters.Add("@.FirstName", FirstName.Text)
cmdInsert.Parameters.Add("@.MedRecord", MedRecord.Text)
cmdInsert.Parameters.Add("@.ddlSex", strddlSex)
cmdInsert.Parameters.Add("@.DOB", DOB.Text)
cmdInsert.Parameters.Add("@.Address1", Address1.Text)
cmdInsert.Parameters.Add("@.Address2", Address2.Text)
cmdInsert.Parameters.Add("@.City", City.Text)
cmdInsert.Parameters.Add("@.Suite", Suite.Text)
cmdInsert.Parameters.Add("@.strddlPatientState", strddlPatientState)
cmdInsert.Parameters.Add("@.ZIP", zip.Text)
cmdInsert.Parameters.Add("@.HomeTelephone", Phone.Text)
cmdInsert.Parameters.Add("@.OutsideNYC", OutsideNYC.Text)
cmdInsert.Parameters.Add("@.ClinicalImpression", ClinicalImpression.Text)
cmdInsert.Parameters.Add("@.Today_Date_Month", Today_Date_Month.Text)
cmdInsert.Parameters.Add("@.Today_Date_Day", Today_Date_Day.Text)
cmdInsert.Parameters.Add("@.Today_Date_Year", Today_Date_Year.Text)

objConn3.Open()
cmdInsert.ExecuteNonQuery()
objConn3.Close()

Try this - a zillion ways to get Scope_Identity back:http://www.mikesdotnetting.com/Article.aspx?ArticleID=54

Retrieving PDF from MS Sql Database using C#

I am now retrieving the PDF from the database and I am getting an error:

Error 1 'GetImages.GetImage(int)': not all code paths return a value

I have:

int imageid =Convert.ToInt32(Request.QueryString["ACTUAL_IMAGE_PDF"]);

And...

privateSqlDataReader GetImage(int imageid)

{

Sql Statement...

}

Could someone help please??

Can you post the complete code?

|||protectedvoid Page_Load(object sender,EventArgs e)

{

int imageid =Convert.ToInt32(Request.QueryString["ACTUAL_IMAGE_PDF"]);SqlDataReader imageContent = GetImage(imageid);

imageContent.Read();

Response.ContentType = imageContent["ImageType"].ToString();

Response.OutputStream.Write((byte[])imageContent["ImageFile"], 0, System.Convert.ToInt32(imageContent["ImageSize"]));

Response.End();

}

privateSqlDataReader GetImage(int imageid)

{

SqlConnection myConnection =newSqlConnection("Data Source=*********");SqlCommand myCommand =newSqlCommand("Select * From DBO.RIMS_TEST_TABLE Where imageid=@.ImagePDF_Name", myConnection);

SqlParameter imageIDParameter =newSqlParameter("@.ImageId",SqlDbType.Int);

imageIDParameter.Value = imageid;

myCommand.Parameters.Add(imageIDParameter);

myConnection.Open();

}

}

|||

Instead ofprivateSqlDataReader GetImage(int imageid)

make it

private void GetImage(int imageid)

since you are not returning anything!

Retrieving OLAP Cube Names from AM2000

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

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

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

srv.MDStore.Count

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

srv.MDStore.Item(object vntIndexKey)

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

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

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

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

thanks in advance

best regards

Tunc OVACIK

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

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

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

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

Code Snippet

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

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

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

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

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

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

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

|||

Hello again,

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

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

string[] cubeNames;

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

dsoServer.Connect("localhost");

// Count will return the number of cubes on AM2000

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

int i = 0;

foreach( DSO.MDStore cube in dsoServer.MDStore)

{

cubeNames = cube.Name;

i++;

}

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

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

thanks for everything

Tunc OVACIK

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

Retrieving OLAP Cube Names from AM2000

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

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

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

srv.MDStore.Count

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

srv.MDStore.Item(object vntIndexKey)

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

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

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

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

thanks in advance

best regards

Tunc OVACIK

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

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

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

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

Code Snippet

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

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

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

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

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

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

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

|||

Hello again,

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

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

string[] cubeNames;

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

dsoServer.Connect("localhost");

// Count will return the number of cubes on AM2000

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

int i = 0;

foreach( DSO.MDStore cube in dsoServer.MDStore)

{

cubeNames = cube.Name;

i++;

}

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

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

thanks for everything

Tunc OVACIK

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