Read entire worksheet in an Excel workbook using VB.net

In the following section you can see How to find the last row data in Excel worksheet or How to find the Used area in a worksheet. For finding last row we are using WorkSheet.UsedRange . It will return the last cell . The follwoing code open an Excel file and read the entire content.

Continue reading “Read entire worksheet in an Excel workbook using VB.net”

Advertisements

Read Data from Excel using OLEDB in VB.NET

Without using Excel Object we can insert , edit , delete , select etc. in cell content of an Excel file using OLEDB in VB.NET . Here we are using OleDbConnection , OleDbDataAdapter , DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations . For read the content from Excel file using ado.net , We can use the SELECT command like in SQL Operations.

If you would like to export to excel, check out this article:

How to export from DataGridView to excel

Continue reading “Read Data from Excel using OLEDB in VB.NET”

Get every n-th record using SQL Server

First, a few things you should already know:
Modulus Returns the remainder of one number divided by another.
SELECT 32/10 -> 3
SELECT 32%10 -> 2

To return the n-th row only of a table, use:

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE row = [value of n]

To return every n-th row in a table, use:

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE [row]%[value of n] = 0

Example:

Return every 10th event from an events table:

WITH tableEvents AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY EventDate)) as row,*
    FROM tblEvents
	Where Month(EventDate) = 7)
SELECT * FROM tableEvents WHERE [row]%10= 0

How to check if a constraint exists

In SQL Server, you can check if a constraint exists using the following script:

SELECT 
    OBJECT_NAME(OBJECT_ID) AS NameofConstraint
        ,SCHEMA_NAME(schema_id) AS SchemaName
        ,OBJECT_NAME(parent_object_id) AS TableName
        ,type_desc AS ConstraintType
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT'
        AND OBJECT_NAME(OBJECT_ID)='[constraint name here]'

or by running one of the following commands:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_Name = '[TableName]'

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME='[constraint name here]'

--Returns one row for each FOREIGN KEY constrain
SELECT *
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_NAME='[constraint name here]'

--Returns one row for each CHECK constraint
SELECT *
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_NAME='[constraint name here]'

Assembly Language: How to Make Sounds example

This code example provides a set of keyboard routines to control sound output while waiting for a user to enter a keyboard character. The advantage to this method is that a main routine can call these sound routines to play a sound sequence, and the sound routines will return control back to the main routine whenever the user enters keyboard data so that the main routine can continue computing while the sound plays in the background. The code example has two different code entry points for getting keyboard data. One code entry point is a standard get_keyinput call which will wait for a key and update the sound data until a key code is found. The other code entry point is the get_keyinput_to call, which will wait a set amount of time for a key code and if none is found, return with a no key code found condition. The calling routine puts a timeout counter value in register AX on entry. The counter value is based on the system clock which ticks at 18.2 times per second. The entry point start_table_sound is used to begin a background sound sequence. On entry, the register BX indexes a table of sound data. The table has a format of four byte entries and is terminated by a data word of zero. The four bytes are used as two words: the first is a duration count and the second is a tone value. There are two code entry points for turning the background sound off and on. There is also a utility to flush out the keyboard buffer that can be executed with a call to flush_keyboard. Continue reading “Assembly Language: How to Make Sounds example”