DataSource Controls :: Find Total Null Values In A Table Without Using Cursor?
Mar 19, 2010
how can i find the total null values in a table without using sql cursor .
i want to find the percentage of the data which is avaibale in the table and how much percentage of data is unavailable ( or null). to make a comparision chart.
i have 6000 rows and 200 columns in a table , if i use sql cursor , it is taking too much time to execute ( 6000 * 200 ) loops. is there any inbuilt-function in sql to do it .?
I wanted to get the total row count in a GridView's databound event handler, so I tried the following:
protected void grid_DataBound(object sender, EventArgs e) { GridView grid = (GridView)sender; DataSet data = grid.DataSource as DataSet; if (data == null) return; int totalRows = data.Tables[0].Rows.Count; }
The problem is that the grid.DataSource property is null. The DataSourceID property is not null, so does that mean that I can't access the DataSource object in the DataBound event handler unless I assign the DataSource property directly?
Edit1: Here is the code in my GridHelper class for adding rowcount to the BottomPagerRow. I would like to get rid of the requirement to pass in the ObjectDataSource, but can't because I need to use the Selected event to get total row count. Hence the reason for the question. I also think this might be better in a custom control where I can access ViewState, and/or create child controls during the Init event (I still have a problem to resolve with the way the pager renders with an extra cell), but then I'd still have the problem of how to get to the total row count when the DataSource itself doesn't appear to be available in any GridView events.
Edit 2: Not really relevant to the specific problem, but I resolved the problem I was having with the pager rendering so i've updated the code posted. I found the trick here: [URL]
#region Fields private int totalRows = -1; #endregion #region Constructors /// <summary> /// Initializes a new instance of the <see cref="GridHelper"/> class. /// Adds EventHandlers to the GridView to display results from the ObjectDataSource in the footer. /// Marked as obsolete because AddResultsToFooter method provides a static access to the same functionality /// An instance of GridHelper is required by the passed in GridView to store the totalRows value between the two event handlers /// </summary> /// <param name="grid">The grid.</param> /// <param name="source">The ObjectDataSource linked to the GridView.</param> [Obsolete("Use AddResultsToFooter instead.")] [EditorBrowsable(EditorBrowsableState.Never)] public GridHelper(GridView grid, ObjectDataSource source) { source.Selected += source_Selected; grid.PreRender += grid_PreRender; } #endregion #region Event Handlers private void grid_PreRender(object sender, EventArgs e) { GridView grid = (GridView)sender; if (grid.HeaderRow != null) grid.HeaderRow.TableSection = TableRowSection.TableHeader; //Add a cell to the bottom pager row to display the total results if (grid.BottomPagerRow == null || !grid.BottomPagerRow.Visible) return; //Get the control used to render the pager //http://michaelmerrell.com/2010/01/dynamically-modifying-the-asp-net-gridview-paging-control/ Table tblPager = grid.BottomPagerRow.Cells[0].Controls[0] as Table; if (tblPager == null) return; if (totalRows < 0) { //The DataSource has not been refreshed so get totalRows from round trip to client addResultsToPagerTable(tblPager, grid.Attributes["results"]); return; } int firstRow = grid.PageIndex * grid.PageSize + 1; int lastRow = firstRow + grid.Rows.Count - 1; string results; if (totalRows <= grid.PageSize) results = string.Format("<span class='grid-pager'>{0} Results</span>", totalRows); else results = string.Format("Results <b>{0}</b> to <b>{1}</b> of <b>{2}</b>", firstRow, lastRow, totalRows); addResultsToPagerTable(tblPager, results); //Need to store the information somewhere that is persisted via ViewState, and we don't have access to ViewState here grid.Attributes.Add("results", results); } /// <summary> /// Handles the Selected event of the source control. Gets the total rows, since it is not possible to access them from the GridView. /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs"/> instance containing the event data.</param> private void source_Selected(object sender, ObjectDataSourceStatusEventArgs e) { if (e.ReturnValue is DataView) totalRows = ((DataView)e.ReturnValue).Count; else if (e.ReturnValue is EntitySet) totalRows = ((EntitySet)e.ReturnValue).Count; } #endregion #region Private Methods private static void addResultsToPagerTable(Table tblPager, string results) { //http://michaelmerrell.com/2010/01/dynamically-modifying-the-asp-net-gridview-paging-control/ //Get a handle to the original pager row TableRow pagesTableRow = tblPager.Rows[0]; //Add enough cells to make the pager row bigger than the label we're adding while (pagesTableRow.Cells.Count < 10) pagesTableRow.Cells.Add(new TableCell { BorderStyle = BorderStyle.None }); //Add a new cell in a new row to the table TableRow newTableRow = new TableRow(); newTableRow.Cells.AddAt(0, new TableCell { Text = results, BorderStyle = BorderStyle.None, ColumnSpan = pagesTableRow.Cells.Count }); tblPager.Rows.AddAt(0, newTableRow); } #endregion #region Public Methods /// <summary> /// Adds EventHandlers to the GridView to display results from the ObjectDataSource in the footer. /// </summary> /// <param name="grid">The GridView.</param> /// <param name="source">The ObjectDataSource linked to the GridView.</param> public static void AddResultsToFooter(GridView grid, ObjectDataSource source) { if (grid == null) throw new ArgumentNullException("grid", "grid is null."); if (source == null) throw new ArgumentNullException("source", "source is null."); new GridHelper(grid, source); } #endregion
How can i get the values even when the Source or url is null from the below query
SELECT a.id, isnull((SELECT STUFF((SELECT '<li><a href="'+url+'">'+Source_Name+'</a> <span>(' + k.Source +')</span></li>' end FROM table1 i, table2 k, table3 j where i.CategoryID = j.Category_ID and i.CategoryID = c.Category_ID and i.Source = k.ID FOR XML PATH('')),1, 0, '')), '') AS Source_url, FROM @table4 c INNER JOIN table5 v on c.Category_ID = v.Category_ID
I have req where i have to check the value coming from db is null or not and assign value according to that.Problem here is the field datatype is 'Datetime' so all the time when I tried to use the below line of code its giving me error.How can I pass a null value to datetimefield if value coming from db is null.
I have created a table in my SQL database that has a primary column (Title) and another column named Callsign. How can I access the table row that has a certain title and Callsign using C# in a web page event handler?
i want to pass null value into varbinary(max) column n to by table
if i do like this i have this function in my app_code .vb class
Public Function Remove_Attachment(ByVal Id As Integer) As Integer Try Dim db As New dbAccess db.commandText = "SP_RemoveAtt_DML" db.commandType = CommandType.StoredProcedure db.addParameter("@InOutVal_Id", Id) db.addParameter("@Document", System.Data.SqlTypes.SqlBinary.Null.Value) db.executeQuery() Catch ex As Exception Throw ex End Try End Function
i got this error. Data is Null. This method or property cannot be called on Null values.
where can i find the tableadapeter to add data to my database table? and is that the best way or there a easier way... i have a csv file that i would like to add to my data table but i don't know how?
i am trying to find the column length of a particular sql table, it is working very fine if its 'nvarchar' or 'text' type. But if i try to get the numeric(int/bigint/bit) data type i am getting error. please help me out. The sample code is given below for you reference,'*** Source Code Start
Dim da As New OleDbDataAdapter(strQuery, PortalConn)
I am very new to ASP .NET and am getting above error when trying to insert a new record into a SQL database. I have turned on Option Explicit On so the default date of 01/01/1900 does not get assigned to this field automatically. Since most of the people in the database will not have a date of death, I need to have "blank" deathDate. When I run the stored procedure within SQL and choose pass null value, the Insert Procedure works fine. When I attach to Web Form, however, I get String was not recognized as a valid DateTime error. How can I set it so Null values are accepted into this date field.
I have a table with a lot of records - it has a column called CallID I have another table with no records, but one that also has a CallID column I want to insert all the values from the CallID column in the first table into the CallID column of the second table.
I am creating an online employment application, and instead of creating a table with 20 columns for job duties that may or may not receive values, I simply want to create 3 columns
ApplicantID EmployerName (Name of Work history Employer) JobDuty
I want to create a form on a page with 10 textboxes so the applicant can type in some job duties they had at that job.
Is there a way in C# or SQL to then cycle through and insert the textbox values as separate rows?
Is it possible in to set up an SQL table column to have multiple values? Or would I have to put my values into a new table and link it to the original one?
I guess the code is totally right and doesn't require any change in it,, however am getting an error which says "Conversion failed when converting the varchar value '11/11/2010' to data type int."check the attached file ..
Running a select * from my table give me data like this:
Field1 Field2 Field3 Field4 Field5 Field6
NULL NULL NULL J 4GG4K C46D
DRIVE 0155 1 NULL W6665 NULL (C46D)
TEST 01444 5 NULL W4434 NULL (C46D)
NULL NULL NULL 1 WGG4K AAVE
HOME 01444 3 NULL 5HTYL NULL (AAVE)
NEW 09888 9 NULL 8HTTTE NULL (AAVE)
The problem is Field6 for the rows that are bolded. I need Field6 to repeat for each row until the next non null value is found in Field6, than I need that value to repeat again. I placed in parenthesis outside the NULL for Field6 what I need to show which is the value of Field6 above the records which Field6 is null.
I know this is confusing, but it's actually pretty simple to understand. I just need to get the first NON NULL value of Field6 and UPDATE the records below it where Field6 IS NULL to that value, then when it reached the next bolded row there is a new value for Field6, I then need to get that value and UPDATE the records below it which are null with the value.
Some ids are pulled out from the XML field of a certain User Profile . Those ids have their text values stored in a SQL table.
What is the best way to map that xml list to the SQL table in order to populate a dropdown list which contains ids from the XML list and text values from the SQL table?
Is there a way to copy a row from one table and insert to another but at the same time insert extra values to destination table?Let's say source table Item has values:
I want to copy row from source table where itemid is 25, insert it into destination table and add extra values: ChangeDate, ChangeReason, ChangedBy. I tried:
INSERT INTO ItemHist (ItemID, ChangeDate, ChangeReason, ChangedBy, ItemType, ItemModel) VALUES (ItemHist.ChangeDate=GetDate(), ItemHist.ChangeReason="why not", ItemHist.ChangedBy="me") SELECT ItemID, ItemType, ItemProducer, ItemModel FROM Item WHERE ItemId=25
but obviously it didn't work ("The name 'ChangeDate' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.").
select AutoIdx,TxDate,Id,AccountLink,Description,Debit,Credit,(Debit-Credit) as Actual from PostGL where Id='JL' and AccountLink=2
AutoIdx TxDate Id AccountLink Description Debit Credit Actual 3 2010-01-22 JL 2 bcb 0 35.09 -35.09 5 2010-01-28 JL 2 g 3.51 0 3.51
I am trying in view like this
declare @cnt int declare @i int set @budget=20 set @cnt= (select COUNT(*) from PostGL where AccountLink=2 ) set @i=1 WHILE (@i<=@cnt ) BEGIN set @tdebit=??
END
I am taking count the no of rows having AccountLink=2 I need to read all debit,credit and add all debit into totaldebit,all credit into totalcredit independently from table. then i need to show the totaldiff as Actual. i need ....totaldiff=totaldebit-totalcredit
