Reading a SAS Dataset with .NET OLEDB

If you have had to read in a SAS Dataset into a .NET Dataset here is some code I found that works to do it with:


Make sure you add these lines to the start of the page

Imports SAS
Imports SASWorkspaceManager
Imports System.Text
Imports System.Data.OleDb


In the Subroutine do this:
 (note: BoldIalics means you should type in your information not what I wrote)
  
   Try  'NEVER FORGET TRY CATCH!!

            Dim sasDs As System.Data.DataSet = New System.Data.DataSet
            Dim sas As OleDbConnection = New OleDbConnection("Provider=sas.LocalProvider; Data Source=pathToDirectoryWhereSASFILEisLocated")
            sas.Open()
            Dim sasCommand As OleDbCommand = sas.CreateCommand
            sasCommand.CommandType = CommandType.TableDirect
            sasCommand.CommandText = "nameOfSASFilewithoutExtension"
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(sasCommand)
            da.Fill(sasDs, "SasData")
            sas.Close()
            DataGridView1.DataSource = sasDs.Tables("SasData")
        Catch ex As Exception
            MessageBox.Show("Unable to load SAS dataset. Error seen was: " + ex.ToString)
        End Try

    End Sub


DOING A JOIN INSIDE AN UPDATE STATEMENT

This example is meant only to illustrate how to do an update statement that involves multiple tables.

dbo.CustomersMasterList Table

CML_ID, Name, Address, City, State, Zip, Phone, CustomerEmailAddress (newly added)

dbo.EmailAddresses Table

E_ID, emailaddress

Sample data from dbo.CustomersMasterList
1,John Doe,12345, Elm Street,Walawala WA, 90085,952-555-1212, NULL

2,Jane Doe,12345, Elm Street,Walawala WA, 90085,952-555-1212, NULL

...

Sample data from dbo.EmailAddresses

1,JohnDoe@aol.com

2,JaneDoe@aol.com

Say you need to take Table A Which is dbo.CustomersMasterList

and you needed to add a column to it called CustomerEmailAddress. So you go modify the table and add CustomerEmailAddress to the table.

But the email addresses are in a seperate table called dbo.EmailAddresses and its a big table so you dont want to retype all that data.

You could do this:

UPDATE dbo.CustomersMasterList

SET CustomerEmailAddress = EmailAddress

FROM dbo.CustomersMasterList CML

JOIN dbo.EmailAddresses ON

CML_ID = E_ID

WHERE any criteria can go here...

Let me know if you have any issues.... Happy programming!

SmallDateTime vs DateTime in SQL Server

I just discovered this after pulling my hair out.... there are date max limitations in SmallDateTime.

SmallDateTime

January 1, 1900, through June 6, 2079
00:00:00 through 23:59:59

Date Time
January 1, 1753, through December 31, 9999

00:00:00 through 23:59:59.997

References:

http://msdn.microsoft.com/en-us/library/ms187819.aspx for DATETIME

http://msdn.microsoft.com/en-us/library/ms182418.aspx for SMALLDATETIME

Who Said You Cant Do A Loop in SQL

You can sort of do loops in SQL. Its not as nice as in regular programming but you do have a WHILE loop.

All you have to do is this:

WHILE whileCondition
BEGIN
What to do in the loop
END

I had to do some parsing of a string and I had to cycle through each letter to find a particular subset of characters.

There is also FETCH/NEXT but this is much easier

MAKE SURE THAT YOU WILL HIT YOUR WHILE CONDITION OR YOU WILL BE IN AN INFINTE LOOP :)

SQL: Dividing 2 Numbers Keeps Returning Zero (0)

This is way too funny....

So I am writing a SQL Stored Procedure.... hum de dum de dum......

Run it.....

WHAT!!!! The value of X/Y is 0 (put in Column Z)

X is 5000,
y is 10000

Both are decimal data types and the third field Z is also a decimal

Shouldnt that be .50

Well to make a long long long story :) short.... the 5,000 and 10,000 values were a sum in a previous step...

What I found out after a little blog research is that when you do a math function like SUM or COUNT, SQL changes the data type to INTEGER. That is why it was returning 0

Basically here is what I did

CAST(X as decimal(18,2)) / Cast(Y as decimal(18,2))

and it worked like a charm....

Thanks to this blog that saved me:

http://blog.pengoworks.com/index.cfm/2008/11/4/Division-of-integers-in-MS-SQL