Friday, March 23, 2012
Reformatting numeric value to zero-filled string
value, for example:
123456 convert to 000123456
I've looked at the CONVERT and CASE and haven't come up with a combination
that will perform this function.
Thanks!
Dougtry this as an example:
SELECT ISNULL(RIGHT('000' + CONVERT(VARCHAR, '123456'), 10), '')
yosh
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||select replace(str( 10,10),' ','0')
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Try,
declare @.num int
set @.num = 123456
select right(replicate('0', 9) + ltrim(@.num), 9)
go
"Leighton.d" wrote:
> I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Wouldn't it be nice if the Access FORMAT$ function were available.
Thanks for all of the input...
Doug|||As a good practice, try the formatting in the client side.
AMB
"Leighton.d" wrote:
> Wouldn't it be nice if the Access FORMAT$ function were available.
> Thanks for all of the input...
> Doug
Wednesday, March 21, 2012
Referring to Sql connection string in web.config from code behind
Every time I move my project from my computer to the testing server I have to change the connection string references in the aspx side and in my code behind.
For the code behind I declared the SqlConnection string at the top of the code-behind page for both connection strings and comment out the one not in use. Obviously I then comment out the string not in use in the WebConfig as well.
Being superlatively lazy I always look for the easiest and quickest way to do most anything - connection strings included. Having to comment out strings twice brought rise to the question of whether I can refer to the connection string in the web.config file from the code-behind page. I'm sure it can be done, and I did a good amount of hunting around, but I couldn't find any examples of code that would do that.
Currently, as I said above, I have two connection strings declared at the top of my code-behind. Here's an example of one:
Private sqlConnAs New SqlConnection("Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD")Then, I just use sqlConn as usual in my binding without having to "dim" it in every sub:
sdaPersonnel =New SqlDataAdapter(strSqlPersonnel, sqlConn)
Then there's the SqlConnections set up by the wizard on the aspx side:
<asp:SqlDataSource ID="sqlDataSourcePayrollCompany" Runat="Server" ConnectionString="<%$ ConnectionStrings:DATABASECONNECTIONSTRING%>" ...>
And for the connection in the web.config:
<add name="DATABASECONNECTIONSTRING" connectionString="Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD" providerName="System.Data.SqlClient" />
So, what would be the code in the code-behind page to refer to the connection string in the web.config file?
Thanks!
To refer to the connection string via code: ConfigurationManager.ConnectionStrings["<ConnectionStringName>"]|||Hey, Stiletto, Thanks! That did the trick (or, at least, it got me on the right track). I wasn't exaclty sure how to actually add that to my connection string code, so I had to do some hunting and trying different syntax, but ended up with this working:
Private sqlConnAs New SqlConnection(ConfigurationManager.ConnectionStrings("DATABASECONNECTIONSTRING").ConnectionString) Thanks again!
Referential Integrity with empty string and ZERO
How could I define referential integrity using FK constraint which allow me to have empty string/ZERO number instead of NULL value ?
Thank you
If you place a foreign key constraint on a column, zero is only a valid value if there is a zero in foreign key table. The same applies to empty strings.
If the foreign key value of a particular row is unknown (or it doesnt have one), that's where the use of null comes in. Of course a NOT NULL constraint used in conjunction with the FK constraint can prevent the use of nulls (forcing a valid FK value).
To the best of my knowledge, it's not possible to change this behavior.
Referential Integrity with empty string and ZERO
How could I define referential integrity using FK constraint which allow me to have empty string/ZERO number instead of NULL value ?
Thank you
If you place a foreign key constraint on a column, zero is only a valid value if there is a zero in foreign key table. The same applies to empty strings.
If the foreign key value of a particular row is unknown (or it doesnt have one), that's where the use of null comes in. Of course a NOT NULL constraint used in conjunction with the FK constraint can prevent the use of nulls (forcing a valid FK value).
To the best of my knowledge, it's not possible to change this behavior.
Monday, February 20, 2012
Reduce string to binary comparison
Value * (1-ABS(SIGN([Column Name] - [Desired Value))) which will evaluate to one or zero.
For strings, I thought it would be easier. I tried:
Value * ([Column Name]=[Desired Value])
But that does not work. I get an "incorrect syntax near = error. I am looking for a solution that does not rely on the case statement.
Thanks,
AndrewComparison operators return a Boolean data type - TRUE, FALSE, UNKNOWN. As such, you cannot perform arithmetic on them. You are stuck with a CASE statement, or you could use a UDF to do the comparison and return a 1 or 0.
Terri|||Thanks for your response.
Andrew