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(); }
I have a package which have two sequence container, first container is used to transfer data to a staging area and second sequence container is used to transfer to destination from that staging area. And I also apply transaction required to second sequence container. There are several execute sql tasks and several data flow tasks inside two sequence container. first sequence container( 1.execution sql task-> 2.data flow ->3.execution sql task) -> second sequence container(4.execution sql task-> 5.execution sql task-> 6.data flow-> 7.data flow -> 8.execution sql task-> 9.data flow...)
I create ExecutionLog table which is used to log status for this package on our sql server. First this status field is null, then during this package run , it change to 'in process', and after this package finished, it change to 'success' or 'failure' depending this package can run successfully or not. This package can be run only if status is 'success' ,'failure' or null. So I need to change this status field during package execution. For updating package to failure, I need to add event handler to change that status using execute sql task.
First time I perform to execute sql task on onError event handler tab (this event handler is applyed on package level ) . And for testing envent handler I use old schema version to make sure I get failure for '8 execution sql task'. But package seems to get stuck at '8 execution sql task' inside second sequence container( always yellow when I run from ssis) and never fire envent handler. '8 execution sql task' is used to update related table.
Second time I remove onError envent handler and change to use on onTaskFailed event handler tab (this event handler is applyed on package level ) . But everything is the same as using onError event handler except I got error output but still can not fire event. Why '8.execution sql task' can not fire onError or onTaskFailed? For this case what kind of event handler I need to use, what kind of level I need to apply for this event handler.
I have an activated SP that is issuing PRINT statements.
These are raised as ErrorLog and EventLog events when using SQLProfiler
However if you attach an event notification for ERRORLOG and EVENTLOG, nothing appears in the queue. Any other ERRORLOG or EVENTLOG event caught by profiler is caught by the event notification as expected.
We have a package that runs on 3 servers, all based in different countries and time zones. Each package does logging to a common SQL Server based in New York. (We log to the same server to make monitoring of the package run results easier.) We expect that our overseas servers will occassionally fail to make a database connection and have coded a Try/Catch block in our Script task to ignore errors that occur when attempting the SQL Server logging.
The problem is that the OnError event handler defined at the package level is being called whenever a server fails to make a database connection. The Catch block appears to not be called at all. I've included below the short method that does the SQL Server logging. The "Dts.Log" line logs to a SQL Server and usually works. However, when it fails, the "WriteEventLogEntry" method is supposed to be called to write an error to the server's event log. (I haven't included the "WriteEventLogEntry" code but it works fine when called elsewhere in the package to do our non-error event logging.)
Public Sub WriteSqlServerLogEntry(ByVal logMessage As String, ByVal dataCode As Integer, ByVal dataBytes() As Byte, ByVal rowsInserted As Integer)
Try
Dts.Log(logMessage, dataCode, dataBytes)
Catch ex As Exception
WriteEventLogEntry("An exception was thrown while attempting to log the following message to the SQL Server provider: " + logMessage, EventLogEntryType.Error, rowsInserted)
End Try
End Sub
Shouldn't a Catch block prevent the OnError event from being raised? How can I change my package to ignore all errors that occur when attempting SQL Server logging?
Hi Folks I hope every thing good.I have this problem I have a master page and an aspx page In aspx page there is an SqlDataSource Componant that call a storage prcedure from db that insert value to a certain table The issue here that he call the sp twice so the row record twince in the database What is the problem ?Thanks Bye
I need access to the underlying DataView associated with a declaratively defined SQLDataSource. Ideally, I need this access in the SQLDataSource's Selected event. I know that I can use the SQLDataSouce.Select() method to return the underlying DataView object. But, there is a GridView control bound to the datasource so I don't want to explicity call Select() since it will end up being called again implicity by the gridview itself. I have seen that this can be done with an ObjectDataSouce as follows: void odsDataSouce_Selected(object sender, ObjectDataSourceStatusEventArgs e) { DataView oDV = (DataView)e.ReturnValue;} Is there any way to accomplish the same thing with a SQLDataSouce? It seems hard to believe that the query results are not exposed to the Selected event. Thanks!
Hi,I have a SqlDataSource and am trying to establish if it returns rows using the selected event e.AffectedRows statement. Unfortunately the selected event does not fire. I am using a stored procedure with 2 parameters:applicantID - Int32 - Parameter source from control label textissueStatus - Int16 - Parameter source 'None' default value = 1 When i test it during configuration it returns 1 row as expected. I am manually running the DataSource using ds.DataBind(); after a button click. Anyone know why its not firing the event?Thanks
When I handle a event for selecting to change the value of a parameter it does not use the new value and the selected event is never called. What am I doing wrong?
Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@" at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005): <asp:GridView ID="gvLoadEditProductPrices" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="ProductID"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="ProductID" HeaderText="ProductID" HeaderStyle-BackColor="white" InsertVisible="False" ReadOnly="True" SortExpression="ProductID" /> <asp:BoundField DataField="Product" HeaderText="Product" SortExpression="Product" ReadOnly="True" /> <asp:BoundField DataField="ProductCat" HeaderText="ProductCat" SortExpression="ProductCat" ReadOnly="True" /> <asp:BoundField DataField="VarRate" HeaderText="VarRate" SortExpression="VarRate" /> <asp:BoundField DataField="loadid" HeaderText="loadid" InsertVisible="False" ReadOnly="True" SortExpression="loadid" /> <asp:BoundField DataField="loadamount" HeaderText="loadamount" SortExpression="loadamount" ReadOnly="True" /> <asp:BoundField DataField="ProductCol" HeaderText="ProductCol" SortExpression="ProductCol" ReadOnly="True" /> <asp:BoundField DataField="PageID" HeaderText="PageID" SortExpression="PageID" ReadOnly="True" /> </Columns> </asp:GridView> and the sqldatasource's info: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>" ProviderName="System.Data.SqlClient" SelectCommand="spGetLoadEditProductPrices" SelectCommandType="StoredProcedure" UpdateCommand="spUpdateProductPrices" UpdateCommandType="StoredProcedure" > <UpdateParameters> <asp:ControlParameter Name="ProductID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter> <asp:ControlParameter Name="LoadID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter> <asp:ControlParameter Name="PageID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter> <asp:ControlParameter Name="ProductCol" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter> <asp:ControlParameter Name="NewRate" Type="Double" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="ddlEstLoadsPerAcre" Name="LoadID" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="txtEditType" Name="PageName" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource>
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
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'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
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
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, 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, 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
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM Log SQL Server (Current - 10/25/2011 10:55:00 AM) Source spid Message Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file. The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30 WHERE targetinstance isa 'CIM_DataFile' AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message. However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup) We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers. We were having
Event Type: Failure Audit Event Source: Security Event Category: Object AccessEvent ID: 560 Date: 5/23/2007 Time: 6:27:15 AM User: domainuser Computer: MACHINENAME Description: Object Open: Object Server: SC Manager Object Type: SC_MANAGER OBJECT Object Name: ServicesActive Handle ID: - Operation ID: {0,1623975729} Process ID: 840 Image File Name: C:WINDOWSsystem32services.exe Primary User Name: MACHINE$ Primary Domain: Domain Primary Logon ID: (0x0,0x3E7) Client User Name: User Client Domain: Domain Client Logon ID: (0x0,0x6097C608) Accesses: READ_CONTROL Connect to service controller Enumerate services Query service database lock state
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
Hi I have a some confusion pleaz resolve it can i fire truncate to the multiple tables if you have any type of syntax then tell me and if not then could u resolve from store procedure
