Jake Cube Jake Cube - 1 month ago 19
ASP.NET (C#) Question

For Loop insert multiple SQL

I'm trying to insert multiple rows of data into

ms-access
by SQL with user insert session. Once I implement it keep show SQL INSERT INTO error, I've checked all my datatypes are correct. Need some helps here..

Button:

protected void AddClassBtn_Click(object sender, EventArgs e)
{
string clvl = DropDownList1.SelectedValue;
DateTime Sdate = Calendar1.SelectedDate;
int Nsession = int.Parse(TextBox1.Text);
string Stime = DropDownList2.SelectedValue;
string duration = DropDownList3.SelectedValue;
string desc = TextBox2.Text;
int teacher = int.Parse(DropDownList4.SelectedValue);
DBconn.AddClass(clvl, Sdate, Nsession, Stime, duration, desc, teacher);


}

Method to insert SQL:

public static void AddClass(string classlevel, DateTime Sdate,int Nsession, string stime, string duration, string desc, int Tid)
{
int no = Nsession;
for (int i=0; i < no; i++)
{
OleDbConnection myconnection = GetConnection();
DateTime d = Sdate;
d.AddDays(i * 7).ToShortDateString();
string mysql = "INSERT INTO Classes (ClassLevel, StartDate, StartTime, Duration, Desc, TID) VALUES('" + classlevel+"', '"+ d.ToShortDateString()+"'. '"+ stime + "', '"+ duration + "', '"+ desc + "', '"+ Tid + "')";
OleDbCommand mycmd = new OleDbCommand(mysql, myconnection);
try
{
myconnection.Open();
mycmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
myconnection.Close();
}
}
}

Answer
VALUES('" + classlevel+"', '"+ d.ToShortDateString() +"'. '"+ stime + "', '"+ duration + "', '"+ desc + "', '"+ Tid + "')

You have . which should be removed after d.ToShortDateString() and replace it with , . Also use Command.Parameters to avoid this problems in the future and protect you from Sql Injection.

string mysql = "INSERT INTO Classes (ClassLevel, StartDate, StartTime, Duration, Desc, TID) VALUES(@ClassLevel, @DateString, @Stime, @Duration, @Desc, @TID)";
OleDbCommand myCmd = new OleDbCommand(mysql, myconnection);

myCmd.Parameters.AddWithValue("@ClassLevel", classlevel);
// my advise is to not store date as string. But you should dicide for yourself!
myCmd.Parameters.AddWithValue("@DateString", d.ToShortDateString());
myCmd.Parameters.AddWithValue("@Stime", stime);
myCmd.Parameters.AddWithValue("@Duration", duration);
myCmd.Parameters.AddWithValue("@Desc", desc);
myCmd.Parameters.AddWithValue("@TID", Tid);

Be aware the OleDbCommand doesn't have named parameters concept, so you MUST define the parameters in same sequence like in the query !