How do I know if there is a duplicate value in database

hello, i am creating a user login system. When people register for the site I need a way for sql to check weather or not their is a duplicate username already in the databse. Currently, I am going through the whole member profile table searching for a duplicate name. Is there a better way to do this?i am not sure how u r performing the check and what is bothering u. however, u can create a unique index on that field and fire the insert without any check. a duplicate value will produce an error.|||Currently, I am going through the whole member profile table searching for a duplicate name. Is there a better way to do this?
If for some strange reason the unique index (actually this should be the primary key) is not an option for you, why do you "go through the whole table"?.
A simple

SELECT couint(*) from user_profile WHERE username = 'input_value';

will also tell you if there is another row with that username.|||hello, i am creating a user login system. When people register for the site I need a way for sql to check weather or not their is a duplicate username already in the databse. Currently, I am going through the whole member profile table searching for a duplicate name. Is there a better way to do this?

SELECT username, count(*)
FROM tblLogins
GROUP BY username
HAVING COUNT(*) > 1|||INSERT INTO Table(Collist) SELECT values
SELECT @.error = @.@.ERROR
IF @.@.ERROR <> 0
BEGIN
Error Handling|||hmmmm..so which way is the fastest and most efficient?|||I'd say the most efficient would be the unique index (or primary key) option, because, with correct error handling, not only can you prevent the problem from occurring, you can also provide a meaningful error message to users.sql

How do i just SUM the group header values?

Here's my table so far in RS:

Facility Name | Claim | Fee | Value | Payment | Table Header
Wisconsin West | | | | | Group1 Header
| 2356 | $45 | $23 | | Group2 Header
| | | | $21 | Details
| | | | $7 | Details
| | | | $16 | Details
| 2357 | $85 | $47 | | Group2 Header
| | | | $21 | Details
| | | | $9 | Details
| | | | $13 | Details
| 2358 | $105 | $65 | | Group2 Header
| | | | $35 | Details
| | | | $12 | Details
| | | | $20 | Details
Facility Totals | 3 | $705 | $405 | $154 | Group1 Footer

*Notes = Table and Group2 footers are hidden and contains nothing. The last column is detailing what level each line is and not actually part of the table.

I believe my issue is with the SUM function and how or where to place it. In the Payment field, the SUM function is adding correctly, but for the Fee and Value field, the SUM fuction is adding as if every line item in the payment field had the fee and value amount, hence the huge amount.

How can make the Fee field total to $235 (45+85+105) instead of $705 (45+45+45+85+85+85+105+105+105); the same goes for the Value field of $135 (23+47+65) instead of $405 (23+23+23+47+47+47+65+65+65).

Below are the expressions I have in place at the Group1 Footer for the Claim, Fee, Value, and Payment fields respectively:

=CountDistinct(Fields!ClaimNumber.Value

=Sum(Fields!dAmount.Value)

=Sum(Fields!Value.Value)

=Sum(Fields!cAmount.Value)

I've tried playing with the scope, but to no avail. Any ideas or maybe I'm doing it all wrong? It's almost as if I need a SumDistinct if such a thing exist.

In the meantime, I'm doing some serious researching. Thanks!

I'm sure this isn't the solution to your problem.

But it does have a sum distinct code sample.
http://msdn2.microsoft.com/en-us/library/bb395166.aspx

quite why they choose to embed the code samples as bitmaps is beyond me

I'm sure you can do something with IIF to fix it.

How do I jump to another report based on a value in my current report? report has no parameters.

How do I jump to another report based on a value in my current report. The report that I am jumping from has no parameters, just values.

Hello,

Does your target report have parameters? For example, you want to go to the target report and run it with the value you selected on the current report?

Or are you saying... If the field value is between a and b, go to report 1, if it's between b and c, go to report 2, otherwise go to report 3.

Can you explain a little more about your situation?

Jarret

|||

the report I am jumping from has no parameters. I want to be able to click on a value and have it jump to another report. That report possibly being jumped too might or might not have parameters.

I just used the following expression and it works to a degree, but it enables all the values in the column for jumping, rather than just the report I want

=iif(Fields!YN_DESC.Value="A","Report 1","Report2")

|||

Sorry for the delay...

This will only enable the "A" values to jump to a report (Report 1), any other values will not have the jump to enabled.

=Switch(Fields!YN_Desc.Value = "A", "Report 1")

If you need to add additional conditions... Example, if the value is "C" - Report 31, "D" - Report 7.

=Switch(Fields!YN_Desc.Value = "A", "Report 1",
Fields!YN_Desc.Value = "C", "Report 31",
Fields!YN_Desc.Value = "D", "Report 7")

Hope this helps.

Jarret

How do I jump to another report based on a value in my current report? report has no parameters.

How do I jump to another report based on a value in my current report. The report that I am jumping from has no parameters, just values.

Hello,

Does your target report have parameters? For example, you want to go to the target report and run it with the value you selected on the current report?

Or are you saying... If the field value is between a and b, go to report 1, if it's between b and c, go to report 2, otherwise go to report 3.

Can you explain a little more about your situation?

Jarret

|||

the report I am jumping from has no parameters. I want to be able to click on a value and have it jump to another report. That report possibly being jumped too might or might not have parameters.

I just used the following expression and it works to a degree, but it enables all the values in the column for jumping, rather than just the report I want

=iif(Fields!YN_DESC.Value="A","Report 1","Report2")

|||

Sorry for the delay...

This will only enable the "A" values to jump to a report (Report 1), any other values will not have the jump to enabled.

=Switch(Fields!YN_Desc.Value = "A", "Report 1")

If you need to add additional conditions... Example, if the value is "C" - Report 31, "D" - Report 7.

=Switch(Fields!YN_Desc.Value = "A", "Report 1",
Fields!YN_Desc.Value = "C", "Report 31",
Fields!YN_Desc.Value = "D", "Report 7")

Hope this helps.

Jarret

How do I jump to another report based on a value

How do I jump to another report based on a value in my current report?

Right-click on the field you wish to link, select Navigation, select jump to report, and specify the report and any parameters required.

Here are a couple of links that may help.

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

http://www.codeproject.com/useritems/InputParmReport.asp?df=100&forumid=290297&exp=0&select=1472409

cheers,

Andrew

|||

Thanks Andrew,

The examples that you gave me are based on parameters. I made a mistake. What I actually need is to be able to jump to a

report based on a value in one of the report columns. The report that I am jumping from has no parameters and is just a table with

columns and values. How can I do this?

|||

Hi,

you can use the hyperlink action "jump to report" using an expression like this:

=IIf(Logical_Expression, "reportname 1", "reportname 2")

Best regards

Sven

|||

Hi Sven

I tried doing this and it jumps to the report. The problem I am encountering is that the whole column is being enabled to jump rather than just the selected being enabled when the user passes his cursour over it. How do I enable just that value and not all the column values?

|||Hi,
then you have to use the switch-function instead of the iif-function.
=switch(Logical_Expression, "reportname")
br
Sven

How do I insert a string value with quotes into a nvarchar column

I am reading data from another data source and storing it in the sqlce database. Some of the string values I'm trying to insert into the database have single quotes in the string (i.e. Johnny's Company). When I try to insert the values with the single quotes, it throws an exception. The code I use to insert the records is as follows:

cmd.CommandText = "INSERT sy_company " +

" (company_id, company, co_name, companyid) " +

"VALUES(" +

"'" + dtSYCompany.Rows[x]["company_id"] + "'," +

"N'" + dtSYCompany.Rows[x]["company"] + "'," +

"N'" + dtSYCompany.Rows[x]["co_name"] + "'," +

"'" + dtSYCompany.Rows[x]["companyid"] + "')";

cmd.ExecuteNonQuery();

When the company name (co_name) has a single quote in it, I get the error. How do I write the insert statement so it will work even though the value being inserted into co_name has a single quote in it?

Thanks so much!

Use parameters. It’s also great for performance and security.

By the way, it appears you’re using DataTable. In that case you could use DataAdapter.Update() instead of running commands manually. CommandBuilder can generate parametrized command for you.

|||That did it!!! Thanks so much.

How do i implement Multi value parameter

Hi..

I want to have a multivalue parameter in my report... I have selected that parameter and clicked on multivalue and then when i run the report.. it works fine for one and and not for more than one...

any help will be appreciated.

Regards

Karen

Karen, a question, how is your multivalue parameter used in your datasource? It should be :

SELECT *
FROM HELLO_DATA
WHERE COLUMN1 IN (@.MULTIVALUEPARAMETER1)

Hope this helps,

|||

thanks i figured it out and now it works fine.

Regards

Karen

sql

How do I hide zeros w/o a conditional stmt

I need a way to format fields without having to write a conditional statement
based on each textbox's value. I'm dealing with hundreds of fields.
Something like a HideZeros setting on the field. Any help would be
appreciatedWhat I do is change the Format property of each textbox to a custom value:
#,##0.0;(#,##0.0);-
the 1st group is how to display positive values;
the 2nd is for negatives;
and the 3rd is for zeroes (I am just displaying a dash here).
good luck,
Greg
"Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
message news:64AAF2A7-F92B-4E91-8F96-3A9AAB2088FC@.microsoft.com...
>I need a way to format fields without having to write a conditional
>statement
> based on each textbox's value. I'm dealing with hundreds of fields.
> Something like a HideZeros setting on the field. Any help would be
> appreciated|||Thanks!
Worked great. Any idea on how to use that with a Percentage field.
"Blake Gremillion" wrote:
> I need a way to format fields without having to write a conditional statement
> based on each textbox's value. I'm dealing with hundreds of fields.
> Something like a HideZeros setting on the field. Any help would be
> appreciated|||I would try custom again and this time use:
0.0%;(0.0%);-
I pesonally use custom with percentages anyways and use
P1
to get just one decimal place. I think the standard "percentage" option,
gives you two. Of course neither hides zeroes. ;(
Greg
"Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
message news:0B62BEDB-123A-4B5A-B363-ACAF9759425C@.microsoft.com...
> Thanks!
> Worked great. Any idea on how to use that with a Percentage field.
>
>
> "Blake Gremillion" wrote:
>> I need a way to format fields without having to write a conditional
>> statement
>> based on each textbox's value. I'm dealing with hundreds of fields.
>> Something like a HideZeros setting on the field. Any help would be
>> appreciated|||Yeah, I think that's what I'll have to do instead of using the Px format.
Thanks Again!
"Greg Burns" wrote:
> I would try custom again and this time use:
> 0.0%;(0.0%);-
> I pesonally use custom with percentages anyways and use
> P1
> to get just one decimal place. I think the standard "percentage" option,
> gives you two. Of course neither hides zeroes. ;(
> Greg
>
> "Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
> message news:0B62BEDB-123A-4B5A-B363-ACAF9759425C@.microsoft.com...
> > Thanks!
> >
> > Worked great. Any idea on how to use that with a Percentage field.
> >
> >
> >
> >
> > "Blake Gremillion" wrote:
> >
> >> I need a way to format fields without having to write a conditional
> >> statement
> >> based on each textbox's value. I'm dealing with hundreds of fields.
> >> Something like a HideZeros setting on the field. Any help would be
> >> appreciated
>
>

How do I hide a table in a report programatically?

Hi,
I have 2 tables in a report and, depending on which value a user picks in a
particular parameter, I want to display only one of the tables. Is there any
way to do something like, "IIF(parameters!param_A.value = "Y", visible = true, visible = false)" or something like that for a table?
--
Thanks,
JeffSet the table's Hidden property to an expression. For example:
=CBool(Parameters!HideTheTable.Value)
The Hidden property can be accessed by expanding Visibility... in the
Properties window.
HTH
On Oct 1, 3:39 pm, Jeff Stroope
<JeffStro...@.discussions.microsoft.com> wrote:
> Hi,
> I have 2 tables in a report and, depending on which value a user picks in a
> particular parameter, I want to display only one of the tables. Is there any
> way to do something like, "IIF(parameters!param_A.value = "Y", visible => true, visible = false)" or something like that for a table?
> --
> Thanks,
> Jeff|||On Oct 1, 5:39 pm, Jeff Stroope
<JeffStro...@.discussions.microsoft.com> wrote:
> Hi,
> I have 2 tables in a report and, depending on which value a user picks in a
> particular parameter, I want to display only one of the tables. Is there any
> way to do something like, "IIF(parameters!param_A.value = "Y", visible => true, visible = false)" or something like that for a table?
> --
> Thanks,
> Jeff
The best way to accomplish this is to control the data populating the
table controls at the stored procedure/query level that is sourcing
the report. For example, pass the parameter value back to the stored
procedure(s)/query(ies) and if it is a certain value, return an empty
resultset to the report. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Chris!
--
Thanks,
Jeff
"Chris Durkin" wrote:
> Set the table's Hidden property to an expression. For example:
> =CBool(Parameters!HideTheTable.Value)
>
> The Hidden property can be accessed by expanding Visibility... in the
> Properties window.
>
> HTH
>
> On Oct 1, 3:39 pm, Jeff Stroope
> <JeffStro...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have 2 tables in a report and, depending on which value a user picks in a
> > particular parameter, I want to display only one of the tables. Is there any
> > way to do something like, "IIF(parameters!param_A.value = "Y", visible => > true, visible = false)" or something like that for a table?
> > --
> > Thanks,
> >
> > Jeff
>
>|||Thanks Enrique!
--
Thanks,
Jeff
"EMartinez" wrote:
> On Oct 1, 5:39 pm, Jeff Stroope
> <JeffStro...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have 2 tables in a report and, depending on which value a user picks in a
> > particular parameter, I want to display only one of the tables. Is there any
> > way to do something like, "IIF(parameters!param_A.value = "Y", visible => > true, visible = false)" or something like that for a table?
> > --
> > Thanks,
> >
> > Jeff
>
> The best way to accomplish this is to control the data populating the
> table controls at the stored procedure/query level that is sourcing
> the report. For example, pass the parameter value back to the stored
> procedure(s)/query(ies) and if it is a certain value, return an empty
> resultset to the report. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

how do I get the value of a DEFAULT

Hi

We have a default defined in our database

CREATE DEFAULT [ schema_name . ] default_name
AS constant_expression [ ; ]

How can I ge the value of the constant using SQL ?

Also - anyone know why this is going to be removed from a future version of SQL - we use it for partitioning with replicated clients (long story) - but every table has one column which is bound to this default. We find it *very* handy.

Ta
Bruce

take a look at the sys.default_constraints catalog view, the definition column will have the value

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||We are recommending that you use default constraints instead of DEFAULTs. Would that not work for you?|||

If we were starting the project now, then yes, but we have many installations around the globe relying on this feature....

Bruce

|||

That shows the default constraints, but not our DEFAULT (terminology so similar but different - I'm confused!)

thanks
bruce

|||

sp_helpconstraint tablename

Adamus

|||

Thats closer, but I really just want to know that the integer value of my DEFAULT is ...

I see it joins on sys.columns and syscomments to get a descriptive string of the default - I was hoping for something a little more direct - in the meantime I'll write a function to work it out..

thanks

|||

syscomments is deprecated in sql server 2005. Please use sys.sql_modules instead or you can use the below built-in object_definition.

select object_definition(default_object_id) from sys.columns where default_object_id <> 0

|||

SELECT d.* FROM sys.default_constraints as d
JOIN sys.objects as o
ON o.object_id = d.parent_object_id
JOIN sys.columns as c
ON c.object_id = o.object_id AND c.column_id = d.parent_column_id
JOIN sys.schemas as s
ON s.schema_id = o.schema_id
WHERE o.name='<TableName>' AND c.name = '<ColumnName>'

The above query will show the default constraint properties of the column specified in the above query.

The default value can be obtained from the column d.[definition] in the above query.

Thanks,
Loonysan

sql

How do I get the value of a column in the last row of my table?

With out using @.@.identity or count(*) how do i retrieve a value in a column in the last row of my table.

here is the situation. ASP.net project has several sessions open. a user needs to get the value of a column in the last row inserted in a particular table.

Select CallID from Calls where 'it is the last row inserted'

thanks in advance.

you can email me at !cbmorton!@.!gmail.com!

Chris Morton

Hard to get the last row in a relational database. Last means many things: last inserted, last in clustered index, last in another sort, last in a particular index, last in the data page.

Does your table have an Identity column, timestamp, CreatedDate that defaults to getDate()? You need something.

|||

In relational databases the data is by definition unordered, therefore the last data row entered is functionally the same as the first row entered.

That being said, if you have an inserted date/time column you could select from the table where the column equals the max(datetime) value in the table.

How do I get the value of a column in the last row of a table?

With out using @.@.identity or count(*) how do i retrieve a value in a column in the last row of my table.

here is the situation. ASP.net project has several sessions open. a user needs to get the value of a column in the last row inserted in a particular table.

Select CallID from Calls where 'it is the last row inserted'

thanks in advance.

you can email me at !cbmorton!@.!gmail.com!

Chris Morton

Define last row?

maybe this will do what you want but it is kind of dangerous because you might pick up the inserted row from another connection

Select Max(CallID) from Calls

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||i do want to pick up the last inserted row from another connection|||

There is no "last" row because SQL Server stores data as it comes in within its own storage schema. There is only a last row if you do a order of the query executed. There could be also a last *physical* row if the table includes a clustered index, which is physically ordered.

But if you want to have the last row in a resultsset, you have to order it backwards and get the TOP 1

e.g. SELECT TOP 1 SomeColumn From SomeTable Order by SomeOtherorthesamecolumn DESC

BTW, this is a public newsgroups, as long as you have MSN Alerts activated you will get a notice everytime a new answer arrives. Private communication should only be done if the thread is extended immensly due to details asking and answering back and forth, but also then the answer and the solution should be posted back here, to help other which might be in the same situation with a similar question.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

How do I get the UserID column value of the User that just has

Hello ML,
Thanks for the reply. May be I am using them in the back ground . All
I do is use controls in the front to get the job done. But I never had
a situation or need to customize these controls and hence had to look
into the aspnetdb database where to make changes inorder to accomplish
my task.
Like I said this is my first time that I have a need to grab the
UserId and push that into another table along with the DelaerShipID. So
I just needed some suggestions on how do I do it.
As a test I Executed the stored proce aspnet_Users_CreateUser and it
returns the @.UserId value here is the output from the Management
Studio...
****************************************
*********************
USE [aspnetdbTest]
GO
DECLARE @.return_value int,
@.UserId uniqueidentifier
EXEC @.return_value = [dbo].[aspnet_Users_CreateUser]
@.ApplicationId = '8203a4f9-995b-4506-aec0-075551d77d02',
@.UserName = N'TestUser',
@.IsUserAnonymous = false,
@.LastActivityDate = N'4/11/2006',
@.UserId = @.UserId OUTPUT
SELECT @.UserId as N'@.UserId'
SELECT 'Return Value' = @.return_value
GO
****************************************
***********************
and the output I got is the @.UserID
'303B2D60-788C-4F96-9930-7E5BA173D165'.
Now my question is :
To get this @.UserID value from the aspnetdb to my Business Layer what
event do I need to use in my code behind page?
Thanks
-LI'm sorry if I sounded harsh. There were no ulterior motives.
You'd better ask this in a more appropriate news group (depending on the
programming language used in your asp code-behind). Basically, you need to
trap the value being returned from the procedure in a local variable in your
asp method by invoking the SQL procedure appropriately. You need to declare
the UserId parameter as an output parameter and assign its value to the loca
l
variable. (Yes, it's obvious that I'm more at home in T-SQL.)
ML
http://milambda.blogspot.com/

how do i get the OUTPUT parameter value from a stored procedure that i am using to fill a

how do i get the OUTPUT parameter value from a stored procedure that i am using to fill a dataset?

Set the Direction property of your Parameter.

Dim myParamAs New SqlParameter' ... set value, name, etc.' if you are sending a value (Default)myParam.Direction = ParameterDirection.Input' if you are requesting a value (assigned in SQL)myParam.Direction = ParameterDirection.Output' if you are sending a value AND requesting' it's changed valuemyParam.Direction = ParameterDirectioin.InputOutput' If you want the return code for the SQLmyParam.Direction = ParameterDirection.ReturnValue

|||

no that doesn't work. i know how to use parameters, but i have only ever seen the Output Parameter used withcommand.executenonquery()when inserting data, but this is no good to me because I am only usingdataAdapter.Fill(myDataset)

how do i get the OutPut Parameter with only filling a dataset?

|||

Theoretically, after your call todataAdapter.Fill(myDataset)

the out parameter should be accessible using

dataAdapter.SelectCommand.Parameters("paramname")

I haven't tried it out though.

|||

Yes, this should work. I believe everything is copied by reference, though, when you add a parameter to a command, then a command to an adapter. Thus, you should still be able to use the original parameter.

'after dataadapter.fill()Dim strValueAs String = myParam.Value

|||I posted some sample code here. Check if it helps:http://forums.asp.net/thread/1478830.aspx|||thanks that works

How do I get CanGrow to work for a textbox in a table?

I have a very basic table that I want to use as a subreport. It has only one row and one column, and it's textbox value is:

Fields!VendorType.Value & ": " & Fields!VendorName.Value & " " & Fields!VendorContactName.Value & IIF(Fields!VendorPhone.Value > " "," " & Fields!VendorPhone.Value,"")

The Data uses a parameter from the parent report to get the values, and there are usually several vendors for each report. The textbox in the row has it's 'CanGrow' property set to Yes, because sometimes these values are too long for one line. But when I run the report, it does not grow. it just truncates the value.

Any ideas? what can I do?

If you just render the table as a stand-alone report, do the textboxes grow? Which renderer are you using? Remember textboxes can only be grown vertically, not horizontally.|||

Well, I was working up some examples for you when I realized that I was not getting the data I thought I was. So, it is working. Sorry! But thanks.

How do i get a count for one value and a different count from the same value?

I have a field, that display either Yes or no. I want to be able to put in a text box or in a table the count for the yes's and the count for the No's for that one column or various columns. Is this possible?

use a matrix or table

group by the yes/no field

in the value cell put Count(Fields!yes_no.Value)

How do I find the highest Unique Identifier?

I'd like to know the current value of my uniqueID column before I
create a new record.

Is there a way to find out this value?
It is numeric in my case, but I can't just look for the MAX value,
since some records may have been deleted, and the value for the
uniqueID still stays at the higher value.

Is there a way to read this internally kept value?Stacey,

It sounds like you are using an IDENTITY column; if that's the case,
then you can use IDENT_CURRENT to find the last generated IDENTITY
value.

HTH,
Stu|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. I hope you know never to use
IDENTITY as a key.|||Stu - Thanks!
That was exactly what I was looking for!

And CELKO - I'm sure everybody reading this thread will appreciate your
useful contribution...|||Do NOT assume that the next IDENTITY value will equal IDENT_CURRENT +
1. In a multi-user system you cannot reliably predict the next IDENTITY
value to be inserted. Nor should it be necessary to do so.

Stacey, if you explain your requirement fully I'm sure we can help you
with a better solution.

--
David Portas
SQL Server MVP
--|||Stu (stuart.ainsworth@.gmail.com) writes:
> It sounds like you are using an IDENTITY column; if that's the case,
> then you can use IDENT_CURRENT to find the last generated IDENTITY
> value.

But beware of that IDENT_CURRENT is not safe from other processes. That is,
if you call IDENT_CURRENT before you insert a row, and the call
scope_identity() to see what you actually got, they may not be the same.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How do I exclude carriage returns as part of a IS NULL exclusion clause...

Hi

I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.

I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.

Does anyone know how I can elegantly get around this problem other than my best guess below:

Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.

Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]

Cheers - AndyThis is a really slippery slope, but if you are dealing with 8 bit ASCII, you could use:LIKE '%[!-~]%' to see if there are any printable characters (this expression ignores whitespace).

-PatP|||Very interesting and very cool (speaking purely as a geek!).

I got it to work by doing this:

and field like '%[a-z0-9]%'

This expression only shows fields that contain letters and/or numbers and excludes stupid entries like periods, comma's, carraige returns...

Thanks very much for your help!

Andy|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'You are quite correct, systems that are derived from MS-DOS see 0x0d0a as a line end. The code that I proposed dodges that bullet altogether though, along with several others, and seems to have solved the problem better than I'd expected!

-PatP

How do I do "If statement" in SSIS?

I need to get value from one table and if it is (for example) string - TodayDate, I need to change it to "Today Date" with a space in it and use it later in my Data Flow.

So I would need something similar to

If value = "TodayDate" Then

value - "Today Date"

Else

.....

How do I do that?

Thanks.

Check on the Derived Column transform in SSIS dataflow task.

|||

To add to Wenyang's answer...you will need to use the Conditonal Operator within the Derived Column transform.

? : (Conditional) (SSIS)

(http://msdn2.microsoft.com/en-us/library/ms141680.aspx)

Wenyang, I don't thinkyour answer is complete. Do you really think it warrants being marked as an answer? Hope you don't mind me asking.

Regards

Jamie

|||

How do I create expresiion?

Column Name - MonitorType

MonitorType ? "SA" : @.MyVariable

This shows error.

I want to see if the MonitorType field returns "SA" and if yes - use the variable I have.

If it returns for example "BA" use another variable.

|||

Vita wrote:

How do I create expresiion?

Column Name - MonitorType

MonitorType ? "SA" : @.MyVariable

This shows error.

I want to see if the MonitorType field returns "SA" and if yes - use the variable I have.

If it returns for example "BA" use another variable.

MonitorType == "SA" ? @.[MyVariable] : @.[SomeOtherVariable]

-Jamie

|||

Thanks.

|||Please don't forget to mark posts as answered.|||

What if I have more than 2 variables.

If "SA" - var 1,

If BA" - var 2

If "DA" - var 3

If "DS" - var 4

Can I do Else If in the same expression?

|||

I believe you can continue to add additional tests in the Derived Column task.

Rob

|||

Vita wrote:

What if I have more than 2 variables.

If "SA" - var 1,

If BA" - var 2

If "DA" - var 3

If "DS" - var 4

Can I do Else If in the same expression?

Yes. You need to nest the Conditonal Operator.

MonitorType == "SA" ? @.var1 : (MonitorType == "BA" ? @.var2 : (MonitorType == "DA" ? @.var3 : (MonitorType == "DS" ? @.var4 : @.SomeDefaultVariable)))

-Jamie

please don't forget to mark as answered.

|||Thanks again. I appreciate it.

How do I display lables outside Chart area of a Pie Chart

How do I display the labels outside of the chart area of a pie chart? The
Point Labels properties tab of the Chart Value does not show a setting to
display labels outside of the chart area. Am I missing something?
PPNote: you will need SP1 installed for this to work. The SP1 readme contains
a section about outside labels in pie charts:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> wrote in
message news:D2C14B13-3314-4553-AF82-41039BFE60F2@.microsoft.com...
> On the Point Labels tab uncheck the Auto checkbox n the Position secition.
> "msnews.microsoft.com" wrote:
> > How do I display the labels outside of the chart area of a pie chart?
The
> > Point Labels properties tab of the Chart Value does not show a setting
to
> > display labels outside of the chart area. Am I missing something?
> >
> > PP
> >
> >
> >