ASP.Net with MySQL Problems.

Problem1. SQL datasource wizard having [] in tablename like [table1] getting error. To resolve this either remove [] manually or set sqlservermode =True in connection string

When set to true enables Connector/Net to support square brackets around symbols instead of backticks. This enables Visual Studio wizards that bracket symbols with [] to work with Connector/Net. This option incurs a performance hit, so should only be used if necessary. This option was added in version 6.3.1.


Problem2. SQL datasource wizard generating insert, update command with ? instead of @parametername in values.
Solution: It's a bug. No solution found except to create command manually..Avoid using wizards in case of mySQLConnector. Make a program to create and complete the command. As i have created one

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click- Vinod Kotiya
www.vinodkotiya.com



Dim insertcmd As String = TextBox1.Text


If insertcmd = Nothing Then


insertcmd = "INSERT INTO [material_diversion] ([sno], [system], [item], [package], [approve_dt], [duno], [qty], [unit], [qty_desc], [from_proj], [from_unit], [to_proj], [to_unit], [reason], [remark], [time_req_replenishment], [ant_dt_repl], [act_dt_repl], [wheather_repl], [closure], [isreplaceable], [uid]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"


' insertcmd = "UPDATE [material_diversion] SET [sno] = ?, [system] = ?, [item] = ?, [package] = ?, [approve_dt] = ?, [duno] = ?, [qty] = ?, [unit] = ?, [qty_desc] = ?, [from_proj] = ?, [from_unit] = ?, [to_proj] = ?, [to_unit] = ?, [reason] = ?, [remark] = ?, [time_req_replenishment] = ?, [ant_dt_repl] = ?, [act_dt_repl] = ?, [wheather_repl] = ?, [closure] = ?, [isreplaceable] = ? WHERE [uid] = ?"


End If


If Strings.InStr(insertcmd, "INSERT") > 0 Then
Dim startbracket = Strings.InStr(insertcmd, "(")
Dim endbracket = Strings.InStr(insertcmd, ")")
Dim insertcmd1 As String = Strings.Left(insertcmd, startbracket - 1)
Label1.Text = insertcmd1
Dim insertcmd2 As String = Strings.Mid(insertcmd, startbracket, endbracket - startbracket - 1)
Label1.Text = Label1.Text & vbCrLf & insertcmd2
Dim parameter As String() = Split(insertcmd2, ",")
Dim p As String
Dim atParameter As String = "" ''contains @eid,@name


For Each p In parameter
Label1.Text = Label1.Text & vbCrLf & p
atParameter = atParameter & "@" & Strings.Mid(p, 3, p.Length - 3) & ", "
Next


TextBox1.Text = insertcmd1 & insertcmd2 & "]) values ( " & atParameter & ")"


ElseIf Strings.InStr(insertcmd, "UPDATE") > 0 Then


TextBox1.Text = "update query"
Dim startbracket = Strings.InStr(insertcmd, "SET")
Dim endbracket = Strings.InStr(insertcmd, "WHERE")
Dim insertcmd1 As String = Strings.Left(insertcmd, startbracket + 3)
Label1.Text = insertcmd1
Dim insertcmd2 As String = Strings.Mid(insertcmd, startbracket + 3, endbracket - startbracket - 3)
Label1.Text = Label1.Text & vbCrLf & insertcmd2
Dim parameter As String() = Split(insertcmd2, ",")
Dim p As String
Dim atParameter As String = "" ''contains @eid,@name


For Each p In parameter
p = Strings.Mid(p, 3, p.Length - 7)
Label1.Text = Label1.Text & vbCrLf & p
atParameter = atParameter & "[" & p & "]= @" & p & ", "
Next
'replace ]] with ]


atParameter = Strings.Replace(atParameter, "]]", "]")


TextBox1.Text = insertcmd1 & " " & Strings.Left(atParameter, atParameter.Length - 3) & " " & Strings.Right(insertcmd, insertcmd.Length - endbracket + 1)


End If

Problem 3:  have been getting this error: Unable to convert MySQL date/time value to System.DateTime.

This is when a DATETIME or DATE field contains an invalid value. It turns out that 0000-00-00 which I have used as default value in a DATE column is considered an invalid date. Instead NULL should be used when date/time is not set.

add Allow Zero Datetime=True to the connection string, this allows for 0000-00-00 00:00:00 in a DATETIME column. Just add it at the end of your connection string so it looks something like this:
Solution : 
connectionString="server=191.254.1.42;User Id=admin;password=***;database=entertrack;Persist Security Info=True; SQLServerMode = true;Allow Zero Datetime=True;"