vb.net - DataGridView to Datatable

Convert DataGridView Source to Datatable
***********************************

dim dt as New DataTable
dt = TryCast(dgv.DataSource, DataTable)

if, the datasource is BindingSource, then
use this:

dim bs as BindingSource
bs = TryCast(dgv.DataSource, BindingSource)
dim dv as New DataView
dv = bs.SyncRoot
dt = dv.ToTable


if, the Datasource is any of above, use this:

dt = New DataTable
Dim bs As New BindingSource
If TypeOf dgv.DataSource Is DataTable Then
 dt = TryCast(dgv.DataSource, DataTable)
Else
   bs = TryCast(dgv.DataSource, BindingSource)
   dv = New DataView
   dv = bs.SyncRoot
   dt = dv.ToTable
End If

Grouping Datetime Column by Date part only.

SELECT DATEADD(dd,(DATEDIFF(dd,0,CRETS)),0), COUNT(*)
FROM Table1
GROUP BY DATEADD(dd,(DATEDIFF(dd,0,CRETS)),0)

SELECT CAST(DATEDIFF(dd,0,CRETS) AS Datetime), COUNT(*)
FROM Table1
GROUP BY CAST(DATEDIFF(dd,0,CRETS) AS Datetime)


--Don't use this:
SELECT CONVERT(Datetime, CONVERT(NCHAR(10), CRETS, 121)), COUNT(*)
FROM Table1
GROUP BY CONVERT(Datetime, CONVERT(nchar(10), CRETS, 121))

Taken from: http://www.sqldisco.com/?p=6

Listview - autosize columns disbale

Private Sub lvwOne_ColumnWidthChanging(ByVal sender As Object, ByVal e As System.Windows.Forms.ColumnWidthChangingEventArgs) Handles lvwOne.ColumnWidthChanging
        e.Cancel = True

        Select Case e.ColumnIndex
            Case Is = 0
                e.NewWidth = 25
            Case 1, 2, 3, 4
                e.NewWidth = 100
            Case Else
                e.NewWidth = 0
        End Select
    End Sub

You can keep the column width unchanged. This way, user cannot resize the columns width.

Import from Excel Sheet into Sql Table with filtering

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Data.xls',

'SELECT * FROM [Sheet1$] where [col_name] <> '' ')

SQL FUNCTION ExtractNumbers

SQL FUNCTION ExtractNumbers

Create Function ExtractNumbers

(

@str_passed varchar(max)

)

Returns varchar(50)

AS

BEGIN

    declare @val varchar(50), @i int

    select @val = '', @i = 1

    while (@i <= len(@ str_passed))

        select @val= @val+ (CASE when substring(@ str_passed,@i,1) like '[0-9]' 

                                  then substring(@ str_passed,@i,1) else '' END),

              SET @i = @i + 1

    Return @val

END

Usage:-

SELECT SrNo,  dbo. ExtractNumbers(AlphaNumCol) as NumColData from TableName

Drop commas from '45,22,135'

or Drop dash & braces from '(91)123-456-789'

or Drop alphabets from 'C99B33A1234'

RupeeMail

Invitation to join RupeeMail!

---

Hi ,

I have something interesting for you, RupeeMail!

It’s really amazing! You get paid to open & read the contents of RupeeMail. You receive promotional offers & special discounts in RupeeMail.

Interestingly RupeeMails will reach you based on the preference list you opted for.

Create your RupeeMail Account & refer your friends to earn launch referral bonus on every new registration.

Try this... http://www.rupeemail.in/rupeemail/invite.do?in=MzU4NTYzJSMlY2dlU2w1NldxdlhQelQ0TThSSFVJOTNtUQ==

RupeeMail, It pays

Sushil Soni

Click on below link to navigate to RupeeMail.com

http://www.rupeemail.in/rupeemail/invite.do?in=MzU4NTYzJSMlY2dlU2w1NldxdlhQelQ0TThSSFVJOTNtUQ==

Scalar User Defined Functions and Computed Columns

Scalar User Defined Functions and Computed Columns

Came across an interesting SQL problem where a customer has a table with a primary key and X numerical fields. For each record the maximum value in these X fields was needed.

The issue was how to accomplish this efficiently? Or more accurately the least inefficient way of doing it is.
One option suggested was to have to UNION the table with itself X times and then do a MAX aggregation since there was a lot of data.

The more interesting option, which it turns out not many people are aware of, is to use a Scalar User Defined Function (UDF) to calculate the maximum value.

Here's an example of this in action:

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END
CREATE TABLE Bricks
(
BrickPartNmbr int PRIMARY KEY,
BrickColor nchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume AS
(
dbo.CubicVolume(BrickHeight,
BrickLength, BrickWidth)
)
)

Basically the scalar UDF is called for each row returned from a SELECT. The column itself is not actually stored in the SQL Server table. Any INSERT and UPDATE on the table do not calculate the value for the computed column. Now obviously this is an overhead that may not be acceptable if the table is very static and read a lot. Here, it may be more efficient to perform the calculation once on INSERT or UPDATE of the table with INSTEAD OF triggers or another similar method.