EASY! Trim string?

I have values in a matrix takin too much space. Since they are just used as the matrix column header, cant i trim it or something. To display just the first 3 letters of the the attribute, and not the whole string? Whats the code for that?

=Fields!Collision_Type.Value thats my field right now.

Please help! thanks!

=LEFT(Fields!Collision_Type.Value,1,3) I believe. Or some variant of that syntax.|||

I believe it should be Mid(Fields!Collision_Type.Value,1,3)

|||

Yes, you can change it to this to always show the first 3 characters:

=Left(Fields!Collision_Type.Value, 3)

Jarret

|||Thanks for the quick response, this worked great!|||This one worked as well, hum i wonder what the difference is? But they both seem to display the same. Thanks!|||

There is no difference really, the Mid function allows you to give it a starting position and how many characters to return.

Jarret

Easy query question

This one should be easy, but for some reason I can't get it.
I have a table with an XML column, containing the following:
<xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
<xs:Caption />
<xs:Description />
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1" />
<xs:Rateplan Caption="TN1|H" Description="" />
<xs:Roomtype Caption="A1A" Description="" />
<xs:Mealplan Caption="None" Description="" />
<xs:GuestIds>
<xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
<xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
</xs:GuestIds>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="107.9" Net="107.9" />
<xs:Target Sell="107.9" Net="107.9" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="124.5" Net="124.5" />
<xs:Target Sell="124.5" Net="124.5" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="132.8" Net="132.8" />
<xs:Target Sell="132.8" Net="132.8" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
ApplicableForCancellation="True">
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1" />
<xs:Caption>32.87</xs:Caption>
<xs:TotalSum>
<xs:Source Sell="32.87" Net="32.87" />
<xs:Target Sell="32.87" Net="32.87" />
</xs:TotalSum>
</xs:Taxes>
<xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
TN1" NumberOfUnits="0">
<xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z"
PickUp="0001-01-01T00:00:00Z" DropOff="0001-01-02T00:00:00Z"
Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z" Nights="1"
Days="2" Weeks="1" />
<xs:Caption>A1A - TN1</xs:Caption>
<xs:Description />
<xs:UnitRate>
<xs:Source Sell="0" Net="0" />
<xs:Target Sell="0" Net="0" />
</xs:UnitRate>
</xs:Products>
<xs:TotalSum>
<xs:Source Sell="398.07" Net="398.07" />
<xs:Target Sell="398.07" Net="398.07" />
</xs:TotalSum>
<xs:MultipleDriversFee ChargeType="0" />
<xs:PickUpFee />
<xs:DropOffFee />
<xs:DistanceLimit DistanceUnit="0">
<xs:FreeLimit Type="None" />
<xs:ExtraLimit Type="PerUnit" Distance="0">
<xs:Costs>
<xs:Source Sell="0" Net="0" />
<xs:Target Sell="0" Net="0" />
</xs:Costs>
</xs:ExtraLimit>
<xs:Information />
</xs:DistanceLimit>
</xs:ReservationChargeItem>
What I want is a select statement that gives me the values for net and sell
from the TotalSum/Source. Seems like it should be simple, but I'm missing
something somewhere. Thanks for any help you can give me.
Hello PK,
How's this:
use scratch
go
declare @.x xml
set @.x = '<?xml version="1.0" encoding="UTF-8"?>
<xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
<xs:Caption/>
<xs:Description/>
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1"/>
<xs:Rateplan Caption="TN1|H" Description=""/>
<xs:Roomtype Caption="A1A" Description=""/>
<xs:Mealplan Caption="None" Description=""/>
<xs:GuestIds>
<xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
<xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
</xs:GuestIds>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="107.9" Net="107.9"/>
<xs:Target Sell="107.9" Net="107.9"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="124.5" Net="124.5"/>
<xs:Target Sell="124.5" Net="124.5"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="132.8" Net="132.8"/>
<xs:Target Sell="132.8" Net="132.8"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
ApplicableForCancellation="True">
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1"/>
<xs:Caption>32.87</xs:Caption>
<xs:TotalSum>
<xs:Source Sell="32.87" Net="32.87"/>
<xs:Target Sell="32.87" Net="32.87"/>
</xs:TotalSum>
</xs:Taxes>
<xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
TN1" NumberOfUnits="0">
<xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z" PickUp="0001-01-01T00:00:00Z"
DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z"
Nights="1" Days="2" Weeks="1"/>
<xs:Caption>A1A - TN1</xs:Caption>
<xs:Description/>
<xs:UnitRate>
<xs:Source Sell="0" Net="0"/>
<xs:Target Sell="0" Net="0"/>
</xs:UnitRate>
</xs:Products>
<xs:TotalSum>
<xs:Source Sell="398.07" Net="398.07"/>
<xs:Target Sell="398.07" Net="398.07"/>
</xs:TotalSum>
<xs:MultipleDriversFee ChargeType="0"/>
<xs:PickUpFee/>
<xs:DropOffFee/>
<xs:DistanceLimit DistanceUnit="0">
<xs:FreeLimit Type="None"/>
<xs:ExtraLimit Type="PerUnit" Distance="0">
<xs:Costs>
<xs:Source Sell="0" Net="0"/>
<xs:Target Sell="0" Net="0"/>
</xs:Costs>
</xs:ExtraLimit>
<xs:Information/>
</xs:DistanceLimit>
</xs:ReservationChargeItem>
'
selectt.c.value('data(@.Sell)','money') as Sell
, t.c.value('data(@.Net)','money') as Net
from @.x.nodes('declare namespace xs = "http://www.hoboo.com/ReservationSchema";
//xs:TotalSum/xs:Source') as t(c);
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Fantastic! Thanks!
"Kent Tegels" wrote:

> Hello PK,
> How's this:
> use scratch
> go
> declare @.x xml
> set @.x = '<?xml version="1.0" encoding="UTF-8"?>
> <xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
> DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
> <xs:Caption/>
> <xs:Description/>
> <xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
> Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
> Days="4" Weeks="1"/>
> <xs:Rateplan Caption="TN1|H" Description=""/>
> <xs:Roomtype Caption="A1A" Description=""/>
> <xs:Mealplan Caption="None" Description=""/>
> <xs:GuestIds>
> <xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
> <xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
> </xs:GuestIds>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="107.9" Net="107.9"/>
> <xs:Target Sell="107.9" Net="107.9"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="124.5" Net="124.5"/>
> <xs:Target Sell="124.5" Net="124.5"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="132.8" Net="132.8"/>
> <xs:Target Sell="132.8" Net="132.8"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
> ApplicableForCancellation="True">
> <xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
> Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
> Days="4" Weeks="1"/>
> <xs:Caption>32.87</xs:Caption>
> <xs:TotalSum>
> <xs:Source Sell="32.87" Net="32.87"/>
> <xs:Target Sell="32.87" Net="32.87"/>
> </xs:TotalSum>
> </xs:Taxes>
> <xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
> TN1" NumberOfUnits="0">
> <xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z" PickUp="0001-01-01T00:00:00Z"
> DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z"
> Nights="1" Days="2" Weeks="1"/>
> <xs:Caption>A1A - TN1</xs:Caption>
> <xs:Description/>
> <xs:UnitRate>
> <xs:Source Sell="0" Net="0"/>
> <xs:Target Sell="0" Net="0"/>
> </xs:UnitRate>
> </xs:Products>
> <xs:TotalSum>
> <xs:Source Sell="398.07" Net="398.07"/>
> <xs:Target Sell="398.07" Net="398.07"/>
> </xs:TotalSum>
> <xs:MultipleDriversFee ChargeType="0"/>
> <xs:PickUpFee/>
> <xs:DropOffFee/>
> <xs:DistanceLimit DistanceUnit="0">
> <xs:FreeLimit Type="None"/>
> <xs:ExtraLimit Type="PerUnit" Distance="0">
> <xs:Costs>
> <xs:Source Sell="0" Net="0"/>
> <xs:Target Sell="0" Net="0"/>
> </xs:Costs>
> </xs:ExtraLimit>
> <xs:Information/>
> </xs:DistanceLimit>
> </xs:ReservationChargeItem>
> '
> selectt.c.value('data(@.Sell)','money') as Sell
> , t.c.value('data(@.Net)','money') as Net
> from @.x.nodes('declare namespace xs = "http://www.hoboo.com/ReservationSchema";
> //xs:TotalSum/xs:Source') as t(c);
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>

Easy Query

Hi,
This is a really simple query and for the life of me I cant remember how to
do it
Name Table
Name
Philip
Steve
Contact Table
Name Position Contact
Philip 1 01244
Philip 2 0777
Steve 2 01352
Steve 3 00000
Join the 2 tables based on name and get the contact number from the second
table based upon the lowest value of the position field,
so the select results would look like
Philip 01244
Steve 01352
Any help would be great, thanks PDUse MIN() agregate fumction along GROUP BY clause
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:902B8202-1189-4503-9498-B57BC7B1D451@.microsoft.com...
> Hi,
> This is a really simple query and for the life of me I cant remember how
> to
> do it
> Name Table
> Name
> Philip
> Steve
> Contact Table
> Name Position Contact
> Philip 1 01244
> Philip 2 0777
> Steve 2 01352
> Steve 3 00000
> Join the 2 tables based on name and get the contact number from the second
> table based upon the lowest value of the position field,
> so the select results would look like
> Philip 01244
> Steve 01352
> Any help would be great, thanks PD
>

Dynamically changing SQL ConnectionString for live/test environments

Hello. What I'm trying to do should be fairly simple, but after an hour of searching and trials, I can't seem to get it working.

When I run my website on my test machine, I would like it to use a certain value for my connectionString. But when it's run on the live server, it should use a different connection string. This way, when it's on the test server it'll use the test database.

In my Web.Config file, I have this:

<connectionStrings>
<add name="myConnectionString" connectionString="Data Source=TEST_DB;Initial Catalog=tester;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

I would like to be able to change the connectionString in something like the Global.asax file, when the application is loaded. I'd check for a certain environment variable, or some other condition, and change the value of the connectionString.

I tried this in my Global.asax, but it told me the value was read_only:

ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString = "Data Source=LIVE_DB;Initial Catalog=live;Integrated Security=True"

Does anyone have a good way of doing this, so I don't need to juggle Web.Config files when I publish my site?

Thanks.

You can have a separate app.config file for production or have a separate configuration section so that when you build the application (deploy), Visual Studio will replace the connection strings

Take a look at this post (http://msdn2.microsoft.com/en-us/library/bb164473(VS.80).aspx)

|||

After a more varied search, I came across the Web Deployment Projects tools.

http://msdn2.microsoft.com/en-us/asp.net/Aa336619.aspx

Now I've got it simply including a different piece of config code when it builds the website in either Debug or Release mode.

Thanks for the reply, though. I'm all set now.