[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

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

I learn the most when using sample code to work through step-by-step, this allows me to see the actual function of specific sections of the code.

I know there are alot of excellent programmers out there that have source code that I could use to teach myself.....

I need to keep track of a job number, and this job number needs to contain (in a databse) the name and location of the job, also track where it is stored physically on our server...this would able the salesman to look up a name and see what job that they should be working on, while the system stores the jobs data in it correct location....

To answer this question is to provide a teaching tool, not a tool to rip off some code, but purely a tool by which I can learn the in's and out's.

A working bit of code and database would be nice....I understand that everybody here is extremely knowledgable and I would never want to waste anybody's time.....

thanks again
Brian
0
brian_leighty
Asked:
brian_leighty
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Diane258Commented:
Are you asking how VB.Net can access a database?

What typed of database? an SQL Database? An orcal database? a MS Acess Database?
0
 
InteractiveMindCommented:
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;
0
 
InteractiveMindCommented:
> 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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
brian_leightyAuthor Commented:
Microsoft SQL database

0
 
TDSnetCommented:
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
0
 
brian_leightyAuthor Commented:
hey jack curious about a few things.....

why are you using northwind

B
0
 
TDSnetCommented:
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
0
 
brian_leightyAuthor Commented:
How different would making a web service (web page) do this very same function.

What would the code look like??

Thanks
0
 
TDSnetCommented:
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
0
 
brian_leightyAuthor Commented:
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.
0
 
TDSnetCommented:
Hi Brian,

As I said previously, the code will look the same.  The only thing you will have to change is the error trapping in the Catch block because the MessageBox object is not used in ASP.  Therefore, do the following:

1.  Create an ASP.NET Web Application project.  On the WebForm1 designer place a CommandButton (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 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
            Label1.Text = 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.

That's all there is to it.  The same working example running as a Web Page instead of a Windows Forms client.

Cheers,
Jack
0
 
TDSnetCommented:
I have answered this question and more...
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now