Hi all,
I was coding in ASP before. And resently started with ASP.NET. When I coded in ASP I used to make a SQL connection function in a file I called config.inc. I called this file with

And used the SLQ connection info by calling the function Connect()

I'm now want to make something simmilar in an ASPX.VB, but after studying some web pages and some books (from Wrox) I just can't figure out how to do this...

So if anyone have done this in some way all help will be accepted with delight..

In advance thanks!

Michael

__________________
------------------------
All help is Good help!
Regards
Michael

Try some searches on the forums, there are numerous threads regarding this topic. Here are a couple I found:
http://p2p.wrox.com/topic.asp?TOPIC_...lobal,variable
http://p2p.wrox.com/topic.asp?TOPIC_...lobal,variable

Also, you can search for "web.config" or "ConfigurationSettings". Most of the solutions involve the use of that configuration file and call.

Peter

Thank Peter... I looked at a reply you made to this other guy..
**********************Copied text from other reply******************************
Public Class myCommonFunctions
    Public Shared Sub myFunction()
    End Sub
End Class

Now, elsewhere in your code you can just call the functions like this:
myCommonFunctions.myFunction()
************************************************** *********
This looks like what I need.
I now my next question may be a bit newbee, but in what file do I put this? config.web?

*****************************
I used to use:
Const CONSTRING = "provider=sqloledb;Server=sql.myweb.com;UID=user;D atabase=BASE;PWD=Password"
Function Connect()
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.CursorLocation = 3
   Conn.Open(CONSTRING)

    Conn.Execute("set dateformat dmy")
End Function
******************************
And whenever I needed a connection I just called connect(). This was SOOOOO simple and nice to use.

Any "spoon fed" pointers??

In you web application create a new class file, just a plain class (maybe "DBhelpers.vb").
Then you have something that ends up looking like this...

Imports System.Data.SqlClient
Public Class DBHelpers
    Public Const CONSTRING As String = "provider=sqloledb;Server=sql.myweb.com;UID=user;D atabase=BASE;PWD=Password"
    Public Shared Function GetDBConn() As SqlConnection
        Dim objConn As SqlConnection
        objConn.ConnectionString = CONSTRING
        objConn.Open()
        Dim objCommand As New SqlCommand("set dateformat dmy", objConn)
        objCommand.ExecuteNonQuery()
        objCommand.Dispose()
        Return objConn
    End Function
End Class


Peter

Thanks Peter... this is exactly what I needed...



All help is Good help!
Regards
Michael

Hi again all...
I've tied out this new code, but I get one error no mather what I do with it.

*********************Error in Browser*******************************
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 15: Public Shared Function Conn() As SqlConnection
Line 16: Dim objConn As SqlConnection
******************Error Line**************************
Line 17: objConn.ConnectionString = CONSTRING
******************Error Line**************************
Line 18: objConn.Open()



******************CODE**************************** ****
Imports System.Data.SqlClient
Public Class GBV
    Public Shared Function Connect() As SqlConnection
        Dim Conn = New SqlConnection
        Dim Command As New SqlCommand("set dateformat dmy", Conn)
        Conn.ConnectionString = "provider=sqloledb;Server=sql.myweb.com;UID=User;D atabase=BASE;PWD=Password"
        Conn.Open()
    End Function
End Class


************************From File .ASPX.VB***********************
        GBV.Connect()
        Response.Write(GBV.Connect())
        Dim SQ As Data.SqlClient.SqlDataReader
        Dim strTable As String
        Try
            GBV.Connect.CreateCommand.CommandText = ("select meTable from BASE where meTable = '" & MobileNr.Text & "'")
            SQ = GBV.Connect.CreateCommand.ExecuteReader()
            Response.Write(SQ)
            Do While SQ.Read = True
                strTable = SQ("meTable")
            Loop
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
        SQ.Close()

*********************Tried*********************
I've tried encasulating everything in ()
Setting the ConnectionString as an variable and hardcoded and constant
raw copy of the code above
++++
After 5 hours I thought I'd try here again!

All help is Good help!
Regards
Michael

IT WORKS... :d
After another 2-3 hours of testing I finally got it to work:

**************************GlobalVar.vb************ **************
Public Class DB2
    Dim myCon As SqlConnection
    Public Shared Function SqlCon()
        Dim myCon As New SqlConnection
        myCon.ConnectionString = "workstation id=COMPUTERNAME;packet size=4096;user id=User;PWD=Password;data source=""sql.myweb.com"";persist security info=False;initial catalog=Table"
        myCon.Open()
        Return myCon
    End Function 'CreateSqlConnection


******************************TEST.ASPX.VB******** ******************
        DB2.SqlCon()
        Dim SQ As Data.SqlClient.SqlDataReader
        Dim strmeTable As String
        Dim myCommand As New System.Data.SqlClient.SqlCommand("set dateformat dmy", DB2.SqlCon)
        Try
            myCommand.CommandText = ("select * from MEMBERS where meTable = 'test'")
            SQ = myCommand.ExecuteReader()
            Do While SQ.Read = True
                strmeTable = SQ("meTable")
            Loop
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
        SQ.Close()
******************************END CODE**********************


All help is Good help!
Regards
Michael

when you are using System.Data.SqlClient you must leave the provider=sqloledb; out of the connection string. Although the error I get is "Keyword not supported: 'provider'. "

Whoops looks like i was late with my reply.
You may want to take a different approach; your connection is never being closed as far as I can tell.

you may want to take a look at http://msdn.microsoft.com/library/de.../html/daag.asp see the section "Pooling with the SQL Server .NET Data Provider" in addition I recommend downloading the Data Access Application Block even if you don't use it take a look at the code http://msdn.microsoft.com/library/en...asp?frame=true

SqlClient automatically creates pooled connections for you but "Developers should be careful not to rely on the garbage collector to free connections because a connection is not necessarily closed when the reference goes out of scope. This a common source of connection leaks, resulting in connection exceptions when new connections are requested."

Your just in time jhouse *smile*
That was my next question here, I've just been trying to close down my SQL connections using ANY type of command I could find in Visual.Studio. But as u guessed, to No avail. So I'm going to print out your links and have myself a bit of bed time reading (time fly when your having fun.. he he he, it's 02:43 here!)

Just as a thought, I've been trying to get some variables from one function in a class to another.. Is this possible??
*****************************GlobalVar.vb********* *******''
Public Class DB2
***Function1**
    Public Shared Function SqlCon() As SqlConnection
        Dim myCon As New SqlConnection
        myCon.ConnectionString = "packet size=4096;user id=User;PWD=Password;data source=192.168.0.10;persist security info=False;Database=BASE"
         myCon.Open()
        Return myCon
    End Function 'CreateSqlConnection
******Function2**
    Public Shared Function SqlDisCon()
        Dim myCon As New SqlConnection
        Dim isNull
        If myCon Is Nothing Then ' Conn is nothing not(Conn = Nothing)
            myCon.Close()
            myCon = Nothing
        End If
        myCon.Close()
    End Function
End Class
****************************************
A big thanks to Peter and jhouse for quick and helpful replys to a newbee! (bear with me huh?)

All help is Good help!
Regards
Michael