Tech and Programming Tips

Providing programming tips for Visual Studio C# and VB, Oracle, SQL Server, HTML, and Tech Tips for Facebook, WordPress, MS Office, Windows, etc.

SQL Server – Add Column Script

Posted by Greg Owen on July 3, 2012

–Add a column to an existing table

ALTER TABLE dbo.Employee
ADD Active varchar(1)
GO

–Add default to new column

ALTER TABLE dbo.Employee
ADD CONSTRAINT Employee_Active 
DEFAULT (‘N’) FOR Active
GO

Posted in Visual Studio Code Examples | Tagged: , , , , | Leave a Comment »

SQL Server List Tables And Columns

Posted by Greg Owen on June 6, 2012

–List Column Names

SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = ‘YOUR_DB_NAME’

–List Tables

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = ‘YOUR_DB_NAME’
–List Views

SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_CATALOG = ‘YOUR_DB_NAME’

–List All Columns in All Views

SELECT *
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE TABLE_CATALOG = ‘YOUR_DB_NAME’
–List Stored Procedures

SELECT *
FROM sys.procedures

–List a Stored Procedure Script

EXEC sp_HelpText N’YourStoredProc’

Posted in SQL Server | Tagged: , , , , , | Leave a Comment »

SQL Server Add Default To Existing Table

Posted by Greg Owen on May 15, 2012

–String Value
ALTER TABLE [Table1] ADD CONSTRAINT [Table1_Column1] DEFAULT (‘Not Applicable’) FOR [Column1]
GO

–Numeric Value
ALTER TABLE [Table1] ADD CONSTRAINT [Table1_Column1] DEFAULT ((0)) FOR [Column1]
GO

–Date Value
ALTER TABLE [Table1] ADD CONSTRAINT [Table1_Column1] DEFAULT (((1)/(1))/(1900)) FOR [Column1]
GO

Posted in SQL Server | Tagged: , , , , , , | Leave a Comment »

HTML Table Example

Posted by Greg Owen on March 27, 2012

The example below displays how to create a table using HTML and the <table> tag.

The example below consists of the following elements:
<table> This tag begins the table.

border = “1” This attribute defines the width of the border. If this attribute is not added to the table tag, no border will be displayed.
<tr> This tag begins a row in the table.

<td> This tag defines a column in the table, and stands for table data.

<th> This tag defines a table header. All text within this tag will be displayed in bold.

align = “right” This attribute is used below in the <td> tag to align the data displayed to the right of the column.

<table border=”1″>
<tr>
<th>Grade</th>
<th>Student Count</th>
</tr>
<tr>
<td>A</td>
<td align=”right”>10</td>
</tr>
<tr>
<td>B</td>
<td align=”right”>11</td>
</tr>
<tr>
<td>C</td>
<td align=”right”>7</td>
</tr>
<tr>
<td>D</td>
<td align=”right”>3</td>
</tr>
<tr>
<td>F</td>
<td align=”right”>2</td>
</tr>
</table>

The code above produces the following table:

Grade Student Count
A 10
B 11
C 7
D 3
F 2

Posted in HTML Examples | Tagged: , , , , , , , , | 2 Comments »

Create a Table From a SQL Statement

Posted by Greg Owen on March 6, 2012

You can create a copy of a table using the SELECT INTO command.

SELECT * INTO TABLE2 FROM TABLE1

You can also join two or more tables and create a new table from the joined tables.

SELECT * INTO TABLE3 FROM TABLE1 a LEFT OUTER JOIN TABLE2 b ON a.field1 = b.field5

The newly created table will not have a primary key even if the existing table or tables do have a primary key.

Posted in SQL Server | Tagged: , , , , , , | Leave a Comment »

SQL Server DatePart Example

Posted by Greg Owen on February 22, 2012


select datepart(quarter,getdate())
–Get Week Day Numerical value, Sunday = 1st day of week
SELECT


 


CASE WHEN datepart(weekday,getdate()) = 1 THEN ‘Sunday’
WHEN datepart(weekday,getdate()) = 2 THEN ‘Monday’
WHEN datepart(weekday,getdate()) = 3 THEN ‘Tuesday’
WHEN datepart(weekday,getdate()) = 4 THEN ‘Wednesday’
WHEN datepart(weekday,getdate()) = 5 THEN ‘Thursday’
WHEN datepart(weekday,getdate()) = 6 THEN ‘Friday’
WHEN datepart(weekday,getdate()) = 7 THEN ‘Saturday’END


DECLARE @Date1 datetime


SET @Date1 = ’01/01/2020′


SELECT


CASE WHEN datepart(weekday,@Date1) = 1 THEN ‘Sunday’
WHEN datepart(weekday,@Date1) = 2 THEN ‘Monday’
WHEN datepart(weekday,@Date1) = 3 THEN ‘Tuesday’
WHEN datepart(weekday,@Date1) = 4 THEN ‘Wednesday’
WHEN datepart(weekday,@Date1) = 5 THEN ‘Thursday’
WHEN datepart(weekday,@Date1) = 6 THEN ‘Friday’
WHEN datepart(weekday,@Date1) = 7 THEN ‘Saturday’END


–Date Format DDD, MMM, DD, YYYY
DECLARE @Date2 datetime


SET @Date2 = ’01/01/2020′


SELECT


CASE WHEN datepart(weekday,@Date2) = 1 THEN ‘Sun’
WHEN datepart(weekday,@Date2) = 2 THEN ‘Mon’
WHEN datepart(weekday,@Date2) = 3 THEN ‘Tue’
WHEN datepart(weekday,@Date2) = 4 THEN ‘Wed’
WHEN datepart(weekday,@Date2) = 5 THEN ‘Thu’
WHEN datepart(weekday,@Date2) = 6 THEN ‘Fri’
WHEN datepart(weekday,@Date2) = 7 THEN ‘Sat’END
+ ‘, ‘ + convert(nvarchar(3),@Date2, 107) + ‘, ‘ + convert(nvarchar(2),@Date2,113) + ‘, ‘ + convert(nvarchar(4),year(@Date2))


select datepart(month,getdate())


select datepart(day,getdate())


select datepart(year,getdate())


select datepart(week,getdate())


select datepart(weekday,getdate())


select datepart(hour,getdate())


select datepart(minute,getdate())


select datepart(second,getdate())


select datepart(millisecond,getdate())


Posted in SQL Server | Tagged: , , , , | Leave a Comment »

Facebook Tip – View Photos The ‘Old’ Way (Without the Pop Up)

Posted by Greg Owen on February 20, 2012

Facebook recently changed the way you view photos.  By default, when you click on a photo, the photo pop ups over the Facebook page.  Previously, when you clicked on a photo, you would be directed to another Facebook page with the photo displayed on the page and previous and next links above the photo.

If you preferred the old way Facebook displayed photos, do the following:

Right Click on the picture.
Select Open link in new tab with the left mouse button.

That is it, you can now view photos without the pop up.

Posted in Facebook Tips | Tagged: , , , , | Leave a Comment »

SQL Server DateDiff Function

Posted by Greg Owen on February 20, 2012

–Date Difference Function
–Number of days between two dates
select datediff(day,getdate(),’09/01/2020′)

–Number of months between two dates
select datediff(month,getdate(),’09/01/2020′)

–Number of years between two dates
select datediff(year,getdate(),’09/01/2020′)


Posted in SQL Server | Tagged: , , , , , | 1 Comment »

SQL Server Date Formatting and Date Examples

Posted by Greg Owen on February 9, 2012

–Get Current Date and Time
select getdate()

–Date Format MMM DD YYYY
select convert(nvarchar(12),getdate(),100)

–Date Format MM/DD/YYYY
select convert(nvarchar(10),getdate(),101)

–Date Format YYYY.MM.DD
select convert(nvarchar(10),getdate(),102)

–Date Format DD/MM/YYYY
select convert(nvarchar(10),getdate(),103)

–Date Format DD.MM.YYYY
select convert(nvarchar(10),getdate(),104)

–Date Format DD-MM-YYYY
select convert(nvarchar(10),getdate(),105)

–Date Format DD MMM YYYY
select convert(nvarchar(11),getdate(),106)

–Date Format MMM DD, YYYY
select convert(nvarchar(12),getdate(),107)

–Date Format hh:mm:ss (24hr)
select convert(nvarchar(10),getdate(),108)

–Date Format hh:mm:ss:mmm (AM PM)
select convert(nvarchar(30),getdate(),109)

–Date Format MM-DD-YYYY (AM PM)
select convert(nvarchar(10),getdate(),110)

–Date Format YYYY/MM/DD
select convert(nvarchar(10),getdate(),111)

–Date Format YYYYMMDD
select convert(nvarchar(10),getdate(),112)

–Date Format DD MMM YYYY hh:mm:ss:mmm(24h)
select convert(nvarchar(24),getdate(),113)

–Date Format hh:mm:ss:mmm(24h)
select convert(nvarchar(12),getdate(),114)

–Date Format hh:mm:ss:mmm(24h)
select convert(nvarchar(19),getdate(),120)

–Date Format YYYY-MM-DD hh:mm:ss:mmm(24h)
select convert(nvarchar(23),getdate(),121)

–Date Format YYYY-MM-DDThh:mm:ss:mmm(24h)
select convert(nvarchar(24),getdate(),126)

–Time Format hh:mm PM
select substring(convert(varchar(20), GetDate(), 109), 13, 5) + ‘ ‘ + substring(convert(varchar(30), GetDate(), 109), 25, 2)

–Time Format hh:mm:ss PM
select substring(convert(varchar(20), GetDate(), 109), 13, 8) + ‘ ‘ + substring(convert(varchar(30), GetDate(), 109), 25, 2)

–Date Format YYYY.MM.DD
select convert(nvarchar(10),getdate(),102)

–Date Get Month
select month(getdate())

–Date Get Numerical Day of Month
select day(getdate())

–Date Get Year
select year(getdate())

–Date Get Last Day of Current Month
select convert(nvarchar(10),dateadd(day,-1,convert(nvarchar(2),month(dateadd(month,1,getdate()))) + ‘/1/’ + convert(nvarchar(4),year(dateadd(month,1,getdate())))),101)

–Date Get First Day of Current Month
select convert(nvarchar(10),cast(convert(varchar(2),month(getdate())) + ‘/01/’ + convert(varchar(4),year(getdate())) as datetime),101)

–Date Get Last Day of Current Year
select ’12/31/’ + convert(nvarchar(4),year(dateadd(month,1,getdate())))

–Date Get First Day of Current Year
select ’01/01/’ + convert(varchar(4),year(getdate()))

Posted in SQL Server | Tagged: , , , , , , | Leave a Comment »

Visual Studio: Display A File List In A DataGridView Control

Posted by Greg Owen on April 21, 2011

This example also shows how to extract an icon image from a file in a windows directory and display the icon in a DataGridView Control.  One advantage of displaying a list of files in the DataGridView Control, is that you can take advantage of the built in sort function and the automatic ability to Select All and Copy built into the DataGridView Control.  The functionality can be accessed by clicking ‘Ctl + A’
and ‘Ctl + C’.  After you have copied the list, you can paste into Excel or Notepad.

First Create a new Visual Studio C# Windows Application Solution.
Drag and Drop a DataGridView Control onto the form.
Right Click on the DataGridView Control and Click Properties.
Select ‘Docking’ in the Properties and select the middle square.
Click on the arrow on the DataGridView Control and Uncheck:

Enable Adding
Enable Deleting
Enable Editing

Display A Directory List In A DataGridView Control

Copy and paste the code below into the load event of the form:

            dataGridView1.AllowUserToAddRows = false;
            dataGridView1.RowHeadersVisible = false;

            DataGridViewImageColumn colIcon = new DataGridViewImageColumn();
            colIcon.Name = "colIcon";
            colIcon.HeaderText = "";
            colIcon.ImageLayout = DataGridViewImageCellLayout.Zoom;
            colIcon.DefaultCellStyle.SelectionBackColor = Color.White;
            colIcon.Width = 18;
            //colIcon.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colIcon);

            DataGridViewColumn colHold = new DataGridViewTextBoxColumn();
            colHold.Name = "colFileName";
            colHold.HeaderText = "Name";
            colHold.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft;
            colHold.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colHold);

            colHold = new DataGridViewTextBoxColumn();

            colHold.Name = "colFileSize";
            colHold.HeaderText = "Size";
            colHold.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
            colHold.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colHold);

            colHold = new DataGridViewTextBoxColumn();

            colHold.Name = "colFileType";
            colHold.HeaderText = "Extension";
            colHold.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft;
            colHold.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colHold);

            colHold = new DataGridViewTextBoxColumn();

            colHold.Name = "colDateModified";
            colHold.HeaderText = "Date Modified";
            colHold.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
            colHold.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colHold);

            dataGridView1.RowTemplate.Height = 18;

            //Replace the directory below with a directory on your computer.
            string strDirLocal = @"C:\documents";
            int nRow = 0;
            string sFileName = "";
            decimal nFileSize = 0;
            string sExt = "";
            string sDateModified = "";

            if (System.IO.Directory.Exists(strDirLocal))
            {                
                foreach (string sPath in System.IO.Directory.GetFiles(strDirLocal, "*.*"))
                {
                    //http://msdn.microsoft.com/en-us/library/ms404308.aspx
                    Icon FileIcon = SystemIcons.WinLogo;
                    FileIcon = Icon.ExtractAssociatedIcon(sPath);
                    //Add the file to the TextBox, and remove the Path from the sPath string,
                    //leaving the file name.
                    dataGridView1.Rows.Add();
                    nRow = dataGridView1.Rows.Count - 1;
                    sFileName = sPath.Replace(strDirLocal + @"\", "");
                    sDateModified = System.IO.File.GetLastWriteTime(sPath).ToString();
                    //sFileSize = System.IO.File.ReadAllBytes(sPath).ToString();
                    nFileSize = System.IO.File.ReadAllBytes(sPath).Length;
                    nFileSize = nFileSize / 1024;
                    nFileSize = System.Math.Round(nFileSize);
                    if (nFileSize < 1)
                    {
                        nFileSize = 1;
                    }
                    if (System.IO.File.ReadAllBytes(sPath).Length == 0)
                    {
                        nFileSize = 0;
                    }
                    string[] sExtHold = sPath.Split('.');
                    sExt = sExtHold[sExtHold.Count()-1];
                    //http://www.codeproject.com/KB/grid/ImagePreviewDataGridView.aspx

                    dataGridView1.Rows[nRow].Cells[0].Value = FileIcon;                                        
                    dataGridView1.Rows[nRow].Cells[1].Value = sFileName;
                    dataGridView1.Rows[nRow].Cells[2].Value = nFileSize;
                    dataGridView1.Rows[nRow].Cells[3].Value = sExt;
                    dataGridView1.Rows[nRow].Cells[4].Value = sDateModified;                    
                }
            }

Please note, if a file is open in the directory, you will receive an error.  You should add error handling to account for this error.

Related Links:

http://msdn.microsoft.com/en-us/library/ms404308.aspx

http://www.codeproject.com/KB/grid/ImagePreviewDataGridView.aspx

Export To A Text File From A DataGridView Control

Create a HTML File From a DataGridView Control

Display A Text File In A DataGridView

Display A Directory List In A TextBox Control

Posted in Visual Studio Code Examples | Tagged: , , , , , , , | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: