Connect Visual Basic with SQL server database

**samualres** · 28-11-2009

Hi, I want to know the steps which have to be followed while connecting the visual basic with the SQL Server 2000. I am not able to do this as we have not teach this in college. So, wanted to know right way by which I can achieve the same. Please help me in this case. I am waiting for your reply.

**kelfro** · 28-11-2009

Hello, you want to connect visual basic with sql server 2000, but which version of VB you are using? As, there are many ways to connect from each version of VB to each version of SQL Server, such as ODBC, ADODB, DSN-less, etc. Since your question does not provide enough specifics to answer adequately, I will suggest you to provide the specific question, so that the reader will able to get your question, So, please provide question in detail.

**Zecho** · 28-11-2009

Hi, to connect the SQL server 2000 with Visual Basic 6.0 USING ODBC mostly I follow the following steps:

Go to Control panel>>Admintrative Tools>>Double click.
Data sources(ODBC)>>click Add button>>Choose SQL server which is last option.
Create a New dataSource in the new screen.
Into the name textbox write anyname.
Click next button.
On the Next screen check the option With Sql server authentication.
Login Id and Password you have to enter and then Click next button>>Next>>Finish.
Click on button Test Data source.
You will get the Mesage "TESTS COMPLETED SUCCESSFULLY".
OK.

**Reegan** · 28-11-2009

Hi, I have not used yet any code to connect the SQL server with vb, but one of my friend had given me the following code, to connect the vb with sql. You can use this code to connect sql with the vb.

Code:

'Set con = New Connection
'con.ConnectionString = "Provider=SQLOLEDB.1;
Integrated Security=SSPI;Persist Security Info=False;
Initial Catalog=account;Data Source=."
'con.Open
'Set rec = New Recordset
'rec.Open "select * from acc_details", con, adOpenDynamic, 
adLockOptimistic

**opaper** · 28-11-2009

Hi, I don't know how to make connection in visual basic and sql, but I am giving you the code to connect the visual basic .net with sql. Make use of it if you want.

This code will be added to module to connected sql server with vb.net:

Code:

 Imports System.Data
      Imports System.Data.SqlClient
      Module Koneksi
      Public con As SqlConnection
      Public Function GetConnect()
      con = New SqlConnection("server = MyServerName;database = MyDatabaseName;Trusted_Connection = yes")
      Return con
      End Function
      End Module

Code to be placed in button click event:

Code:

Private Sub btnAdd_Click(ByVal send As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
      Dim test As Integer
      Dim conn As SqlConnection
      Dim cmdStud As New SqlCommand
      Dim cmdStud1 As New SqlCommand
      Dim daStudAs New SqlDataAdapter
      Dim dsStudAs New DataSet
      Dim dtStudAs New DataTable
      If txtId.text = "" Or txtFirstName.Text = "" txtLastName.Text = "" Or txtAge.Text = "" Then
      MsgBox("Student data wrong", MsgBoxStyle.OKOnly)
      Else
      If MsgBox("You really want to save dat with id : " & txtId.text & " ?", MsgBoxStyle.OKCancel, "Input confirm") = MsgBoxResult.Cancel Then
' do nothing
      Else
      conn = GetConnect()
      conn.Open()
      cmdStud = conn.CreateCommand
      cmdStud.CommandText = "SELECT * FROM StudWHERE Id='" & Trim(txtId.text) & " ' "
      daStudent.SelectCommand = cmdStud
       daStudent.Fill(dsStudent, "Student")
      dtStud= dsStudent.Tables("Student")
      If (dtStudent.Rows.Count > 0) Then
      MsgBox("Studdengan Id " & Trim(cmbId.Text) & " already in database", MsgBoxStyle.OKOnly, "Message :")
      Else
      cmdStud1 = conn.CreateCommand
      cmdStud1.CommandText = "INSERT INTO Student(Id, FirstName, LastName,Age) VALUES('" & Trim(txtId.text) & "','" & Trim(txtFirstName.Text) & "','" & Trim(txtLastName.Text) & "','" & Trim(txtAge.Text) & "')"
      test = cmdStud1.ExecuteReader.RecordsAffected()
      If test > 0 Then
      MsgBox("StudWith Id " & Trim(cmbId.Text) & " succesfully to added", MsgBoxStyle.OKOnly, "Message :")
      Else
 MsgBox("StudWith Id " & Trim(cmbId.Text) & " Failure to added", MsgBoxStyle.OKOnly, "Message :")
     End If
      Refresh_Form()
      conn.Close()
      End If
      Catch ex As Exception
     MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
      End Try
      End If
      End If
      End Sub