Expression.Like for numbers?

Hi,

Is it possible to search in columns with a number datatype (I'm using an MS SQL database with bigint columns) with the ICriterion Expression.Like?

Normally the Expression.Like is used for varchar columns. However, if there's a bigint column with the value 167829 I want to search for example on %678%.

Cheers,

koekie

You can cast the BIGINT to varchar ad test that like:

WHERE CONVERT(VARCHAR(20), ColName) LIKE '%678%'

|||

In other words, something like this:

Expression.Sql("CONVERT(VARCHAR(20), {alias}.ColName) LIKE ?", "%678%", NHibernateUtil.String )

|||

Cool, thanks a lot.

Expression Editor - avg problem

This is my data set

PersonIdType (varchar 20)

153 NewlyEnrolled

58 8

248 Enrolled

85723

I am trying to show an avg for all the numbers in the ‘Type’ field

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, CDbl(Fields!Type.value)))

I get the error “Input string was not in a correct format.”

If I use:

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, 1))

It works and it returns ‘1’

so the problem is CDbl(Fields!Type.value). i'm tring to convert it to a number so it can be used in the avg function.

any idea what I'm doing wrong?

CDbl returns a double from (typically) a floating point number.

How about trying out CInt or StrConv?

|||

CInt, Cdbl, Cdec, Csng.... all do the same thing.

StrConv is for string manipulation, cant use that to convert to a number

|||

What happens when you simply take out Cdbl?

I suppose you get an error stating that you can't average a varchar field?

Try this:

=CStr(CInt(Fields!Type.Value))

|||

What happens when you simply take out Cdbl?

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

=CStr(CInt(Fields!Type.Value))

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

I also tried

=CInt(CStr(Fields!Type.Value))

Input string was not in a correct format.

|||I just made a new field that held the numbers and did an average on that. Not what I wanted but it worked.

Expression Editor - avg problem

This is my data set

PersonIdType (varchar 20)

153 NewlyEnrolled

58 8

248 Enrolled

85723

I am trying to show an avg for all the numbers in the ‘Type’ field

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, CDbl(Fields!Type.value)))

I get the error “Input string was not in a correct format.”

If I use:

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, 1))

It works and it returns ‘1’

so the problem is CDbl(Fields!Type.value). i'm tring to convert it to a number so it can be used in the avg function.

any idea what I'm doing wrong?

CDbl returns a double from (typically) a floating point number.

How about trying out CInt or StrConv?

|||

CInt, Cdbl, Cdec, Csng.... all do the same thing.

StrConv is for string manipulation, cant use that to convert to a number

|||

What happens when you simply take out Cdbl?

I suppose you get an error stating that you can't average a varchar field?

Try this:

=CStr(CInt(Fields!Type.Value))

|||

What happens when you simply take out Cdbl?

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

=CStr(CInt(Fields!Type.Value))

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

I also tried

=CInt(CStr(Fields!Type.Value))

Input string was not in a correct format.

|||I just made a new field that held the numbers and did an average on that. Not what I wanted but it worked.

Exporting table structures to another database

We have databases with large numbers of tables. We have a separate database for each year. For various reasons, we need to export about 100 of the tables (Structure only, not their data) from last years database into this year's database. What is the best method for doing this? The import/export wizard creates the tables but does not bring in important things like keys.

Regards Shirley A

hi Shirley,

if you are using sql server 2000.

you can use the enterprise manager.

you can right click the database click on "all task" and then

click on "generate Sql scripts" then clcik on options.

click on the objects you need such as PKs, triggers and foreign keys"

for Sql server 2005 you can use the Management studio

right click the database. clcik on task. clcik on generate scripts

and check the options that you need.

after you generate the scripts run it on the new server

regards,

joey

|||

Thanks very much, that worked a treat. I used Enterprise Manager as our databases are SQL Server 2000. Regards

Shirley