How to connect Vb.net to SQL Server 2000

by | Section: SQL Server 2000, VB.NET | Posted on: February 6, 2011 | 1 Comment | 3419 Views

Since these days every body is working on ADO.net with Dataset,DataAdapter,DataReader e.t.c so what about those application which have been written using ADODB.Visual Studio.net allows you to use ADODB component  in your application.

Follwing is the step by step tutorial of how to access data from your database by using recordset through vb.net application.

Goto your Microsoft Visual Studio click file from Menu bar then new Project you will see a window similar to the following:

 

 

 

Name the application and click “OK”

Click Project from menu bar and then select Add Reference as shown below:

Add Reference: Figure 1:

Microsoft Active DataObject Libray: Figure 3:

After selecting DataObject Libray you have to import ADODB:

   1: Imports ADODB

Then you have specify a connection string

Connection String:

   1: Public DBConnection As String = "PROVIDER=SQLOLEDB;PASSWORD=***;PERSIST SECURITY INFO=true;USER ID=**;INITIAL CATALOG=***;DATA SOURCE=***"

After writing connection string to open a connection use following syntax:

   1: Dim Conn As New ADODB.Connection

   2: Conn.Open(DBConnection)

Inorder to interact with you table you have to use ADODB command and recordset through following syntax:

   1: Public rsperm As New ADODB.Recordset

   2: Public cmdperm As New ADODB.Command

After declaring ADODB Recordset and Command write your SQL Statement in the rsperm and the open rsperm recordset.

   1: cmdperm.CommandText = "select Edit,Viw,Validate from usermanagement where username='" & LoginForm.txtUsername.Text & "' and pasword='" & LoginForm.txtPassword.Text & "'"

   2: rsperm.Open(cmdperm.CommandText, DBConnection, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, -1)

you have populated your recordset with data from your table “usermanagement” now you can easily use your recoredset fields with following syntax:

   1: TextBox1.Text = rsperm.Fields.Item(0).Value.ToString

   2: TextBox2.Text = rsperm.Fields.Item(1).Value.ToString

   3: TextBox3.Text = rsperm.Fields.Item(2).Value.ToString

   4: TextBox4.Text = rsperm.Fields.Item(3).Value.ToString

Tagged: , , , , , , , , ,
  • Jaspal Sran

    Hi,

    I am new to VB.net and using ADODB to update record in Datagrid. I am getting error. some one can help me.

    Dim DPrs As New ADODB.Recordset
    Dim Sql As String

    If DPrs.State = 1 Then DPrs.Close()
    Sql = “Select * From DPMT”
    DPrs.CursorType = CursorTypeEnum.adOpenKeyset
    DPrs.LockType = LockTypeEnum.adLockOptimistic
    DPrs.Open(Sql, Conn)

    With DPrs

    .AddNew()
    .Fields(“Dept”).Value = Trim(DCode.Text)
    .Fields(“Ddesc”).Value = Trim(DName.Text)
    .Fields(“Used”).Value = “N”
    .Fields(“Dtype”).Value = (If(RB1.Checked = True, “S”, “P”))
    .Fields(“CrBy”).Value = UserID
    .Fields(“Crdt”).Value = Now.Date
    .Update()
    End With

    End If

GetSocial