mysql data reader

Aug 18 2015 2:03 AM

Hi, I am a beginner c# programmer and still having problems with my DataReader.
I searched and tried several codes but no positive result.
So I decided to post some parts from my sql, asp, cs code project.
The problem area is commented //.
I would be very greatful if anyone helped me giving specific recomendation how to fix that issue.

===========================================================
CREATE TABLE `images`
(
`Image_ID` int(32) NOT NULL AUTO_INCREMENT,
`Image` blob,
PRIMARY KEY (`Image_ID`)
)
ENGINE=InnoDB AUTO_INCREMENT=3608 DEFAULT CHARSET=binary;
============================================================
CREATE TABLE `groups`
(

`Group_ID` char(02) NOT NULL,

`Number` varchar(5) DEFAULT NULL,

PRIMARY KEY (`Group_ID`)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8;
============================================================
CREATE TABLE `register`
(
`ID` int(32) NOT NULL AUTO_INCREMENT,
`Arrive_Img_ID` int(32) DEFAULT NULL,
`Leave_Img_ID` int(32) DEFAULT NULL,
`Students_ID` char(2) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `FK_register_ArriveImg` (`Arrive_Img_ID`),
KEY `FK_register_LeaveImg` (`Leave_Img_ID`),
KEY `FK_register_Students` (`Students_ID`),
KEY `Date` (`Date`),
CONSTRAINT `FK_register_ArriveImg` FOREIGN KEY (`Arrive_Img_ID`) REFERENCES `images` (`Image_ID`) ON DELETE SET NULL ON UPDATE SET NULL,
CONSTRAINT `FK_register_LeaveImg` FOREIGN KEY (`Leave_Img_ID`) REFERENCES `images` (`Image_ID`) ON DELETE SET NULL ON UPDATE SET NULL,
CONSTRAINT `FK_register_Students` FOREIGN KEY (`Students_ID`) REFERENCES `students` (`Students_ID`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB AUTO_INCREMENT=2248 DEFAULT CHARSET=utf8;
==============================================================

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ImageRetrieve.aspx.cs" Inherits="ImageRetrieve" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.auto-style1 {
width: 100%;
}
.auto-style2 {
width: 389px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>

</div>
<br />
<br />
<br />
<table class="auto-style1">
<tr>
<td class="auto-style2">
<asp:DropDownList ID="ddlGroup" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlGroup1_SelectedIndexChanged">
</asp:DropDownList>
</td>
<td> </td>
</tr>
<tr>
<td class="auto-style2">
<asp:DropDownList ID="ddlName" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlName_SelectedIndexChanged">
</asp:DropDownList>
</td>
<td> </td>
</tr>
<tr>
<td class="auto-style2">
 </td>
<td>
<asp:GridView ID="gvImages" runat="server" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" OnRowDataBound="OnRowDataBound" Width="105px">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="Image_ID" HeaderText="Image_ID" />
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="Image1" runat="server" Height="80" Width="80" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
</td>
</tr>
</table>
<br />
</form>
</body>
</html>
====================================================================================================
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class ImageRetrieve : System.Web.UI.Page
{
string constr = "Data Source=localhost;port=3306;Initial Catalog=test;User Id=root;password=2525";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGroupdropdown();
}
}
protected void BindGroupdropdown()
{
MySqlConnection con = new MySqlConnection(constr);
con.Open();
MySqlCommand cmd = new MySqlCommand("select * from groups", con);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
ddlGroup.DataSource = ds;
ddlGroup.DataTextField = "Number";
ddlGroup.DataValueField = "Number";
ddlGroup.DataBind();
ddlGroup.Items.Insert(0, new ListItem("--Select--", "0"));
ddlName.Items.Insert(0, new ListItem("--Select--", "0"));
}
private string getGroupID()
{
MySqlConnection con = new MySqlConnection(constr);
con.Open();
MySqlCommand cmd = new MySqlCommand("select Group_ID from groups where Number='" + ddlGroup.SelectedValue + "'", con);
string appid = (cmd.ExecuteScalar() ?? String.Empty).ToString();
con.Close();
return appid;
}
protected void ddlGroup1_SelectedIndexChanged(object sender, EventArgs e)
{
string aid = getGroupID();
MySqlConnection con = new MySqlConnection(constr);
con.Open();
MySqlCommand cmd = new MySqlCommand("select CONCAT(SurName , ' ', Name , ' ', MiddleName) AS SNM from students where Group_ID='" + aid + "'", con);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
ddlName.DataSource = ds;
ddlName.DataTextField = "SNM";
ddlName.DataValueField = "SNM";
ddlName.DataBind();
ddlName.Items.Insert(0, new ListItem("--Select--", "0"));
BindGrid();
}
private string getStudent_ID()
{
var SNM = ddlName.Text.Replace("'", "''");
MySqlConnection con = new MySqlConnection(constr);
con.Open();
MySqlCommand cmd = new MySqlCommand("Select Students_ID from students where CONCAT(SurName , ' ', Name , ' ', MiddleName)='" + SNM + "'");
cmd.Connection = con;
String s = (cmd.ExecuteScalar() ?? String.Empty).ToString();
con.Close();
return s;
}
private void BindGrid()
{
MySqlConnection con = new MySqlConnection(constr);
MySqlCommand cmd = new MySqlCommand("SELECT Image_ID, Image FROM images where Image_ID='" + getImage_ID() + "'", con);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
gvImages.DataSource = dt;
gvImages.DataBind();
}
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
byte[] bytes = (byte[])(e.Row.DataItem as DataRowView)["Image"];
string base64String = Convert.ToBase64String(bytes, 0, bytes.Length);
(e.Row.FindControl("Image1") as Image).ImageUrl = "data:image/png;base64," + base64String;
}
}
private string getImage_ID()
{
MySqlConnection con = new MySqlConnection(constr);
con.Open();
MySqlCommand cmd = new MySqlCommand("Select Arrive_Img_ID,Leave_Img_ID from register where Students_ID='" + getStudent_ID() + "'");
cmd.Connection = con;
MySqlDataReader reader = cmd.ExecuteReader();
//Here I can't find suitable code to call multiple results from register table.
//return s;
}
protected void ddlName_SelectedIndexChanged(object sender, EventArgs e)
{
BindGrid();
}
}

Answers (6)

Video Compression Through Code

i need a application with code in c