asked on

How do I connect a Windows Application (vb.net) to SQL Server DB...in visual studios

Hey Experts...

Thanks soooo much in advance for any help you can throw my way on this its really appreciated!

I am doing a project at the and i cant figure out for the life of me how to connect the DB (SQL server) to the Application (vb.net windows app).

I have the code to do it using an Access DB.... and the following code is contained in the module of the project!!

'declaring the database connection and database modules!
Friend objConnection As New OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=RoyalAbbeyBanking.mdb")

Friend objCustomerDA As New OleDb.OleDbDataAdapter("Select * from CustomerTable", _
objConnection)

Friend objCustomerCB As New OleDb.OleDbCommandBuilder(objCustomerDA)

Friend objDataSet As New DataSet()

Friend objAccountDA As New OleDb.OleDbDataAdapter("Select * from AccountTable",
objConnection)

Friend objAccountCB As New OleDb.OleDbCommandBuilder(objAccountDA)

But i was wondering if anyone could give me a step by step on how to do this for a SQL Database... is it the same or does visual studios allow this to be done in the background.. I'm so confused!

YZlat

use two functions below. Then, all you need is sql query and a connection string, you pass those to GetDataSet function, which, in turn, creates a Sql connection and returns data requested by your sql query in form of a dataset

Public conn As SqlConnection
    Function GetConnection(ByVal strConn As String) As SqlConnection
        Try
            conn = New SqlConnection(strConn)
            conn.Open()
        Catch ex As SqlException
            Console.Write("SQL ERROR: " & ex.Message)
        Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
        End Try
        GetConnection = conn
    End Function
    Function GetDataSet(ByVal strConn As String, ByVal query As String) As DataSet
        Dim dset As New DataSet
        Try
            conn = GetConnection(strConn)
            Dim da As SqlDataAdapter = New SqlDataAdapter(query, conn)
            ''make sure command does not timeout
            da.SelectCommand.CommandTimeout = 0
            ''fill dataset
            da.Fill(dset)
        Catch ex As SqlException
            Console.Write("SQL ERROR: " & ex.Message)
        Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try
        GetDataSet = dset
    End Function

Open in new window

Keirameister

ASKER

Hey thanks for that...

So do you suggest that I open a new project and place this where???

What do you mean by sql query and connection string .. are they generated or do i have to write these also?

Thanks again.

ASKER CERTIFIED SOLUTION