|Forum Home > VB.Net > Using SQL statments in VB.Net|
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
SQL = "UPDATE EMPLOYEE SET SALARY = SALARY * 0.1 WHERE EMPNO = @EMPNO"
Comm.CommandText = SQL
vb.net ado.net tutorial step by step