New Programmer - VB.NET and MySQL

[xreyuk]

Hi All,

I'm a fairly new programmer so you'll have to bear with me on this.

Basically, I have a database with 3 tables, Engineers, Equipment and Signouts. I want to create a VB application that allows engineers to sign out equipment. I have all of the forms setup the way I want, and have followed a tutorial online for how to connect to the database with the MySQL .NET Connector.

I have 3 forms, and have a class with all of my global variables in it.

All I have managed to thus far, is define connStr as a global variable, and use it to run a connection test when the main form starts up.

My next job is to query for information about an engineer based on their ID. I have a text input box, and a button to click, and then way to display the retreived information on the next page. I have setup variables for the returned values to be put against, so they can be displayed on the form.

My question is, when the button is clicked, how do I make the application query the database based on the ID, and then use the information it returns (Engineers ID, Name and Telephone Number) to assign those values to the variables?

Also, on a side note, instead of using forms, I want to use panels, is there a sub routine similar to form_load for using panels when they are shown/hidden?

Thanks for any help!

[Pgmer]

You can write the stored procedure in ur sql server and using ADO.NET objects you can query and get the information, or else in your code only you can write the query.
you need code or just an idea?:)

[xreyuk]

Code would help please! I'm really out of my depth. Thanks for the response.

Which is the best way of doing it, and which is the easiest way of doing it?

[xreyuk]

Here is the code I have so far.

This is on my GlobalVariables Form

Imports MySql.Data.MySqlClient

Public Class GlobalVariables
     Public Shared EngID As String
     Public Shared PartID As String
     
     Public Shared connStr As String = "Database=Database1;" & _
                "Data Source = localhost;" & _
                "User ID = root;Password = Password123"
     Public Shared connection As New MySqlConnection(GlobalVariables.connStr)
End class[/B]

Then on my main form.

[B]Imports MySql.Data.MySqlClient

Public Class Main

     Private Sub Main_Load
          TestConnection()
     End sub

     Private Sub TestConnection()
        Try
         GlobalVariables.connection.Open()
         MsgBox("Connection is Okay")
         GlobalVariables.connection.Close()
        Catch ex As Exception
         MsgBox(ex.Message)
        End Try
     End sub
End Class

[Pgmer]

Dim cn As SqlConnection
        Dim strCnn As String ="Your connection string"
        cn = New SqlConnection(strCnn)
        cn.Open()
        Dim comm As New SqlCommand("Ur procedure name", cn)
        comm.CommandType = CommandType.StoredProcedure
        Dim da As New SqlDataAdapter(comm)
        Dim ds As New DataSet
        da.Fill(ds)

Then use the data set to assign to ur variables

[Shantanu88d]

you can either use stored procedures or use sql queries in your code directly. U have to decide, if ur queries are complex with lots of parameters then use stored procedures. but if u just want to get info about an engineer on his ID, then u can just write a query and use

cmd.CommandText = "Select * from Engineers where ID = " & EngineersID 
adapter = new SqlDataAdapter(cmd)
adapter.SelectCommand.Connection = connectionObject
adapter.fill(datasetObject)

now you can get your data from dataset object

[Pgmer]

Hi Shantanu,

I Agree with you, but still the good practice is to write the stored procudures rather than writing the query in front end. and as xreyuk is new to programing let him start with best practices only..

Regards
PGMER

[Shantanu88d]

@pgmr: You are correct, I agree with you. Writing queries in stored procedures is a best practice. Helps to cope with complexities and separates database part business logic from presentation logic and codes

[xreyuk]

Thanks a bunch for the responses guys!

I'm a little bit confused by the stored procedure way. Is this creating the query, within the SQL and then calling it? Also, does I need to use MySqlConnection or can I just use SqlConnection?

How would I use the dataset to assign to variables? I have 3 columns, EngineerID, Name and TelNo, so how would I assign each to their own variable using the method pgmer described? Can I use adapter and dataset for different functions in the program, or each time I do a query do I need to use a different adapter and dataset.

@pgmer - would you be able to tell me what each step in your code is doing?

Sorry if it's frustrating but I am really new and taking baby steps!

[Pgmer]

'' Declare and assign connction to SQL Server
        Dim cn As SqlConnection
        Dim strCnn As String = "Your connection string"
        cn = New SqlConnection(strCnn)
        Try


            ' Open up the connection
            cn.Open()
            ''Declare command object and pass commandtext and Connection to Command Object
            Dim comm As New SqlCommand("pr_GetData", cn)
            ''Set commandtype=Stored Procedure as ur passing the command text as SP
            comm.CommandType = CommandType.StoredProcedure
            '' Add parameter to SP
            comm.Parameters.Add("@Engg_Id", SqlDbType.Int)
            ''Pass the value 
            comm.Parameters("@Engg_Id").Value = "Pass your Eng Id"

            ''Declare Dataadapter 
            Dim da As New SqlDataAdapter(comm)
            Dim ds As New DataSet
            'Fill dataset with result set
            da.Fill(ds)

        Catch ex As Exception
            ''Handle Exception if any
        Finally
            ''Check if connection is open if yes please close it.
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

And Here is ur SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE pr_GetData 
	@Engg_Id as int
AS
BEGIN
	Select * from Engineers where ID =@Engg_Id 
END
GO

Instead of * please specify the column names you want get from server.

Please let me know if you need any help.
Happy coding.. :)