How To Insert Multiple Rows

doyler442

Hi All

I'm using Vs 2005 and MySQL as my database.

I'm connected to my DB and I'm able to write multiple items into a row but for some reason its only inserting one row and when I try it again nothing happens.

Here is what I have done:

try
{
myConn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=project;User=root;Password=*****;");

myConn.Open();
string sql = "INSERT INTO lancasterhall (Location, Price) VALUES ('3', '4')";

OdbcCommand cmd = new OdbcCommand(sql, myConn);

MyReader = cmd.ExecuteReader();

while (MyReader.Read())
{
Console.WriteLine(MyReader[""].ToString());
}

MyReader.Close();
}
catch (Exception ee)
{
Console.WriteLine(ee.ToString());
}
finally
{
if (MyReader != null && !MyReader.IsClosed)
{
MyReader.Close();
}

Could someone please tell me how i get this to continously insert a new row each time it runs.

Thanks

UPDATE: When I change the values I have set above it does insert a new row but I want to insert a new row each time without having to change these

nialo

Dont you ExecuteReader for one thing. ExecuteNonQuery()

create a loop with your insert statement inside this. declare your command. set the values and then call executeNonQuery on it. this will continue till your loops finishes.

Not pretty but you get the idea.

stevenmu

I think your problem is on the data side not the code side. The code looks fine (apart from the executereader executenonquery thing). Basically your problem is in trying to insert the same data each time. Is location possibly a primary key or have some kind of unique constraint on it? This would mean that each row must have a different value for location. If this is the case I'd have expected trying to insert the same data again to cause an actual exception, but I don't know MySQL, maybe not ?

Either way it's very bad practice to not have something uniquely identifying each row, even if it's logically ok to have multiple rows with the same location/price you should have an extra column in that's unique for each row.

doyler442

Cheers for the replies guys.

I did what you said by inserting a Primary key and like you said the data now enters into multiple rows at a time.

Cheers