Help with MySQL and typed datasets with asp.net+vb.net

Darragh-o-h

Hello,
I'm Trying to connect to a MySQL database using the mysqlMySQL connecter. using a data grid and displaying everything in the table works fine but I want to use a typed dataset.

Maybe I'm trying to run before I can crawl but I'd really appreciate it if someone could give me a hand. I've read a few pages so far and they all talk about connecting using ODBC and SQL. I don't want to use ODBC as there seem to be some performance issues.

I've been reading Microsoft press books so I do have some idea about how to create the xsd file and how to compile it but its the vb.net side that I could do with help on.

Oh by the way, I want to use a code behind file but when I try to import the mysql name space I get errors but the line should be correct, the dll is in the bin folder and the @ import statement works when I use it in the .aspx page. Any ideas why that would be a problem?

Thanks again in advance

Evil Phil

Post your code so we can have a look at it

Darragh-o-h

Yeah, no problem. the code that I've atempted so far is below. the output error that I get when I try to run the page is at the bottom.

Imports MySql.Data.MysqlClient
Imports System.Data
Imports System

Public Class WebForm1
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub
Protected WithEvents Content As System.Web.UI.WebControls.Label
Protected WithEvents Value As System.Web.UI.WebControls.Literal

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim conn As MySqlConnection
Dim DataAdapter As MySqlDataAdapter
Dim DataSet As DataSet
Dim SQLCMD As String

SQLCMD = "SELECT * FROM Content;"
DataAdapter = New MySqlDataAdapter(SQLCMD, conn)
DataSet = New DataSet
DataAdapter.Fill(DataSet, "Content")
Try
conn = New MySqlConnection
conn.ConnectionString = "server=localhost;" _
& "user id=user;" _
& "password=user;" _
& "database=database"
conn.Open()
Dim ContentTable As DataTable = DataSet.Tables("Content")
Dim CurrentTable As DataTable
Dim CurrentRow As DataRow
Dim Currentcolumn As DataColumn
For Each CurrentTable In DataSet.Tables
Value.Text &= "Table: " & _
CurrentTable.TableName.ToString & "<BR/>"
Value.Text &= "

---

<br/>"
For Each CurrentRow In CurrentTable.Rows
Value.Text &= "<br/>   "
For Each Currentcolumn In CurrentTable.Columns
If Not CurrentRow(Currentcolumn) Is Nothing Then
If Not IsDBNull(CurrentRow(Currentcolumn)) Then
Value.Text &= CStr(CurrentRow(Currentcolumn))
Else
Value.Text &= "Null"
End If
Value.Text += "<br/>   "
End If
Next
Next
Value.Text &= "

---

<br/>"
Value.Text &= "<br/><br/>"
Next
Catch myerror As MySqlException
Finally
conn.Close()
conn.Dispose()
End Try
End Sub

End Class



Output screen:

Fill: SelectCommand.Connection property has not been initialized.

Server Error in '/database002' Application.

Fill: SelectCommand.Connection property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.

Source Error:


Line 36: DataAdapter = New MySqlDataAdapter(SQLCMD, conn)
Line 37: DataSet = New DataSet
Line 38: DataAdapter.Fill(DataSet, "Content")
Line 39: Try
Line 40: conn = New MySqlConnection

Source File: c:\inetpub\wwwroot\database002\Index.aspx.vb Line: 38

Stack Trace:


[InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.]
System.Data.Common.DbDataAdapter.GetConnection(IDbCommand command, String method) +40
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +45
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
+77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +36
database002.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\database002\Index.aspx.vb:38
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731

Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

Darragh-o-h

sorry, should have noted that the code above is not using a typed dataset but it is using a dataset. I'm atempting to work up to getting it to work with a typed data set. My aim is to have a select statement and pick out the PageText column on that row. I might be thinking in record sets in traditional asp but I'm hoping I can work along the same lines. I was writing the code above after reading a few examples. Again, any help welcome. I'm very stuck. Spent most of yesterday searching but havent had much success in finding what I'm looking for.

Evil Phil

SQLCMD = "SELECT * FROM Content;"
DataAdapter = New MySqlDataAdapter(SQLCMD, conn)
DataSet = New DataSet
DataAdapter.Fill(DataSet, "Content")
Try
conn = New MySqlConnection
conn.ConnectionString = "server=localhost;" _
& "user id=user;" _
& "password=user;" _
& "database=database"
conn.Open()

Should this be?

SQLCMD = "SELECT * FROM Content;"
[color=red]conn = New MySqlConnection
conn.ConnectionString = "server=localhost;" _
& "user id=user;" _
& "password=user;" _
& "database=database"
[/color]
DataAdapter = New MySqlDataAdapter(SQLCMD, conn)
[color=red]conn.Open()[/color]
DataSet = New DataSet
DataAdapter.Fill(DataSet, "Content")
Try

This opens the connection to the db before you try and fill your dataset. Dunno MySQL so I may be wrong. Worth a try though.

Darragh-o-h

Thanks for the response. well, we're getting a bit further this time. The error I'm now getting is: [invalidcastexception: cast from type 'Uint32' to type 'String' is not valid.] just from reading the error it sounds like its having a problem with the ID field in the database which is an auto-incrementing integer. after doing a few searches I've found nothing that could help. Any ideas? Again, thanks for the help. its further than I've been able to get in what feels like ages!
the lines causing the problem according to the error report are:

If Not CurrentRow(Currentcolumn) Is Nothing Then
If Not IsDBNull(CurrentRow(Currentcolumn)) Then
Value.Text &= CStr(CurrentRow(Currentcolumn))
Else
Value.Text &= "Null"

mewso

Try Value.Text &= convert.tostring(CurrentRow(Currentcolumn))

Darragh-o-h

Fantastic. that worked. now onto trying to create a typed data set with the aim of only printing one column to the screen.
Again, if anyone has any suggestions I'd be really happy to hear them. I'm only starting out with asp.net and although I find it really interesting there's just so much in it that its taking a lot longer than I anticipated.

Evil Phil

There's a lot more to Asp.net than classic asp. Keep at it though. Btw typed datasets are really good once you get your head around them.

Darragh-o-h

Hello again,
I've created and compiled the xsd file which is now called "TypedDataSet.Dll" and which is sitting in the bin directory. I've added it to the references and am kind of stuck as to what to do next. If I'm not mistaken, because I'm using the mysql connecter and not an ODBC connection I cant use any of the wizzards so code is my only option? Not that that's a bad thing of course but it would be good to see what needs to be added to the code to make this work.

I think from reading some of this MS press book that I have to add the line:
Protected WithEvents TDS As database002.TDS
to the code but I'm not sure what the whole thing means. I think I could do with some clarification as to what to do next.
The error I get when I add this line is:
Error! Type Database002.TDS is undefined
but I'm sure I'm not doing something right.

Just in case I'm including the code I have so far at the end of this message. It doesn't make use of a typed dataset because I'm not particularly sure how to make sse of it yet and it wouldn't work unless the project recognises that I'm using it.

Imports MySql.Data.MysqlClient
Imports System.Data
Imports System

Public Class WebForm1
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub
Protected WithEvents Value1 As System.Web.UI.WebControls.Literal
Protected WithEvents Value As System.Web.UI.WebControls.Label
Protected WithEvents TDS As database002.TDS
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim conn As MySqlConnection
Dim DataAdapter As MySqlDataAdapter
Dim DataSet As DataSet
Dim SQLCMD As String

SQLCMD = "SELECT PageText FROM Content Where ID = 1;"

Try
conn = New MySqlConnection
conn.ConnectionString = "server=localhost;" _
& "user id=User;" _
& "password=User;" _
& "database=DataBase"
DataAdapter = New MySqlDataAdapter(SQLCMD, conn)
DataSet = New DataSet
DataAdapter.Fill(DataSet, "Content")
conn.Open()
Dim ContentTable As DataTable = DataSet.Tables("Content")
Dim CurrentTable As DataTable
Dim CurrentRow As DataRow
Dim Currentcolumn As DataColumn
For Each CurrentTable In DataSet.Tables
Value.Text &= "Table: " & _
CurrentTable.TableName.ToString & "<BR/>"
Value.Text &= "

---

<br/>"
For Each CurrentRow In CurrentTable.Rows
Value.Text &= "<br/>   "
For Each Currentcolumn In CurrentTable.Columns
If Not CurrentRow(Currentcolumn) Is Nothing Then
If Not IsDBNull(CurrentRow(Currentcolumn)) Then
Value.Text &= Convert.ToString(CurrentRow(Currentcolumn))
Else
Value.Text &= "Null"
End If
Value.Text += "<br/>   "
End If
Next
Next
Value.Text &= "

---

<br/>"
Value.Text &= "<br/><br/>"
Next
Catch myerror As MySqlException
Finally
conn.Close()
conn.Dispose()
End Try
End Sub

End Class

Kernel32

If you are using typed datasets you need to do a tablemapping on the DataAdapter. Otherwise how does the DataAdapter know which returned table to map into which datatable?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondataadapterclasstablemappingstopic.asp

Darragh-o-h

hmmm. thanks but this doesn't make any sense to me at all.
Does that page tell me anything about connecting the typed DataSet to the application? It seems like its using another method.

Kernel32

to the code but I'm not sure what the whole thing means. I think I could do with some clarification as to what to do next.
The error I get when I add this line is:
Error! Type Database002.TDS is undefined
but I'm sure I'm not doing something right.

This error is because you didn't import the namespace for your dataset. You added a reference to your project but you need to add the namespace in the Imports. I don't know what your namespace is.

Even when you fix it your code will not work properly because you need to do a tablemapping. When you create a typed dataset you create datatables. Each datatable has columns, indexes and possibly relationships to other datatables. An untyped dataset on the other hand has no structure until it contains data.

When you call the Fill method of a DataAdapter you pass it a dataset. It can be a typed or untyped dataset. If its untyped it will create datatables on the fly and name them Table, Table1, Table2, TableN, depending on how many resultsets the proc of sql returns. If its a typed dataset then you must do a tablemapping first(before the fill), which is in the link I pasted. This tells the DataAdapter which resultset from the proc maps to which Datatable. The kicker is if you don't do the mapping you won't get an error. The DataAdapter will simply create the generic datatables on the fly, Table, Table1 etc and your datatables will remain empty. This is a common mistake and often leads to developers abandoning typed datasets because they can't get them to work.

Darragh-o-h

That's great. I'll tackle that as soon as I get a chance. that's a lot clearer now.

Darragh-o-h

Hello, I've had another stab at using a typed dataset. Using the same microsoft press book I've adapted the code to work with a MySQL database. For some reason I don't get any errors but the page never lodes. I've pasted the code below. Any ideas?

Imports MySql.Data.MysqlClient
Imports System.Data
Imports System
Imports TDS



Public Class WebForm1
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub
Protected WithEvents title_id As System.Web.UI.WebControls.TextBox
Protected WithEvents title As System.Web.UI.WebControls.TextBox
Protected WithEvents type As System.Web.UI.WebControls.TextBox
Protected WithEvents pub_id As System.Web.UI.WebControls.TextBox
Protected WithEvents price As System.Web.UI.WebControls.TextBox
Protected WithEvents advance As System.Web.UI.WebControls.TextBox
Protected WithEvents royalty As System.Web.UI.WebControls.TextBox
Protected WithEvents ytd_sales As System.Web.UI.WebControls.TextBox
Protected WithEvents notes As System.Web.UI.WebControls.TextBox
Protected WithEvents pubdate As System.Web.UI.WebControls.TextBox
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents titlegrid As System.Web.UI.WebControls.DataGrid
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim MyDS As New DataSet
Dim ContentTable As DataTable
Dim ContentRow As DataRow
Dim connStr As String
connStr = "server=localhost;" _
& "user id=User;" _
& "password=User;" _
& "database=DataBase"
Dim SQL As String
SQL = "SELECT * FROM Content"
Dim MySqlConn As New MySqlConnection(connStr)
Dim MySqlAdapter As New MySqlDataAdapter(SQL, connStr)
Dim MySqlCB As New MySqlCommandBuilder(MySqlAdapter)

MyDS.ReadXmlSchema(Server.MapPath("AddTitle.xsd"))
If IsPostBack = True Then
ContentTable = MyDS.Tables(0)
ContentRow = ContentTable.NewRow()

ContentRow("ID") = title_id.Text
ContentRow("PageTitle") = title.Text
ContentRow("PageText") = type.Text
ContentRow("Date") = DateTime.Parse(pubdate.Text)
ContentRow("KeyWords") = notes.Text

ContentTable.Rows.Add(ContentRow)
'Update back-end table based on new row
MySqlAdapter.Update(MyDS)
'Reset dataset before filling with data from DB
MyDS.Reset()
'Fill dataset with data from the Titles table
MySqlAdapter.Fill(MyDS)
titlegrid.DataSource = MyDS.Tables(0).DefaultView
titlegrid.DataBind()

Else
'To prevent conflicts on multiple inserts, we can
'generate a random value for title_id
Dim RandomNum As New Random
title_id.Text = "XX" & String.Format("{0:000#}", _
RandomNum.Next(9999))
End If

End Sub
End Class