How to cast the value in C# resulted from max() command in SQL Server Developer

Could anyone help of how to cast the value in C# resulted from max() command. To complicate the matter, from the query result I see in the Microsoft SQL Server Management Studio, if there are records the value is number. But if there are no records, the value is NULL. How do I handle these two possible different conditions?

I have tried:

- stringtest = (string)reader["MaxOrderID"];

- inttest = Convert.ToInt32((string)reader["MaxOrderID"]);

- stringtest = Convert.ToInt32(reader["MaxOrderID"]).ToString();

all failed. And what do I do if the value is NULL. And also how do I do to cope with these two different possible conditions?

For example, I have the following code:

command.CommandText ="Select max(OrderID) as 'MaxOrderID' from [Order]";command.CommandType =CommandType.Text;

command.Connection = conn;

command.Connection.Open();

reader = command.ExecuteReader();

reader.Read();

? orderID = ?reader["MaxOrderID"];

Select MAX(ISNULL(OrderID,0) as MaxOrderID from [Order] try this statment and retest your three statments again...

- stringtest = (string)reader["MaxOrderID"];

- inttest = Convert.ToInt32((string)reader["MaxOrderID"]);

- stringtest = Convert.ToInt32(reader["MaxOrderID"]).ToString();

|||

I executedSelect MAX(ISNULL(OrderID,0)) as MaxOrderID from [Order] , but the value is still NULL, when the table has no records.

I can see that it should be 0.

|||

hi dedyandy,

dedyandy:

I executedSelect MAX(ISNULL(OrderID,0)) as MaxOrderID from [Order] , but the value is still NULL, when the table has no records.

what you are getting is correct, max will return value if there's any record in table else it wont return anything i.e. its a null. you can either use 1 as default value in case there are no records or in case you've procedure then you can check something like

if @.@.Rowcount = 0 Select 1 as 'MaxOrderID'

thanks,

satish.

|||

Thanks Satish. Yes, I will use Count function first. If there are records, I will use AVG function, else return 1.

Andy.

|||

cheers

thanks,

Satish.

How to cast empty string

I want to replace a column value with a null if the string is empty. I would have thought this simple expression would do it:

RTRIM([FromContractSymbol]) == "" ? NULL(DT_STR, 0, 1252) : (DT_STR, 6, 1252)FromContractSymbol

Yet, I get the following error:

For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operators. The expression "...see above..." has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation.

The expression works if I replace the NULL(DT_STR, 0, 1252) with say "A" and the expression works on other non-string columns. (As in "NULL(DT_I1) : (DT_I1)100")

The error does explain how to solve it. Although you have specified the type for the NULL you have to cast it.

So if you change your line to

RTRIM([FromContractSymbol]) == "" ? (DT_STR, 6, 1252)NULL(DT_STR, 6, 1252) : (DT_STR, 6, 1252)FromContractSymbol

It should work

HOW to Cast an AS ConnectionManager into a SriptTask?

In fact, we could use AMO in the Sript Task, just by include the AMO.dll, many guys have talked about it on the forum.

But now, O my god, I met a big problem.

I declare a AS connectionManager in the SSIS package. In the Script Task I can't use it.

If this is a OEL DB ConnectionManager and connect to SQL Sever, I know I could write this inside the Sript Task:

Public myKPIConnection As SqlClient.SqlConnection

myKPIConnection = _
DirectCast(Dts.Connections("CYF.KPIOperation").AcquireConnection(Dts.Transaction), _
SqlClient.SqlConnection)

Then I could use myKPIConnection inside the Task.

But, How to do the similar thing to a AS ConnectionManager? I need to DirectCast the AS connectionManager to What?

By the way, the only thing I want to do is to Start an AS transaction inside the Sript Task, and let a Process Task outside to be enlisted in the trransaction. So I need to use the same AS connectionmanager.

Thanks.

Your example is not correct. You cannot use an OLE-DB connection for SQL and utilise it inside a Script Task. You have to be using the ADO.NET connection, such as with sub-type of SqlClient.SqlConnection, which means you can cast the connection manager AcquireConnection to that type.

You have the same issue here, the AS connection is the MSOLAP90 OLE-DB provider, which cannot be used in .Net directly, as this a native OLE-DB provider.

The best thing to do woudl be to read the ConnectionString property from the connection and use that against a Microsoft.AnalysisServices.Server class, calling the Connect method. That is no doubt what MS have done when using AS connections in managed code.

|||

Thanks, DarrenSQLIS.

I know I could do this, but I want to bound another Process Task in the package into the same stransaction of the Script Task. I mean, I could use that against a Microsoft.AnalysisServices.Server class, calling the Connect method, and begin an AS transaction inside the Script Task, then I want to enlist the Process Task into this transaction.

How could I do this?

How to cast a numeric database field to character

Hello.
I have a report and need to concatenate two numeric database fields, a month
and a year, into a string with a slash (/) between them and put it on the
report header.
How can I do this?
Thanks in advance,
MikeUse CStr(Month) & "/" & CStr(Year)
"MikeL" wrote:
> Hello.
> I have a report and need to concatenate two numeric database fields, a month
> and a year, into a string with a slash (/) between them and put it on the
> report header.
> How can I do this?
> Thanks in advance,
> Mike
>
>