SQL queries with parameters
Posted: Tue 01 Feb 2005 01:58
I have had this provider since 2.30 but only now have I started using it regularly (version 2.70). I realize that it will take me some time to get used to this. I am using VS 2003 and ASP.NET.
I get the following error when I try to pass parameter values to the database to see if the name already exists.
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 ' MI= 'C.', Lastname= 'Bass' ' at line 1
Here is the code:
MySqlConnection cn = new MySqlConnection(csb.ConnectionString);
MySqlCommand cmdC = new MySqlCommand();
cmdC.CommandText = ("Select authorid from author where firstname = :firstName, MI= :MI, Lastname= :LastName ");
cmdC.Parameters.Add("firstname", txtFirst.Text.ToString());
cmdC.Parameters.Add("LastName", txtLast.Text.ToString());
cmdC.Parameters.Add("MI", txtMiddle.Text.ToString());
cn.Open();
cmdC.Connection = cn;
MySqlDataReader dr = cmdC.ExecuteReader();
txtResult.Text = dr.RecordCount.ToString();
if (txtResult.Text != null)
{
lblAuthorAdded.Text = "This author is already in the database.";
}
else
{
MySqlCommand cmd = new MySqlCommand("Insert Into Author Set FirstName = :FirstName, MI = :MI, LastName = :LastName ");
cmd.Parameters.Add("FirstName", txtFirst.Text.ToString());
cmd.Parameters.Add("MI", txtMiddle.Text.ToString());
cmd.Parameters.Add("LastName", txtLast.Text.ToString());
cn.Open();
cmd.Connection = cn;
cmd.ExecuteNonQuery();
if(cmd.InsertId.ToString() != "")
{
lblAuthorAdded.Text = txtFirst.Text + " " + txtMiddle.Text+ " " + txtLast.Text + " " +"has been added to the Authors database.";
txtFirst.Text = "";
txtMiddle.Text = "";
txtLast.Text = "";
}
cn.Close();
}
Any ideas what am I doing wrong? Thanks.
I get the following error when I try to pass parameter values to the database to see if the name already exists.
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 ' MI= 'C.', Lastname= 'Bass' ' at line 1
Here is the code:
MySqlConnection cn = new MySqlConnection(csb.ConnectionString);
MySqlCommand cmdC = new MySqlCommand();
cmdC.CommandText = ("Select authorid from author where firstname = :firstName, MI= :MI, Lastname= :LastName ");
cmdC.Parameters.Add("firstname", txtFirst.Text.ToString());
cmdC.Parameters.Add("LastName", txtLast.Text.ToString());
cmdC.Parameters.Add("MI", txtMiddle.Text.ToString());
cn.Open();
cmdC.Connection = cn;
MySqlDataReader dr = cmdC.ExecuteReader();
txtResult.Text = dr.RecordCount.ToString();
if (txtResult.Text != null)
{
lblAuthorAdded.Text = "This author is already in the database.";
}
else
{
MySqlCommand cmd = new MySqlCommand("Insert Into Author Set FirstName = :FirstName, MI = :MI, LastName = :LastName ");
cmd.Parameters.Add("FirstName", txtFirst.Text.ToString());
cmd.Parameters.Add("MI", txtMiddle.Text.ToString());
cmd.Parameters.Add("LastName", txtLast.Text.ToString());
cn.Open();
cmd.Connection = cn;
cmd.ExecuteNonQuery();
if(cmd.InsertId.ToString() != "")
{
lblAuthorAdded.Text = txtFirst.Text + " " + txtMiddle.Text+ " " + txtLast.Text + " " +"has been added to the Authors database.";
txtFirst.Text = "";
txtMiddle.Text = "";
txtLast.Text = "";
}
cn.Close();
}
Any ideas what am I doing wrong? Thanks.