RowCount is returning null

i have 2 stored procedures: a delete and a select. the delete sp returns the rowcount properly. the select returns null. the code for both sp's is extremely simple and extremely similar. when i execute the select sp in server management studio the rowcount shows a 1 as expected. but the calling method gets null.

SP Code

ALTER

PROCEDURE [dbo].[RetrieveEmployeeKeyFromAssignmentTable]

@.assignmentPrimaryKey

int,

@.rowCount

intOUTPUT

AS

BEGIN

SETNOCOUNTON;SELECT employeePrimaryKeyFROM assignmentTableWHERE primaryKey= @.assignmentPrimaryKey;SET @.rowCount=@.@.RowCount;

END

c# code

SqlConnection

conn = GetOpenSqlConnection();if (conn ==null) returntrue;SqlDataReader reader =null;SqlParameter p1 =newSqlParameter(); SqlParameter p2 =newSqlParameter();try{SqlCommand command =newSqlCommand();

command.CommandText =

"RetrieveEmployeeKeyFromAssignmentTable";

command.CommandType =

CommandType.StoredProcedure;

command.Connection = conn;

p1.ParameterName =

"@.assignmentPrimaryKey";

p1.Value = assignmentPrimaryKey;

p2.ParameterName =

"@.rowCount";

p2.Direction =

ParameterDirection.Output;

p2.Value = 0;

command.Parameters.Add(p1); command.Parameters.Add(p2);

reader = command.ExecuteReader();

if (p2.Value ==null)//always true

any suggestions would be appreciated.

thanks. matt

also, reader.HasRows is true.

matt

|||

Hello,

first, I was suspicious of your code that you assigned the value of output parameter p2.value=0.

but it was O.K., and I was wrong , I tested with query analyzer.

I looked carefully in your code, find that you called with ExecuteReader ==> it is connected data object.

you have to close the Reader object before you try to get Output or else paremeter values.

verified with internet search...

|||

EXCELLENT.

thank you very much.

matt

Row_number selecting from a complex select statement

Hi,

Code Snippet

This is difficult to explain in words, but the following code outlines what I am trying to do:

with myTableWithRowNum as
(
select 'row' = row_number() over (order by insertdate desc), myValue
from
(
select table1Id As myValue from myTable1
union
select table2Id As myValue from myTable2
)
)

select * from myTableWithRowNum

Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?

The following query might help you,

Code Snippet

;with UnionResult(myvalue,insertdate)

as

(

select table1Id As myValue,insertdate from myTable1

union

select table2Id As myValue,insertdate from myTable2

),

OrderedResult(myValue,Row)

as

(

select myValue, row_number() over (order by insertdate desc)

from UnionResult

)

select * from OrderedResult

|||

I m not sure I understand your requirment correctly,anyhow your query throws error,

Try the following

Code Snippet

;with myTableWithRowNum as

(

select 'row' = row_number() over (order by insertdate desc), myValue

from

(

select insertdate,table1Id As myValue from myTable1

union

select insertdate,table2Id As myValue from myTable2

) as temp

)

select * from myTableWithRowNum

|||Thanks that's exactly what I'm looking for.
sql

Row Insertion Error

I am writing code to interface with a pre-existing and unchangeable dBase
database that we access through ODBC. This database has column names of the
format "NAME$0101" and "NAME$0102" etc. These column names work fine for
retrieving and updating data via SELECT and UPDATE, but when I try to insert
a new row via INSERT INTO I get the error:
The INSERT INTO statement contains the following unknown field name:
'TRACK$0101'. Make sure you have typed the name correctly, and try the
operation again.
If I create an access database, link to the dbase file, and use the
Microsoft Access ODBC driver instead of the dBase driver, this query works
fine. I have tried all combinations of surrounding the column name in [
] and
"" and ``.
Any ideas?
IanIf no one else responds, perhaps you can get a response from the 'dBase'
discssion alias.
If you get a sloution can you post a response here? This is a very
interesting bug.
--
| Thread-Topic: Row Insertion Error
| thread-index: AcWjacAZLKPUNA2hTIaxHhhgvdybmQ==
| X-WBNR-Posting-Host: 67.20.172.139
| From: "examnotes"
<IanMascomm@.discussions.microsoft.com>
| Subject: Row Insertion Error
| Date: Wed, 17 Aug 2005 13:25:03 -0700
| Lines: 18
| Message-ID: <55CE13A6-1DB1-4F5C-BB40-2C290FD53343@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2595
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| I am writing code to interface with a pre-existing and unchangeable dBase
| database that we access through ODBC. This database has column names of
the
| format "NAME$0101" and "NAME$0102" etc. These column names work fine for
| retrieving and updating data via SELECT and UPDATE, but when I try to
insert
| a new row via INSERT INTO I get the error:
|
| The INSERT INTO statement contains the following unknown field name:
| 'TRACK$0101'. Make sure you have typed the name correctly, and try the
| operation again.
|
| If I create an access database, link to the dbase file, and use the
| Microsoft Access ODBC driver instead of the dBase driver, this query
works
| fine. I have tried all combinations of surrounding the column name in
1;]
and
| "" and ``.
|
| Any ideas?
|
| Ian
|

Row Insertion Error

I am writing code to interface with a pre-existing and unchangeable dBase
database that we access through ODBC. This database has column names of the
format "NAME$0101" and "NAME$0102" etc. These column names work fine for
retrieving and updating data via SELECT and UPDATE, but when I try to insert
a new row via INSERT INTO I get the error:
The INSERT INTO statement contains the following unknown field name:
'TRACK$0101'. Make sure you have typed the name correctly, and try the
operation again.
If I create an access database, link to the dbase file, and use the
Microsoft Access ODBC driver instead of the dBase driver, this query works
fine. I have tried all combinations of surrounding the column name in [] and
"" and ``.
Any ideas?
Ian
If no one else responds, perhaps you can get a response from the 'dBase'
discssion alias.
If you get a sloution can you post a response here? This is a very
interesting bug.
| Thread-Topic: Row Insertion Error
| thread-index: AcWjacAZLKPUNA2hTIaxHhhgvdybmQ==
| X-WBNR-Posting-Host: 67.20.172.139
| From: "=?Utf-8?B?SWFuTWFzY29tbQ==?="
<IanMascomm@.discussions.microsoft.com>
| Subject: Row Insertion Error
| Date: Wed, 17 Aug 2005 13:25:03 -0700
| Lines: 18
| Message-ID: <55CE13A6-1DB1-4F5C-BB40-2C290FD53343@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2595
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| I am writing code to interface with a pre-existing and unchangeable dBase
| database that we access through ODBC. This database has column names of
the
| format "NAME$0101" and "NAME$0102" etc. These column names work fine for
| retrieving and updating data via SELECT and UPDATE, but when I try to
insert
| a new row via INSERT INTO I get the error:
|
| The INSERT INTO statement contains the following unknown field name:
| 'TRACK$0101'. Make sure you have typed the name correctly, and try the
| operation again.
|
| If I create an access database, link to the dbase file, and use the
| Microsoft Access ODBC driver instead of the dBase driver, this query
works
| fine. I have tried all combinations of surrounding the column name in []
and
| "" and ``.
|
| Any ideas?
|
| Ian
|

Row Count problem

I've got a table which I know has 10108 rows in (I've looped through the table with some C# code), yet, in Enterprise manager, when i right click and view properties, the ROWS returns 10043 - a difference of 65 rows!

Why is this?

BTW - not that it should make any difference, but its on SQL Server 2000 standard edition.run

select count(*) from tablename

That will give the true result. Then it will either be down to some dodgy stats/code in EM or your code ;)|||I know that! Like I said - I already KNOW how many rows are in the table - that isn't the issue. The issue is why the right click > Properties gives me a different answer. Even if I DROP the table, re-CREATE it and INSERT maybe 50 rows, I still don't get the correct number of rows that I input.|||You didn't explain that at all. You said you've used c# code and that maybe faulty. IF the count(*) is tallying with your c# code then fair enough. Is it?

row count

I want to get a row count at the end of a report that I am running. Is there
a way to do this, or do I have to write the code? If I have to write it, can
anyone give me a how to guide on writing .net components for reporting
services?
--
www.duanelawrence.comCheck the CountRows() function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_0k6r.asp
E.g.
=CountRows("DataSetName")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"duanelawrence.com" <duanelawrencecom@.discussions.microsoft.com> wrote in
message news:A7A14867-F57E-4C54-B1DF-A3A70FE186FE@.microsoft.com...
> I want to get a row count at the end of a report that I am running. Is
there
> a way to do this, or do I have to write the code? If I have to write it,
can
> anyone give me a how to guide on writing .net components for reporting
> services?
> --
>
> www.duanelawrence.com
>

Row Cannot be Located for Update Error

Can anyone offer any suggetions as to what could be causing this error
in my ASP. Please dont mind the newbie code, I'm still pretty green
at this:
*******ERROR*******
Microsoft Cursor Engine error '80040e38'
Row cannot be located for updating. Some values may have been changed
since it was last read.
-- ASP Code Causing the Trouble --
strSQL = "SELECT * FROM WorkLog WHERE QuoteID = ""eID&" And stepID
= "&eventID
'Create the Recordset object and run SQL statement
Set logRS = Server.CreateObject ("ADODB.Recordset")
logRS.CursorLocation = adUseClient
logRS.Open strSQL, objConn,, adLockOptimistic
logRS("Completed") = True
logRS("CompletedBy") = secID
logRS("DateComplete") = Date()
logRS.Update <--ERROR HERE
'-- Trigger On Effected Table --
CREATE TRIGGER UpdateNextDue ON [dbo].[WorkLog]
FOR UPDATE
AS
Declare @.logID int,
@.quoteID int,
@.stepnum int,
@.userID int,
@.stepID int
Alter Table WorkLog Disable Trigger All
Set @.logID = (Select Log_ID From Inserted)
Set @.quoteID =( Select QuoteID From WorkLog Where Log_ID = @.logID)
Set @.stepID = (Select stepID From WorkLog Where Log_ID = @.logID)
Set @.stepNum =( Select Max(StepNum) From WorkLog Where quoteID = @.quoteID And Completed = 1)
Set @.userID =( Select UserID From WorkLog Where quoteID = @.quoteID And
StepNum = @.stepnum + 1)
If @.stepID <> '65'
Begin
Update WorkLog
Set NextDue = 1
Where QuoteID = @.quoteID And
StepNum = @.stepnum + 1
Alter Table Quotes Disable Trigger All
Update Quotes
Set NextStep = @.userID
Where @.quoteID = quoteID
Alter Table Quotes Enable Trigger All
End
Alter Table WorkLog Enable Trigger AllHow about instead of opening up a recordset, you just execute an UPDATE
statement?
objConn.Execute("UPDATE ... WHERE ...")
The error sounds like you don't have a primary key, or there's not enough
information to identify the row based on the primary key. Since you use
SELECT *, I'm assuming the former. Are you familiar with primary keys? Is
it possible that there is more than one row WHERE QuoteID = ""eID&" And
stepID
= "&eventID ?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"belacyrf" <bela@.webnet-x.com> wrote in message
news:d0e7d44f.0405141126.20601f4@.posting.google.com...
> Can anyone offer any suggetions as to what could be causing this error
> in my ASP. Please dont mind the newbie code, I'm still pretty green
> at this:
> *******ERROR*******
> Microsoft Cursor Engine error '80040e38'
> Row cannot be located for updating. Some values may have been changed
> since it was last read.
> -- ASP Code Causing the Trouble --
> strSQL = "SELECT * FROM WorkLog WHERE QuoteID = ""eID&" And stepID
> = "&eventID
> 'Create the Recordset object and run SQL statement
> Set logRS = Server.CreateObject ("ADODB.Recordset")
> logRS.CursorLocation = adUseClient
> logRS.Open strSQL, objConn,, adLockOptimistic
> logRS("Completed") = True
> logRS("CompletedBy") = secID
> logRS("DateComplete") = Date()
> logRS.Update <--ERROR HERE
> '-- Trigger On Effected Table --
> CREATE TRIGGER UpdateNextDue ON [dbo].[WorkLog]
> FOR UPDATE
> AS
> Declare @.logID int,
> @.quoteID int,
> @.stepnum int,
> @.userID int,
> @.stepID int
> Alter Table WorkLog Disable Trigger All
> Set @.logID = (Select Log_ID From Inserted)
> Set @.quoteID =( Select QuoteID From WorkLog Where Log_ID = @.logID)
> Set @.stepID = (Select stepID From WorkLog Where Log_ID = @.logID)
> Set @.stepNum =( Select Max(StepNum) From WorkLog Where quoteID => @.quoteID And Completed = 1)
> Set @.userID =( Select UserID From WorkLog Where quoteID = @.quoteID And
> StepNum = @.stepnum + 1)
> If @.stepID <> '65'
> Begin
> Update WorkLog
> Set NextDue = 1
> Where QuoteID = @.quoteID And
> StepNum = @.stepnum + 1
> Alter Table Quotes Disable Trigger All
> Update Quotes
> Set NextStep = @.userID
> Where @.quoteID = quoteID
> Alter Table Quotes Enable Trigger All
> End
> Alter Table WorkLog Enable Trigger All

Row Cannot be Located for Update Error

Can anyone offer any suggetions as to what could be causing this error
in my ASP. Please dont mind the newbie code, I'm still pretty green
at this:
*******ERROR*******
Microsoft Cursor Engine error '80040e38'
Row cannot be located for updating. Some values may have been changed
since it was last read.
-- ASP Code Causing the Trouble --
strSQL = "SELECT * FROM WorkLog WHERE QuoteID = ""eID&" And stepID
= "&eventID
'Create the Recordset object and run SQL statement
Set logRS = Server.CreateObject ("ADODB.Recordset")
logRS.CursorLocation = adUseClient
logRS.Open strSQL, objConn,, adLockOptimistic
logRS("Completed") = True
logRS("CompletedBy") = secID
logRS("DateComplete") = Date()
logRS.Update <--ERROR HERE
'-- Trigger On Effected Table --
CREATE TRIGGER UpdateNextDue ON [dbo].[WorkLog]
FOR UPDATE
AS
Declare @.logID int,
@.quoteID int,
@.stepnum int,
@.userID int,
@.stepID int
Alter Table WorkLog Disable Trigger All
Set @.logID = (Select Log_ID From Inserted)
Set @.quoteID =( Select QuoteID From WorkLog Where Log_ID = @.logID)
Set @.stepID = (Select stepID From WorkLog Where Log_ID = @.logID)
Set @.stepNum =( Select Max(StepNum) From WorkLog Where quoteID =
@.quoteID And Completed = 1)
Set @.userID =( Select UserID From WorkLog Where quoteID = @.quoteID And
StepNum = @.stepnum + 1)
If @.stepID <> '65'
Begin
Update WorkLog
Set NextDue = 1
Where QuoteID = @.quoteID And
StepNum = @.stepnum + 1
Alter Table Quotes Disable Trigger All
Update Quotes
Set NextStep = @.userID
Where @.quoteID = quoteID
Alter Table Quotes Enable Trigger All
End
Alter Table WorkLog Enable Trigger All
How about instead of opening up a recordset, you just execute an UPDATE
statement?
objConn.Execute("UPDATE ... WHERE ...")
The error sounds like you don't have a primary key, or there's not enough
information to identify the row based on the primary key. Since you use
SELECT *, I'm assuming the former. Are you familiar with primary keys? Is
it possible that there is more than one row WHERE QuoteID = ""eID&" And
stepID
= "&eventID ?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"belacyrf" <bela@.webnet-x.com> wrote in message
news:d0e7d44f.0405141126.20601f4@.posting.google.co m...
> Can anyone offer any suggetions as to what could be causing this error
> in my ASP. Please dont mind the newbie code, I'm still pretty green
> at this:
> *******ERROR*******
> Microsoft Cursor Engine error '80040e38'
> Row cannot be located for updating. Some values may have been changed
> since it was last read.
> -- ASP Code Causing the Trouble --
> strSQL = "SELECT * FROM WorkLog WHERE QuoteID = ""eID&" And stepID
> = "&eventID
> 'Create the Recordset object and run SQL statement
> Set logRS = Server.CreateObject ("ADODB.Recordset")
> logRS.CursorLocation = adUseClient
> logRS.Open strSQL, objConn,, adLockOptimistic
> logRS("Completed") = True
> logRS("CompletedBy") = secID
> logRS("DateComplete") = Date()
> logRS.Update <--ERROR HERE
> '-- Trigger On Effected Table --
> CREATE TRIGGER UpdateNextDue ON [dbo].[WorkLog]
> FOR UPDATE
> AS
> Declare @.logID int,
> @.quoteID int,
> @.stepnum int,
> @.userID int,
> @.stepID int
> Alter Table WorkLog Disable Trigger All
> Set @.logID = (Select Log_ID From Inserted)
> Set @.quoteID =( Select QuoteID From WorkLog Where Log_ID = @.logID)
> Set @.stepID = (Select stepID From WorkLog Where Log_ID = @.logID)
> Set @.stepNum =( Select Max(StepNum) From WorkLog Where quoteID =
> @.quoteID And Completed = 1)
> Set @.userID =( Select UserID From WorkLog Where quoteID = @.quoteID And
> StepNum = @.stepnum + 1)
> If @.stepID <> '65'
> Begin
> Update WorkLog
> Set NextDue = 1
> Where QuoteID = @.quoteID And
> StepNum = @.stepnum + 1
> Alter Table Quotes Disable Trigger All
> Update Quotes
> Set NextStep = @.userID
> Where @.quoteID = quoteID
> Alter Table Quotes Enable Trigger All
> End
> Alter Table WorkLog Enable Trigger All

Row Cannot be Located for Update Error

Can anyone offer any suggetions as to what could be causing this error
in my ASP. Please dont mind the newbie code, I'm still pretty green
at this:
*******ERROR*******
Microsoft Cursor Engine error '80040e38'
Row cannot be located for updating. Some values may have been changed
since it was last read.
-- ASP Code Causing the Trouble --
strSQL = "SELECT * FROM WorkLog WHERE QuoteID = ""eID&" And stepID
= "&eventID
'Create the Recordset object and run SQL statement
Set logRS = Server.CreateObject ("ADODB.Recordset")
logRS.CursorLocation = adUseClient
logRS.Open strSQL, objConn,, adLockOptimistic
logRS("Completed") = True
logRS("CompletedBy") = secID
logRS("DateComplete") = Date()
logRS.Update <--ERROR HERE
'-- Trigger On Effected Table --
CREATE TRIGGER UpdateNextDue ON [dbo].[WorkLog]
FOR UPDATE
AS
Declare @.logID int,
@.quoteID int,
@.stepnum int,
@.userID int,
@.stepID int
Alter Table WorkLog Disable Trigger All
Set @.logID = (Select Log_ID From Inserted)
Set @.quoteID =( Select QuoteID From WorkLog Where Log_ID = @.logID)
Set @.stepID = (Select stepID From WorkLog Where Log_ID = @.logID)
Set @.stepNum =( Select Max(StepNum) From WorkLog Where quoteID =
@.quoteID And Completed = 1)
Set @.userID =( Select UserID From WorkLog Where quoteID = @.quoteID And
StepNum = @.stepnum + 1)
If @.stepID <> '65'
Begin
Update WorkLog
Set NextDue = 1
Where QuoteID = @.quoteID And
StepNum = @.stepnum + 1
Alter Table Quotes Disable Trigger All
Update Quotes
Set NextStep = @.userID
Where @.quoteID = quoteID
Alter Table Quotes Enable Trigger All
End
Alter Table WorkLog Enable Trigger AllHow about instead of opening up a recordset, you just execute an UPDATE
statement?
objConn.Execute("UPDATE ... WHERE ...")
The error sounds like you don't have a primary key, or there's not enough
information to identify the row based on the primary key. Since you use
SELECT *, I'm assuming the former. Are you familiar with primary keys? Is
it possible that there is more than one row WHERE QuoteID = ""eID&" And
stepID
= "&eventID ?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"belacyrf" <bela@.webnet-x.com> wrote in message
news:d0e7d44f.0405141126.20601f4@.posting.google.com...
> Can anyone offer any suggetions as to what could be causing this error
> in my ASP. Please dont mind the newbie code, I'm still pretty green
> at this:
> *******ERROR*******
> Microsoft Cursor Engine error '80040e38'
> Row cannot be located for updating. Some values may have been changed
> since it was last read.
> -- ASP Code Causing the Trouble --
> strSQL = "SELECT * FROM WorkLog WHERE QuoteID = ""eID&" And stepID
> = "&eventID
> 'Create the Recordset object and run SQL statement
> Set logRS = Server.CreateObject ("ADODB.Recordset")
> logRS.CursorLocation = adUseClient
> logRS.Open strSQL, objConn,, adLockOptimistic
> logRS("Completed") = True
> logRS("CompletedBy") = secID
> logRS("DateComplete") = Date()
> logRS.Update <--ERROR HERE
> '-- Trigger On Effected Table --
> CREATE TRIGGER UpdateNextDue ON [dbo].[WorkLog]
> FOR UPDATE
> AS
> Declare @.logID int,
> @.quoteID int,
> @.stepnum int,
> @.userID int,
> @.stepID int
> Alter Table WorkLog Disable Trigger All
> Set @.logID = (Select Log_ID From Inserted)
> Set @.quoteID =( Select QuoteID From WorkLog Where Log_ID = @.logID)
> Set @.stepID = (Select stepID From WorkLog Where Log_ID = @.logID)
> Set @.stepNum =( Select Max(StepNum) From WorkLog Where quoteID =
> @.quoteID And Completed = 1)
> Set @.userID =( Select UserID From WorkLog Where quoteID = @.quoteID And
> StepNum = @.stepnum + 1)
> If @.stepID <> '65'
> Begin
> Update WorkLog
> Set NextDue = 1
> Where QuoteID = @.quoteID And
> StepNum = @.stepnum + 1
> Alter Table Quotes Disable Trigger All
> Update Quotes
> Set NextStep = @.userID
> Where @.quoteID = quoteID
> Alter Table Quotes Enable Trigger All
> End
> Alter Table WorkLog Enable Trigger All

Rounding Up

I have the following code that retreives the current value of the item price. however it always rounds up. If I manually enter a return value like so:
return (decimal)12.47
It returns the correct value, however if I set it with an expression like this:
return(decimal)arParam[1].Value;
It rounds the number up: How can I get it to not round up when insertign a value based ona expression?

publicdecimal GetCreditPrice(string CustomerSecurityKey)

{

try

{

System.Data.SqlClient.SqlParameter prmCrnt;

System.Data.SqlClient.SqlParameter[] arParam =new System.Data.SqlClient.SqlParameter[2];

prmCrnt =new System.Data.SqlClient.SqlParameter("@.CustomerSecurityKey", SqlDbType.VarChar,25);

prmCrnt.Value = CustomerSecurityKey;

arParam[0] = prmCrnt;

prmCrnt =new System.Data.SqlClient.SqlParameter("@.Price", SqlDbType.Decimal);

prmCrnt.Direction = ParameterDirection.Output;

arParam[1] = prmCrnt;

SqlHelper.ExecuteNonQuery(stConnection, CommandType.StoredProcedure, "GetCreditPrice", arParam);

return(decimal)arParam[1].Value;

}

catch(System.Exception ex)

{

throw ex;

}

}

Try the link I posted in this post for custom String Formatting. Hope this helps.
http://forums.asp.net/887067/ShowPost.aspx

Rounding differences

I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different:
eg.
ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding

SQL SProc: 1665.91 Rounds down
ADP VBA: 1665.92 Rounds up

Does this make sense?Originally posted by ingineu
I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different:
eg.
ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding

SQL SProc: 1665.91 Rounds down
ADP VBA: 1665.92 Rounds up

Does this make sense?
try using

select ROUND(17 * 97995.0 / 1000.0, 2)|||That didn't change anything. Any other ideas?|||I am getting

select ROUND(17.0 * 97995.0 / 1000.0,2)

------
1665.92000000

Is this not what you wanted?|||Originally posted by ingineu
I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different:
eg.
ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding

SQL SProc: 1665.91 Rounds down
ADP VBA: 1665.92 Rounds up

Does this make sense?

My guess is that you've dimensioned the variable that catches the calculation (17 * 97995 / 1000,2) as something like a numeric(n,2) instead a numeric(n,2)|||Okay ... I had not tried the 17.0. When I enter all values with 1 decimal place, it works. However, this is not satisfactory as I am using fields in the calculation as follows:

SELECT #Work1.*
ZAmt = CASE
WHEN TestCd = 'A' THEN Round(TestVal * ZFlow / 1000, 2)
ELSE Round(TestVal * ZFlow2 / 1000, 2) END
FROM #Work1

How would I define ZAmt?|||Originally posted by ingineu
Okay ... I had not tried the 17.0. When I enter all values with 1 decimal place, it works. However, this is not satisfactory as I am using fields in the calculation as follows:

SELECT #Work1.*
ZAmt = CASE
WHEN TestCd = 'A' THEN Round(TestVal * ZFlow / 1000, 2)
ELSE Round(TestVal * ZFlow2 / 1000, 2) END
FROM #Work1

How would I define ZAmt?

You're going to want ZFlow, TestVal and ZFlow2 to all have at least 3 digits to the right of the decimal place. I'd also have ZAmt have at least 3 digits to the right of the decimal place.|||Assuming all of your fields are defined as integer (if they are defined as numeric or decimal, you are already set).

ZAmt = CASE
WHEN TestCd = 'A' THEN Round(convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow) / 1000.0, 2)
ELSE Round(convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow2) / 1000.0, 2) END|||Originally posted by MCrowley
Assuming all of your fields are defined as integer (if they are defined as numeric or decimal, you are already set).

ZAmt = CASE
WHEN TestCd = 'A' THEN Round(convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow) / 1000.0, 2)
ELSE Round(convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow2) / 1000.0, 2) END

NO!!!!! 20,3!!!!!

otherwise, you might as not use the rounding function at all!!!|||After testing on a machine here:

create table test1
(testcd varchar(10),
testval int,
zflow int,
zflow2 int)
go
insert into test1
values ('a', 10, 10, 10)

insert into test1
values ('b', 10, 10, 1)

insert into test1
values ('a', 1, 5, 10)

select ZAmt = CASE
WHEN TestCd = 'A' THEN Round(convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow) / 1000.0, 2)
ELSE Round(convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow2) / 1000.0, 2) END ,
testcd, convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow) / 1000.0
from test1
go
drop table test1|||My fields are defined as 'float'. I tried the 'convert(numeric' with no change. Do I need to convert the float field? Is there no function around that takes a column and Rounds it to whatever significant digits you request?|||I tried it with the (20,3) and it works. Thanks for helping me out on this one. Much appreciated. I decided to do it in 2 steps, 1. calculate the column, 2. Round the column, for accuracy.

round function in sql statement not executing

Hi , I'm new to t-sql so bear with me cos this might be a stupid question.

The code below

SELECT [CODE], [PERIOD], [OPERATOR], [RATE], Round(IIF([OPERATOR]='/',[RATE],1/[RATE]),3) AS EXCHRATE
INTO tbl_currency_test
FROM [SUNDB].[dbo].[SSRFCNV]
WHERE [CODE]='NGN' AND [PERIOD]<>0

always gives me the following error message

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.

However when i remove the round() code from the statement it executes fine.

Can someone please please let me know what i'm doing wrong here....

Cheers

Hi,

The IIF is for MDX statements. For SQL statements, you need to use the CASE keyword.

SELECT [CODE], [PERIOD], [OPERATOR], [RATE], Round(CASE WHEN [OPERATOR]='/' THEN [RATE] ELSE 1/[RATE] END,3) AS EXCHRATE
INTO tbl_currency_test
FROM [SUNDB].[dbo].[SSRFCNV]
WHERE [CODE]='NGN' AND [PERIOD]<>0

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Here is the definition for the CASE keyword:

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

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Dude,

That was pure ....

Thanks a mil

Rotating Text with Custom Code

Is it possible to actually rotate text 90 degrees to the left or right in a text box using custom code?The only WritingMode options supported in the current release are tb-rl and
lr-tb.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chandler" <Chandler@.discussions.microsoft.com> wrote in message
news:A965AF5C-80B3-403F-96B9-E486151F2CB3@.microsoft.com...
> Is it possible to actually rotate text 90 degrees to the left or right in
a text box using custom code?