Data Access Layer with DataSet

[guitardave78]

Ok, gonna rephrase a question i asked.
I am using mysql with the .net connecter in vb 2005 express.

I would like to make dll that I can use as objectdatasource in asp.net.
I can get the basic Idea (code below) but it does not quite work. If you run the asp page it displays the correct columns and data, but the editor does no display any columns, so i cannot edit the gridview!!
I know it can be done this way!!
The code in the dll is basically this

   Private Sub dbConn()
        If Not conn Is Nothing Then conn.Close()

        Dim connStr As String
        connStr = "Server=localhost;Database=dls;Uid=blag;Pwd=blahblah;"

        conn = New MySqlConnection(connStr)

    End Sub
    Public Function selectAllMessages() As DataTable
        dbConn()
        Dim da As MySqlDataAdapter
        Dim Data = New DataTable("messages")

        da = New MySqlDataAdapter("SELECT * FROM tbl_messages", conn)

        conn.Open()

        da.Fill(Data)

        Return Data
    End Function

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[caoamo]

My initial thoughts are you are creating your connection in a sub. The connection will only have local scope for that sub.

Try this:

Public Function dbConn() As MySqlConnection	
	
	'comment this out if conn exists as a class member
	Dim conn As MySqlConnection
	Dim connStr As String

	connStr = "Server=localhost;Database=dls;Uid=blag;Pwd=blahblah;"

	'not needed if conn is not a class member and you keep the Dim conn statement
	If Not conn Is Nothing Then conn.Close()
	
	conn = New MySqlConnection(connStr)
	Return conn

End Function

Public Function selectAllMessages() As DataTable
	
	Dim conn as MySqlDataAdapter
	Dim da As MySqlDataAdapter
        Dim Data = New DataTable("messages")

	conn = dbConn()
        da = New MySqlDataAdapter("SELECT * FROM tbl_messages", conn)

        conn.Open()

        da.Fill(Data)

        Return Data

End Function

I haven't tested this, but hopefully it helps.

 

[guitardave78]

Ok will look at that thanks. My only question is if that is the case why would the actual web page work !!?? lol
I hate learning new stuff

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[phinoppix]

You mentioned something about the editor. Were you referring to the IDE? If so...

Nothing wrong with your DAL code, but you can't use that with the gridview designer. Unless your DAL assembly exposes components with design-time interfaces so you can integrate it with the editor, you will have to manually design the gridview.

 

[guitardave78]

Ok, that make me feel a bit better!!
How whould i do the design time interface bit?

}...the bane of my life!

http://www.fuzzyd.co.uk