Welcome to Osama M.Yaser Site

This site is under construction

Forums

Post Reply
Forum Home > VB.Net > Using SQL statments in VB.Net

Osama M.Yaser Dweik
Site Owner
Posts: 9

Sometimes we need to execute some SQL statements in our code, then I will list here the things we must take care of and how to do it:

 

 

1- We need a connection string to be defined and ready.

2- Open the database connection.

3- We will use a command which we will assign our sql statment to.

4- Define the parameters we will use.

5- Execute the sql statment.

6- Clear the parameters.

7- Close the database connection.

 

 These steps are always needed, eventhough there are some notes I want to talk about:

 

a- Don't ever forget to close the connection, even this is not a syntax error which the editor will notice you about; but this will keep the connection opened and overtime will case some problems for the speed and good functionality of the databse, specially if your application is a web application.

 

b- Using parameter is very important rather than concatenate text to the sql statment; specially if it comes from the user input control, this will cases what we call (SQL Injection).

 

c- Be carful of the parameter type you are defining and that it comes with the field type in the database to avoid some casting errors.

 

Here is a code example of how it is done:

 

---------------------

Dim Conn As New Data.SqlClient.SqlConnection

Dim Comm As New Data.SqlClient.SqlCommand

Dim SQL As String

Conn.ConnectionString = CONN_STR   ' CONN_STR is the connection string

Comm.Connection = Conn

Conn.Open()

SQL = "UPDATE EMPLOYEE SET SALARY = SALARY * 0.1 WHERE EMPNO = @EMPNO"

Comm.CommandText = SQL

Comm.Parameters.AddWithValue("EMPNO", TXT_EMPNO.TEXT)

Comm.ExecuteNoneQuery

Comm.Parameters.Clear()

Conn.Close()

---------------------

 

Osama Dweik

 

September 27, 2010 at 5:53 AM Flag Quote & Reply

liam_w
Member
Posts: 1

vb.net ado.net tutorial step by step

http://vb.net-informations.com/ado.net/vb.net-ado.net-tutorial.htm

li.

November 25, 2011 at 12:44 PM Flag Quote & Reply

You must login to post.