Using SqlDatASource Programmatically - Output Parameters
May 13, 2006
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)
AS
DECLARE @err int
INSERT INTO dbo.ALBUMS (ARTISTID, TITLE)
VALUES (@ArtistID, @Title)
SELECT @err = @@error IF @err <> 0 RETURN @err
SET @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 = ConnectionString
dsrc.InsertCommand = "InsertAlbum"
dsrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
Dim parms As ParameterCollection = dsrc.InsertParameters
Dim newid As Integer
AddParameter(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.Output
parms.Add(p)
Try
Dim rv As Integer = dsrc.Insert()
newid = parms("NewID")
Return newid
Catch ex As Exception
Return -1
End 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?
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 have an event: Private Sub SqlDataSourceIncome_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourceIncome.Deleted Dim command As SqlClient.SqlCommand command = e.Command If command.Parameters("@nReturnCode").Value <> 0 Then DROPDEAD() End If That fires from: <DeleteParameters> <asp:Parameter Name="nDeletebyId" Type="Int64" /> <asp:Parameter Name="nOtherId" Type="Int64" /> <asp:Parameter Direction="Output" Name="nReturnCode" Type="Int64" /> <asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" /> </DeleteParameters> End Sub
When I: GridViewIncome.DeleteRow(GridViewIncome.SelectedRow.RowIndex) But nReturnCode is ALWAYS NULL... I even did a stored procedure that just: ALTER PROCEDURE [dbo].[sp_nDeletebyId] @nReturnCode bigint output, @nReturnId bigint output AS SET @nReturnCode = 0 SET @nReturnId = 0 And STILL got nothing but the NULLS... the insert & update stuff works fine, with identical code... it's just the DELETED event that I can't seem to knock. Has anyone seen this before? The above sample stored proc did return 0 when executed one the server... and, BTW, the row is deleted!
Hello. Im trying to create an SQLDataSource control programmatically. I need to do this because I want to do some stuff on my MasterPage's 'Page_Init' event. heres my code (Master.master.vb): Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init lblUser.Text = Page.User.Identity.Name
Dim PUser As New ControlParameter PUser.ControlID = "lblUser" PUser.Name = "LoginName" PUser.PropertyName = "Text" PUser.Type = TypeCode.String PUser.DefaultValue = Page.User.Identity.Name
Dim SQLDS_Login As New SqlDataSource SQLDS_Login.ID = "SQLDS_Login" SQLDS_Login.ConnectionString = "I put conection string here. How do I use the one on my web.config?" SQLDS_Login.SelectCommand = "SELECT [LoginID], [LoginName], [Role], [Status] FROM [myLogin] WHERE ([LoginName] = @LoginName)" SQLDS_Login.SelectParameters.Add(PUser) SQLDS_Login.SelectCommandType = SqlDataSourceCommandType.Text
When i run, i get this error message: The SqlDataSource control 'SQLDS_Login' does not have a naming container. Ensure that the control is added to the page before calling DataBind. I never had any problem with Inserts, Updates and Deleting, but I have never made it work for Select when doing it programmatically. Can you help me with this?
I am trying to add a number of dates into a Sql database. Basically I want the user to add the start and end date and then all the dates in between are are added to a database in unique records. I can create an ArrayList but I don't know how to bind it to an SqlDataSource Dim startdate As DateTime = tbstartdate.Text Dim enddate As DateTime = tbenddate.Text Dim datediff As TimeSpan = enddate.Subtract(startdate) Dim noofdays As Integer = datediff.Days Dim ar As New ArrayList Dim i For i = 0 To noofdays ar.Add(startdate.AddDays(i)) Next Sorry if this is a total noob question....
Background - I have a page that uses a numeric value stored in a Session object variable as the parameter for three different SQLDataSource objects, which provide data to two asp:Repeaters and an asp:DataList. Also, in the Page_Load, I use this value to to seed a stored procedure and an SQLDataReader to populate several unbound Labels. This works fine. In addition, I have a collection of 6 TextBoxes, an unbound Listbox, and two Buttons to allow the user to do searching and selection of potential matches. This basically identifies a new numeric value that I store in the Session variable and PostBack the page (via one of the buttons). This also works fine.Problem - I have been tasked with taking a different page and adding six textboxes to collect the search values, but to post over to this page, populate the existing search-oriented TextBoxes, adn programmatically triggering the search. Furthermore, I have to detect the number of matching records and, if only 1, have the Repeaters and DataList display the results based on the newly selected record's key numeric value, as well as populating the unbound Labels. I have managed to get all of this accomplished except for programmatically triggering the Repeaters and DataList "refresh". These controls only populate as expected if a button is clicked a subsequent time, which makes sense, since that would trigger a PostBack and the Page_Load uses the new saved numeric key value from the Session.My history in app development is largely from Windows Forms development (VB6), this is my second foray into Web Form dev with ASP.NET 2.0. I am willing to acceptthat what I am trying to do does not fit into the ASP environment, but I have to think that this is something that has been done before, and (hopefully) there is a way to do what I need. Any ideas, oh great and wise Forum readers? *smile*
ello all Would someone be so kind as to save me from getting balder through pulling my hair out. My aim is to extract data from a database using SQLDataSource, then edit the data and update the database using the SQLDataSource. I have achieve the problem of retrieving the data from the sqlDataSource:DataView openRemindingSeats = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty); //Int32 openRemindingSeats = SqlDataSource2.Select(DataSourceSelectArguments.Empty), DataView; foreach (DataRowView rowProduct in openReminding) { //Output the name and price lbl_NumOfSeatsLeft.Text = rowProduct["Remaining"].ToString(); } Within the sqlDataSource the sql code is as follows:SELECT [refNumber], [refRemaining] FROM [refFlights] WHERE ([refNumber] = @Number) So at the moment my problems is being able to edit and update data to the same SELECTed data.Thank you for any help that you might have... SynDrome
Hi, All I'm using Gridview and SqlDataSource to dynamically display the contents in different tables, as followed: <% dataSource.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"]; dataSource.SelectCommand = "SELECT * FROM " + tableName; gridView.DataBind(); dataSource.UpdateCommand = "";%> <asp:SqlDataSource ID="dataSource" runat="server"></asp:SqlDataSource><asp:GridView ID="gridView" runat="server" DataSourceID="dataSource" AllowPaging="True" AllowSorting="True" AutoGenerateEditButton="True" BorderColor="Silver" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" PageSize="20" DataKeyNames="ID" OnRowDataBound="tableGridView_RowDataBound"> <HeaderStyle BackColor="#C0C0FF" /> <AlternatingRowStyle BackColor="#C0FFC0" /></asp:GridView> The datasource take the "tableName" as argument to determine which table to display. My problem is I can't figure out a way to programmatically construct the UpdataCommand for the SqlDataSource. I try to get the field names from the HeaderRow, but all the cells are empty in this row. Does anyone know what causes the problem or how to construct the UpdateCommand properly. Thanks!
I need to provide defaults and sometimes overrides for items in SQLDataSource's UpdateParameters. I am attempting to do this in a FormView's ItemUpdating and ItemInserting events as follows: //======================================================================== // FormView1_ItemUpdating: //======================================================================== protected void FormView1_ItemUpdating(object sender, FormViewUpdateEventArgs e) { // not sure if this is the bets place to put this or not? dsDataSource.UpdateParameters["UpdatedTS"].DefaultValue = DateTime.Now.ToString(); dsDataSource.UpdateParameters["UpdatedUserID"].DefaultValue = ((csi.UserInfo)Session["UserInfo"]).QuotaUserID; } In the example above I am attempting to set new values for the parameters which will replace the existing values. I have found that using the DefaultValue property works ONLY if there is no current value for the parameter. Otherwise the values I specify are ingnored.The parameters of an ObjectDataSource provide a Value property but SQLDataSource parameters do not.How can I provide an override value without needing to place the value in the visible bound form element???If you can answer this you will be the FIRST person ever to answer one of my questions here!!!Thanks,Tony
I just want to insert a record into a table using a SqlDataSource control. But I'm having a hard time finding examples that don't use data bound controlsI have this so far (I deleted the parts not related to the insert):<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:UserPolls %>" InsertCommand="INSERT INTO [PollAnswers] ([PollId], [AnswerText], [AnswerCount]) VALUES (@PollId, @AnswerText, @AnswerCount)" <InsertParameters> <asp:Parameter Name="PollId" Type="Int32" /> <asp:Parameter Name="AnswerText" Type="String" /> <asp:Parameter Name="AnswerCount" Type="Int32" /> </InsertParameters> </asp:SqlDataSource> This is the data source for a gridview control I have on the page. I could set up an SqlDataSource for this alone if I need to, but i don't know if it would help. From what I could find, in the code behind I should haveSqlDataSource2.Insert()and SqlDataSource2 will grab the parameters and insert the record. The problem is I need to set the Pollid (from a session variable) and AnswerText (from a text box) at run time. Can I do this?Diane
Hey All for some reason I can not get this right and/or find what I am looking for. I have an SQLDataSource with a PartID set as the filtered value in the Datasource Query. I am trying to use code beside to set the value and I am failing...lol... Here is my attempt at it, SqlDataSource1.SelectParameters("PartID") = txtPartID.Text Any help would be great!
Hi I think I've programmatically created a SqlDataSource - which is what I want to do; but I can't seem to access details from the source - row 1, column 1, for example???? If Not Page.IsPostBack Then 'Start by determining the connection string valueDim connString As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 'Create a SqlConnection instanceUsing connString 'Specify the SQL query Const sql As String = "SELECT eventID FROM viewEvents WHERE eventID=17" 'Create a SqlCommand instanceDim myCommand As New Data.SqlClient.SqlCommand(sql, connString) 'Get back a DataSetDim myDataSet As New Data.DataSet 'Create a SqlDataAdapter instanceDim myAdapter As New Data.SqlClient.SqlDataAdapter(myCommand) myAdapter.Fill(myDataSet)Label1.Text = myAdapter.Tables.Rows(0).Item("eventID").ToString() -?????????????? 'Close the connection connString.Close() End Using End IfThanks for any helpRichard
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
Im ripping my hair out here.I need to access the field in a datasource control of use in non presentation layer code based actions.I know the I can use a code base connection and query but I dont see why i need to make two trips the the DB when the info is already available.The datasource is attached to a details view control and the details view control is nested in a loginview controlI've tried defining but all I can get in the header name of the field but not the dataitem, the dataitem causes an error help please jim
I've found example code of accessing an SQLDataSource and even have it working in my own code - an example would be Dim datastuff As DataView = CType(srcSoftwareSelected.Select(DataSourceSelectArguments.Empty), DataView) Dim row As DataRow = datastuff.Table.Rows(0) Dim installtype As Integer = row("InstallMethod") Dim install As String = row("Install").ToString Dim notes As String = row("Notes").ToString The above only works on a single row, of course. If I needed more, I know I can loop it.The query in srcSoftwareSelected is something like "SELECT InstallMethod, Install, Notes FROM Software"My problem lies in trying to access the data in a simliar way when I'm using a SELECT COUNT query. Dim datastuff As DataView = CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), DataView) Dim row As DataRow = datastuff.Table.Rows(0) Dim count As Integer = row("rowcnt") The query here is "SELECT COUNT(*) as rowcnt FROM Software"The variable count is 1 every time I query this, no matter what the actual count is. I know I've got to be accessing the incorrect data member in the 2nd query because a gridview tied to srcSoftwareUsage (the SQLDataSource) always displays the correct value. Where am I going wrong here?
I am sending a GUID to a form via the query string. If it exists I use helper functions to load most of the form text boxes. However, if it does not then a blank form is presented and the GUID is stored in a hidden field. Regardless, I use this hidden field to populate a grid that is attached to a sqldatasource. If I then add new datarows to the backend database programmatically, I cannot 'requery' the datasource to include those row upon a postback. I cannot seem to find a simple way to force the sqldatasource to rerun the query. Can anyone help.
Hello, I want to loop through the first 10 records that are showing in a gridview with several pages that is populated by a sqldatasource. I can loop through the sqldatasource and get the list of values, but I'm doing something wrong because the 10 records it prints out are not the same 10 records the user sees in the gridview...They can click a search button which changes the sort, and they can click on the column headings to change the sort order. Where's the best place to put the looping code? I need the result to be the same as what the users sees. 1 Protected Sub GridView1_Sorted(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.Sorted2 Dim i As Integer = -13 Dim sTest As String = ""4 Dim vwExpensiveItems As Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), Data.DataView)5 6 'Loop through each record7 i = -18 For Each rowProduct As Data.DataRowView In vwExpensiveItems9 i = i + 110 'Output the name and price11 If i > 9 Then12 Exit For13 End If14 sTest = rowProduct("employeeid")15 Response.Write("RowSorting " & i.ToString & " [" & sTest & "]<br>")16 Next17 End Sub18
Hi all, From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Hi There - is it possible to output the SQL that a SQLDataSource control is generating. I am having a difficult time diagnosing errors such as the following:
Keyword not supported: 'unicode'. Any help is appreciated.
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
I have a stored proc that I'd like to return an output param from. I'm using a SQLDataSource and invoking the Update method which calls the sproc.The proc looks like this currently: ALTER proc [dbo].[k_sp_Load_IMIS_to_POP_x]@vcOutputMsg varchar(255) OUTPUT AS SET NOCOUNT ON ; select @vcOutputMsg = 'asdf'
The code behind looks like this:protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e) { //handle error on return string returnmessage = (string)e.Command.Parameters["@vcOutputMsg"].Value; }
On the page source side, the params are defined declaratively: <UpdateParameters> <asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" /> <asp:Parameter Direction="InputOutput" Name="vcOutputMsg" Type="String" /> </UpdateParameters>
When I run it, the code behind throws the following exception - "Unable to cast object of type 'System.DBNull' to type 'System.String'" PLEASE HELP! What am I doing wrong? Is there a better way to get output from a stored proc?
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 am having a problem getting the output parameter from a stored procedure that deletes a record. I use the EXACT same process for the insert and update procs and they work fine. For some reason, the delete proc is either not returning an output parameter, or the SqlDataSource (dsCompany) is not able to get the information in the dsCompany_Deleted method. I simply want the proc to return an output value so that when I eventually get around to checking if the record can be deleted due to foreign key restraints, I can deliver the appropriate message to the user.I have highlighted the line that is giving the problem. I also highlighted a default value in the parameter, that when removed causes the app to crash because it is getting a null value back. Thanks in advance to anyone who can help!STORED PROC (eventually I will add foreign key restraints) ALTER PROCEDURE CompanyDelete @iCompanyId int, @iOutput int OUTPUT AS BEGIN DELETE FROM tblCompany WHERE iCompanyId = @iCompanyId
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)
