VB.NET: How to connect to SQL Server

This topic is a part of VB.NET SQL Database Basics Tutorial series. On this article, you will learn how to connect to any version of SQL server using VB.NET. For programmers, database programming is a must if your working on data oriented companies. Before you can insert, update or query records from database, you must first know how to connect to it and this is my objective for this article. At the end of this article, you should be able to know how easy to connect to SQL server.


Note: I assumed that you already installed your SQL Server and Visual Studio .NET.

Here is the step-by-step procedure to connect to SQL server:
1. Create your VB.NET project.
2. Include the following namespaces.

Imports System.Data
Imports System.Data.SqlClient

The System.Data namespace provides access to classes that represent the ADO.NET architecture while the System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.
3. Declare and instantiate your SQLConnection object as shown below

Dim con As New SqlConnection

SQLConnection class represents an open connection to a SQL Server database.
4. Pass the SQL connection string to ConnectionString property of your SqlConnection object.

con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"

The connectionstring value usually contains the following :
Data Source - physical server hostname
Initial Catalog - your database name
User ID - SQL username use to connect to the server
Password - SQL username's password

On this sample, I am using an SQL Server 2005. For the connectionstring for other SQL version, you can get it from here http://www.connectionstrings.com/.

5. Last step is to invoke the Open method of the connection object

con.Open()

The complete sample sourcecode:

Imports System.Data.SqlClient
Imports System.Data

Private Sub ConnectToSQL()
Dim con As New SqlConnection
Dim cmd As New SqlCommand

con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
con.Open()
End Sub

To capture if the connection was successful or not, just tweak the above code:

Imports System.Data.SqlClient
Imports System.Data

Private Sub ConnectToSQL()
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
con.Open()
Catch ex As Exception
MessageBox.Show("Error while connecting to SQL Server." & ex.Message) Finally
con.Close() 'Whether there is error or not. Close the connection.
End Try
End Sub

Related Topic: VB.NET Tutorial: SQL Database Basics

VB.NET Tutorial: SQL Database Basics

On this tutorial, you will learn how to INSERT, UPDATE, and DELETE records to Microsoft SQL Server Database. The target audience of this topic are beginners. I will be using my own coding technique which I use in real life experience as a programmer. Before you can start, what you need is an SQL Server installed. Sample sourcecodes used throughout this tutorial series are compatible with either SQL sever versions: SQL 2000, SQL 2005 and SQL 2008.


Upon completion this tutorial series, you should be able to:
1. Know how to connect to SQL server
2. Know how to use how to query from SQL server using SqlDataReader class
3. Know how to Insert, Update and Delete Records.

Assumptions:
I assumed that you already performed the following:
1. Installation of SQL Server
2. Creation of SQL Database
3. Installation of Visual Studio 2005 or higher (Express/Professional/Enterprise)


Table Of Contents:
Topic 1: How to connect to SQL Server
Topic 2: How to SELECT records from SQL Database
Topic 3: How to INSERT record to SQL Database
Topic 4: How to UPDATE record on SQL Database
Topic 5: How to DELETE record on SQL Database

C#: Eject or Undock a Laptop PC

Here is a worth sharing tip from Noah Coad of MSDN blogs. This code is worth to try if you have a mobile computer such as laptop and notebook with a docking station and you want the PC to eject from the station using C# or command line. So instead manually hitting the undock button and removing the PC and put it to sleep, you can compile the C# code to form as EXE and double-click it to eject the laptop from dock station for you.


The code uses DLL import from "cfgmgr32.dll" and the System.Runtime.InteropServices namespace.

using System;
using System.Runtime.InteropServices;

namespace EjectPC
{
 class Program
 {
  [DllImport("cfgmgr32.dll", SetLastError = true)]
  static extern int CM_Request_Eject_PC();

  static void Main(string[] args)
  { CM_Request_Eject_PC(); }
}
}

You can also do the same task from the command line

rundll32 cfgmgr32.dll,CM_Request_Eject_PC

This code was originally posted from Noah Coad's blog.

To stay up-to-date on Technology news, subscribe now.

Behold Mac and Linux users. Meet Google Chrome

Good news to Mac and Linux users, the waiting period for Google Chrome is almost officially over. On Thursday, the search giant releases the developer channel version of Google Chrome in Mac and Linux machines. Google forewarned eager users not to download incomplete and buggy version unless you are a developer that love to take risks. According to Google blog, the browser is incomplete that you won't yet be able to view YouTube videos, change your privacy settings, set your default search provider, or even print.




Although its almost useless for ordinary internet users, the public announcement is still a good sign that we are soon going to see stable well supported builds of the Google browser for both Mac and Linux.


To stay up-to-date on Technology news, subscribe now.

How to list field or column names in SQL

My brother was writing a code of a search module as part of his inventory system project. The search module should provide options to users to select the field to search from. For example, I should be able to search Product database using ProductCode, ProductName or Part Number. What he decided to do is to list all available fields in the table and put it on a combobox. So his challenge was, How to select all column names in SQL? (He is using SQL Server).


SQL server database has a syscolumns table which contains columns of each table in the database. You can get the column name by using the following query.

SELECT [Name] FROM syscolumns
WHERE id=OBJECT_ID(N'dbo.tblProducts')
ORDER BY colid

To call it from a VB.NET application, you can use SqlDataReader the same way you do it on a ordinary table.

cmd.CommandText = "SELECT [Name] FROM syscolumns WHERE id=OBJECT_ID(N'dbo.tblProducts') ORDER BY colid"

Dim lrd As SqlDataReader = cmd.ExecuteReader()
cboProducts.Items.Clear()
While lrd.Read()
cboProducts.Items.Add(lrd("[Name]").ToString())
End While

To stay up-to-date on Technology news, subscribe now.

Bing Travel has just arrived

Following the earlier release of Microsoft Bing, the software giant rolled-out its latest vertical- the Bing Travel. It is one of Microsoft answer to traditional search engines which mostly provide information and links instead of tools that help visitors make more informed decision quicker. Bing Travel consist of tools and features like Price Predictor, Travel Deals, Rate Indicator, Comparison Flight & Hotel Search, Fare Alerts and travel editorial content.




Bing Travel is here to help consumers make smart travel decisions. In addition to comprehensive flight and hotel search, Bing Travel leverages Farecast technology to provide airfare Price Predictors and hotel Rate Indicators. The Farecast technology was recognized as the 2008 Webby Award Travel category winner, named Condé Nast Traveler's "Best Site for Knowing When to Book" in 2008, named one of Travel + Leisure's Top 25 Travel Websites in 2007, awarded Budget Travel's 2007 Extra Mile Award, named one of PC World's 20 Most Innovative Products for 2006, listed in "Best of What's New 2006" by Popular Science, named one of Time Magazine's "50 Coolest Websites" in 2006, and named one of the "Best Trip Planning Tools" by BusinessWeek readers in 2006.

To stay up-to-date on Technology news, subscribe now.

C#: How to remove Characters from String

A common friend emailed me yesterday asking for help. He is a VB.NET beginner who wants to try C# programming. His request is a sample source code that demonstrate how to remove characters from string. Below is a string manipulation source code that I previously used on my old project, I tweaked it a little bit to fit his requirement.



The following sourcecode can strip charaters from string. For example: removing "F9" from "FRYAN0911" will give you "RYAN011"

public static string RemoveChars(string s, string myChars)
{
int i = 0, j = 0;

int CharLength = myChars.Length;
int StringLength = s.Length;
int[] intCollection = new int[256];
char[] s2 = new char[StringLength];

for (i = 0; i < CharLength; i++)
{
intCollection[myChars[i]] = 1;
}

i = j = 0;
for (i = 0; i < StringLength; i++)
{
if (intCollection[s[i]] != 1)
{
s2[j] = s[i];
j++;
}
}

return new string(s2);

}

For more C# coding tips, subscribe now

Spreadsheet Web Search with Google Squared

Google Squared, the so called answer of Google to Wolfram Alpha is now live. Google Squared is an experimental search engine that gives you organized search results broken down by categories which they call "square" of information. User can modify the starter square by removing rows and columns they don't like. You can add new rows and columns and having Google Squared attempt to fetch the relevant facts. If you're happy with your square you can save it and come back to it later. Google Squared does the grunt work for you, making research fast and easy.


For example, I tried to search for the keyword "cleveland cavaliers" and It returns me a spreadsheet result of cavaliers team roster including inactive players.



I modified the keyword to "cleveland cavaliers 2009" hoping to fetch only active roster but instead it provided me unrelated results.



Although it seems the technology still needs lots of refinements to turn it into a useful tool, I still give it "4 stars out of 5" for the the idea.

To stay up-to-date on Technology news, subscribe now.

Opera 10 is now on Beta

Today, Opera has just release the BETA version of the upcoming release version of its web browser. Opera 10 Beta is available for Windows, Mac and Linux for free. The Beta version showcases a number of interesting updates, including Opera Turbo for slow connections, New visual tabs and sleek design, Speed Dial enhancements, and 40% faster engine. Some other worth to mention features are Inline spell-check (better blogging experience for me), and enhanced Web developer debugging tool.


The most revealing feature in Opera 10 is its "Turbo Mode". Mobile users of Opera (Mini) are the direct benefactor of this technology. It uses compression technology that provides significant improvements in browsing speeds over limited-bandwidth connections like a crowded Wi-Fi in a cafe or browsing through your mobile phone while commuting.



It can load images considerably faster.



To stay up-to-date on Technology news, subscribe now.

How to get the server name and user name you are currently logged-in on a SQL server

Most of SQL administrators do multi-tasking on different SQL servers at the same time. So sometime it is confusing if your have several SQL Server Management Studio (SSMS) windows opened. Executing a right SQL statement on a wrong server could be fatal to your organization. So before you start clicking that little green play button on your SSMS toolbar, you might want to practice checking first what server you are currently logged-in with this useful code.


To check what server you are current logged in and the username you are using, run the following on your SSMS script window:

SELECT @@SERVERNAME() AS ServerName, SUSER_NAME() Username

For more SQL Coding Tips and Tricks, subscribe now

YouTube on your TV Screens

This morning YouTube unveils its own TV-sized interface called YouTube XL. In a nutshell, YouTube XL is a YouTube with a different skin and optimized for TV. It is made to be simple, easier to use on big screens. Because it's optimized for the big screen, It's a stripped down player where there are no scroll bars, comments and other community features.



You can access YouTube XL here. YouTube XL will work on any internet-enabled device and on any browser. For example you can watch YouTube XL on TVs hooked up to laptops or video game consoles, such as Xbox 360 or Sony PlayStation 3.




Currently, YouTube XL UI is so clean due the fact that it currently doesn’t have any ads but of course I expect that this can change (free service comes with a price).

To stay up-to-date on Technology news, subscribe now.

How to change target framework in Visual Studio 2008

One of the biggest change on the Visual Studio 2008 is that it already support "Multi-Targeting" which means that you can create projects in Visual Studio using multiple versions of the .NET Framework. Developers will now be able to open an existing project or create a new one with VS 2008 no matter what version of the .NET Framework was used. Features, controls, projects, item-templates, and assembly references that don't work with that version of the current open project framework will be hidden, and when you build your application you can choose the framework of the compiled output and copy it onto a machine that has only the same .NET Framework installed.


If you are creating a new project, you can select the target framework on the drop-down located at the right-corner of the new project dialog box as shown below.




To upgrade an existing project, you can right-click on the project in the solution explorer and clicks properties page:




For more coding tips and tricks, subscribe now

Bing it on! Microsoft Bing is now live

Three (3) days ago, I posted the soon-to-launch plan of Microsoft to bring on Microsoft Bing to search engine war. Today, it is officially launched to the public to test. Some interesting features of bing is it can auto-plays videos in results when you hover over them so user can take peek of the video without going to the actual site. The images search was also better (more relevant) than what I was getting at Google, It also provide you more information about the image when you hover on it.


Here are some screenshots of my testing of Microsoft Bing.

Search Results


Image Results


Video Results


Final thoughts. Although Microsoft Bing will not surpass Google overnight, It still best for the internet. Competition like this will just make everything better. So try it for yourself, Bing It on!

To stay up-to-date on Technology news, subscribe now.

How to read multiple files in a directory using LINQ

On my previous blog entries, I already have discussed How to read XML, CSV and SQL using LINQ. For my today's entry, I will try to show you another coding trick with the use of LINQ: How to read multiple files in a directory using Language Integrated Query. I also prepared a sample C# project associated to this blog entry, the download URL is posted below.


To read multiple files in a directory using LINQ:

1. Include the following namespaces on your C# project.

using System.Linq; //LINQ capability
using System.Text; //For string builder
using System.IO; //File and directory handling

2. To reading multiple files, use the following LINQ code.

var filecontent = Directory.GetFiles(@"c:\test\")
.Where(filename => filename.EndsWith(".txt"))
.Select(filename => File.ReadAllText(filename))
.Aggregate(
new StringBuilder(),
(sb, s) => sb.Append(s).Append(Environment.NewLine),
sb => sb.ToString()
);

3. Now compile and test run your project.


Here is the complete source code of the C# project

static void Main(string[] args)
{
var filecontent = Directory.GetFiles(@"c:\test\")
.Where(filename => filename.EndsWith(".txt"))
.Select(filename => File.ReadAllText(filename))
.Aggregate(
new StringBuilder(),
(sb, s) => sb.Append(s).Append(Environment.NewLine),
sb => sb.ToString()
);


Console.WriteLine(filecontent);
Console.ReadKey();
}

You can also download the C# project for this article here.

For more LINQ coding tips, subscribe now

How to backup your Blogger blog

If you have blogs hosted at blogspot.com there are several ways to backup your posts. The first method was orginally posted at googlesystem blog. By accessing the following blog URL: http://blogname.blogspot.com/search?max-results=N, where N is equal to number of posts from the blog. If your blog has less than 1000 posts, you can save this page: http://blogname.blogspot.com/search?max-results=1000


You can also download all the photos uploaded to your blog by using a firefox extension called DownThemAll. The Firefox extension lets you download all the files with a certain extension from the current page, so it's a good way to download all the images from the previous listing.



The second method to backup your blog is to use the Export feature in Blogger that can be accessed from the Settings tab of your Blogger dashboard. To export your entire blog, follow these steps.
1. Login to your Blogger account.
2. Go to Settings > Basic.
3. From Blog Tools, click Export blog.
4. Then on Export blog page, click the download blog button.



For more Blogging Tips and Tricks, subscribe now