private void read_btn_Click(object sender, EventArgs e)
{
MySqlConnection conn = null;
MySqlDataReader rdr = null;
int count;
DateTime time = DateTime.Now;
string date_started = String.Format("{0:yyyy-MM-dd}", time);
string time_started = String.Format("{0:HH:mm:ss}", time);
try
{
conn = new MySqlConnection(ConnectionString);
conn.Open();
//อ่านค่า last_number จากตาราง queue_wait
string stm = "SELECT * FROM queue_wait ORDER BY time ASC LIMIT 1 ";
MySqlCommand cmd_sel = new MySqlCommand(stm, conn);
rdr = cmd_sel.ExecuteReader();
if(rdr.Read())
{
Console.WriteLine(rdr.GetString(1) + ": "
+ rdr.GetString(2));
last_number = int.Parse(rdr.GetString(2));
}
rdr.Close(); // ต้อง close ก่อนไม่งั้นจะมี error There is already an open DataReader
//ตรวจสอบว่ามีข้อมูลอยู่แล้วหรือไม่
string chk_data = "SELECT * FROM queue_data WHERE last_date = '" + date_started + "' LIMIT 1 ";
MySqlCommand cmd_chk = new MySqlCommand(chk_data, conn);
rdr = cmd_chk.ExecuteReader();
if (rdr.Read())
{
//ถ้ามีข้อมูลอยู่แล้วให้ update
count = int.Parse(rdr.GetString(2));
count += 1;
Console.WriteLine("count = " + count + ", last_number=" + last_number + ",last_time =" + time_started + ", last_date = " + date_started);
if (rdr != null)
{
rdr.Close(); // ต้อง close ก่อนไม่งั้นจะมี error There is already an open DataReader
}
sql = "UPDATE queue_data SET count = " + count + ", last_number = " + last_number + ", last_time = " + time_started + ", status = false WHERE last_date ='" + date_started + "' ";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
else
{
//ถ้าไม่มีข้อมูลให้ Insert
}
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
ผลการ Run
Code
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
4: 3
count = 6, last_number=3,last_time =15:22:25, last_date = 2014-12-22
Error: MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':22:25, status = false WHERE last_date ='2014-12-22'' at line 1
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at Queue_Read.Form1.read_btn_Click(Object sender, EventArgs e) in C:\Users\Admin\documents\visual studio 2010\Projects\Queue_Read\Queue_Read\Form1.cs:line 252
The thread 'vshost.RunParkingWindow' (0x1d3c) has exited with code 0 (0x0).
The thread '<No Name>' (0x178) has exited with code 0 (0x0).
The program '[6796] Queue_Read.vshost.exe: Managed (v2.0.50727)' has exited with code 0 (0x0).
The program '[6796] Queue_Read.vshost.exe: Program Trace' has exited with code 0 (0x0).
ดาต้าเบส
Code
CREATE TABLE `queue_data` (
`id` int(11) NOT NULL auto_increment,
`last_number` int(11) NOT NULL,
`channel` int(11) NOT NULL,
`count` int(11) NOT NULL,
`language` char(3) NOT NULL,
`last_date` date NOT NULL,
`last_time` time NOT NULL,
`status` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;