I'm having serious issues trying to refresh a schema in a SQLDatasource. It is hooked to a stored procedure that takes two varchar(39) parameters. The default parameters in this case are '%'. Note I am working in the designer.
If I set it up as a stored proc, I can't even get the 'test query' to run in the builder.
If I set it up as a select statement, ala 'exec <procname> @p1, @p2', the 'test query' will run.
In neither case will hitting refresh schema work. It returns 'Invalid length parameter passed to the substring function'
The stored proc is nothing special, simply returning a select based upon the parameters.
I am having issues getting my report parameters to refresh when changing their values. I am running SP1 and am trying to mimic the functionality of the Select All for the multi-valued drop-down list. I have a boolean field that I pass to the query that populates the multi-valued list and set a field value to either what is displayed in the list or a -1 (a non-valid option). I then set the default value of the multi-valued list to the value of the new field I created in the query based on if they want to select all values or not.
This works great on the first choice, if I select no, nothing is checked in the list and if I select yes then everything is selected. However, if I select Yes and select No, everything remains checked. It acts like it does a refresh, but does not.
Is it possible to specify different parameters for the select/update/delete stored procedures used by a sqldatasource? When I 'configure the datasource' it lists the different stored procedures for each command, but when it comes to specifying the parameters it only lets me do so for the select command. Is there another way to do this for the other commands?
I have tried doing it manually in the aspx file (using the DeleteParemeters etc), but I don't know how to reference a specific cell in the selected row of the gridview for the controlparameter). Any thoughts?
Hello I need help withsetting parameters for SqlDataSource I have a simple program. I want display date from database on MS SQLSERVER from the table USERS only for current sing on user select by his login. I save into this variable login current user: string @LOGIN = Context.User.Identity.Name; I have already done with this way without SqlDataSource: string login = Context.User.Identity.Name; SqlConnection conn1 = new SqlConnection("server=CR\SQLEXPRESS;database=myData;integrated security=SSPI"); conn1.Open(); SqlCommand cmd1 = new SqlCommand(" SELECT IN_OUT.TIME_START, IN_OUT.TIME_END, FROM IN_OUT INNER JOIN USER ON USER.USER_ID=IN_OUT.RC_USER_ID where USER.LOGIN=@LOGIN", conn1); cmd1.Parameters.Add("@LOGIN", SqlDbType.NVarChar, 50); cmd1.Parameters["@LOGIN"].Value = login; 1.Parameters.Add("@LOGIN", SqlDbType.NVarChar, 50); cmd1.Parameters["@LOGIN"].Value = login; Now I don't know how to do with SqlDataSource, what I have to set in SqlDataSource1 yet <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" ProviderName="<%$ ConnectionStrings:myConnectionString.ProviderName %>" SelectCommand="SELECT IN_OUT.TIME_START, IN_OUT.TIME_END, FROM IN_OUT INNER JOIN USER ON USER.USER_ID=IN_OUT.RC_USER_ID where USER.LOGIN=@LOGIN"> </asp:SqlDataSource>
I set up a sqldatasource based on a stored procedure which takes one parameter. The sqldatasrouce wizard generates the following code for the parameter below. The question is how do I value the DeptID parameter on the load of the form. I tried the following code in the load of the page, but get a null reference error:Me.SqlDataSource1.InsertParameters("DeptID").DefaultValue = Session("DeptID") <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:FDConn %>" SelectCommand="GetTruckStatus" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="DeptID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <radG:RadGrid ID="RadGrid1" runat="server"> </radG:RadGrid>
I need to do some select & update from VB code behind a web page. Using VS2005, ASP.NET. To me, the most logical approach would be to use a SqlDataSource. I can select the connection string, predefine select, insert and update queries and call the select(), and other commands from this control. I need to use parameters in the queries but I cannot connect parameters straight to controls, I need to do it from VB. But nowhere can I find how to set the parameter values from code. I can find all kind of examples from VS2003 or using SqlCommand, but it should be possible from this control as well, but the help documentation is very poor in this respect. Please provide me with some example. Kind regards
Hi,How do I set the parameters of an SqlDataSource programatically?I have tried the following...dsDraftBudgetPI.SelectParameters.Add(new Parameter("@person_id", TypeCode.Int32, pID.ToString()));But that didn't work. I already have the parameter defined at design time so I don't need to create one I just want to set its value and then bind it to a ListBox to display the result.Thanks,Scott
Hello! I am pulling my hair out over this latest problem. I'm trying to do a simple SQL SELECT statement by taking values from textboxes and inserting them into the WHERE part of the statement. I'm using MySQL and have to use ? prefix for all inserts, that I have working by the way. Now for some reason I cannot get the values from the text boxes to replace the prefixes! Here is my code:
I have omitted obvious code. The test label, when updated, shows the SQL statement but with the ?. I also use controlparameters that took the values directly from the textboxes but again this did not work. I'm really stuck with this one. Your help is much needed!
I'm try to use a SqlDataSource control for binding data (stored procedure) to a GridView control. But I only get the error message below. What am I doing wrong? ----------- Error message ----------------------Procedure or function 'StatisticCustomers' expects parameter '@From', which was not supplied. ------------- Customer.aspx -------------------- From <asp:TextBox id="txtDateFrom" CssClass="datebox" runat="server"></asp:TextBox> To <asp:TextBox id="txtDateTo" CssClass="datebox" runat="server"></asp:TextBox> Min: <asp:TextBox id="txtMinimumOrders" CssClass="" runat="server"></asp:TextBox> <asp:GridView ID="grdvwCustomers" AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="UserId" HeaderText="UserId" SortExpression="UserId" /> <asp:BoundField DataField="Column1" HeaderText="Column1" ReadOnly="True" SortExpression="Column1" /> <asp:BoundField DataField="Items" HeaderText="Items" ReadOnly="True" SortExpression="Items" /> <asp:BoundField DataField="Column2" HeaderText="Column2" ReadOnly="True" SortExpression="Column2" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>" SelectCommand="StatisticCustomers" SelectCommandType="StoredProcedure" runat="server"> <SelectParameters> <asp:ControlParameter ControlID="txtDateFrom" Name="dtFrom" PropertyName="Text" Type="DateTime" /> <asp:ControlParameter ControlID="txtDateTo" Name="dtTo" PropertyName="Text" Type="DateTime" /> <asp:ControlParameter ControlID="txtMinimumOrders" Name="iMinimumOrders" PropertyName="Text" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> ALTER PROCEDURE [dbo].[StatisticCustomers] (@From datetime, @To datetime, @MinimumOrders int )AS SELECT Orders.UserId, 'name', COUNT(*) AS Items, SUM(OrderDetail.Subtotal) FROM Orders INNER JOIN OrderDetail ON Orders.OrderId = OrderDetail.OrderId WHERE DateCreated > @From AND DateCreated < @To GROUP BY Orders.UserId ORDER BY Items DESC Regards, Sigurd
I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find is http://forums.asp.net/thread/1233258.aspx but no answer was posted. What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0]) Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user? Thank you very much for your help.
I'm passing a parameter to a stored procedure stored on my sqlserver, or trying to atleast. And then firing off the update command that contains that parameter from a button. But it's not changing my data on my server when I do so. I'm filling a dropdown list from a stored procedure and I have a little loop run another sp that grabs what the selected value should be in the dropdown list when the page loads/refreshes. All this works fine, just not sp that should update my data when I hit the submit button. It's supposed to update one of my tables to whatever the selected value is from my drop down list. But it doesn't even change anything. It just refreshes the page and goes back to the original value for my drop down list. Just to make sure that it's my update command that's failing, I've even changed the back end data manually to a different value and on page load it shows the proper selected item that I changed the data to, etc. It just won't change the data from the page when I try to.
This is what the stored procedure looks like: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ALTER PROCEDURE [dbo].[UPDATE_sp] (@SelectedID int) AS BEGIN UPDATE [Current_tbl] SET ID = @SelectedID WHERE PrimID = '1' END ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ And here's my aspx page: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Editor.aspx.vb" Inherits="Editor" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Data Verification Editor</title> </head> <body> <form id="form1" runat="server"> <asp:SqlDataSource ID="SQLDS_Fill" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>" SelectCommand="Current_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataSet"> </asp:SqlDataSource> <asp:SqlDataSource ID="SQLDS_Update" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>" SelectCommand="Validation_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataReader" UpdateCommand="UPDATE_sp" UpdateCommandType="StoredProcedure"> <UpdateParameters> <asp:ControlParameter Name="SelectedID" ControlID="Ver_ddl" PropertyName="SelectedValue" Type="Int16" /> </UpdateParameters> </asp:SqlDataSource> <table style="width:320px; background-color:menu; border-right:menu thin ridge; border-top:menu thin ridge; border-left:menu thin ridge; border-bottom:menu thin ridge; left:3px; position:absolute; top:3px;"> <tr> <td colspan="2" style="font-family:Tahoma; font-size:10pt;"> Please select one of the following:<br /> </td> </tr> <tr> <td colspan="2"> <asp:DropDownList ID="Ver_ddl" runat="server" DataSourceID="SQLDS_Update" DataTextField="Title" DataValueField="ID" style="width: 100%; height: 24px; background: gold"> </asp:DropDownList> </td> </tr> <tr> <td style="width:50%;"> <asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True" Font-Size="8pt" Width="100%" /> </td> <td style="width:50%;"> <asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True" Font-Size="8pt" Width="100%" /> </td> </tr> <tr> <td colspan="2"> <asp:Label runat="server" ID="Saved_lbl" style="font-family:Tahoma; font-size:10pt;"></asp:Label> </td> </tr> </table> </form> </body> </html> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ And here's my code behind: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Imports System.Data Imports System.Data.SqlClient Partial Class Editor Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load Saved_lbl.Text = "" Done_btn.Attributes.Add("OnClick", "window.location.href='Rpt.htm';return false;") Dim View1 As New DataView Dim args As New DataSourceSelectArguments View1 = SQLDS_Fill.Select(args) Dim Row As DataRow For Each Row In View1.Table.Rows Ver_ddl.SelectedValue = Row("ID") Next Row SQLDS_Fill.Dispose() End Sub Protected Sub Submit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit_btn.Click SQLDS_Update.Update() Saved_lbl.Text = "Thank you. Your changes have been saved." SQLDS_Update.Dispose() End Sub End Class ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If I have a lot of dropdowns of similar type but only differ in one field, is there a way to set parameters so that the dropdownlist can specify what the parameter values are? For example dropdowns: expenseaccount, incomeaccount, assetaccount SqlDataSource: SelectCommand="select accountid,accountname from accounts, accounttypest where accounts.accounttypeid=acounttypes.accounttypeid and accounttypes.accounttypename=? order by accountname" <SelectParameters> <asp:Parameter Name="accountTypeName" Type="string" /> </SelectParameters> ------- I would like expenseaccount to be able to specify "EXPENSE", incomeaccount to specify "INCOME", etc... Or is sqldatasource reusage bad practice? (damn vs2k5 colors)
I have a sqldatasource in a wizard. When I click the finishbutton in the wizard I want to insert some values (from textboxes, dropdownlists in my wizard) into a mysql-database. For this I use parameters, I add some values in the design mode and some in codebehind. The parameters in codebehind are added perfectly but the other parameters in designmode (name, email) just inserts null. Why? My datasource: <asp:SqlDataSource ID="dsInsert" InsertCommand="INSERT INTO tbltest (name, email, city) VALUES (?name, ?email, ?city)" runat="server" ConnectionString="<%$ ConnectionStrings:conn %>" ProviderName="MySql.Data.MySqlClient"> <InsertParameters> <asp:FormParameter Name="?name" ConvertEmptyStringToNull="true" Type="string" FormField="name" /> <asp:FormParameter Name="?email" ConvertEmptyStringToNull="true" Type="string" FormField="email" /> </InsertParameters> </asp:SqlDataSource> The codebehind: protected void finishButtonClick(object sender, EventArgs e) { dsInsert.InsertParameters.Add("?city", TypeCode.Int32, city.SelectedValue); dsInsert.Insert(); }
Hi All, Maybe because it's Friday afternoon and I can't think clearly anymore... A really (I guess) simple problem: DataView with SqlDataSource <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"> <ItemTemplate> <asp:Label ID="id" runat="Server" Text='<%# Eval("ID")%>' /> </ItemTemplate> </asp:DataList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" SelectCommand="GetItem" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> Now, what I have to do is to pass two parameters to the stored procedure: 1) ProviderUserKey2) Int ValueBut the question becomes "how"?Even if I define: <SelectParameters> <asp:Parameter Name="I_GUID" /> <asp:Parameter Name="I_TYPE" Type="Int32" DefaultValue="1" /> </SelectParameters>I need to set the parameters from code behind.... Thanks for any suggestions Adam
how do i pass the paramenters and storedprocedure to dataview from code-behind? the below code have sqldatasource control but i want to pass through code-behind everything...
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." DataKeyNames="NewsId"> <Columns> <asp:BoundField DataField="NewsId" HeaderText="NewsId" InsertVisible="False" ReadOnly="True" SortExpression="NewsId" /> <asp:BoundField DataField="PostDate" HeaderText="PostDate" SortExpression="PostDate" /> <asp:BoundField DataField="PostedBy" HeaderText="PostedBy" SortExpression="PostedBy" /> <asp:BoundField DataField="PostedByName" HeaderText="PostedByName" SortExpression="PostedByName" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" /> <asp:BoundField DataField="Body" HeaderText="Body" SortExpression="Body" /> <asp:BoundField DataField="LastUpdated" HeaderText="LastUpdated" SortExpression="LastUpdated" /> <asp:CheckBoxField DataField="IsVisible" HeaderText="IsVisible" SortExpression="IsVisible" /> </Columns> </asp:GridView> i have storedprocdure which accepts 4 parameters 3 paramenters which user will supply and 1 parameter will be supplied from code-behindwhat i mean by that is:the 4 parameters storedprocedure accepts is:empid, start_date, end_date (user will supply those 3 parameters)internal_id - which internally pass along with other 3 parmeterssomething like this:internal_id, empid, start_date, end_dateany thoughts?thanks.
I am using a SQLDataSource with Stored Procedures. The Select, Insert and Update all work well. However I cannot get the delete to work. My stored procedures are tested and verified and the parameter names are the same as the source columns. When I try to run the delete an error that the stored procedure expects the parameter @locationStationId, however this value passes properly for the Update command?!? I tried to change the parameter to original_locationStationID to pass the original value, however this result in Null being passed for the parameter. I cannot understand why this works for Update and passes the location ID, but will not work for DELETE. Can anyone shed any light onto the matter? Thanks.OldValuesParameterFormatString="original_{0}" UpdateCommand="spUpdateLocation" UpdateCommandType="StoredProcedure" DeleteCommand="spDeleteLocation" DeleteCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="locationStationId" Type="String" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="locationStationId" Type="String" /> <asp:Parameter Name="locationType" Type="String" /> <asp:Parameter Name="locationName" /> <asp:Parameter Name="division" Type="String" /> </InsertParameters>
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
CancelSelectOnNullParameterHi,Suppose I have a SQLDataSource with a SelectCommand="SELECT * FROM STOCKS WHERE USER=@USER ORDER BY STOCK_NAME" and @USER is set as a session parameter. No problem till this part, but in some postbacks, I wish to add a new WHERE criteria to my SQL, so I do:DSSQL.SelectParameters.Add("@STOCK_NAME", "ABC123"); DSSQL.SelectCommand = DSgvStoklar.SelectCommand.Replace(" ORDER BY ", " AND STOCK_NAME LIKE @STOCK_NAME+'%' ORDER BY "); gridViewDS.DataBind(); and wish that my SelectCommand is modified and a new parameter is added. The SQL is modified, however -I guess- new parameter is not added, so I get "Must declare the scalar variable "@STOCK_NAME" error. How can I solve, any ideas?
I have a sqlDatasource with 3 parameters based on the input of 3 text boxes on the page. The datasource returns sales details for a company based on a from and to date. I am attempting to set the value of the 3 parameters in the Selecting event of the datasource control but I'm not getting any data back. If I set the values literally then I get data back. Also when I step through the code I can see the 3 parameters getting their values from the textboxes and the drop down list as they should. This is driving me insane as I'm new to .net and just can't see what is stopping me retrieving the data when using the form fields to set the datasources parameters. Below is the aspx and the code behind for the page. Thanks in advance for any help. aspx..... <%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="LabelSales.aspx.cs" Inherits="LabelSales" Title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSourceSales" runat="server" EnableCaching="false" ConnectionString="<%$ ConnectionStrings:streetwisedigitalConnectionString %>" SelectCommand="DL_GET_SALES_BY_LABEL" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSourceSales_Selecting" > <SelectParameters> <asp:Parameter Name="fromDate" /> <asp:Parameter Name="toDate" /> <asp:Parameter Name="label" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSourceLabels" runat="server" SelectCommand="select label_id, label_name from dl_label order by label_name asc" ConnectionString="<%$ConnectionStrings:streetwisedigitalConnectionString%>"> </asp:SqlDataSource> <div> <table> <tr> <td>Start Date: </td> <td> <asp:TextBox ID="FromDate" Runat="server" Width="70"/> <asp:Button ID="btnFrom" Runat="server" Text="..." UseSubmitBehavior="false" /> </td> </tr> <tr> <td>End Date: </td> <td> <asp:TextBox id="ToDate" Runat="server" Width="70" /> <asp:Button ID="btnTo" Runat="server" Text="..." UseSubmitBehavior="false" /> </td> </tr> <tr> <td>Label: </td> <td> <asp:DropDownList ID="LabelList" Runat="server" DataSourceID="SqlDataSourceLabels" DataTextField="label_name" DataValueField="label_name"> </asp:DropDownList> </td> </tr> <tr> <td colspan="2" align="right"> <asp:Button ID="Button1" OnClick="SubmitButton_OnClick" Text="View Sales" runat="server" /> </td> </tr> </table> <p> <asp:GridView ID="GridViewSales" runat="server" DataSourceID="SqlDataSourceSales" ShowFooter="True" AllowSorting="True" AutoGenerateColumns="False" OnRowDataBound="GridViewSales_RowDataBound" EmptyDataText="No data to display."> <Columns> <asp:BoundField DataField="cat_no" HeaderText="Cat No" /> <asp:BoundField DataField="artist" HeaderText="Artist" /> <asp:BoundField DataField="title" HeaderText="Title" /> <asp:BoundField DataField="remix" HeaderText="Remix" /> <asp:BoundField DataField="qty" HeaderText="Sold" /> <asp:boundfield datafield="commission" HtmlEncode="False" dataformatstring="{0:F2}" headertext="Commission"> <ItemStyle HorizontalAlign="Right" /> </asp:boundfield> </Columns> </asp:GridView> </p> </div></asp:Content>code behind...using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class LabelSales : System.Web.UI.Page{ int totalSold; decimal totalCommssion; protected void GridViewSales_RowDataBound(object sender, GridViewRowEventArgs e) { // add column totals to gridview if (e.Row.RowType == DataControlRowType.DataRow) { totalSold += (int)DataBinder.Eval(e.Row.DataItem, "qty"); totalCommssion += (decimal)DataBinder.Eval(e.Row.DataItem, "commission"); } // display the totals else if (e.Row.RowType == DataControlRowType.Footer) { e.Row.Cells[0].Text = "<b>Total</b>"; e.Row.Cells[4].Text = totalSold.ToString(); e.Row.Cells[5].Text = totalCommssion.ToString("f2"); } } protected void Page_Load(object sender, EventArgs e) { } protected void SqlDataSourceSales_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { // *** This method does not work *** e.Command.Parameters[0].Value = FromDate.Text; e.Command.Parameters[1].Value = ToDate.Text; e.Command.Parameters[2].Value = LabelList.Text; // *** This method works! *** e.Command.Parameters[0].Value = "02/01/2007"; e.Command.Parameters[1].Value = "02/01/2008"; e.Command.Parameters[2].Value = "Fat!"; } protected void SubmitButton_OnClick(object sender, EventArgs e) { SqlDataSourceSales.Select(DataSourceSelectArguments.Empty); }} Many thanks Simon
UpdateCommand="UPDATE PsnQualifications SET quantity=@quantity WHERE rowstatus=1 and qualId=@original_qualId"
DeleteCommand="UPDATE PsnQualifications SET rowStatus=0, lastUpdateOn=getdate(), lastUpdateBy=@createdBy WHERE rowstatus=1 and qualId=@original_qualId"
hi, i am using a sqldatasource with a formview that has three stored procedures associated with it. when i update a record in my formview, i need to increase two parameters that are displayed as labels in the formview by their value plus one. i can't figure out how to increase the value in the stored procedure, or using the codebehind, or with some script in the html. any solution works for me. any suggestions? if i can figure out how to increment one of my fields, i can use the same logic for the other. below is an example of what i am trying to do. in my formview i have three fields: Count, Rate, Price. Count is a label, Rate and Price are editable textboxes. once i commit the update for the values of Rate and Price, i need the value of Count to increase by 1, and then have that value updated in the data base along with the new values for Rate and Price. is there a way to do this in the codebehind, or in my stored procedure? i am using Asp.Net 2.0, vs2005, and sql server. any help is greatly appreciated. thank you.
Hi, I'm having an issue trying to send parameters to a SQLDatasource object. I"m creating it in code (and I need it to stay there) I have determined that the stored procs called with no parameters work fine. I start having problems when I have any parameters. Interestingly, it doesn't crash. It just fails to run (the onselected event never fires). dsUser is a protected SQLDatasource property repMain is a repeater on the aspx sheet lstUser is a stored proc with one varchar(10) parameter called cSort Here is my current code that will result in no called to StatementCompleted and no data. Pull cSort parameter from the proc and it works just fine. Obviously I'm setting it wrong. string cSelect = "dbo.lstUser"; dsUser = new SqlDataSource(); dsUser .ConnectionString = GetConStr(); dsUser .SelectCommand = cSelect;
Whn I use a UniqueIdentifier field as a parameter for SQLDataSource the parameter type defaults as an object. I just want to use the string value of the field.How do I do this?Thanks.
I am using SqlDataSource programmatically in my data access layer - mainly for convenience but it does generally work fine with no obvious performance issues. The problem I have is with getting back an output parameter. I have an insert-type stored procedure (in Sql Server 2005) operating on a table with an identity column as the primary key: ALTER PROCEDURE [dbo].[InsertAlbum](@ArtistID int, @Title nvarchar(70), @NewID int OUTPUT)ASDECLARE @err intINSERT INTO dbo.ALBUMS (ARTISTID, TITLE)VALUES (@ArtistID, @Title)SELECT @err = @@error IF @err <> 0 RETURN @errSET @NewID = SCOPE_IDENTITY() This works fine when run from Sql Server Management Studio and @NewID has the correct value. My data access code is roughly as follows: dsrc = New SqlDataSource()dsrc.ConnectionString = ConnectionStringdsrc.InsertCommand = "InsertAlbum"dsrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedureDim parms As ParameterCollection = dsrc.InsertParametersDim newid As IntegerAddParameter(parms, "ArtistID", TypeCode.Int32, ParameterDirection.Input, 0, album.ArtistID)AddParameter(parms, "Title", TypeCode.String, ParameterDirection.Input, 0, album.Title)Dim p As New Parameter("NewID", TypeCode.Int32)p.Direction = ParameterDirection.Outputparms.Add(p)Try Dim rv As Integer = dsrc.Insert() newid = parms("NewID") Return newidCatch ex As Exception Return -1End Try The row is inserted into the database, but however I try to define and add the NewID parameter it never has a value. Has anyone tried to do this and can tell me what I am doing wrong? Jon
Hi, I'm developing a website using vwd express and I have created a GridView that bounds data from a stored procedure. The stored procedure takes one parameter. I tested it by using a default value and it works fine. Now, instead of the default value i want to pass the current logged in user name as a parameter. How do i do this. All the info i found around are for passing parameters to the select command of sqldatasource but i cant get it to work when i use a stored procedure. Thanks, M.
Everyone,I have an application that pulls a list of employees into a web form. I use the same stored procedure to search for employees by various demographic characteristics, and have set all of these search parameters to be optional in the stored procedure.Everything works fine when I pull the entire list (without declaring any select parameters). However, on my Search page, I have declared a number of SelectParameter (ControlParameters, to be specific) objects and mapped each one to its respective control (text boxes and a checkbox). When I attempt a search, the SqlDataSource does not select any records unless I specify an argument for ALL of the search parameters. If I omit even a single search value, the page just posts back and does not pull any information from the database. I have verified with SQL Profiler that it is not attempting to hit the database.I have experimented with various declarations in the ControlParameter object, including the DefaultValue and ConvertEmptyStringToNull items. If I specify a non-empty string as a DefaultValue for all of the parameters, it will perform the query, but it does not query if I use an empty string as a default value - unless my user specifies a value in the form. Any ideas?ThanksTim
I'm having trouble using session data in my select parameters. If I manually plug a value into the selectcommand or create a hard coded value using a parameter it works, but if I try to use the session data the query pulls no results. I know the session data is set because I print the value at the top of the page, but for some reason it's not getting passed to the sessionparameter??? This is the datasource code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:flautoconnstr %>" SelectCommand="SELECT * FROM [tblVehicles] WHERE ([profileid] = @profileid)"> <SelectParameters> <asp:SessionParameter Name="profileid" SessionField="profileid" Type="String" /> </SelectParameters> </asp:SqlDataSource>
This is the line I use to set the session data. Session["profileid"] = myQuote.Profileid;
I'm new (very new) to asp.net and now at 3am after maybe 12 straight hours of trying to go through examples and understand the syntax, I have a somewhat working program... basically a query parameters screen that upon a click generates a report. The query parameter screen basically is a HTML form with a procedure that puts the field values into session variables. The report uses <asp:sessionparameters> in the sqldatasource to apply aforementioned session variables to filter the SELECT statement's results. They are then displayed using the gridview control. It actually works! Now I'm trying to figure out: if I need/want my users to be able to enter comma-separated list of values into the HTML form field and somehow get these into the SELECT statement's WHERE clause, is there a way to do this? I'm not very experienced with asp nor sql server and have been struggling with this new challenge. I really could use pointers as to whether there's an easy way to handle this. In other languages in the past I would have had to parse the comma-separated list and use those to construct my SELECT statement programmatically. I get the impression that with all this ASP.NET fanciness, there has to be a better way? If it matters, I'm using ASP.NET 2.0 with SQL Server 2005 Express and Visual Studio Web 2005 Express. Thanks in advance for any replies, they would be GREATLY appreciated :)
