?
Solved

Move sqldatasource from client side to server side

Posted on 2009-04-28
4
Medium Priority
?
517 Views
Last Modified: 2012-05-06
Hi Experts,
I have a combobox and gridview that use a SqlDataSource to fill them. All works correctly now, but I want to include an all option in the dropdown and use this in the selectcommand. This requires more dynamic code and I am not able to produce this.

I would be able to do this server side, but I do not know how to define a SqlDataSource server side that can be used client side.

Best regards, MB

<asp:SqlDataSource
                    ID="BunkeringDataSource"
                    runat="server"
                    ConnectionString="<%$ ConnectionStrings:Connection %>"
                    SelectCommand="SELECT *
                                   FROM [Bunkerings] WHERE [Vessel] = @VesselName
                                   ORDER BY [ReportDateTimeGMT] DESC "
                    UpdateCommand="UPDATE [Bunkerings] SET [Supplier] = @Supplier, Broker=@Broker, [CST] = @CST, [DueDate] = @DueDate,
                                   [PriceHFOHS] = @PriceHFOHS, [PriceHFOLS] = @PriceHFOLS, [PriceMDOHS] = @PriceMDOHS, [PriceMDOLS] = @PriceMDOLS,
                                   [BunkerTakenHFOHS] = @BunkerTakenHFOHS, [BunkerTakenHFOLS] = @BunkerTakenHFOLS, [BunkerTakenMDOHS] = @BunkerTakenMDOHS,
                                   [BunkerTakenMDOLS] = @BunkerTakenMDOLS, [AdditionalCosts] = @AdditionalCosts, [Remarks] = @Remarks
                                   WHERE [BunkeringID] = @BunkeringID ">
                    <SelectParameters>
                        <asp:ControlParameter
                            Name="VesselName"
                            ControlID="cboVessels"
                            PropertyName ="text"
                            Type="String" />
                    </SelectParameters>
                    <UpdateParameters>
                        <asp:Parameter Name="BunkeringID" Type="String" />
                        <asp:Parameter Name="Supplier" Type="String" />
                        <asp:Parameter Name="CST" Type="Decimal" />
                        <asp:Parameter Name="Broker" Type="String" />
                        <asp:Parameter Name="DueDate" Type="DateTime" />
                        <asp:Parameter Name="PriceHFOHS" Type="Decimal" />
                        <asp:Parameter Name="PriceHFOLS" Type="Decimal" />
                        <asp:Parameter Name="PriceMDOHS" Type="Decimal" />
                        <asp:Parameter Name="PriceMDOLS" Type="Decimal" />
                        <asp:Parameter Name="BunkerTakenHFOHS" Type="Decimal" />
                        <asp:Parameter Name="BunkerTakenHFOLS" Type="Decimal" />
                        <asp:Parameter Name="BunkerTakenMDOHS" Type="Decimal" />
                        <asp:Parameter Name="BunkerTakenMDOLS" Type="Decimal" />
                        <asp:Parameter Name="AdditionalCosts" Type="String" />
                        <asp:Parameter Name="Remarks" Type="String" />
                    </UpdateParameters>
                </asp:SqlDataSource>
                <asp:SqlDataSource
                    ID="VesselsDataSource"
                    Runat="server"
                    ConnectionString="<%$ ConnectionStrings:Connection %>"
                    SelectCommand="SELECT DISTINCT [VesselName] FROM [Vessels] WHERE [Status]='10' ORDER BY [VesselName] ASC">
                </asp:SqlDataSource>
0
Comment
Question by:mark_norge
  • 2
  • 2
4 Comments
 
LVL 14

Accepted Solution

by:
GiftsonDJohn earned 2000 total points
ID: 24257057
Hi,

Use as below

 <asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="true" DataSource="BunkeringDataSource" DataTextField="" DataValueField="">
        <asp:ListItem Text="All" Value="All"></asp:ListItem>
    </asp:DropDownList>

The AppendDataBoundItems = true ensures that the databound items are appended after the All item and it won't overwrite or remove any items previously added to the dropdownlist.
0
 

Author Comment

by:mark_norge
ID: 24280857
Thanks for your reply, very helpful,

I am using Telerik controls and with this tip it was easy to achieve the first part. Now I have the option all, but how do I use this in the sql select command.
The select command needs a where clause in case of a specific vessel and does not need this if all vessels need to be shown. Server side this would be no problem, but how do I use a dynamic select command client side.

Regards, MB
0
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24281454
Use this statement

strQuery =  "Select * from tablename where columnname = '" & DropDownList.SelectedValue  & "%'"
0
 

Author Closing Comment

by:mark_norge
ID: 31575464
Thanks for your help. I ended up using the following WHERE clause:
WHERE  ( @Vessel  = 'All vessels' or [Vessel] = @Vessel )

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question