Recently I have been studying how to use parameters to store data from c# to xampp database. I just need a few clarification about some topics.
using System.Data;
using MySql.Data.MySqlClient;
ConnectionClass cc = new ConnectionClass();
private MySqlConnection myConn = new MySqlConnection(ConnectionClass.GetConnection());
private MySqlCommand cmDB;
cc.SetCMD("UPDATE mia_payroll.tbl_attendance SET TimeIn = @TimeIn, Date = @Date, EID = @EID;");
using(myConn) ** // 1) can I do it like this or instantiate another new MysqlConnection?**
{
cmDB = new MySqlCommand(cc.GetCMD(), myConn);
try
{
myConn.Open();
myReader = cmDB.ExecuteReader();
// 2)In MSDN it's SqlDataAdapter is mine okay?
cmDB.Parameters.Add("@EID", Text, 11);
// 3) is the position of this line correct or do I need to adjust this?
// 4) the text in () generates error how do I do this?
cmDB.Parameters["@EID"].Value = lblEID.Text;
// 5) How do I instantiate another 5 variables? (an int, a text, a time, date and a varchar)
// 6) and does parameters allow special characters into database? or should I just use regex?
while (myReader.Read())
{
TimeSpan TIN = myReader.GetTimeSpan("TimeIn");
lblTIN.Text = TIN.ToString(@"hh\:mm\:ss");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
So school is not helping that much for they only taught us only the basic of C# and I have been watching Programming Knowledge from youtube and he doesn't have any vids on parameters.
try this sample, please edit accordingly to suit your needs accordingly.
using System.Data;
using MySql.Data.MySqlClient;
var timeIn = blabla;
var eID = lblEID.Text;
//var blablabla = blablabla;
var query = @"UPDATE mia_payroll.tbl_attendance SET TimeIn = @TimeIn, Date = @Date, EID = @EID"; //are you missing a WHERE clause? otherwise you will update all row. maybe you want to do something like below.
//var query = @"UPDATE mia_payroll.tbl_attendance SET TimeIn = @TimeIn, Date = @Date, EID = @EID WHERE rowID = @rowID";
using(MySqlConnection myConn = new MySqlConnection(ConnectionClass.GetConnection()))
using(MySqlCommand cmDB = new MySqlCommand(query, myConn))
{
try
{
//for your question to me about adding parameters value, just keep adding.
cmDB.Parameters.AddWithValue("@TimeIn", timeIn);
cmDB.Parameters.AddWithValue("@eID", eID);
//cmDb.Parameters....... and so on
//now execute query
myConn.Open();
using(MySqlDataReader myReader = cmDB.ExecuteReader())
{
while(myReader.Read())
{
//do what you want with your result.
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
myConn.Close();
}
}