Inserting The Date As Defaultvalue In A Sqldatasource Parameter
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 %>" />
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
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.
I set up a Sqldatasource control in 2.0 and I can retrieve data through a SQL Server connection from a stored procedure. My problem is when I set up the insert command object through the wizard for the Sqldatasource control with another stored procedure for inserting data and call the insert method of my Sqldatasource object i get nothing not even an error it just goes through the code like nothing was wrong and I don't get anything inserted. I don't know if this could be a problem but one of the parameters in the stored procedure is declared like this: @return tinyint output. I don't know how the Sqldatasource accounts for return parameters. Here is the code for the insert for the Sqldatasource object.<asp:SqlDataSource ID="sdsMain" runat="server" ConnectionString="<%$ ConnectionStrings:SN_CUSTOMERConnectionString %>"InsertCommand="uspSNOrder_Promo_Live" InsertCommandType="StoredProcedure" SelectCommand="uspOPFillPromo"SelectCommandType="StoredProcedure"><InsertParameters><asp:ControlParameter ControlID="ddlPromo" Name="promoid" PropertyName="SelectedValue"Type="Int32" /><asp:Parameter DefaultValue="1" Name="datasourceid" Type="Int32" /><asp:Parameter DefaultValue="0" Name="datasourcekey" Type="String" /><asp:Parameter DefaultValue="9" Name="salesroomid" Type="Int32" /><asp:Parameter DefaultValue="9999" Name="userid" Type="Int32" /><asp:ControlParameter ControlID="txtFName" DefaultValue="" Name="firstname" PropertyName="Text"Type="String" /><asp:ControlParameter ControlID="txtLName" Name="lastname" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtAddress" Name="address" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtCity" Name="city" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtState" Name="state" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtZip" Name="zip" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtPhone" Name="phone" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtEmail" Name="email" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="ddlStatus" Name="status" PropertyName="SelectedValue"Type="Int32" /><asp:Parameter DefaultValue="0" Direction="InputOutput" Name="return" Type="Byte" /></InsertParameters></asp:SqlDataSource>Thanks in advance
Greetings, When using Inserting event of SqlDataSource ASP.NET gives me an error when I reference InsertParameter by Name An SqlParameter with ParameterName 'CreatedByEmployeeId' is not contained by this SqlParameterCollection. However, when I reference parameter by index everything works. Is this a bug or I'm doing something wrong? Here's the code: <asp:SqlDataSource ID="dsRole" runat="server" ConnectionString="<%$ ConnectionStrings:SecurityConnectionString %>" DeleteCommand="spDeleteRole" InsertCommand="spAddRole" SelectCommand="spGetRole" UpdateCommand="spUpdateRole" DeleteCommandType="StoredProcedure" InsertCommandType="StoredProcedure" SelectCommandType="StoredProcedure" UpdateCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="RoleId" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="RoleId" Type="Int32" /> <asp:Parameter Name="RoleName" Type="String" /> <asp:Parameter Name="RoleDescription" Type="String" /> <asp:Parameter Name="UpdatedByEmployeeId" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="RoleName" Type="String" /> <asp:Parameter Name="RoleDescription" Type="String" /> <asp:Parameter Name="CreatedByEmployeeId" Type="Int32" /> </InsertParameters> <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="RoleId" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource> When using parameter name, I get an error: Protected Sub dsRole_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles dsRole.Inserting e.Command.Parameters("CreatedByEmployeeId").Value = Internal.Security.GetEmployeeIdFromCookie(Page.Request.Cookies) End Sub When using index instead of name, there's no problem: Protected Sub dsRole_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles dsRole.Inserting e.Command.Parameters(2).Value = Internal.Security.GetEmployeeIdFromCookie(Page.Request.Cookies) End Sub
Hi all, I was wondering if anyone can help me figure out how to insert a variable into a query using Visual Studio 2005 with the SqlDataSource control. I cant seem to be able to enter a var into the query parameters.
this is my SqlDataSource: <asp: SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imLookinLikeConnectionString %>" DeleteCommand="DELETE FROM [tblDiaryEntries] WHERE [DiaryEntryID] = @DiaryEntryID" SelectCommand="SELECT tblDiaryEntries.DiaryEntryID, tblDiaryEntries.EntryDate, tblDiaryEntries.Subject, tblDiaryEntries.DiaryEntry, aspnet_Users.UserName FROM tblDiaryEntries INNER JOIN aspnet_Users ON tblDiaryEntries.UserID = aspnet_Users.UserId WHERE UserName=@UserName ORDER BY tblDiaryEntries.EntryDate DESC" UpdateCommand="UPDATE [tblDiaryEntries] SET [DiaryEntry] = @DiaryEntry, [EntryDate] = @EntryDate, [Subject] = @Subject WHERE [DiaryEntryID] = @DiaryEntryID" InsertCommand="INSERT INTO tblDiaryEntries(UserID, EntryDate, Subject, DiaryEntry) VALUES (@UserId, GETDATE(), @Subject, @DiaryEntry)"> <DeleteParameters> <asp: Parameter Name="DiaryEntryID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp: Parameter Name="DiaryEntry" Type="String" /> <asp: Parameter Name="EntryDate" Type="String" /> <asp: Parameter Name="Subject" Type="String" /> <asp: Parameter Name="DiaryEntryID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp: Parameter Name="DiaryEntry" Type="String" /> <asp: Parameter Name="EntryDate" Type="String" /> <asp: Parameter Name="Subject" Type="String" /> <asp: ProfileParameter DefaultValue="Anonymous" Name="UserName" PropertyName="UserName" /> <asp: Parameter Name="UserId" /> </InsertParameters> <SelectParameters> <asp: ProfileParameter DefaultValue="Anonymous" Name="UserName" PropertyName="UserName" /> </SelectParameters> </asp: SqlDataSource> What I want to do is to tell the SqlDataSource that @UserName = this.User.Identity.Name, but I only know how to do that in code-behind, not sure how to insert it into the code above.Any ideas?
Hi, I have created a website and I have a web form, I need to capture data and calculate fields and then insert the calculated field including the data into a sqlserver database. I created a store procedure and the parameters for the txt boxes is working fine. But the calculated fieldds are not getting inserted. Can someone please help? the code for insert is below: For the calculated field, I used txtboxes which "hold" the calculated field in the submit button event. I also tried using query string field like this: but that did not work either. Help will be very much appreciated. <asp:QueryStringParameter DefaultValue="1" Name="totlabchg" QueryStringField="laborcharge" Type="Decimal" />__________________ <InsertParameters> <asp:FormParameter DefaultValue="0" FormField="txtaccount" Name="ActNumber" Type="String" /> <asp:FormParameter DefaultValue="0" FormField="txtmeter" Name="MeterNumber" Type="String" /> <asp:FormParameter DefaultValue="0" FormField="txtdov" Name="VisitDate" Type="DateTime" /> <asp:FormParameter DefaultValue="" FormField="txtsvadd" Name="SvAdd" Type="String" /> <asp:Parameter Name="TampId2" Type="Int32" /> <asp:Parameter DefaultValue="64.44" Name="charge1" Type="Decimal" /> <asp:FormParameter FormField="txtq1" Name="quantity1" Type="Int32" />
I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers). I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr). I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table. I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds). A where clause seems to be needed but I don’t know the syntax or don’t find the right function I use the following code to insert the row :
and the following code to get the row back (to put in arraylist):
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())� WHERE LDate = '" + DateTime.Today.ToString() + "'"
Which is the correct syntax? Is there a better way to insert and select based on the date?
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging). Today’s date is in the database but isn’t found by my tsql code I think.
I want to make a sqldatasource to insert data ito a table with values from textfields page. I want to use the sqldatasource programically (not bind it to a formcontrol). I drag a sqldatasource from the toolbox to the design surface and start configuring the datasource. I spesify a custom sql statement, select the "Insert" tab and insert the table I want to store into the builder. Then I select the fields and the values (parameters from tue textfields. I test it with the "Execute query" button and the results is stored in the table. (everything seems ok) I press the "OK" button but both the "Next" and "Finish" buttons are dissabled, o I can not store the query. What is going wrong. Can someone please help me ? Tom Knardahl
I have a database with columnsuserOwnListsuserID uniqueidentifieruserName nvarchar100userList nvrachar100createdDateI have created successfully a gridview controller to edit these values in database. The Gridview data is populated by SqlDataSource.I have also created a EmptyDataTemplate and created a form into it. There is only one textBox and submit button to create the First entry to userOwnLists -table.Now I collect the value from EmptyDataTemplate textbox with id userList1 and create a codebehind logic for the submitbutton.protected void Button2_Click(object sender, EventArgs e) { TextBox listName = (TextBox)this.FindControl("listName1", GridView1.Controls);SqlDataSource1.InsertParameters["userId"].DefaultValue = Membership.GetUser().ProviderUserKey; SqlDataSource1.InsertParameters["userName"].DefaultValue = Membership.GetUser().UserName.ToString(); SqlDataSource1.InsertParameters["listName"].DefaultValue = listName.Text; SqlDataSource1.InsertParameters["createdDate"].DefaultValue = DateTime.Now.ToString(); SqlDataSource1.Insert(); }The problem is now that I get error: Exception Details: System.Data.SqlClient.SqlException: Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.OK. So I Googled a bit and found this:http://scottonwriting.net/sowblog/posts/4690.aspxMy Question is: How do I convert userId so I can insert it to database successfully?This does not work:String userId = Membership.GetUser().ProviderUserKey.ToString(); SqlDataSource1.InsertParameters["userId"].DefaultValue = Convert.ToString(userId);
Hello everybody, I have following datasource <asp:SqlDataSource ID="sqlIncidentTemplates" runat="server" ConnectionString="<%$ ConnectionStrings:Standard %>" SelectCommand="SELECT [IncidentText] FROM [IncidentTemplates] WHERE [ConstrAreaID] = @ConstrAreaID AND [ConstrDeviceID] = @ConstrDeviceID"> <SelectParameters> <asp:Parameter DefaultValue="" Name="ConstrAreaID" Type="Int32" ConvertEmptyStringToNull="true" /> <asp:Parameter DefaultValue="" Name="ConstrDeviceID" Type="int32" ConvertEmptyStringToNull="true" /> </SelectParameters> As far as I understand default select command should be "SELECT IncidentText FROM IncidentTemplates WHERE ConstrAreaID IS NULL AND ConstrDeviceID IS NULL". Unfortunately this is not the case. I assume that the "[ConstrAreaID] = @ConstrAreaID " is translated into "ConstrAreaID = NULL". Could this be the reason and how can I solve this problem? Thanks in advance
Hi, it is few days I posted here my question, but received no answer. Maybe the problem is just my problem, maybe I put my question some strange way. OK, I try to put it again, more simply. I have few textboxes, their values I need to transport to database. I set SqlDataSource, parameters... and used SqlDataSource.Insert() method. I got NULL values in the database's record. So I tried find problem by using Microsoft's sample code from address http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert.aspx. After some changes I tried that code and everything went well, data were put into database. Next step was to separate code beside and structure of page to two separate files followed by new test. Good again, data were delivered to database. Next step: to use MasterPage, very simple, just with one ContentPlaceHolder. After this step the program stoped to deliver data to database and delivers only NULLs to new record. It is exactly the same problem which I have found in my application. The functionless code is here:http://forums.asp.net/thread/1437716.aspx I cannot find any answer this problem on forums worldwide. I cannot believe it is only my problem. I compared html code of two generated pages - with maserPage and without. There are differentions in code in ids' of input fields generated by NET.Framework:Without masterpage:<input name="NazevBox" type="text" id="NazevBox" /><span id="RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span><p><input name="CodeBox" type="text" id="CodeBox" /><span id="RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span><p><input type="submit" name="Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("Button1", "", true, "", "", false, false))" id="Button1" /> With masterpage:<input name="ctl00$Obsah$NazevBox" type="text" id="ctl00_Obsah_NazevBox" /><span id="ctl00_Obsah_RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span><p><input name="ctl00$Obsah$CodeBox" type="text" id="ctl00_Obsah_CodeBox" /><span id="ctl00_Obsah_RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span><p><input type="submit" name="ctl00$Obsah$Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$Obsah$Button1", "", true, "", "", false, false))" id="ctl00_Obsah_Button1" />In second case ids' of input fields have different names, but I hope it is inner business of NET.Framework.There must be something I haven't noticed, maybe NET's bug, maybe my own. Thanks for any suggestion.
I have parameters in my report. The user can choose the year, month and date (3 parameters). Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension . Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007". But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All". Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
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
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
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.
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.
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
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.
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
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.
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>
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.
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.
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...
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
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.
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
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?
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
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
