need help with connectionstring

[purplehaze1]

I am building class library (dll) that has 2 classes,eg. Login class & Vendor Class.
I build the connection string when the user logs in. I need to use this connection
string in vendor class to get all vendors information.
How can I make this connection string accessible to vendor class module (ideally to all class
modules. Similar to when you declare public variable in module, you can access it anywhere)?

Thanks for your help.

eg.

Public Class clsLogin
Private Server, userName, database, password as string
Private SQL_ConnectionString as string

Function Connect() As Boolean
Try
ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info = False;" & _
"Initial Catalog = " & database & ";Data Source = " & Server & ";User ID =" & userName & ";Password =" & password & ";"
myConnection = New OleDbConnection(ConnectionString)
myConnection.Open()
myConnectFlag = True

If myConnectFlag Then

SQL_ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info = False;" & _
"Initial Catalog =Payroll;Server=" & Server & ";User ID =" & userName & ";Password =" & password & ";"

End If

Return myConnectFlag
Catch e As Exception
Console.WriteLine(e.ToString())
End Try

End Function

End Class

 

[steverbs]

Well, I'm not quite sure what you are trying to achieve here, but couldn't you just create a Public Property named ConnectionString and set it in the Connect Function?

Public Class clsLogin
Private Server, userName, database, password as string
Private _SQL_ConnectionString as string

Public Property SQL_ConnectionString() As String
Get
Return Me._SQL_ConnectionString
End Get
Set(ByVal Value As String)
Me._SQL_ConnectionString = Value
End Set
End Property

Function Connect() As Boolean
Try
ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info = False;" & _
"Initial Catalog = " & database & ";Data Source = " & Server & ";User ID =" & userName & ";Password =" & password & ";"
myConnection = New OleDbConnection(ConnectionString)
myConnection.Open()
myConnectFlag = True

If myConnectFlag Then

Me._SQL_ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info = False;" & _
"Initial Catalog =Payroll;Server=" & Server & ";User ID =" & userName & ";Password =" & password & ";"
End If

Return myConnectFlag
Catch e As Exception
Console.WriteLine(e.ToString())
End Try

End Function

End Class

Any Class with a reference to this Class's Assembly could then access it.

 

[RiverGuy]

Create a static variable.

 

[purplehaze1]

I created static connection variable in clsLogin.
Now to access this connection string in clsVendor,
do I have to instatiate clsLogin everytime to use this connection string?
Will there be memory leak if I don't dispose the instantiated object (i.e. Private oLogin as new clsLogin)? If so, how to prevent it? Thanks again.


Public Class clsLogin

Friend Const SQL_ConnectionString As String = "Provider=SQLOLEDB.1;Persist Security Info = False;" & _
"Initial Catalog =mdp_paf_prd;Server=" & Server & ";User ID =" & userName & ";Password =" & password & ";"

End Class

Public Class Vendor
Private oLogin as new clsLogin

Public Function getVendor(ByVal vendorID As Integer) As DataSet
Dim myConnection As OdbcConnection
Dim mycommand As OdbcCommand
Dim intRowsAffected As Integer
Dim sdavendorInfo As OdbcDataAdapter
Dim scb As OdbcCommandBuilder
Dim dsvendorInfo As DataSet
Dim strSQL As String

strSQL = "SELECT frst_nme, lst_nme, mid_init, addr1, addr2, cty, st, zip,cntry, phn, sex "
strSQL += "FROM t_vendor WHERE vendorid = " & vendorID



myConnection = New OdbcConnection(oLogin.SQL_ConnectionString)
myConnection.Open()
mycommand = New OdbcCommand(strSQL, myConnection)
sdaVendorInfo = New OdbcDataAdapter(mycommand)
scb = New OdbcCommandBuilder(sdaVendorInfo)
dsVendorInfo = New DataSet()

Try
sdaVendorInfo.Fill(dsvendorInfo, strPInfo)
Catch ex As OdbcException
_mylocalError += "[Error Source]: " + ex.Source + "[Error Message]: " + ex.Message
Catch e As Exception
_mylocalError += "[Error Source]: " + e.Source + "[Error Message]: " + e.Message
Finally
myConnection.Close()
mycommand.Dispose()
sdaVendorInfo.Dispose()
scb.Dispose()
End Try

Return dsVendorInfo

End Function