Dynamic SELECT Command In SqlDataSource

Jul 20, 2007

I have a GridView (that uses SqlDataSource1) and a Dropdownlist.  Depending upon the value selected on the DropDownList I need to select different stored procedures for the gridview.  The problem is that I can do it without taking SqlDataSource1 by using DataSet or DataTable.  But, I need to Use SQLDataSource1 for easy way of Header SORTING.  So, is there any way to change the SQLDatasource1.SELECT Command dynamically. So that, I can use different queries for the Single DataGrid.
 I have attached the sample code of the SqlDataSource1 I'm using.  I need to change the Command i.e.
SelectCommand="usp_reports_shortages" to "usp_reports_shortagesbyID" and "usp_reports_shortagesbyDate"    
depending on the value selected in the dropdownlist.  So, is there any way to do this????
<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$
ConnectionStrings:TESTDrivercommunication %>"



    SelectCommand="usp_reports_shortages" SelectCommandType="StoredProcedure">



    <SelectParameters>



        <asp:ControlParameter ControlID="lblDriver" Name="date1" PropertyName="Text" Type="DateTime" />



        <asp:ControlParameter ControlID="lblTODate" Name="date2" PropertyName="Text" Type="DateTime" />



        <asp:ControlParameter ControlID="DDlDriver" Name="driver" PropertyName="SelectedValue"



            Type="Int32" />



        <asp:SessionParameter Name="week" SessionField="s_week" Type="DateTime" />



    </SelectParameters>


</asp:SqlDataSource>

View 1 Replies


ADVERTISEMENT

Dynamic SqlDataSource Select Command?

Apr 25, 2008

I am trying to implement an "advanced search" feature on my ASP.NET 2.0 web form.  I have a GridView control and a SqlDataSource.  The SqlDataSource control successfully retrieves data when the SelectCommand attribute is set in the aspx page.  I need to make it so when a user clicks on a button, it can take a value from a text box and use it in the WHERE clause.  I have tried setting the SelectCommand programmatically and then DataBinding but it never accepts the new SelectCommand. What can I do to fix this?

View 5 Replies View Related

ASP: SqlDataSource - Select Command

Aug 10, 2007

I am using  <asp:SqlDataSource ID and for the Select Command, the following, where the WHERE clause ... for an exact match (=) works correctly:
SelectCommand="SELECT [PatientID], [MedRecord] , [Accession], [FirstName], [LastName], [Address1] FROM [ClinicalPatient] WHERE (LastName = @LastName) ORDER BY [LastName]DESC">
 I would like to do a "LIKE" search where the LastName Parameter is matched using "LIKE".  In  this situation how would the syntax be written.... I tried:
LastName LIKE '%" & LastName & "%'"
But I get an error???? Any suggestions, please...
Thanks !!

View 4 Replies View Related

SqlDataSource Select Command

Nov 15, 2007

I have a SqlDataSource object that is bound to a GridView control. I have configured the SqlDataSource with a default select command. Under certain values of query strings on the URL for this page (Default.aspx), I want to change the select command. So I put the statements in the Page_Load method for Default.aspx to define SqlDataSource1.SelectCommand. The changed SelectCommand works fine for the first page of GridView data and shows 5 GridView pages, but if I switch to one of the other pages, it seems to revert to the default SelectCommand (which generates 19 GridView pages). I assume I should put my code to change the SelectCommand somewhere else. Can someone help me with where to put it? Thanks!

View 1 Replies View Related

SqlDataSource.Select Command Not Working?

May 26, 2007

My compiler says that the line in bold below is illegal. The error msg I'm getting is: No overload for method 'select' takes '0' arguments. How can I correct this error and execute a SELECT?
 protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource2.Select ();
 }
 protected void SqlDataSource2_Selected(object sender, SqlDataSourceStatusEventArgs e)
{string strReadyFirstName = e.Command.Parameters["@FirstName"].Value.ToString();string strReadyLastName = e.Command.Parameters["@LastName"].Value.ToString();
}
 <asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [User_ID], [User_Name], [FirstName], [LastName], [Company_Name], [Department_Name] FROM [CompanyDepartment] WHERE ([User_Name] = @User_Name)" OnSelected="SqlDataSource2_Selected">
<selectparameters>
<asp:sessionparameter DefaultValue="TheirUserName" Name="User_Name" SessionField="TheirUserName" Type="String" />
</selectparameters>
</asp:SqlDataSource>

View 1 Replies View Related

SQLDatasource With Multiple Controlparameters For Select Command

Sep 1, 2006

I would like to introduce myself as ASP.Net 2.0 beginner. I face a problem when using gridview and sqldatasource.

View 1 Replies View Related

SqlDataSource And SSMSE Returning Different Results For Same SELECT Command

Oct 2, 2007

Hi,
Hope you guys won't mind this rather newbie question.  I'm writing a simple blog page for my website and have created a SqlDataSource which queries the database for a list of blog post titles (from the web.Blog table) and the number of comments (from the web.BlogComments table).  The SqlDataSource control is:
<asp:SqlDataSource ID="sourceBlogArticles" ProviderName="System.Data.SqlClient" connectionString="<%$ ConnectionStrings:myDatabase %>" runat="server" SelectCommand="SELECT gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded, COUNT(gbc.blogID) AS noOfComments FROM web.Blog gb LEFT OUTER JOIN web.BlogComments gbc ON gb.blogID = gbc.blogID GROUP BY gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded ORDER BY gb.dateAdded"></asp:SqlDataSource>
This works perfectly well if each blog entry in the web.Blog table has associated comments in the web.BlogComments table.  However, if there are no comments yet defined in the web.BlogComments table for that blogID then no row is returned in ASP.Net (as checked with a GridView control or similar linked to the data source to view what I get)
 HOWEVER, I think the SELECT command IS correct: if I use the select command as a query in SQL Server Managment Studio Express, I do get the rows returned, with 0 for the number of comments which is what I would expect for that query:
blogID, title, description, tags, dateAdded, noOfComments
1, title 1, description for title 1, tag1, 2007-09-27 06:49:03.810, 32, title 2, description for title 2, tag2, 2007-09-27 06:49:37.513, 03, title 3, description for title3, tag3, 2007-10-02 18:21:30.467, 0
Can anyone help?  The result from the SSMSE query is what I want, yet when I use the very same SELECT statement in my SqlDataSource I don't get any rows returned if the BlogComment count is zero (in the above example I get only the first row).  Many thanks for any suggestions!

View 6 Replies View Related

SELECT A Single Row With One SqlDataSource, Then INSERT One Of The Fields Into Another SqlDataSource

Jul 23, 2007

What is the C# code I use to do this?
I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.

View 7 Replies View Related

Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE

Feb 23, 2007

i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()        test.InsertCommandType = SqlDataSourceCommandType.Text        test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) "                  test.InsertParameters.Add("roll", TextBox1.Text)        test.InsertParameters.Add("name", TextBox2.Text)        test.InsertParameters.Add("age", TextBox3.Text)        test.InsertParameters.Add("email", TextBox4.Text)        test.Insert() i am using UPDATE command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()        test.UpdateCommandType = SqlDataSourceCommandType.Text        test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll                                                         123 "        test.Update()but i have to use the SELECT command like this which is completely different from INSERT and  UPDATE commands   Dim tblData As New Data.DataTable()         Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated                                                                                Security=True;User Instance=True")   Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn)   Dim da As New Data.SqlClient.SqlDataAdapter(Command)   da.Fill(tblData)   conn.Close()                   TextBox4.Text = tblData.Rows(1).Item("name").ToString()        TextBox5.Text = tblData.Rows(1).Item("age").ToString()        TextBox6.Text = tblData.Rows(1).Item("email").ToString()       for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me

View 2 Replies View Related

SqlDataSource Insert Command....

Oct 12, 2007

Hello all,
I am having problem to insert the record into database from sqldatasource control. my code is listed below, and i can't find anything why it cause the exception...
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"                ConnectionString="<%$ ConnectionStrings:WebsiteDataConnection %>"                 SelectCommand="SELECT * FROM [ServiceAgents]"                InsertCommand="INSERT INTO ServiceAgents VALUES(@Ser_STATE, @Ser_CITY, @Ser_AGENT, @Ser_PHONE, @Ser_EQUIPMENT)">                                <InsertParameters>                    <asp:FormParameter Name="Ser_STATE" FormField="TextBox_state"/>                    <asp:FormParameter Name="Ser_CITY" FormField="TextBox_city"/>                    <asp:FormParameter Name="Ser_AGENT" FormField="TextBox_agent"/>                    <asp:FormParameter Name="Ser_PHONE" FormField="TextBox_phone"/>                    <asp:FormParameter Name="Ser_EQUIPMENT" FormField="TextBox_equip" />                   </InsertParameters>            </asp:SqlDataSource>
The table has got the fields that needed for insert command, for example:
            <table>                    <tr>                        <td>State:</td>                        <td>                            <asp:TextBox ID="TextBox_state" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator1"                            runat="server"                            ControlToValidate="TextBox_state"                            Display="Static"                            ErrorMessage="Please enter a state." />                        </td>                    </tr>                                        <tr>                        <td>City:</td>                        <td>                            <asp:TextBox ID="TextBox_city" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator2"                            runat="server"                            ControlToValidate="TextBox_city"                            Display="Static"                            ErrorMessage="Please enter a city." />                        </td>                    </tr>                                        <tr>                        <td>Agent:</td>                        <td>                            <asp:TextBox ID="TextBox_agent" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator3"                            runat="server"                            ControlToValidate="TextBox_agent"                            Display="Static"                            ErrorMessage="Please enter a agent." />                        </td>                    </tr>                                        <tr>                        <td>Phone:</td>                        <td>                            <asp:TextBox ID="TextBox_phone" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator4"                            runat="server"                            ControlToValidate="TextBox_phone"                            Display="Static"                            ErrorMessage="Please enter a phone No." />                        </td>                    </tr>                                        <tr>                        <td>Equipment:</td>                        <td>                            <asp:TextBox ID="TextBox_equip" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator5"                            runat="server"                            ControlToValidate="TextBox_equip"                            Display="Static"                            ErrorMessage="Please enter a equipment." />                        </td>                    </tr>                                        <tr>                        <td></td>                        <td>                            <asp:Button ID="Button2" runat="server" Text="Add" OnClick="addEntry"                             BackColor="#FFFBFF"                             BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"                            Font-Names="Verdana" Font-Size="1.0em" ForeColor="#284775"/>                          </td>                    </tr>                </table>
And in the code i just called: SqlDataSource1.Insert(); Then the page gives me the exception like:
Cannot insert the value NULL into column 'STATE', table 'WebsiteData.dbo.ServiceAgents'; column does not allow nulls. INSERT fails. The statement has been terminated.
But i actually input all the required text in the textbox.... Any idea guys?
Thanks

View 2 Replies View Related

SqlDataSource And Insert Command

Nov 20, 2007

I get the following error, when I try to create a company. Any help would be appreciated. Error Code: Cannot insert the value NULL into column 'CompanyName', table 'Telemetry.dbo.Company'; column does not allow nulls. INSERT fails.The statement has been terminated. Here is my aspx file: <table>     <tr ><td style="width:110px"><b>Company Name:</b></td><td ><asp:TextBox ID="CompanyName" Text="" runat="server" width="250px"/></td></tr>    <tr><td ><b>Phone Number:</b></td><td ><asp:TextBox runat="server" ID="CompanyPhone" Text=""  Width="250px"/></td></tr>                        <tr><td ><b>Company E-mail:</b></td><td ><asp:TextBox runat="server" ID="CompanyEmail" Text="" Width="250px"/></td></tr>                        <tr><td ><b>Street Address:</b></td><td><asp:TextBox runat="server" ID="AddressStreet" Text="" Width="250px"/></td></tr>            <tr><td ><b>City :</b></td><td><asp:TextBox runat="server" ID="AddressCity" Text="" Width="200px"/></td></tr>            <tr><td ><b>State/Province:</b></td><td><asp:TextBox runat="server" ID="AddressState" Text="" Width="150px"/></td></tr>            <tr><td ><b>Zip Code:</b></td><td><asp:TextBox runat="server" ID="AddressZip" Text="" Width="150px"/></td></tr>            <tr></tr>                </table>          <asp:Button ID="Submit" runat="server" OnClick="Submitinfo" />     <br />    <asp:SqlDataSource ID="sqlCreateCompany" runat="server" ConnectionString="<%$ ConnectionStrings:SqlServer1 %>"        InsertCommand="INSERT INTO Company(CompanyName, CompanyPhone, CompanyEmail, AddressStreet, AddressCity, AddressState, AddressZip) VALUES (@CompanyName, @CompanyPhone, @CompanyEmail, @AddressStreet, @AddressCity, @AddressState, @AddressZip)"        SelectCommand="SELECT [CompanyID], [CompanyName], [CompanyPhone], [CompanyEmail], [AddressStreet], [AddressZip], [AddressState], [AddressCity] FROM [Company]"  >                <InsertParameters>            <asp:FormParameter Name="CompanyName"  FormField="CompanyName"/>            <asp:FormParameter Name="CompanyPhone" FormField="companyPhone" />            <asp:FormParameter Name="CompanyEmail" FormField="CompanyEmail" />           <asp:FormParameter Name="AddressStreet" FormField="AddressStreet" />                       <asp:FormParameter Name="AddressCity" FormField="AddressCity" />            <asp:FormParameter Name="AddressState" FormField="AddressState" />            <asp:FormParameter Name="AddressZip" FormField="AddressZip" />                    </InsertParameters>            </asp:SqlDataSource> .....Behind the code.............. protected void Submitinfo(object sender, EventArgs e)    {        //TextBox t = (TextBox)FormView1.FindControl("CompanyName");        sqlCreateCompany.Insert();    }  .........Database Company Table Design..............CompanyID    int    UncheckedCompanyName    varchar(100)    UncheckedCompanyPhone    varchar(50)    CheckedCompanyEmail    varchar(100)    CheckedAddressStreet    varchar(100)    CheckedAddressCity    varchar(50)    CheckedAddressState    varchar(2)    CheckedAddressZip    varchar(5)    CheckedCompanyLogo    varchar(100)    Checked  

View 2 Replies View Related

Using Sqldatasource Insert Command Manually

Aug 4, 2006

hi i have an sqldatasource which has an insert command - a stored procedue is used.I have a text box with a button next to it . it is not in a datagrid.on the onclick event I would like to pass the value of the text box to the sqldatasource insert parameter ( it only expects this one parameter , and use the sqldatasource to do the insert basically doing a manual insert using the sqldatasource.does anyone know if this is possible thanks

View 2 Replies View Related

Runtime Sqldatasource Delete Command

Aug 12, 2006

i need to dyanamically generate my SQL commands so to do that i am generating sqldatasource commands programmatically rather declaratively. SELECT commands seems to work fine but DELETE isnt doing anything, here is my code:
 
SqlDataSource sdsConsultant = new SqlDataSource();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
loadDataSet();  //it just loads dataset used by formview
initializeSDS();
}


}
protected void initializeSDS()
{
string strConnection = ConfigurationManager.ConnectionStrings["myDB"].ToString();
sdsConsultant.ConnectionString = strConnection;

//SELECT
sdsConsultant.SelectCommand = "SELECT * FROM Consultants WHERE (id=@id)";
QueryStringParameter id = new QueryStringParameter("id", "id");
sdsConsultant.SelectParameters.Add(id);

//DELETE
sdsConsultant.DeleteCommand = "DELETE * FROM Consultants WHERE (id=@id2)";
QueryStringParameter id2 = new QueryStringParameter("id2", "id");
sdsConsultant.DeleteParameters.Add(id2);

//UPDATE
Page.Controls.Add(sdsConsultant);
FormView1.DataSource = sdsConsultant;
FormView1.DataBind();
}
 
my formview control looks like:
<asp:FormView DefaultMode="Edit" ID="FormView1" runat="server" DataKeyNames="id"
OnItemDeleted="FormView1_ItemDeleted"
OnItemDeleting="FormView1_ItemDeleting">
 
the DELETE doesnt execute and the OnItemDeleted event doesnt do anything either. am i doing something wrong here? plz help
 

View 2 Replies View Related

[ask] How To Invoke Sqldatasource Command In Method??

Apr 24, 2007

hello everybody, i have a question to ask, suppose i have a sqldatasource, can i use it in a method??this is my case, i need to make a new method to count the rows in a datagrid, so i will have to read the sqldatasource. the problem is, how to retrieve it?? usually i use the built in sqldatasource_selected to count the rows.... is there any other way?? 

View 6 Replies View Related

Setting Command Parameters For SQLDataSource

Dec 26, 2007

I have an SQLDataSource. The SQL is
 SELECT UserName, Category, ItemDescription, Size, Price, Reduce, Donate, Sold, ItemNumber, SoldDate, SoldPrice, PrintedFROM Tags WHERE (Printed = @Printed1 OR Printed = @Printed2 OR Printed = @Printed3) ORDER BY ItemNumber DESCThe bit field "printed" can be NULL, True or False.In the Selecting event of the SQLDataSource I have the following to show ALL records. But it does not work. If I remove these parameters it show ALL records.
e.Command.Parameters("@Printed1").Value = Nothing           'ASP.NET 2.0 using Visual basice.Command.Parameters("@Printed2").Value = Truee.Command.Parameters("@Printed3").Value = False
What am I doing wrong???
Thanks
Craig

View 10 Replies View Related

Using @@IDENTITY In The Sqldatasource Insert Command

Jan 11, 2008

I apologise if i have not posted this in the correct Topic before i start. But was uncertain where to post this query.
This is my first project in ASP.NET, MS Visual Web Developer 2005 Express and SQL Server 2005 Express. I have relatively little experience, so please bare with me.
I have managed to create a form that inserts data into a table and then inserts the Automatically Created Primary Key(as a foreign key) in another table. I have done this by inserting what is highlighted in red in the code of my InsertCommand below (Please scroll across to the end of the code):-InsertCommand="INSERT INTO [PrinterModel] ([Model], [PrinterMakeID], [CartridgeCode], [PartCode], [Duplex], [NIC], [Wireless], [Parallel], [USB], [Colour], [PrinterTypeID]) VALUES (@Model, @PrinterMakeID, @CartridgeCode, @PartCode, @Duplex, @NIC, @Wireless, @Parallel, @USB, @Colour, @PrinterTypeID) INSERT INTO [Model] ([PrinterModelID],[TypeID]) VALUES (@@IDENTITY, 3)" Can you see any problems that may arise from using this method. This project is an Asset Management System and will be used by no more than a handful of users. My Concern is the use of the @@IDENTITY (As it only stores the last Key used). Should I be using it here? If there is more than one user inserting into tables (Chances of this happening are very low), will the correct Primary key be insert to the table in the above code?Thank you for your comments.

View 2 Replies View Related

How To Trigger The Update Command Of The SQLDataSource

Jun 13, 2006

Hi - I'm using .net2, and have a gridview, populated by a SQL Datasource (Edit, Insert, Delete, Select).
Like we all used to do with the datagrid, I've added text boxes into the footer, and a link button, which I'd like to use to fire the Update command.
How do I get the link button to trigger the update command?
Thanks, Mark

View 1 Replies View Related

SQLDataSource Dynamic DB

Nov 14, 2006

Hello,
Has anyone used/fiqured out how to set one of the properties below to a variable with a dynamic database.
<asp:SqlDataSource ID="Sql_Imports" runat="server" selectcommand='EXEC <%# Session("CompanyDB") %>..IMPORT_S' ConnectionString=""></asp:SqlDataSource>
You would have thought that Microsoft would have allowed catalog/database on this command.
 Thanks.

View 2 Replies View Related

Dynamic SqlDataSource

May 14, 2007

Ok, here's my situation.
 I have a dynamic page that accepts a "type" query string.  This type query string is the name of a table I want to display on the page in the GridView.  Creating a different SqlDataSource/Strongly typed class for every type isn't possible because I need to make this flexible for future updates.  So therefor I basically need to be able to Sort, Page, Edit and Delete with the Grid View without knowing the table name at compile-time.
 Any help is appreciated,
Thanks!

View 3 Replies View Related

Passing '% Variable %' To SqlDataSource Through E.Command.Parameters

Feb 23, 2008

 Hello all,I'm writing a site with one page that uses the session variable (User ID) to pick one user ID out of a comma separated list in the field Faculty. The default parameterized query designed in the SqlDataSource wizard only returns lines that contain an exact match:SELECT * FROM tStudents WHERE ([faculty] = @faculty) The query: SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') works as I need when I hard code the query with a specific user ID into the SqlDataSource in the aspx page.  It will not work if I leave the @faculty parameter in it:SELECT * FROM tStudents WHERE ([faculty] LIKE '%@faculty%') e.Command.Parameters works to replace the @Faculty with a user ID, but again, adding the single quote and percentage sign either causes errors or returns no results.  I've tried several variations of:         string strEraiderID = "'%" + Session["eRaiderID"].ToString() + "%'";        e.Command.Parameters["@faculty"].Value = strEraiderID;no results are returned, not even the lines returned with the default select query.How do generate the equivalent of SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') into the SqlDataSource? Thanks much! 

View 3 Replies View Related

SqlDataSource Use Code Behind Variables In Update Command

Apr 14, 2008

Is there any way I can use a variable from my code behind file in the UpdateCommand of a sqlDataSource? I have tried
<%$ strUserGuid %>and<% strUserGuid %>
any help appreciated.Thanks
 Dave

View 2 Replies View Related

Multiple Sql Staements In SqlDataSource's Update Command

May 5, 2006

I have a gridview with a sqlDataSource with the SelectCommand as
"SELECT Movie.Title, Movie.Category, Movie.ReleaseDate, ItemForSale.Quantity, ItemForSale.HasUnLimitedQuantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"

what kinda 'UpdateCommand' do I set so that ItemForSale is also updated from the grid? I tried two update statements seperated with a semicolon but that wouldn't work, any suggestions...

View 3 Replies View Related

SqlDataSource : Dynamic SQL : Apostrophe

Sep 20, 2007

OK so I need to use a dynamic SQL statement in a SqlDataSource object because I need to pass in the column name.  I'm having trouble accounting for the apostophes I have to interpret literals within the statement.  This is connecting to an Oracle database.
This is what I originally tried...
<asp:SqlDataSource ID="SqlDataSourceMine" runat="server" ConnectionString="<%$ ConnectionStrings:My_Connection_String %>" ProviderName="<%$ ConnectionStrings:My_Connection_String.ProviderName %>" SelectCommand="SELECT m.YIE, :selecteditem, m.ENDTIME FROM MyMAP.MAP_M_SUM m WHERE (m.GROUPID LIKE 'YC%' AND m.GROUPID NOT LIKE 'YCX%' AND m.ENDTIME >= SYSDATE-14)">    <SelectParameters>        <asp:ControlParameter Name="selecteditem" ControlID="itemlabel" PropertyName="Text" Type="String" />    </SelectParameters></asp:SqlDataSource>
And the second column returned as :selecteditem for the column name and the value of itemlabel.text (what I wanted to be the column that was queried) as the value in each of the rows of that column.  So I tried dynamic SQL to put the value of itemlabel.txt as the column to be queried, but I'm not sure how to get the query to read the literals.  How can I accomplish this?
<asp:SqlDataSource ID="SqlDataSourceMine" runat="server" ConnectionString="<%$ ConnectionStrings:My_Connection_String %>" ProviderName="<%$ ConnectionStrings:MY_Connection_String.ProviderName %>" SelectCommand="EXEC('SELECT m.YIE, '+selectedbin+', m.ENDTIME FROM MMAP.MAP_M_SUM m WHERE (m.GROUPID LIKE '+paramlike+' AND m.GROUPID NOT LIKE '+paramnotlike+' AND m.ENDTIME >= SYSDATE-14)')">    <SelectParameters>        <asp:ControlParameter Name="selectedbin" ControlID="binlabel" PropertyName="Text" Type="String" />        <asp:ControlParameter Name="paramlike" ControlID="Label1" PropertyName="Text" Type="String" />        <asp:ControlParameter Name="paramnotlike" ControlID="Label2" PropertyName="Text" Type="String" />    </SelectParameters></asp:SqlDataSource>
This errors to "illegal variable name"
Can someone help me out please?  Thanks a lot.
-steve

View 3 Replies View Related

Dynamic ConnectionString For SqlDataSource

Mar 20, 2008

I have a datasource on my ASP.NET 2.0 control I want to make dynamic.  I have 6 different connection strings in my web.config file and want to change the connection with the selection in dropdownbox.
Here is my VB code:Protected Sub GetDatabase(ByVal intDb As Integer)
Select Case intDb

Case 1 ' Americas
srcCustomers.ConnectionString = "RWSqlServer"
Exit Sub
Case 2 ' Asia
srcCustomers.ConnectionString = "SGSqlServer"
Exit Sub
Case 3 ' Australia
srcCustomers.ConnectionString = "SYSqlServer"
Exit Sub
Case 4 ' Canada
srcCustomers.ConnectionString = "MSSqlServer"
Exit Sub
Case 5 ' EMEA
srcCustomers.ConnectionString = "NCSqlServer"
Exit Sub
Case 6 ' NE
srcCustomers.ConnectionString = "RUSqlServer"
Exit Sub
End Select
End SubProtected Sub ddlBusinessUnit_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

If (ddlBusinessUnit.SelectedIndex = 0) Then
panUser.Visible = False
panDistributor.Visible = False
panEndUser.Visible = False
GetDatabase(ddlBusinessUnit.SelectedIndex)
lblDb.Text = ddlBusinessUnit.SelectedIndex.ToString
Else
 
panUser.Visible = True
End If
End Sub
Here is my ASP.NET code:<asp:SqlDataSource ID="srcCustomers" runat="server" SelectCommand="SELECT cm_addr + ' - ' + cm_sort AS [name], cm_addr, cm_sort FROM cm_mstr WHERE cm_type <> 'I'">
</asp:SqlDataSource><asp:DropDownList ID="ddlBusinessUnit"
runat="server"AutoPostBack="True"
OnSelectedIndexChanged="ddlBusinessUnit_SelectedIndexChanged">
<asp:ListItem Value="" Text="Select One"></asp:ListItem>
<asp:ListItem Value="1" Text="Americas"></asp:ListItem>
<asp:ListItem Value="2" Text="Asia"></asp:ListItem>
<asp:ListItem Value="3" Text="Australia"></asp:ListItem>
<asp:ListItem Value="4" Text="Canada"></asp:ListItem>
<asp:ListItem Value="5" Text="EMEA"></asp:ListItem><asp:ListItem Value="6" Text="NE"></asp:ListItem>
</asp:DropDownList>
What am I missing?  Do I need to reference the whole connection string or is there a way I can reference the "NAME" id in the web.config file for the connection string?

View 2 Replies View Related

SqlDataSource And Dynamic SelectCommand

Jan 16, 2006

Good morning.I'm writing an application that allows users to generate Personal Leave requests and route them to their managers.I'm using the integrated Windows Authentication, so I'm able to get the user's username from HttpContext.Current.User.Identity.Name.How do I dynamically generate the SelectCommand?  Right now, I do:Dim strQuery As String = "SELECT * FROM [tblPLRequest] WHERE employee = '" & strUsername & "'"to get the query I want, but when I set my SelectCommand to that, it thinks it's a stored procedure (and can't find it).Am I going about this in the completely wrong way?Thanks.

View 3 Replies View Related

Get @@rowcount Data From MSSQL Using SqlDataSource With Delete Command

Mar 8, 2007

HiI'm using a simple SqlDataSource to connect to a stored proc to delete a number of rows from different tables.In my SQL im using: DECLARE @table1Count int

DELETE FROM Table1 WHERE id = @new_id

SET @table1Count=@@rowcount

SELECT @table1Count  I'm then using an input box and linking it to the delete control parameter.  Then on a button click event i'm running SqlDataSource1.Delete() which all works fine.  But how do i get the @table1Count back into my aspx page? Thanks 

View 3 Replies View Related

Setting SqlDataSource Update Command And Parameters Dynamically C#

Aug 31, 2007

Hello all,
Ok, I finally got my SqlDataSource working with Oracle once I found out what Oracle was looking for. My next hurdle is to try and set the Update Command and Parameters dynamically from a variable or radiobutton list. What I'm trying to accomplish is creating a hardware database for our computers by querying WMI and sending the info to textboxes for insertion and updating. I got that part all working nicely. Now I want to send the Computer name info to a different table column depending on if it is a laptop or desktop. I have been tossing around 2 ideas. A radiobutton list to select what it is and change the SQL parameters or do it by computer name since we have a unique identifier as the first letter ("W" for workstation, "L" for Laptop). I'm not sure what would be easiest but I'm still stuck on how this can be done. I posted this same question in here a few days ago, but I didn't have my SqlDataSources setup like I do now, I was using Dreamweaver 8, it is now ported to VS 2005. Below is my code, in bold is what I think needs to be changed dynamically, basically i need to change DESKTOP to LAPTOP...Thanks for all the help I've gotten from this forum already, I'm very new to ASP.NET and I couldn't do this without all the help. Thanks again!
 
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CAT %>"ProviderName="<%$ ConnectionStrings:CAT.ProviderName %>" SelectCommand='SELECT * FROM "COMPUTER"' UpdateCommand="UPDATE COMPUTER SET DESKTOP = :DESKTOP, TECH = :TECH, SERVICE_TAG = :SERVICE_TAG WHERE USERNAME=:USERNAME">
<UpdateParameters>
<asp:ControlParameter Name="USERNAME" ControlId="txtUserName" PropertyName="Text"/>
<asp:ControlParameter Name="SERVICE_TAG" ControlId="txtServiceTag" PropertyName="Text"/>
<asp:ControlParameter Name="TECH" ControlId="txtTech" PropertyName="Text"/>
<asp:ControlParameter Name="DESKTOP" ControlId="txtComputerName" PropertyName="Text"/>
</UpdateParameters>
</asp:SqlDataSource>

View 1 Replies View Related

[SQLDatasource]Dynamic Selection Of Column

Mar 29, 2006

Hey, I have a search form with a selectbox. This selectbox contains the columnnames.I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session.
If you see I have put in the querystring as columnname @sescolumn which I have initialised as asp:sessionparameter.But it gives no results. When I put @sescolumn between [] like normal columnnames are it doesn't work also.
Can someon put my on the right path?

 
<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @sescolumn like @seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @sesdatefrom and [Eindeverhuur] <= @sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource> 

View 3 Replies View Related

Using Datetime In Dynamic Sql Command

Feb 22, 2008

Hi. I get problem when I convert DateTime to literal value. It didn't match sql syntax.  ALTER PROCEDURE dbo.Search

(
@Table nvarchar(50),
@Field nvarchar(50),
@BeginDate DateTime,
@EndDate DateTime
)

AS

DECLARE @Sql nvarchar(1000)
SET @Sql = 'SELECT ' + @Table + '.* FROM ' + @Table + ' WHERE ' + @Field + ' BETWEEN ' + convert(nvarchar(20), @BeginDate) + ' AND ' + convert(nvarchar(20), @EndDate)
EXEC sp_executesql @Sql 

View 5 Replies View Related

How To Create A Dynamic SQL Command For OLE DB Source

Mar 21, 2008

Hi All,

I have a requirement to create a dynamic SQL Command in an OLE DB Source due to the fact that I need to read data from another database based on a date range. For example, the SQL command would look like

SELECT * FROM Table1 WHERE DateField BETWEEN '17/03/2008' and '21/03/2008'

and I need to change the dates - '17/03/2008' and '21/03/2008' to different dates when the package is deployed in production, how do I do that ?

Regards
Ash

View 12 Replies View Related

Sqldatasource Select Statement

Mar 27, 2007

Hi all,I drag sqldatasource to my form, and then adding a button there. I want when clicking the button to be able to use the sqldatasource1.select statement . I found some parameters that this method used but still dont know how to figure it out, which was IEnumerable Select (DataSourceSelectArguments a)for example when the button it clicked I want to perform the select * from employee Thanks

View 1 Replies View Related

How Do I Use A Value Returned From SQLDataSource Select?

Mar 28, 2007

I would like to use the value returned from my SqlDataSource SELECT method, in the INSERT method for the same SqlDataSource.
 Any ideas how this is done?

View 1 Replies View Related

If SqlDataSource.Select = Empty Then...?

May 28, 2007

(Newbie). I'm trying to: 1) check if the SELECT command has returned any records, and 2) put a msg box on the screen if there are no records returned from the SELECT query.  The type of code I'm heading towards is:  (but it's wrong). Thank you in advance for your C# code suggestion.
 protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{if (SqlDataSource1.SelectParameters.Contains is "") then
MessageBox.show ("There are no records available") ;
}

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved