Deleted Event On Sqldatasource And Output Parameters... Always NULL!!
Apr 18, 2007
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
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...
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
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 am using version 9.00.2047.00 SP1 of Visual Studio 2005.
Using ADO.NET, I have been unable to get the Execute SQL task to successfully return the value of an output parameter defined as varchar or nvarchar when the value is null. No other data types seem to have this problem, including the sql_variant data type.
The variables to which the three output parameters return their values have a data type of Object. The task runs fine when the integer or datetime parameters are used, and the variables can be identified as null using IsDBNull. But as soon as the nvarchar (or varchar) parameter is included, the task fails with this message:
"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@vc"): Data type 0xE7 has an invalid data length or metadata length."
I have seen a couple of postings that sound similar to this problem, but so far I have found no resolution. Any advice would be much appreciated.
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
I have two SQLDataSource controls on my page that are dynamically fed an SQL SELECT statement. I was thinking that the best way to do this was to give it the select statement that it needs inside the OnSelecting event. Here is the revelent code.
switch ((sender as SqlDataSource).ID) { case "DS1": if (Checkbox1.Checked) { e.Command.CommandText = "SELECT * FROM table1 WHERE " + BuildQuery(getMylarColumns(), SearchBox.Text); DS1Panel.Visible = true; } break;
case "DS2": if (Checkbox2.Checked) { e.Command.CommandText = "SELECT * FROM table2 WHERE " + BuildQuery(getFlatFileColumns(), SearchBox.Text); DS2Panel.Visible = true; } break; }
The problem with this is that the GetData_Selecting method is never executed and thus when I try to execute the query the page PostBacks and nothing happens. Putting equivalent code in the Page_Load method works fine, however I believe having the code execute on each PostBack is the reason I'm having another problem sorting the data in the DataGrids these controls are bound to.
Why is the function never being executed? Is this the ideal way to handle the inclusion of the query in the SQLDataSource?
Hello, on my site I have a sqldataSource and a listview working together. But now after selecting data from my database and before binding data to the listview i want to change the data. For example: I select an image filename and i want to check if the file exists, and if it not exists i want to change this filename. How can I do that. My Idea was to use the OnSelected Event of the datasource, but i don't know how to acces the selected data ... I hope someone can help me Party-Pansen
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.
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 In the page load of my webpage I call a databind for a gridview. It generally calls this event handler : protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
However sometimes (seemingly randomly) it doesn't. Any ideas? Thanks p
Hi All - I've got a simple gridview/sqldatasource page, but the sqldatasource_onSelected event isn't firing. heres the parameters <SelectParameters> <asp:QueryStringParameter Name="LicenceID" QueryStringField="LicenceID" Type="string" /> <asp:QueryStringParameter Name="SiteID" QueryStringField="SamplingSiteID" Type="string" /> </SelectParameters>
either or both parameters may be null (ie. not in querystring ) . If only one of the selectparameters is null, and I remove it, the event fires!!! The parameters in the stored proc are optional(ie. default = NULL) and it works fine if I test it in SQL . Whats going on? If there's some error happening, why no error raised? if there are no records returned, the onselected event should still fire shouldn't it? Geoff
Using Sql server 2005, SQLdatasource, I need to display total rows count. But the selected event is not fired? Am I mssing something or doing something wrong? Please comment. thanks Code<asp:SqlDataSource ID="DataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AuditToolConnection%>" ProviderName="System.Data.SqlClient"SelectCommandType="StoredProcedure" SelectCommand="usp_Dashboard_GetAll" > -------------------------------- public in recordCount = 0;protected void DataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e) { recordCount = e.AffectedRows; lblCount.Text += recordCount.ToString(); }
Hi, I have a SqlDataSource whose select statement uses parameters from the selected row on a gridview on the page. So when a row is selected in the gridview, I want the SqlDataSource to do a select using the new parameters, and then I want to inspect the number of rows returned. If 0, I want to set a FormView to insert mode, if >0 I want to set the FormView to edit mode. The "SqlDataSource2_Selected" sub never fires, so I cannot retrieve the number of rows returned. How can I deal with this? I would like the SqlDataSource to execute a Select each time the Gridview selected row changes. What could prevent "OnSelected" from firing? I do have "OnSelected="SqlDataSource2_Selected" within the SqlDataSource tag. Thanks in advance for any help with this.
Hello I have a piece of VB.NET code that generates an email on the SqlDataSource Inserted event. It appears to be executing twice because it is sending two emails. If I place the code on any other event, it just sends the one email. Does any have a suggestion on how to handle this? Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted Dim MailServerName As String = "alvexch01"Dim Message As MailMessage = New MailMessage Message.From = New MailAddress("sender@email.com")Message.To.Add("receiver@email.com") Message.Subject = "Near Miss" Message.Body = "Test" Message.IsBodyHtml = True Message.Priority = MailPriority.NormalDim MailClient As SmtpClient = New SmtpClient MailClient.Host = MailServerName MailClient.Send(Message) Message.IsBodyHtml = True Message.Dispose() End Sub
i have formview and gridview in onepage, and formview bind to one sqldatasource, which have select commandtype is storedprocedure, now stored procedure have one optional parameter and select command. in gridview i have one button,now i want when user click on tha button that record's value comes in formview and its mode is edit. now for that i have to pass that stored procedures parameter value,to stored procedure now how can i add the parameter value when the onclick event of gridview button is fire. thanks
Hello, I have a sqldatasource and a textcontrol on a webform, i assign programmatically the text of the textcontrol to the filterexpression. If the filterexpression is incorrect the page hang, how can i handle this event Thanks JPR
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
I facing a problem when i want to modified the sqldatasource.filterExpression while trigger sqldatasource.selecting event. 1 Protected Sub SqlDsProduct_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) 2 Dim SqlDsProduct As SqlDataSource = CType(CompleteGridView1.DetailRows(0).FindControl("SqlDsProduct"), SqlDataSource) 3 SqlDsProduct.FilterExpression = Session("filter") ' filter i add up after user press search button 4 End subActually i'm using the CompleteGridview, which i downloaded from the web. the "SqlDsProduct" i reffering to is inside the CompleteGridView1. I'm using complet grid view because i want the hierarchy look for the gridview. So please help me how i gonna change the filter expression of the 2nd sqldatasource inside detailsTemplate of the completeGridview. Thank you. best regardvince
Hi All In My application when i want to work with sqldatasource for updation and delteion tasks it is working properly when all the columns consists the data. If any of the column consists the null values its not updating and deleting. Advices are needed.Thank uBaba
hi, i want to execute a finctionX() based on the returned resultset of SQLDataSource. For example, if the SELECT command returns nothing based on a specific search criteria then I want to execute functionX(). Currently, I have a string that displays "No Result" in the GridView in such case. How to catch the resultset of SQLDataSource? -nero
I am generally a C programmer and have little experience with DB programming, so I apologize right from the start.
I have a table that allows a registration item with a verification item that will be NULL when the item is created. What I wish to do is delete these if the verification item is still NULL after a specified time (say 24 to 48 hours).
I would prefer to do this with some sort of trigger in one of two ways and any suggestions are much appreciated. 1. Have a stored procedure or function (not sure which is best to utilize) that runs every 2 days which checks the table for the NULL values and deletes any older than 48 hours. 2. Create a stored procedure or function when the registration is made that will check if the verification is still NULL for that particular record 24 hours later. I would think the first would be the simplest, but am not certain.
Again, forgive my inexperience with DB programming and again I appreciate any advice given.
Not receiving any errors. The update runs perfectly on all fields, except for the added parameter during the sub. I need to change the hidden field's value after parsing out several fields in the form and generating a logfile entry of sorts. This needs to happen after all the form fields are updated, but before the update is executed. aspx: (relevant parts only...)<asp:SqlDataSource ID="TicketDetails" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"UpdateCommand="UPDATE Tickets SET TicketSuspense = @TicketSuspense, TicketPriority = @TicketPriority, TicketLastUpdated = CURRENT_TIMESTAMP, TicketStatus = 'Assigned', TicketTechnicianNotes = @TicketTechnicianNotes WHERE (TicketID = @TicketID)"><UpdateParameters><asp:Parameter Name="TicketPriority" Type="String" /><asp:Parameter Name="TicketSuspense" Type="DateTime" /><asp:Parameter Name="TicketID" Type="Int32" /></UpdateParameters></asp:SqlDataSource> codebehind: (once again, the relevant parts only)Protected Sub TicketDetails_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles TicketDetails.UpdatingTicketTechnicianNotesHiddenField.Value = "some text..." & TicketTechnicianNotesHiddenField.ValueTicketDetails.UpdateParameters.Add(New Parameter("TicketTechnicianNotes", TypeCode.String, TicketTechnicianNotesHiddenField.Value))End Sub Thanks, - Brad
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 sproce that accepts null for one of its parameters I can execute the sproce and enter null and it works fine, it returns all rows. When I try doing this with my GridView and the SQLDataSource it does not work. I need some help in understanding how the SQLDatasource wants a null. Here is what the parameter row of the SQLDataSource looks like. <asp:ControlParameter ControlID="EnteredByText" DefaultValue="Null" Name="EnteredBy" PropertyName="Text" Type="String" ConvertEmptyStringToNull="true" /> In my sproce I have setup the parameter as follows; @EnteredBy Nvarchar(50)=Null In my WHERE Clause I have: WHERE (tblClient.EnteredBy = @EnteredBy OR @EnteredBy IS NULL)
hi i am trying to get the output of the select statements of sqldatasource : protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataView dv; dv = (DataView)(this.SqlDataSourcePictures.Select(DataSourceSelectArguments.Empty)); } } the problem is that dv returns null ? and here is the sqldatasource definition in .aspx page <asp:SqlDataSource ID="SqlDataSourcePictures" runat="server" ConnectionString="<%$ ConnectionString:con1%>" SelectCommand="SELECT URL FROM SchoolPictures WHERE (School_Code = @School_Code) AND (SchoolPictureCategory = @SchoolPictureCategory)" OnSelecting="SqlDataSourcePictures_Selecting" OnSelected="SqlDataSourcePictures_Selected"> <SelectParameters> <asp:QueryStringParameter Name="School_Code" QueryStringField="bid" Type="Int16" /> <asp:ControlParameter ControlID="ddlCat" Name="SchoolPictureCategory" PropertyName="SelectedValue" Type="Int16" /> </SelectParameters> </asp:SqlDataSource>
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
