Connect to SQL Server Database using ASP.Net and VB.Net

 

For connecting to SQL database from asp.net, we have to import namespace System.Data.SqlClient provided by Visual Studio.

 

First, you have to define connection like this.

 

Imports System.Data.SqlClient 'place this in top of page

 

Dim strCnn As String

Dim cnn As SqlConnection

 

strCnn = = "Data Source=(local);Initial Catalog=<database name>;User ID=" + <user name> + "; pwd =" + <password>

cnn = New SqlConnection(strCnn)

 

 

 

Now, to make any transactions in database, you need to define, SqlDataCommand and SqlDataReader, SqlDataAdapter, SqlTransaction and others as per necessity.

Here, is an example.

 

Dim cmd As SqlCommand

Dim transaction As SqlTransaction

Dim rs As SqlDataReader

 

cmd = New SqlCommand()

cmd.Connection = cnn 'connection defined above

cnn.Open()

 

transaction = cnn.BeginTransaction()

cmd.Transaction = transaction

'get the customer code for new customer

 

query = "Select dbo.fnReturnCustomerCode()"

cmd.CommandText = query

rs = cmd.ExecuteReader

        If rs.Read Then

            CustomerCode = rs(0)

        End If

 

'now insert into the details of the customer

query = "Insert into tbl_customer values ('" & CustomerCode & "','" & Trim(Me.txtName.Text) & "','" & Trim(Me.txtCity.Text) & "','" & Trim(Me.txtAddress.Text) & "','" & Trim(Me.txtContactPerson.Text) & "','" & Trim(Me.txtContactNo.Text) & "')"

 

cmd.CommandText = query

cmd.ExecuteNonQuery()

transaction.Commit()

cnn.Close()