›

Connection

lucky_melucky_me
in Visual Basic
Hi all
I wanna know how to create a connection globally in VB? I want to create a global function and use it in all oer application (same project) to connect to the database. Database would be Oracle. Can anyone please give me the idea please.

Thanks in advance.

Comments

  • infidelinfidel
    : Hi all
    : I wanna know how to create a connection globally in VB? I want to create a global function and use it in all oer application (same project) to connect to the database. Database would be Oracle. Can anyone please give me the idea please.
    :
    : Thanks in advance.

    Well, you can either put a public function in a module, or you could create a class that encapsulates the connection object, or you could just have a public variable of type ADODB.Connection in a module and use the same connection object everywhere instead of a function that creates new connections all the time.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

  • lucky_melucky_me
    [b][red]This message was edited by lucky_me at 2006-2-8 18:18:56[/red][/b][hr]
    : : Hi all
    : : I wanna know how to create a connection globally in VB? I want to create a global function and use it in all oer application (same project) to connect to the database. Database would be Oracle. Can anyone please give me the idea please.
    : :
    : : Thanks in advance.
    :
    : Well, you can either put a public function in a module, or you could create a class that encapsulates the connection object, or you could just have a public variable of type ADODB.Connection in a module and use the same connection object everywhere instead of a function that creates new connections all the time.
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    : [code]
    : $ select * from users where clue > 0
    : no rows returned
    : [/code]
    :
    :
    Thank you so much Infedel :)
    I created the Module and it's corresponding functions and Global connections but was doing a silly mistake while calling the global function.It can't open the database.Shows an erorr " Cand find C:mydb.mdb"

    Please help me in finding out the reason of this error.

    Thank you so much for your quick reply as always :-)


    Here is my code :
    *****************
    Option Explicit
    Public GCon As New ADODB.Connection


    Public Function ConnectString()

    ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydb.mdb;User Id=admin;Password=;"
    End Function



    Public Function Open_Connection(oConn As Connection) As Boolean
    On Error GoTo open_error_handler


    oConn.CursorLocation = adUseClient
    oConn.ConnectionString = ConnectString()

    oConn.Mode = adModeReadWrite
    oConn.Open ConnectString

    Open_Connection = True ' Connection successfully open
    Exit Function

    ' Exception handling
    open_error_handler:
    Call ErrorHandler(GCon)
    Open_Connection = False
    Exit Function

    End Function

    ************************
    I am calling these as :


    Private Sub cmd_show_Click()
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim SQL_String As String

    SQL_String = "select * from customer"

    rs.Open SQL_String, Open_Connection(GCon), adOpenDynamic, adLockOptimistic, adCmdText


    End Sub






  • lucky_melucky_me
    : [b][red]This message was edited by lucky_me at 2006-2-8 18:18:56[/red][/b][hr]
    : : : Hi all
    : : : I wanna know how to create a connection globally in VB? I want to create a global function and use it in all oer application (same project) to connect to the database. Database would be Oracle. Can anyone please give me the idea please.
    : : :
    : : : Thanks in advance.
    : :
    : : Well, you can either put a public function in a module, or you could create a class that encapsulates the connection object, or you could just have a public variable of type ADODB.Connection in a module and use the same connection object everywhere instead of a function that creates new connections all the time.
    : :
    : :
    : : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    : :
    : : [code]
    : : $ select * from users where clue > 0
    : : no rows returned
    : : [/code]
    : :
    : :
    : Thank you so much Infedel :)
    : I created the Module and it's corresponding functions and Global connections but was doing a silly mistake while calling the global function.It can't open the database.Shows an erorr " Cand find C:mydb.mdb"
    :
    : Please help me in finding out the reason of this error.
    :
    : Thank you so much for your quick reply as always :-)
    :
    :
    : Here is my code :
    : *****************
    : Option Explicit
    : Public GCon As New ADODB.Connection
    :
    :
    : Public Function ConnectString()
    :
    : ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydb.mdb;User Id=admin;Password=;"
    : End Function
    :
    :
    :
    : Public Function Open_Connection(oConn As Connection) As Boolean
    : On Error GoTo open_error_handler
    :
    :
    : oConn.CursorLocation = adUseClient
    : oConn.ConnectionString = ConnectString()
    :
    : oConn.Mode = adModeReadWrite
    : oConn.Open ConnectString
    :
    : Open_Connection = True ' Connection successfully open
    : Exit Function
    :
    : ' Exception handling
    : open_error_handler:
    : Call ErrorHandler(GCon)
    : Open_Connection = False
    : Exit Function
    :
    : End Function
    :
    : ************************
    : I am calling these as :
    :
    :
    : Private Sub cmd_show_Click()
    : Dim rs As ADODB.Recordset
    : Set rs = New ADODB.Recordset
    : Dim SQL_String As String
    :
    : SQL_String = "select * from customer"
    :
    : rs.Open SQL_String, Open_Connection(GCon), adOpenDynamic, adLockOptimistic, adCmdText
    :
    :
    : End Sub
    :
    :
    :
    :
    Please help me :-(
  • lionblionb
    Please help me :-(
    :
    [blue]ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydb.mdb;User Id=admin;Password=;" [/blue]

    First of all, check the Path. Seearch your machine for mydb.mdb file. You probably saved it somewhere else like C:MyDocumentsmydb.mdb

    You can also try this
    [code]
    ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" [blue]& App.Path & [/blue]"myDb.mdb;" & _
    "User Id=admin;" & _
    "Password="
    [/code]
    Where [blue]App.Path[/blue] is path where you saved your application
  • infidelinfidel
    : Thank you so much Infedel :)
    : I created the Module and it's corresponding functions and Global connections but was doing a silly mistake while calling the global function.It can't open the database.Shows an erorr " Cand find C:mydb.mdb"
    :
    : Please help me in finding out the reason of this error.
    :
    : Thank you so much for your quick reply as always :-)
    :
    :
    : Here is my code :
    : *****************
    : Option Explicit
    : Public GCon As New ADODB.Connection
    :
    :
    : Public Function ConnectString()
    :
    : ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydb.mdb;User Id=admin;Password=;"
    : End Function
    :
    :
    :
    : Public Function Open_Connection(oConn As Connection) As Boolean
    : On Error GoTo open_error_handler
    :
    :
    : oConn.CursorLocation = adUseClient
    : oConn.ConnectionString = ConnectString()
    :
    : oConn.Mode = adModeReadWrite
    : oConn.Open ConnectString
    :
    : Open_Connection = True ' Connection successfully open
    : Exit Function
    :
    : ' Exception handling
    : open_error_handler:
    : Call ErrorHandler(GCon)
    : Open_Connection = False
    : Exit Function
    :
    : End Function
    :
    : ************************
    : I am calling these as :
    :
    :
    : Private Sub cmd_show_Click()
    : Dim rs As ADODB.Recordset
    : Set rs = New ADODB.Recordset
    : Dim SQL_String As String
    :
    : SQL_String = "select * from customer"
    :
    : rs.Open SQL_String, Open_Connection(GCon), adOpenDynamic, adLockOptimistic, adCmdText
    :
    :
    : End Sub

    Well I can't help you with the location of your database file, but I can point out a couple of other problems you have. First, Open_Connection returns a Boolean, which is no good to a Recordset object.

    Another thing is that if you're going to have a global connection object, it doesn't make much sense to open it every time you're going to use it. A) it will generate some kind of error, I am sure, and B) it's just a waste. Open the connection once and then just keep using it until you're done.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

  • lucky_melucky_me
    Hi Infedel,
    Thanks for the informative reply. I was trying to use global connection so that I can change the table as well as database on same form.
    My databse path name is correct.I tried to use this code to connect with oracle (except access I used ODBC connection for oracle) And getting the errors that can't connect to the ODBC. When I am using Data bound control for both Access and Oracle, It shows data.And ODBC and JET Engine connection seems correc.But it gives error when I try to connect programatically.

    One question infedel... Why boolean is not good for record set?
    I am totally new in VB and have no idea how it works.Got a chance to do a project in VB so just trying that :-(

    Once again Thanks alot for your reply.Everytime I learn so much from you.

    Regards
    Lucky

    :
    : Well I can't help you with the location of your database file, but I can point out a couple of other problems you have. First, Open_Connection returns a Boolean, which is no good to a Recordset object.
    :
    : Another thing is that if you're going to have a global connection object, it doesn't make much sense to open it every time you're going to use it. A) it will generate some kind of error, I am sure, and B) it's just a waste. Open the connection once and then just keep using it until you're done.
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    : [code]
    : $ select * from users where clue > 0
    : no rows returned
    : [/code]
    :
    :

  • lucky_melucky_me
    Thanks Lion,
    My database path is correct. It gives error with ODBC connection for Oracle also.y DSN is working but still getting error :-(

    Regards
    Lucky

    :
    : First of all, check the Path. Seearch your machine for mydb.mdb file. You probably saved it somewhere else like C:MyDocumentsmydb.mdb
    :
    : You can also try this
    : [code]
    : ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    : "Data Source=" [blue]& App.Path & [/blue]"myDb.mdb;" & _
    : "User Id=admin;" & _
    : "Password="
    : [/code]
    : Where [blue]App.Path[/blue] is path where you saved your application
    :

  • lionblionb
    : Thanks Lion,
    : My database path is correct. It gives error with ODBC connection for Oracle also.y DSN is working but still getting error :-(
    :
    : Regards
    : Lucky
    :
    I think I found the problem
    Yours code
    [code]
    oConn.CursorLocation = adUseClient
    oConn.ConnectionString = ConnectString()[blue] You do not need that [/blue]

    oConn.Mode = adModeReadWrite
    oConn.Open ConnectString
    [/code]
    Change it to this way
    [code]
    oConn.Open ConnectString ()
    oConn.CursorLocation = adUseClient
    oConn.Mode = adModeReadWrite

    Public Function ConnectString() as String
    ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Dataource=C:mydb.mdb;User Id=admin;Password=;"
    End Function
    [/code]



  • infidelinfidel
    : One question infedel... Why boolean is not good for record set?

    The recordset object needs a connection object passed to it, but your function returns a boolean.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

  • lucky_melucky_me
    : : Thanks Lion,
    : : My database path is correct. It gives error with ODBC connection for Oracle also.y DSN is working but still getting error :-(
    : :
    : : Regards
    : : Lucky
    : :
    : I think I found the problem
    : Yours code
    : [code]
    : oConn.CursorLocation = adUseClient
    : oConn.ConnectionString = ConnectString()[blue] You do not need that [/blue]
    :
    : oConn.Mode = adModeReadWrite
    : oConn.Open ConnectString
    : [/code]
    : Change it to this way
    : [code]
    : oConn.Open ConnectString ()
    : oConn.CursorLocation = adUseClient
    : oConn.Mode = adModeReadWrite
    :
    : Public Function ConnectString() as String
    : ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Dataource=C:mydb.mdb;User Id=admin;Password=;"
    : End Function
    : [/code]
    Hi Lion,
    It was not working.Finally I upgraded my Windows XP OS and now all my function is working fine.Withought any changing in code. Tha's really wiered to me

    But yes I got one error while terminating function. It was giving Run time error : 3001


  • lucky_melucky_me
    I upgraded my XP Os and everything started working but function was unable to terminate. I reallized that it was because of Boolean return.
    Thanks alot man!
    I didn't reallize that, I know it's my neglegence. Sorry to bother you guys and thanks alot for helping me and pointing it out where I am wrong.

    Hats off to you.
    Regards
    Lucky

    : : One question infedel... Why boolean is not good for record set?
    :
    : The recordset object needs a connection object passed to it, but your function returns a boolean.
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    : [code]
    : $ select * from users where clue > 0
    : no rows returned
    : [/code]
    :
    :

  • lionblionb
    [b][red]This message was edited by lionb at 2006-2-13 7:58:33[/red][/b][hr]
    : It was not working.Finally I upgraded my Windows XP OS and now all my function is working fine.Withought any changing in code. Tha's really wiered to me
    :
    [blue]Welcome to Microsoft World .... deep sigh :-( [/blue]
    : But yes I got one error while terminating function. It was giving Run time error : 3001
    :
    What did exactly message say? Could you post a code?



  • lucky_melucky_me
    : [b][red]This message was edited by lionb at 2006-2-13 7:58:33[/red][/b][hr]
    : : It was not working.Finally I upgraded my Windows XP OS and now all my function is working fine.Withought any changing in code. Tha's really wiered to me
    : :
    : [blue]Welcome to Microsoft World .... deep sigh :-( [/blue]
    : : But yes I got one error while terminating function. It was giving Run time error : 3001
    : :
    : What did exactly message say? Could you post a code?

    Yea Welcome to Microsoft world. :-)
    Well the exact message was...

    "Run Time Error :3001"
    Argument are of wrong type, are out of acceptable range, or are in
    conflict with one another
  • lionblionb
    [b][red]This message was edited by lionb at 2006-2-13 11:59:8[/red][/b][hr]
    : Well the exact message was...
    :
    : "Run Time Error :3001"
    : Argument are of wrong type, are out of acceptable range, or are in
    : conflict with one another
    :
    I think your problem is here
    [code]
    rs.Open SQL_String,[blue][b][italic]Open_Connection(GCon)[/b][/italic][/blue], adOpenDynamic, adLockOptimistic, adCmdText
    [/code]
    See infidel's message about recordset/connection



Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Categories

In this Discussion