What Is The Best Parameter To Use For A Sqldatasource When The Parameter Is The Sqlmembership Username?
Mar 15, 2008
I know i can accomplish this by setting a session variable on page load on the server side (Session["UserName"]= User.Identity.Name) and then use a session parameter, but I was wondering how to do it without using session variables, i was hoping the following code would work, but it doesn't. Thanks for any ideas.
" Name="UserName" Type="String" />
View 3 Replies
ADVERTISEMENT
Dec 26, 2006
I have looked and tried with no luck on using the Profile.UserName in an SQLDataSource Update select statement. and anyone please help me with this?UpdateCommand="UPDATE tblDocuments SET DocumentTypeID = @DocumentTypeID, DocDescription = @DocDescription, DocLocation = @DocLocation, DocStartDate = @DocStartDate, DocEndDate = @DocEndDate, LastUpdate = GETDATE(), LastUpdateBy = @ProfileUser WHERE (DocumentID = @DocumentID)" <UpdateParameters>
<asp:Parameter Name="DocumentID" />
<asp:Parameter Name="DocumentTypeID" />
<asp:Parameter Name="DocDescription" />
<asp:Parameter Name="DocLocation" />
<asp:Parameter Name="DocStartDate" />
<asp:Parameter Name="DocEndDate" />
<asp:Parameter Name="ClientID" />
<asp:Parameter Name="ProfileUser" />
</UpdateParameters>
View 2 Replies
View Related
Oct 14, 2006
I am creating a membership site. I’d like to be able to have members be able to send customized e-mail to their own individual e-mail list. In creating the mail function (at least just a simplified version so I can get the basics to work), I’m using a select statement that uses @UserName as a parameter in the WHERE clause. I can’t get this work, however, and one of the error messages I get says “must declare the scalar variable '@UserName'". Below is the code I’ve come up with, which may be far off. I'm new to programming so please keep that in mind. I’m using Visual Studio 2005 and SQL Server 2005. If anyone has any suggestions, I’d really appreciate it! Thank you.Imports System.Net.MailImports System.Data.SqlClientImports System.Web.UI.WebControlsImports System.WebPartial Class emailInherits System.Web.UI.PageProtected Sub btnSend_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSend.ClickDim MyEmail = New MailMessageDim MyClient = New SmtpClient("localhost")Dim FromAddress = New MailAddress(Profile.Email)MyEmail.From = FromAddress'-- Create MailAddresses from a database'-- Open a database connectionDim DBConnection = New SqlConnection( _"Server=localhost; Integrated Security=True; database=WebSite18database")DBConnection.Open()Dim SQLString As StringDim SelectParameters = New Data.SqlClient.SqlParameter("@UserName", Data.SqlDbType.VarChar)SQLString = "SELECT [UserName], [ClientFirstName], [ClientLastName], [CleintEmail], [ClientID] FROM [Clients] WHERE ([UserName] = @UserName)"Dim DBCommand = New Data.SqlClient.SqlCommand(SQLString, DBConnection)'-- Retrieve a recordset of selected records from the databaseDim DBReader As SqlDataReader = DBCommand.ExecuteReader()While DBReader.Read()Dim ToAddress = New MailAddress(DBReader("ClientEmail"))MyEmail.To.Add(ToAddress)End WhileDBReader.Close()DBConnection.Close()MyClient.Send(MyEmail)End SubEnd Class
View 2 Replies
View Related
Jan 22, 2008
This is probably a simple question, but I have a form with two content windows. In content1 using an sqldatsource1 I select a single record and display it using a FormView.
What I need to do is using one field from sqldatsource1 ("CategoryName", which is not displayed in formview1), in content window 2; I need to display all records with the same CategoryName.
So the simple English version is:
Using CategoryName from SqlDataSource1 (in content window 1), select all records in SqlDatasource2 where CategoryName is = CategoryName (in content window 2).
I am using vb code behind.
Thanks
View 1 Replies
View Related
Mar 2, 2008
I have a SqlDataSource, a GridView and a TextBox (whose ID is searchTB) on my page. I can use the SqlDataSource like this:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="Data Source=TORNADO;Initial Catalog=AdventureWorks;Integrated Security=True"
ProviderName="System.Data.SqlClient"
SelectCommand="select * from production.product where name = @nameparam">
<SelectParameters>
<asp:ControlParameter ControlID="searchTB" Name="nameparam"
PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
However, I want the search to be made using the LIKE operator, ie, select * from production.product where name LIKE %THE_TEXT_FROM_TEXTBOX%.
How can I do this? Thanks
View 5 Replies
View Related
Mar 31, 2008
Hi there,
I'm still new, so please be patient with me...
I am using C# ASP.NET 2.0. I have a web page that uses a Calendar control to display links for events from my database. The links use the __doPostBack to pass the EventID back to the same page. I was getting the EventID as a string okay and then passing it into a TextBox control. I then had an SqlDataSource control that set to display the Event in a DetailsView. The SqlDataSource uses a control to get the EventID - I used the TextBox control. It is all working fine, but...
What I want to do is instead of using a TextBox control - pass the string in to a public property in the class in my code-behind. I want to use an <asp:Parameter> on my SqlDataSource control to set the control to get the EventID from my public property. How do I do this?
Thanks in advance.
View 3 Replies
View Related
Jun 3, 2008
I have a gridview which is displaying a bunch of data. However, I'd only like to display data based on the query string. Here's my code: <asp:GridView ID="gv" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CssClass="GridControl" DataKeyNames="ID">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" Visible="false" SortExpression="ID" />
<asp:HyperLinkField DataNavigateUrlFields="ID" DataNavigateUrlFormatString="Details.aspx?id={0}" Text="Details" />
<asp:BoundField DataField="Name" HeaderText="Attendee" SortExpression="Name" />
<asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:conn %>" SelectCommand="sprocCUSTOM_GetDetail" SelectCommandType="StoredProcedure">
</asp:SqlDataSource>and my stored procedure: SELECT ID, Name, Email FROM Events WHERE (ID = @eventID) So as you can see, I need to get the value from the query string to add to my stored procedure. I know this can be done in the code-behind, but isn't there a way to add <SelectParameter> to the SqlDataSource? I just can't get the query string in there.
View 12 Replies
View Related
Aug 25, 2006
Hello all, This may be a simple question, but it is causing me some grief at the moment.I put a SqlDataSource control on my form, and pointed it to the stored procedures I had written to insert/update/delete rows in my database.The DataSource control notices that I am using parameters in my queries, and asks me to select where the parameters will be assigned from (Control, Form, Session, etc.)I am keeping the primary key value (in this case an ID number for a real estate listing) persisted in ViewState.Is there any way to access ViewState from the SqlDataSource control, or do I need to find another way to do this? Thanks,Adam
View 1 Replies
View Related
Nov 6, 2006
One of the requirements of the UpdateParameters for a GridView I'm building is that the fields that are being edited via EditItemTemplates are passed back to the UpdateParameter as XML. How would I go about combining the fields from the GridView/EditItems into an XML string that I can set as an asp:Parameter?
Thanks.
View 1 Replies
View Related
Feb 28, 2007
I'm trying to pass my SqlDataSource a parameter that is defined in the code-behind file for the same page. I've tried the method below but it does not work. Is there a better way?SubmitForm.ascx page: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ connection string...... %>" SelectCommand="sp_CourseMaterialShipment_GetCourses" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter DefaultValue="<% ProgramID %>" Name="programID" Type="Int32" /> </SelectParameters></asp:SqlDataSource> SubmitForm.ascx.vb page:Private ProgramID as string = "25"Public ReadOnly Property ProgramID() As String Get Return _ProgramID End GetEnd Property ThanksJason
View 2 Replies
View Related
May 4, 2007
Hi,
In the SqlDataSource control if I go to the SelectQuery property and I set one parameter with the “direction� property to “Output� the result doesn’t display in the control, why?
Points:
The procedure witch is in the selectquery property the parameter in it is set to output two.
View 3 Replies
View Related
Jun 14, 2007
I need to know how to setup the ControlParameter for a template control in my Gridview? I have a datepicker in my template and I need to know how to refer to it in the ControlParameter of the SQLDataSource control.
<UpdateParameters>
<asp:ControlParameter Name="dp_start" ControlID="bdpPlanStart" PropertyName="SelectedValue" Type="Datetime" /> Here is the template:<asp:TemplateField HeaderText="Start" SortExpression="dp_start">
<ItemTemplate>
<%#DataBinder.Eval(Container, "DataItem.dp_start", "{0:d}")%>
</ItemTemplate>
<EditItemTemplate>
<BDP:BasicDatePicker id="bdpPlanStart" SelectedValue='<%# DataBinder.Eval(Container.DataItem,"dp_start") %>' runat="server" DateFormat="d">
</BDP:BasicDatePicker>
</EditItemTemplate>
</asp:TemplateField>
View 1 Replies
View Related
Jul 2, 2007
I have a table with with some column name includes a space. for example [Product ID] [Product Name] Instead of Product_ID, Product_Name. when I try to create a gridview and enable delete, insert. It just won't work.
I've been trying for several hours without success. When I click on delete. the page postback without any error, but the record doesn't get deleted or updated.
<asp:SqlDataSource id="sourceProducts" runat="server" SelectCommand="SELECT [Product ID], [Product Name] FROM Products" ConnectionString="<%$ ConnectionStrings:mydb %>"DeleteCommand="Delete from Products where [Product ID]=@ProductIDUpdateCommand="UPDATE Products SET [Product Name]=@ProductName WHERE [Product ID]=@ProductID" > <UpdateParameters> <asp:Parameter Name="ProductName" /> <asp:Parameter Name="ProductID" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="ProductID" Type="Int32"/> </DeleteParameters></asp:SqlDataSource><asp:GridView ID="GridView2" runat="server" DataSourceID="sourceProducts" AutoGenerateColumns="False" DataKeyNames="Product ID" > <Columns> <asp:BoundField DataField="Product ID" HeaderText="ID" ReadOnly="True" /> <asp:BoundField DataField="Product Name" HeaderText="Product Name"/> <asp:CommandField ShowEditButton="True" ShowDeleteButton="True"></asp:GridView>
Another testing I did was to use another table with no space in the Column name, Product_ID, Product_Name. and I can't name my parameter as PID, PNAME. I have to name it as @Product_ID, @Product_Name in order for Delete, update to work. My understanding is if I declare the parameter explicitly(<asp:Parameter Name="PID" />, I can use any name I want. Did I must missed something?
I'm new to ASP.NET, could someone help me?
Thanks.
View 5 Replies
View Related
Jul 20, 2007
I'm sure that is really simple, but how do I pass a parameter with multiple value to a SQLdatasource?
ex: SELECT field1 from tblTableA where idTableA IN ( @Param1)
Let's say I want to pass 1,2,3,4 as Param1 (SELECT field1 from tblTableA where idTableA IN ( 1,2,3,4))
How I am supposed tu use the .SelectParameters.Add() to pass a list of integers instead of a single value??
Thanks in advance.
View 2 Replies
View Related
Dec 5, 2007
Is it possible to use a WHERE-IN statement with a SqlDataSource control. For instance:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
SelectCommand="SELECT [Id], [Name], [Phone] FROM [Table1] WHERE ([Id] IN @Id)">
<SelectParameters>
<asp:Parameter DefaultValue="( 1, 3, 5, 7, 11 )" Name="Id" Type="Int32" />
</SelectParameters></asp:SqlDataSource>
I'm hoping the gridview would then display a table with rows for records 1,3,5,7,11. Thanks for any help...
View 2 Replies
View Related
Jan 31, 2008
Hi there,I'm new to db stuffs and I'm using sqldatasource to pull my data from the server. Here's the codes.<asp:SqlDataSource ID="testSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:p01impConnectionString %>" SelectCommand="SELECT [a], [b], [c], [d], [e] FROM [MYDB] WHERE (([a] = @a) AND ([b= @b))" CancelSelectOnNullParameter="False"> <SelectParameters> <asp:ControlParameter ControlID="TextBox1" Name="a" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBox2" Name="b" PropertyName="Text" Type="String" /> </SelectParameters></asp:SqlDataSource> notice that I haven't used [c], [d], [e] and I want to declare a parameter, something like: Total = c + (d*e)can anyone show me the syntax to do this UNDER sqldatasource? I then will have to put that Total in a gridview (i can solve this part)Thanks
View 1 Replies
View Related
Dec 14, 2005
Problem: The system throws the following error"Procedure or Function 'sp_TestRequestFormMaster_StatusChange' expects parameter '@Status', which was not supplied."I'm using VS 2005 Final.Recreate the problem:I've created a simple stored procedure with two parameters on SQL 2005 on Win 2003 Server. @ID INT, & @Stutus INTOn a SQLDataSource Control for the Delete query, using the build button to open the Command and Parameter Editor, I click the Refresh Paramater.I set ID Parameter Source: Control, ControlID: GridView1I set Status Parameter Source: None, DefaultValue: 1001.Partial Source View:
<DeleteParameters><asp:ControlParameter ControlID="GridView1" Name="ID" propertyName="SelectedValue" Type="Int32" DefaultValue="" /> <asp:Parameter DefaultValue="1001" Name="Status" Type="Int32" /><asp:Parameter DefaultValue="" Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" /></DeleteParameters>I run the code, click the delete in the GridView and the error appears. How can I pass a status value without relating it to a source.
View 1 Replies
View Related
Jan 10, 2006
Hi, I am using a SQL DataSource with a few parameters. I need to specify the value of the parameters at run time but I need a custom way to do it as the value needs to be calculated not come from Cookie, Control, Form, Profile, QueryString or Session. Is there a way to bind your own value to these parameters. For instance if I had a variable how would I bind that to the parameter?
At the moment i am doing the following which works but I dont think it is the correct way
dsMyDataSource.SelectParameters["MyParameter"].DefaultValue = MyCalculatedValue;
In previous projects i have added a value to the Session and then bound the parameter value to the session but that doesnt seem like a good solution either.
Thanks for any help you can give.
Martin
View 9 Replies
View Related
Aug 8, 2006
Hi, I have a little problem with a SqlDataSource.
A table in my database has an ID collumn, which is an integer auto-increment. The datasource has a SelectQuery with an [ID] parameter, which it retrieves from the querystring.
I want the DataSource to return all rows when no querystring parameter is passed, and when an ID is given via the querystring, a 'WHERE' clause in the SelectQuery would return only certain records.
However, suppose I have the following query:
SELECT * FROM SomeTable WHERE ([ID] = @ID)
When I bind @ID to the QueryString with '*' as DefaultValue, it throws an exception saying it can't convert '*' to a smallint, which makes sense.
I haven't got a clue how to solve this, except for using different DataSources based on the presence of the querystring parameter.
Any idea's?
View 7 Replies
View Related
Oct 6, 2006
Hello, I just started working with ASP.NET.I'm trying to use the CheckBoxList Control. As I understand it, you can bind a SqlDataSource to this control and it loads the list for you. However tp precheck the items, you have to do this manually. This part works fine. Next part was to save whatever the user checks. I wrote stored Procedure and now just trying to pass 1 parameter to the stored procedure using a second SqlDataSource. I get the error: "A severe error occurred on the current command. The results, if any, should be discarded."Here is the second datasource I am using to try and save the data:<asp:SqlDataSource ID="sds_PersonRole" runat="server" ConnectionString="<%$ ConnectionStrings:Development %>"SelectCommand="usp_selectPersonRole"SelectCommandType="StoredProcedure"UpdateCommand="usp_updatePersonRole"UpdateCommandType="StoredProcedure"><SelectParameters> <asp:ControlParameter ControlID="gv_Person" Name="PERSON_ID" PropertyName="SelectedValue" Type="Int32" /></SelectParameters><UpdateParameters> <asp:Parameter Name="strXML" Size="8000" Type="String" /> <asp:Parameter Direction="InputOutput" Name="err_msg" Type="String" Size="150" DefaultValue="0" /></UpdateParameters></asp:SqlDataSource>I have a code behind file with the following modules: (btnEditPerson is clicked to start the process. the sds_PersonDetails is updated via form contolls and works fine.) Error occurs on the bolded line (Stored procedure is just accepting the string and saving to a field. It works fine, I tested it. Its just erroring out before it runs the stored procedure. Protected Sub btnEditPerson_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEditPerson.Click sds_PersonDetails.Update() gv_Person.DataBind() sds_PersonRole.Update()End Sub Protected Sub sds_PersonRole_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles sds_PersonRole.Updating Dim command As Data.Common.DbCommand command = e.Command 'un-check all checkboxlist items (count - 1 to account for starting at 0) Dim listCount As Integer = cbl_Role.Items.Count() - 1 Dim strXML As String strXML = "<personRole>" For x As Integer = 0 To listCount If cbl_Role.Items(x).Selected() = False Then strXML = strXML & "<person id='" & gv_Person.SelectedValue & "' />" strXML = strXML & "<role id='" & cbl_Role.Items(x).Value & "' />" End If Next strXML = strXML & "</personRole>" command.Parameters("@strXML").Value = strXML lbl_ErrMsg.Text = command.Parameters("@err_msg").Value.ToString() End Sub
View 1 Replies
View Related
Oct 8, 2006
I am not sure (and I've been known to overlook things) if it is possible to use a Table parameter type as parameter to a stored procedure? Is it possible? I would prefer not to create a SQL batch statement calling an stored proc w/ one parameter over and over. Instead i'd rather just create the stored proc to take a table parameter and pass the table into the stored proc. I know I could use a CSV string of IDs instead of a table... or I could create a big SQL batch and use that. But I'd rather not. Could anyone provide some guidance? Thanks!Joe
View 2 Replies
View Related
Jan 12, 2007
My sql-string looks like this:
SelectCommand="SELECT * FROM Table1 WHERE Field1 IN @target"
And my parameter looks like this:
<asp:ControlParameter Name="target" ControlID="CheckBoxList1" PropertyName="SelectedValue" />
This code gives me a syntax error near @target. Someone got a solution?
View 2 Replies
View Related
Dec 29, 2007
hi i want to select data based on a user id, which is stored in a custom object-> sessionhandler.user.id
how can i put that in the select parameter?
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:RentTodaySQL %>" DeleteCommand="DELETE FROM [Rentals] WHERE [RentalID] = @original_RentalID"
InsertCommand="INSERT INTO [Rentals] ([Headline], [Description], [MoveInSpecial], [MonthlyCost], [Deposit], [AvailableDate], [FeaturedListing], [UserID], [Address], [Address2], [City], [State], [Zip], [LeaseTermID], [LeaseDetails], [Section8], [Section8Details], [PetsAllowed], [PetsDetails], [PetDeposit], [ApplicationFee], [ApplicationDetails], [SmokingAllowed], [RentalCategoryID], [Bedrooms], [Bathrooms], [SqFootage], [LotSize], [YearBuilt], [DefaultImageID]) VALUES (@Headline, @Description, @MoveInSpecial, @MonthlyCost, @Deposit, @AvailableDate, @FeaturedListing, @UserID, @Address, @Address2, @City, @State, @Zip, @LeaseTermID, @LeaseDetails, @Section8, @Section8Details, @PetsAllowed, @PetsDetails, @PetDeposit, @ApplicationFee, @ApplicationDetails, @SmokingAllowed, @RentalCategoryID, @Bedrooms, @Bathrooms, @SqFootage, @LotSize, @YearBuilt, @DefaultImageID)"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Rentals] WHERE ([UserID] = @UserID)"
UpdateCommand="UPDATE [Rentals] SET [Headline] = @Headline, [Description] = @Description, [MoveInSpecial] = @MoveInSpecial, [MonthlyCost] = @MonthlyCost, [Deposit] = @Deposit, [AvailableDate] = @AvailableDate, [FeaturedListing] = @FeaturedListing, [UserID] = @UserID, [Address] = @Address, [Address2] = @Address2, [City] = @City, [State] = @State, [Zip] = @Zip, [LeaseTermID] = @LeaseTermID, [LeaseDetails] = @LeaseDetails, [Section8] = @Section8, [Section8Details] = @Section8Details, [PetsAllowed] = @PetsAllowed, [PetsDetails] = @PetsDetails, [PetDeposit] = @PetDeposit, [ApplicationFee] = @ApplicationFee, [ApplicationDetails] = @ApplicationDetails, [SmokingAllowed] = @SmokingAllowed, [RentalCategoryID] = @RentalCategoryID, [Bedrooms] = @Bedrooms, [Bathrooms] = @Bathrooms, [SqFootage] = @SqFootage, [LotSize] = @LotSize, [YearBuilt] = @YearBuilt, [DefaultImageID] = @DefaultImageID WHERE [RentalID] = @original_RentalID">
<SelectParameters>
<asp:Parameter DefaultValue="<%= SessionHandler.User.ID %>" Name="UserID" Type="Int32" />
</SelectParameters>
View 1 Replies
View Related
Apr 8, 2008
i created a cookie as follows
HttpCookie myCookie = new HttpCookie("Portal");
myCookie["EMail_ID"] = Email_ID;myCookie["Role"] = Role_ID.Value.ToString();myCookie.Expires = DateTime.Now.AddMinutes(1);
Response.Cookies.Add(myCookie);
Now i have to take the"EMail_ID" as input parameter in sqldatasourse.
<asp:SqlDataSource ID="SqlDS_DC_List" runat="server" ConnectionString="<%$ ConnectionStrings:EnR_Portal_ConnectionString %>"
SelectCommand="proc_DC_List" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:CookieParameter CookieName=" " Name="Email_ID" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
how can i specify Email ID as cookie name?
View 11 Replies
View Related
May 21, 2008
Why does this not work? <asp:SqlDataSource ID="employeeSource" runat="server" ConnectionString="<%$ ConnectionStrings:mainDB %>" DataSourceMode="DataReader" ProviderName="System.Data.SqlClient" OnSelecting="OnSourceSelecting" SelectCommand="SELECT * FROM Employees WHERE ID = @employeeID"> <SelectParameters> <asp:Parameter Name="@employeeID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>In code behind: protected void OnSourceSelecting(object sender, SqlDataSourceCommandEventArgs args) { // THIS LINE THROWS EXCEPTION: An SqlParameter with ParameterName '@campaignID' is not contained by this SqlParameterCollection. args.Command.Parameters["@employeeID"].Value = 3; // In future, will use dynamic value. } If, instead I do this: <asp:SqlDataSource ID="employeeSource" runat="server" ConnectionString="<%$ ConnectionStrings:mainDB %>" DataSourceMode="DataReader" ProviderName="System.Data.SqlClient" OnSelecting="OnSourceSelecting" SelectCommand="SELECT * FROM Employees WHERE ID = @employeeID"> <SelectParameters> <asp:QueryStringParameter Name="@campaignID" QueryStringField="id" /> </SelectParameters> </asp:SqlDataSource>(Of course I call page with EmployeePage.aspx?id=123)I get the exception: Must declare the scalar variable "@employeeID".
View 5 Replies
View Related
Dec 5, 2005
Hi, I'm having some issues with the SqlDataSource. I want to use it
to
populate a GridView, but using an optional parameter to filter the
results.
This is what I have right now (hopefully haven't made any typos
- can't
copy/paste):
<asp:SqlDataSource ID="test1" runat="server"
SelectCommand="SELECT * FROM
SomeTable WHERE (@MyParam IS NULL OR MyColumn =
@MyParam) ORDER BY
SomeColumn" ConnectionString="<%
ConnectionStrings:MyConnString %>" >
<SelectParameters>
<asp:ControlParameter Name="MyParam"
ControlID="DropDownList1"
PropertyName="SelectedValue" Type="String"
ConvertEmptyStringToNull="True"
DefaultValue=""
/>
</SelectParameters>
</asp:SqlDataSource>
<asp:DropDownList
ID="DropDownList1" runat="server" AutoPostBack="True">
<asp:ListItem
Selected="True" Value="">All</asp:ListItem>
<asp:ListItem>AnotherValue</asp:ListItem>
<asp:ListItem>SomeElse</asp:ListItem>
<asp:ListItem>Whatever</asp:ListItem>
</asp:DropDownList>
<asp:GridView
ID="GV" runat="server" DataSourceID="test1"
AutoGenerateColumns="False"
DataKeyNames="SomeID"
<columns>
<asp:BoundField
DataField="SomeColumn" HeaderText="A Title"
SortExpression="SomeColumn"
/>
(more bound columns here...)
</columns>
</asp:GridView>
When I test the SQL query in
the query designer it works (returns only rows
having the value passed as a
parameter when one is specified, otherwise it
returns all rows), so it seems
like that part is OK. The "All" (as in
"return all"/no filtering) entry in
the DropDownList has a value of a zero
lenght string, and the
ControlParameter has the convert empty string to null
to true (and the
default value is the same), so it should get converted to a
null when "All"
is selected, hence returning all rows. But it doesn't work.
It works fine for
all the entries with text, but the zero lenght string
somehow doesn't work -
I get no results at all instead of it returning all
rows (but the query
itself worked fine when I tested it).
What am I missing? I just can't
find what I'm doing wrong. Any ideas/hints?
(I also need to do the same with
an ObjectDataSource, so hopefully I can get
this to work!) I can't think of
an easy way to find out if the zero lenght
string gets converted to a null or
not (I've even tried adding OR @MyParam =
'' to the query and it still didn't
work....) Right now I'm stuck....
(Also posted this question on microsoft.public.dotnet.framework.adonet newsgroup)
Thanks a lot in advance for the
replies!
Carl B.
View 1 Replies
View Related
Mar 13, 2006
I Have a SQLDataSource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"ConnectionString="<%$ ConnectionStrings:SqlServer %>"ProviderName="<%$ ConnectionStrings:SqlServer.ProviderName %>"SelectCommandType="StoredProcedure"SelectCommand="pe_getAppraisals" ConflictDetection="CompareAllValues"><SelectParameters><asp:ControlParameter ControlID="ddlPType" PropertyName="Value" Direction="Input" Name="PType" ConvertEmptyStringToNull="true" /><asp:ControlParameter ControlID="ddlClient" PropertyName="Value" Direction="Input" Name="Client" ConvertEmptyStringToNull="true" /><asp:Parameter Direction="Input" Name="PageSize" DefaultValue="20" Type="Int32" /></SelectParameters></asp:SqlDataSource>
When I load the page no results are returned. The only way I get results is to select an item from both drop down lists... My stored procedure works if I pass NULL or '' (an empty string) for PType and.or Client. Any Ideas on how to fix this or work around it? Is there any way for me to see what is being passed to the database for these values before I select an item from the dropdown lists?
Thank YouJason
View 3 Replies
View Related
Mar 21, 2006
Can anyone help me out with using parameters in my SqlDataSource?
I have my UpdateCommand set as follows:
UpdateCommand="UPDATE Custom_ContainerRequest_ESTWEIGHTS SET WEIGHTRANGES = @WEIGHTRANGES WHERE ESTWEIGHTS_ID = @original_ESTWEIGHTS_ID"
<UpdateParameters> <asp:Parameter Name="WEIGHTRANGES" Type="String" /> <asp:Parameter Name="original_ESTWEIGHTS_ID" Type="Int32" /></UpdateParameters>
And the application is just hanging when I run it. (No error message.)
If, however, I hardcode the values instead of using parameters, it works just fine everytime.
UpdateCommand="UPDATE Custom_ContainerRequest_ESTWEIGHTS SET WEIGHTRANGES = 'test' WHERE ESTWEIGHTS_ID = 1"
So I don't know if it's a single-quote issue, or if there's some other syntax I need, or what. One last note is that this is a DB2 database I'm using. But I'm sure that my connection is valid because I can read and update data...only not using parameters.
Any help is greatly appreciated.
Thanks,Billy
View 2 Replies
View Related
Sep 21, 2006
Hi Guys. I am trying to insert the date as the default value into the DatePosted parameter in the sqldatasource object. I have put have the following below but it doesn't work. I have also tried <asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= Date.Now %>" /> <asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= now() %>" /> I know the solution is probably simple and I look like an idiot, but excuse me because I am very knew and fragile at this lol... any help would be great :). Mike.
View 7 Replies
View Related
Feb 1, 2007
i have a sqldatasource on my asp.net page -- select * from table where id = @id
i want to set the @id in the backend and set the result to textbox1.text
how do i do this?
View 1 Replies
View Related
Feb 3, 2007
Hi all:
I have a list of items (actually a relation in which a user has selected an item, along with a rating for the item) in an Access database table, connected to my app with a SqlDataSource and bound to a repeater. The repeater displays the items to the user along with a dropdown box to show the rating, and allow the user to update it. The page connects and displays correctly.
My problem is that when the user submits the page and I iterate through the repeater items to update each rating, the updates are not being completed in the database. The update works if I hard-code a value for the rating into the query itself, but not when using an updateparameter (pTaskRating below). In other words if I replace pTaskRating with '5', all the correct records will be found and have their ratings updated to 5. That means that the mySurveyId and pTaskId(DefaultValue) parameters have to be working, because the right records are found, but I can't seem to update records based on the DefaultValue of the pTaskRating parameter, even though I can verify that the DefaultValue is correct by placing a watch on it. It seems that my problem must be in my use of that particular parameter in the query, either in properties of the parameter or in the value assigned to it. I am extremely frustrated - any ideas would be greatly, greatly appreciated. Thanks!
Bruck
The table I'm pulling from and updating looks like this:
SURVEY_ID (Text 50), TASK_ID (Long Int), RATING_ID (Long Int)
Here's my ASPX for the main data source:
<asp:SqlDataSource ID="sqlTaskSelections" runat="server" ConnectionString='Provider=Microsoft.Jet.OLEDB.4.0;Data Source="abc.mdb";Persist Security Info=True;Jet OLEDB:Database Password=xyz' ProviderName="System.Data.OleDb" SelectCommand="SELECT [SURVEY_ID], [TASK_ID], [RATING_ID] FROM [TBL_TASK_SELECTION] WHERE [SURVEY_ID] = mySurveyId" UpdateCommand="UPDATE [TBL_TASK_SELECTION] SET [RATING_ID] = pTaskRating WHERE ([SURVEY_ID] = mySurveyId) AND ([TASK_ID] = pTaskId)">
<UpdateParameters>
<asp:SessionParameter Name="mySurveyId" SessionField="SurveyId" DefaultValue="" /><asp:Parameter Name="pTaskId" DefaultValue="" /><asp:Parameter Name="pTaskRating" DefaultValue="" />
</UpdateParameters>
And here's the repeater (the Task ID and Rating are stored in hidden fields for easy access later):
<asp:Repeater ID="rptTaskSelections" runat="server">
<HeaderTemplate><table border="0"></HeaderTemplate>
<ItemTemplate>
<tr class="abctr"><td class="normal"><asp:DropDownList ID="cbRatings" runat="server"></asp:DropDownList><asp:HiddenField ID="hTaskId" Runat="server" Visible="false" Value='<%# Eval("TASK_ID") %>' /><asp:HiddenField ID="hRating" Runat="server" Visible="false" Value='<%# Eval("RATING_ID") %>' /> <%# Eval("TASK_ID") %></td></tr>
</ItemTemplate>
<FooterTemplate></td></tr></table></FooterTemplate>
</asp:Repeater>
And here's the page load and submit VB:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
'BIND / LOAD RATINGS TO DROPDOWN BOXES HEREDim i As IntegerDim cbCurrentRating As DropDownListDim hCurrentRating As HiddenFieldrptTaskSelections.DataSource = sqlTaskSelectionsrptTaskSelections.DataBind()
For i = 0 To rptTaskSelections.Items.Count - 1
cbCurrentRating = rptTaskSelections.Items(i).FindControl("cbRatings")hCurrentRating = rptTaskSelections.Items(i).FindControl("hRating")
cbCurrentRating.DataSource = sqlRatingscbCurrentRating.DataTextField = "RATING"cbCurrentRating.DataValueField = "ID"cbCurrentRating.DataBind()cbCurrentRating.SelectedValue = hCurrentRating.Value
Next
End If
End Sub
Protected Sub btnSubmitRateTasks_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmitRateTasks.Click
'UPDATE RATINGS HERE
Dim i As IntegerDim cbCurrentRating As DropDownListDim hCurrentTaskId As HiddenField
For i = 0 To rptTaskSelections.Items.Count - 1
cbCurrentRating = rptTaskSelections.Items(i).FindControl("cbRatings")hCurrentTaskId = rptTaskSelections.Items(i).FindControl("hTaskId")
sqlTaskSelections.UpdateParameters.Item("pTaskId").DefaultValue = hCurrentTaskId.ValuesqlTaskSelections.UpdateParameters.Item("pTaskRating").DefaultValue = cbCurrentRating.SelectedValue
sqlTaskSelections.Update()
Next
Response.Redirect("nextpage.aspx")
End Sub
View 3 Replies
View Related
Feb 22, 2007
Hi,
I am trying to get the selected options from a listbox and either pass a SqlDataSource object the array or loop through it and pass each element of the array. I then need to modify the returned databtable to graphing function, but first drop the last column. I was wondering if anyone can help me with the following:
1. Pass an array into SqlDataSource Select OR 2. Pass a single argument into the Select statement and populate a datatable without it writing over the current row each time it iterates through the foreach statement. I am looking for the dataview to append to dt each time it loops. Is there a property for dataview that behaves like the "ClearBeforeFill" for table adapters?3. Update a parameter programmatically
Below code works, but I think it can be more efficient. Any suggestions would be greatly appreciated.
Thanks in advance!!
DataTable dt = new DataTable(); DataTable dt2 = new DataTable(); DataView dv = new DataView();
foreach(ListItem liOptions in ListBox1.Items) { if(liOptions.Selected) { SqlDataSource1.SelectParameters.Add("Parameter1", liOptions); dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty); dt2 = dv.Table; dt.Merge(dt2); dt2.Dispose(); SqlDataSource1.SelectParameters.Clear(); } }
if (dt.Rows.Count > 0) { Graph(dt); //Pass original datatable (dt) to Graph(); dt.Columns.RemoveAt(2); //Reformat datatable (dt) and remove last column before binding to Gridview1
GridView1.DataSource = dt; GridView1.DataBind(); } else {
errorMessage.Text = "No data was returned!"; }
View 3 Replies
View Related
Jul 7, 2007
Hi How do I set the default value of a SQLdatasource parameter to the current date-time <asp:Parameter Name="original_lastsaved" Type="DateTime" defaultvalue="???"/> The sql column field type is "datetime", so it will not accept a stringThanks for the help.Bones
View 3 Replies
View Related
