Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal
Solved
Query to MySQL from ASP.net returns System.Byte[]
Posted on 2009-06-27
I have an ASP.net website that has been working fine. Some of what is displayed is retrieved from a MySQL database. For example, news items.
Recently where a date was retrieved and a URL consructed from info from the db suddenly started returning System.Byte[].
On googling this seemed to be related to a version of the odbc. After a lot of to-ing and fro-ing with the site's support, the odbc is the latest and MySQL is v5. The problem still remains.
All the while my "production" machine is an ancient laptop running win2000 (latest odbc 3.51 and MySQL5) everything works fine.
I have discovered however that on my vista desktop (latest odbc, MySQL) the problem also happens.
As dealing with the website support is like wading through treacle I thought I should try to fix it on the vista machine and I might then have an idea of what to do with the live site.
A workaround may be changing my code.
Still reading?
the c# code is posted below for one page where the problem occurs. The fields that show as System.Byte[] are the modified date and the "read more" link. Both have string operations applied to them. Removing this formatting results in the correct raw data being retrieved.
But one other field has string operations but is ok. The two that error involve a date and the other an int.
The url of an example is http://www.badttc.org.uk/olditems.aspx
Any idea what is going on here? and how I might get round it?
Recently where a date was retrieved and a URL consructed from info from the db suddenly started returning System.Byte[].
On googling this seemed to be related to a version of the odbc. After a lot of to-ing and fro-ing with the site's support, the odbc is the latest and MySQL is v5. The problem still remains.
All the while my "production" machine is an ancient laptop running win2000 (latest odbc 3.51 and MySQL5) everything works fine.
I have discovered however that on my vista desktop (latest odbc, MySQL) the problem also happens.
As dealing with the website support is like wading through treacle I thought I should try to fix it on the vista machine and I might then have an idea of what to do with the live site.
A workaround may be changing my code.
Still reading?
the c# code is posted below for one page where the problem occurs. The fields that show as System.Byte[] are the modified date and the "read more" link. Both have string operations applied to them. Removing this formatting results in the correct raw data being retrieved.
But one other field has string operations but is ok. The two that error involve a date and the other an int.
The url of an example is http://www.badttc.org.uk/olditems.aspx
Any idea what is going on here? and how I might get round it?
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Data.Odbc;
using System.Data;
using System.Drawing;
using System.Web.Security;
using System.Web.SessionState;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Web;
using System;
public partial class _Default : System.Web.UI.Page
{
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
LoadItems();
}
private void LoadItems()
{
string strConnstring = "DSN=MyODBCName;USER=user;PASSWORD=pass;";
System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(strConnstring);
try
{
conn.Open();
string sql = "select ID, head, ";
sql += "if(length(item)> 50, concat(substring(item,1,50),' ...'),item) as item, ";
sql += "concat('<A HREF=\"newsitem.aspx?from=ai&id=',ID,'\">Read item</a>') as ReadItem, ";
sql += "substring(modified,1,11) as modified, forcetop, forceverytop ";
sql += "from newsitems where deleted = 0 order by modified desc ";
System.Data.Odbc.OdbcCommand cmd = new OdbcCommand(sql, conn);
System.Data.Odbc.OdbcDataAdapter da = new OdbcDataAdapter(cmd);
System.Data.DataSet ds = new DataSet();
da.Fill(ds);
//this.txtHead1.ScrollBars = ScrollBars.None ;
this.DataGrid1.DataSource = ds;
this.DataGrid1.DataBind();
}
catch (Exception ex)
{
this.lblItem1.Text = ex.Message.ToString();
}
finally
{
conn.Close();
}
}
}
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
2-
2
7 Comments
Active 2 days ago
Have you ever tried using the MySQL .NET data provider?
http://www.devart.com/dotconnect/mysql/
http://dev.mysql.com/downloads/connector/net/5.0.html
http://www.devart.com/dotconnect/mysql/
http://dev.mysql.com/downloads/connector/net/5.0.html
One option maybe to switch to using the dedicated MySQL .Net Connector instead:
http://dev.mysql.com/downloads/connector/net/5.2.html
We use both and over the last few years I have found that the MySQL one is getting very stable while the odbc one is getting worse! We even get odbc errors know that require us to restart the server.
http://dev.mysql.com/downloads/connector/net/5.2.html
We use both and over the last few years I have found that the MySQL one is getting very stable while the odbc one is getting worse! We even get odbc errors know that require us to restart the server.
Active 1 day ago
Thanks for your suggestion. I will try to get the hosts to implement the .net connector anyway.
However, as often happens, describing the problem has set my mind off in another direction in order to fix this.
My thoughts were that a stored procedure might do the trick. If it only returned strings there should not be a problem. My thinking perhaps wasn't too clear here but following this approach I discovered some flaws in my query. Presumably being "forgiven" under Win2000.
The query that now reside in my stored proc is as follows
SELECT
ID,
head,
IF(LENGTH(item)> 50, CONCAT(SUBSTRING(item,1,50
CONCAT("<A HREF=\"newsitem.aspx?from=
DATE_FORMAT(modified,"%D %b %y") AS modified,
forcetop,
forceverytop,
modified as sortdate
FROM newsitems WHERE deleted = 0 ORDER BY sortdate DESC ;
The third field "item" has the single quotes replaced with double quotes.
The fourth likewise with escaped double quotes and, perhaps more importantly, a cast of the integer ID.
Fifth field, a proper format of the date rather than just using substring.
so with this in a sp the page works! here I don't know if the same as in line sql works but I don't care.
just need to fix all the other bits than don't work now.
Again thanks for your participation.
However, as often happens, describing the problem has set my mind off in another direction in order to fix this.
My thoughts were that a stored procedure might do the trick. If it only returned strings there should not be a problem. My thinking perhaps wasn't too clear here but following this approach I discovered some flaws in my query. Presumably being "forgiven" under Win2000.
The query that now reside in my stored proc is as follows
SELECT
ID,
head,
IF(LENGTH(item)> 50, CONCAT(SUBSTRING(item,1,50
CONCAT("<A HREF=\"newsitem.aspx?from=
DATE_FORMAT(modified,"%D %b %y") AS modified,
forcetop,
forceverytop,
modified as sortdate
FROM newsitems WHERE deleted = 0 ORDER BY sortdate DESC ;
The third field "item" has the single quotes replaced with double quotes.
The fourth likewise with escaped double quotes and, perhaps more importantly, a cast of the integer ID.
Fifth field, a proper format of the date rather than just using substring.
so with this in a sp the page works! here I don't know if the same as in line sql works but I don't care.
just need to fix all the other bits than don't work now.
Again thanks for your participation.
Active 1 day ago
It remains to be seen whether the .net connector would forgive the "weaknessses" in my query.
Active 2 days ago
ODBC is not as optimized as .NET data providers, especially when dealing with SQL Server, so you should always go that direction when there is the opportunity.
Featured Post
New feature! Upgrade and increase expert visibility of your issues with Priority Questions.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month7 days, 20 hours left to enroll
715 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.