MySQL connection error that I have never seen
New mysql error:
ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.5.9]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 'System.Data.Odbc.OdbcCommand' at line 1
Ive never seen this error before and dont know what its relating to?
using (OdbcConnection connection = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite2; User=root; Password=fakepass;"))
{
// ODBC command and transaction objects
OdbcCommand command = new OdbcCommand();
OdbcTransaction transaction = null;
// tell the command to use our connection
command.Connection = connection;
try
{
// open the connection
connection.开发者_如何学GoOpen();
// start the transaction
transaction = connection.BeginTransaction();
// Assign transaction object for a pending local transaction.
command.Connection = connection;
command.Transaction = transaction;
// TODO: Build a SQL INSERT statement
OdbcCommand cmd = new OdbcCommand("INSERT INTO User (Email, FirstName, SecondName, DOB, Location, Aboutme, username, password) VALUES ('" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "', '" + TextBox4.Text + "', '" + TextBox5.Text + "', '" + TextBox6.Text + "', '" + TextBox7.Text + "', '" + TextBox8.Text + "')", connection);
// run the insert using a non query call
command.CommandText = cmd.ToString();
command.ExecuteNonQuery();
/* now we want to make a second call to MYSQL to get the new index
value it created for the primary key. This is called using scalar so it will
return the value of the SQL statement. We convert that to an int for later use.*/
command.CommandText = "select last_insert_id();";
int id = Convert.ToInt32(command.ExecuteScalar());
Label10.Text = Convert.ToString(id);
// the name id doesnt not exist in the current context
// Commit the transaction.
transaction.Commit();
}
catch (Exception ex)
{
Label10.Text = ": " + ex.Message;
try
{
// Attempt to roll back the transaction.
transaction.Rollback();
}
catch
{
// Do nothing here; transaction is not active.
}
}
}
EDIT:
using (var conn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite2; User=root; Password=fakepass;"))
{
conn.Open();
using (var tx = conn.BeginTransaction())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO User (Email, FirstName, SecondName, DOB, Location, Aboutme, username, password) VALUES (@Email, @FirstName, @SecondName, @DOB, @Location, @Aboutme, @username, @password)";
cmd.Parameters.AddWithValue("@Email", TextBox1.Text);
cmd.Parameters.AddWithValue("@FirstName", TextBox2.Text);
cmd.Parameters.AddWithValue("@SecondName", TextBox3.Text);
// TODO: might require a parsing if the column is of type date in SQL
cmd.Parameters.AddWithValue("@DOB", TextBox4.Text);
cmd.Parameters.AddWithValue("@Location", TextBox5.Text);
cmd.Parameters.AddWithValue("@Aboutme", TextBox6.Text);
cmd.Parameters.AddWithValue("@username", TextBox7.Text);
cmd.Parameters.AddWithValue("@password", TextBox8.Text);
cmd.ExecuteNonQuery();
//error on this line
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select last_insert_id();";
int id = Convert.ToInt32(cmd.ExecuteScalar());
Label10.Text = Convert.ToString(id);
}
tx.Commit();
}
}
{"ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."}
Why are you still using the buggy ODBC to connect to MySql when there is an ADO.NET connector? Also what is this horrible string concatenation when forming your query?:
OdbcCommand cmd = new OdbcCommand("INSERT INTO User (Email, FirstName, SecondName, DOB, Location, Aboutme, username, password) VALUES ('" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "', '" + TextBox4.Text + "', '" + TextBox5.Text + "', '" + TextBox6.Text + "', '" + TextBox7.Text + "', '" + TextBox8.Text + "')", connection);
Haven't you heard of SQL injection and parametrized queries which allow to avoid it?
All I can say is that if you use the +
sign when writing a SQL query it's like taking a gun and shooting right at your foot (or head depending on the scenario, but in all cases you are shooting at yourself, basically a suicidal behavior).
So, here's the proper way to do things:
using (var conn = new MySqlConnection("Server=localhost; Database=gymwebsite2; User=root; Password=commando;"))
{
conn.Open();
using (var tx = conn.BeginTransaction())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO User (Email, FirstName, SecondName, DOB, Location, Aboutme, username, password) VALUES (@Email, @FirstName, @SecondName, @DOB, @Location, @Aboutme, @username, @password)";
cmd.Parameters.AddWithValue("@Email", TextBox1.Text);
cmd.Parameters.AddWithValue("@FirstName", TextBox2.Text);
cmd.Parameters.AddWithValue("@SecondName", TextBox3.Text);
// TODO: might require a parsing if the column is of type date in SQL
cmd.Parameters.AddWithValue("@DOB", TextBox4.Text);
cmd.Parameters.AddWithValue("@Location", TextBox5.Text);
cmd.Parameters.AddWithValue("@Aboutme", TextBox6.Text);
cmd.Parameters.AddWithValue("@username", TextBox7.Text);
cmd.Parameters.AddWithValue("@password", TextBox8.Text);
cmd.ExecuteNonQuery();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select last_insert_id();";
int id = Convert.ToInt32(cmd.ExecuteScalar());
Label10.Text = Convert.ToString(id);
}
tx.Commit();
}
}
Also please name those textboxes appropriately. The poor guy that's gonna maintain this code might emit screams of despair.
精彩评论