How to connect to SQL server from Visual Basic

Question

_{Friday, July 12, 2013 5:24 PM}

Hi All,

I have installed Microsoft Visual Basic 2008 express edition and SQL server 2008 in my personal laptop. Since I dont have a separate database, I have used <my computer name>\SQLEXPRESS  as my server name to login to SQL server 2008. There I created databases and tables for my development. Now I am not able to connect to SQL from VB. 

First I tried as, in VB, using Data tab -> Add Data source -> New connection -> MS SQL Sever database file -> Select the database as I created in SQL server 2008 and then test the connection. Connection is tested properly. then I am not able to use that data source in my code. Not sure how to use it.

Later I tried with conenction strings which mentioned in www.connectionstring.com...but still not able to connect. Can any one please provide me how to connect to SQL server from VB?

Thanks,
Sridhar A

All replies (2)

_{Friday, July 12, 2013 7:28 PM ✅Answered}

Here is a sample using ADO.NET:

code.msdn.microsoft.com/Database-Creation-Sample

Dan Randolph - My Code Samples List

_{Friday, July 12, 2013 9:31 PM ✅Answered}

You can connect to SQL Server and query the database with the code below

        'Imports System.Data.SqlClient

        Dim con As New SqlClient.SqlConnection
        Dim strCon As String = "Data Source=SERVERNAME\SQLEXPRESS;Initial Catalog=SQLEXPRESS;Integrated Security=SSPI;Connection Timeout=10;" 'NT Authentication
        'For SQL Authentication Dim strCon As String = "Data Source=SERVERNAME\SQLEXPRESS;Initial Catalog=DATABASENAME;User ID=USERNAME;Connection Timeout=50;"
        Dim strCommand As String = "SELECT * FROM TABLE"
        Dim command As SqlCommand
        Dim da As SqlDataAdapter
        Dim dt As New DataTable
        Try
            con.ConnectionString = strCon
            command = New SqlCommand(strCommand, con)
            command.CommandTimeout = 3000

            da = New SqlDataAdapter(command)
            da.Fill(dt)
            MsgBox(dt.Rows.Count.ToString())



            'FOR INSERT, UPDATE, DELETE, USE BELOW

            'Dim count as Integer = command.ExecuteNonQuery()

            'count is the affected row number

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error")
        Finally
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try