Page 1 of 1

SQL queries with parameters

Posted: Tue 01 Feb 2005 01:58
by smark
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.

Posted: Tue 01 Feb 2005 10:45
by Serious
Try to use insert into () values() syntax. Before write us check your query in console (to example: MySQL Query Browser).
If it is still not working with MySQLDirect .NET send us small test project to reproduce the problem; it is desirable to use 'test' schema objects otherwise include definition of your own database objects; don't use third party components.