• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

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!

0
Keirameister
Asked:
Keirameister
  • 3
  • 3
  • 2
2 Solutions
 
YZlatCommented:
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

0
 
KeirameisterAuthor Commented:
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.
0
 
käµfm³d 👽Commented:
In your example, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=RoyalAbbeyBanking.mdb" was the connection string used. Connection strings contain variables used by the connection layer and the DB engine itself. Connection strings vary by product.

A very useful site is http://www.connectionstrings.com .
This site lists many connection strings by product (and by connection method as well).
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
YZlatCommented:
you can add this code to the same project or you can create a separate reusable component. Whichever is easier for you
0
 
KeirameisterAuthor Commented:
Thanks so much you guys for the help.
0
 
käµfm³d 👽Commented:
:)
0
 
YZlatCommented:
did you get it working?
0
 
KeirameisterAuthor Commented:
Yes thank you...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now