Looking for ideas how to interface a SQL database to a VB6 or .NET GUI frontend...???

Are you asking how VB.Net can access a database?

What typed of database? an SQL Database? An orcal database? a MS Acess Database?

Hello Brian,

I don't do VB myself, so I couldn't recommend anything personally, but until some of the VB Gurus' come across this thread, then perhaps have a search through these sites:

  http://www.sourceforge.net/
  http://www.planet-source-code.com/
  http://www.codeproject.com/

Have fun!
Regards;

> What typed of database? an SQL Database? An orcal database? a MS Acess Database?
Diane, I'm guessing from the thread title, that he's after an SQL database.

Microsoft SQL database

Hi Brian,

The programming interface you are looking for is called ADO (for VB6) or ADO.NET (for VB.NET).  ADO stands for ActiveX Data Objects and is the programming library provided by Microsoft for interacting with Databases via code.

Here is a very basic example using VB.NET.

1.  Create a WindowsApplication project with a form (Form1) and put a CommandButton on it (Button1) and a Label (Label1)
2.  Go to your code window and above the Class declaration add the two following lines:

     Imports System.Data
     Imports System.Data.SqlClient

2.  Double click the CommandButton from the Forms Designer which will take you to the code window in the button's Click Event.
3.  Make your code look like this:

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sqlCnn As New SqlConnection
        Dim sqlCmd As New SqlCommand
        Dim sqlAdp As New SqlDataAdapter
        Dim dsTest As New DataSet

        Try
            With sqlCnn
                .ConnectionString = "User ID=USER_NAME;Password=PASSWORD;Initial Catalog=NORTHWIND;Data Source=SERVER_NAME;Connect Timeout=30;"
                .Open()
            End With

            With sqlCmd
                .CommandType = CommandType.Text
                .CommandTimeout = 30
                .Connection = sqlCnn
                .CommandText = "Select LastName + ', ' + FirstName From Employees Where EmployeeID = 7"
            End With

            With sqlAdp
                .SelectCommand = sqlCmd
                .Fill(dsTest)
            End With

            Label1.Text = dsTest.Tables(0).Rows(0).ItemArray(0)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

4.  In the ConnectionString, replace "USER_NAME", "PASSWORD" and "SERVER_NAME" with values relevant to your environment.
5.  Run the application.

Clicking the CommandButton will select a value from the Employees Table in the Northwind Database and display it in the Label control.  This is just a very quick, very basic introduction to how you can connect to an SQL database and perform basic operations using ADO.NET.

There are absolutely tonnes of samples out there on ADO and ADO.NET particularly on those sites listed by InteractiveMind.  Just look through some of them and try applying them to what you're trying to achieve.

Cheers,
Jack

hey jack curious about a few things.....

why are you using northwind

B

Hi Brian,

Purely as an example.  You can replace Northwind with any database you have access to.  Basically the ConnectionString controls the passing of all the important details about your connection to ADO (Username, Password, Database Name, Server Name).  You can put whatever you like.

Then you just need to change the CommandText property of the SqlCommand object so that it applies to the current database.  What I have given you is a very basic example showing how a connection is achieved and how ADO interacts with the data.

I have shown you a Select example, but it is also just as easy to accomplish Update, Insert, Delete commands as well as being able to execute Stored Procedures.

Cheers,
Jack

How different would making a web service (web page) do this very same function.

What would the code look like??

Thanks

Hi Brian,

Web Services and Web Pages are two completely different things.  The code would look the same but you would put it in either an ASP.NET Web Service project or an ASP.NET Web Application project.

Cheers,
Jack

do you have any examples of a ASP.NET web application??  That will help show the difference and give me a visual picture of what I'm trying to acheive.

I have answered this question and more...