explicit function call in form_load

[firoz.raj]

i simple made one funciton in a module .here is the code what i have written in a module.

Code:

Function GetconnectionString() As String ' The result needs to be a string !!! 
Dim con As ADODB.Connection 
Set con = New ADODB.Connection 
       con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb") 
       GetconnectionString = "providwer=Microsoft.jet.OLEDB.4.0;" 
       End Function

and Now i want to call GetconnectionString in form1.but it is not working.
here is the code what i have written in a form1

Code:

Private Sub Form_Load() 
Combo1.AddItem "Production Consumable" 
Combo1.AddItem "Maintenance Consumable" 
Combo1.AddItem "Tools Consumable" 
Set con = New ADODB.Connection 
'con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb") 
'Label10.Caption = Date 
con.ConnectionString = GetconnectionString() 
Label10.Caption = Format$(Now, "dddd, d mmmm yyyy") 
Text1.CausesValidation = True 
End Sub

when i remove commented line and comment to con.connectionstrin=getconnectionstring() it is working fine.but i want to hardcode the path in module function. i don't
Know why it is not working .can anybody help me.any help would be greately appreciated.

[FunkyDexter]

In your Get Connection String method your creating and opening a connection but it's scope is local to the function. It dissapears as soon as that function ends (or as soon as the garbage collector picks it up, anyway).

In your form load you creating another connection, setting it's connection string to the return value of the GetconnectionString function but you never open it.

I believe what you want to do is either have GetconnectionString return the open connection itself rather than the connection string, or you want to add a con.open command to your form load and remove the code in GetConnectionString that creates and opens the connection (it's redundant).

[techgnome]

it doesn't work because of several reasons... 1) the "connection string" you are passing back from the function isn't complete. It only has the provider included. You need the whole connectionstring. Also, you are openning a connection (with the correct connection string I might add) but then doing nothing with it. Either change the function to open the connection and pass that back, or don't even both with the connection and just pass back the connection string.

In the calling sub (Form_Load) you're calling things right, but once you set the connection string, you need to open the connection before you can use it.

-tg

EDIT - also I don't see why the connection even needs to be opened in Form_Load... you don't appear to be using it.

[firoz.raj]

Now i have changed the code in module.here is the code what i have written in a module.

[techgnome]

define "not working" ... what does that mean? Do you have eels in your hovercraft? You've essentially walked into the doctor's office and said "Doc it hurts" ... WHAT hurts? WHAT's broken? Details.... do you get error messages? If so, what are they? Does something not happen that you expect to happen or does something happen that shouldn't?

You're also still not opening your connection.... I'm not even sure why you think you neeed a connection... also you're using con before creating it....

-tg

[firoz.raj]

Sir Now i got Type Mismatch Compiler Error. Error is Generating at Bold Line.
Here is the code what i have Written In a Module.

 Function GetconnectionString(ByRef con As adodb.Connection) As String  ' The result needs to be a string !!!
      con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")
End Function

[techgnome]

Why did you change it? it was fine in the previous code snip, you just weren't opening the connection.... I'm still confused as to why you're even trying to open the connection on the form load when you aren't doing anything with it.

-tg

[FunkyDexter]

I assume you've declared con at form level and I'm guessing you've declared it incorrectly. Also you're passing the connection around by ref, which is bad.

I think you're missunderstanding some of the basic concepts so let's go back a bit.

Your function GetConnectionString will return something. It can be anything you want. It can be either an actual connection or a Connection String. Given the fact that you've named it GetConnectionString and you've got a remark that it has to return a string, lets make it return the connection string. Your code is roughly correct to do that but you don't need to pass the connection in:-

Function GetconnectionString() As String  ' The result needs to be a string !!!
      con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")
End Function

OK, now you need to use that function in your form load event. But remember all it's going to do is pass you back the connection string, not the connection itself. You're going to need to create the connection and open it in the load event.:-

Private Sub Form_Load()
'First declare a variable of the apropriate type to contain your connection
Dim con as adodb.Connection
'Now set that variable to be a new connection
Set con = New adodb.Connection
'Now set the connection string of the connection to be the connection string returned by your GetConnectionString function
con.ConnectionString = GetconnectionString
'Now Open your connection
con.Open
'Now you can go ahead and do anything else you want to.

I hope that helps but you really need to get head around a few basic concepts.
1. Always declare your variables. Never turn option explicit off. This lets vb understand what it's dealing with.
2. Functions return something. This can be as simple as a number or as complex as a fully opened and set up database connection. You decide what you want them to return and you program them to return what you want.
3. A connection needs 2 things. It needs a conection string which tells it how to connect to a database and it needs to be opened. It's the open command that actually makes it connect.

Then there's a few pointers to good practice that you should take on board.
1. Don't pass variables by ref. It leads to spaghetti code. (there are performance benefits to By Ref but they're far outweighed by the downsides)
2. If you're going to open your connection in your form load event then make sure you close it in your form close event or at the end of your form load event. This is acceptable (IMO) in a single form application or if you're going to carry out some operations in that event and then close it. Generally though, you should follow one of two strategies:-
2.1 Open the connection when the application starts and use that connection throughout your aplication or
2.2 Open a connection when you need it and close it again immediately afterwards
Generally I'd recommend 2.1 if you're using VB6 or earlier and 2.2 If you're using VB.Net.

[firoz.raj]

Open the connection when the application starts and use that connection throughout your aplication or
2.2 Open a connection when you need it and close it again immediately afterwards
Generally I'd recommend 2.1 if you're using VB6 or earlier and 2.2 If you're using VB.Net.

Open the connection when the application starts and use that connection throughout your aplication .that is ok But why not this is applicable in the case of vb.net let me know please.

Public Function CreateConnection() As Boolean
        Try
            ' Dim conn As New System.Data.OleDb.OleDbConnection
            conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Imp\product_table.mdb")
            conn.Open()

            If conn.State <> ConnectionState.Open Then
                MessageBox.Show("Connection is Not Open")

                Return False
                Exit Function
            Else
                Return True
                Exit Function
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
    End Function
    Public Sub Closeconnection()
        If conn.State = ConnectionState.Open Then
            conn.Close()
            conn.Dispose()
        End If
    End Sub
End Class

[techgnome]

In previous versions of VB, everything was in a "connected" state. With ADO.NET, the opposite of it is true. Everything works in as disconnected state. This means that all changes are done locally and held until specifically sent to the server. Also, in these situations you no longer need to care about if a connection is open or not and test for it. You should be able to automatically assume that the connection isn't there. You open it, do your database queries, close the connection.

-tg

[techgnome]

This is why I don't respond normally to PM's.... things get lost... against my better judgement:

In previous versions of VB, everything was in a "connected" state

means in vb.net or c#.net everything is available in disconnected state.how dataset ,datareader is available in disconnected state.let me clarify please.this is very important to me.

In VB6, when you made a change to a recordset, that recordset by default was connected to the database... you make a change in the recordset, it gets pushed to the database. This is "connected".

With .NET, that is no longer true. When you make a change to a datatable (you CANNOT EDIT a datareader... datareaders by nature are READ ONLY)... the change remains in the datatable. You have to specifically and explicitly call a method (like .Update) to actually handle the edits and send them to the database.

I'm not sure how much clearer I can be about this.

-tg