MySQL Connected to C# problem

dulpit

hey, i have a small database, done in MySQL... I can connect to it through C# alright, but i'm having a problem reading in more than 1 row at a time...

I have a table in DB which stores details on trucks, I want to read in the TruckID for each. What i do is a SELECT * FROM TruckDetails; and then do the following:

            while (Reader.Read())
            {
                truckList[i] = Reader.GetValue(0).ToString();
                i++;
            }

however, all i get is the 1st truckID, copied 10 times into my truckList struct, instead of the names of the 10 different trucks...

Am i making a real dumb mistake?

aidan_walsh

Reader.GetValue(i).ToString()?

dulpit

Nah, the number in getValue() refers to which field to take it out from (i.e. 0 is the first column in my table etc.)

Evil Phil

I can't see anything wrong with that. Can we see the code for the truckList struct?

fasty

Can you check the RecordsAffected property of your DataReader to make sure it matches the number of records in the table? Do you set i = 0?

patrickolee

try something like?

static SqlConnection cn = new SqlConnection("your connection string");
static SqlDataAdapter da = new SqlDataAdapter("your sql here", cn);
static DataTable dataTable = new DataTable();

int recordsAffected = da.Fill(dataTable);

if (recordsAffected > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
System.Console.WriteLine(dr[0]);
}
}

dulpit

Evil Phil wrote:

I can't see anything wrong with that. Can we see the code for the truckList struct?

It's just an array of strings at the moment... I'm still playing around with things so its not too detailed....

fasty wrote:

Can you check the RecordsAffected property of your DataReader to make sure it matches the number of records in the table? Do you set i = 0?

RecordsAffected is showing up as -1 in the debugger... Am i right in saying that no records would be affected in a SELECT statement?

It's wrecking my buzz, can't see why it wouldn't work, its exactly the code i'm after getting off a number of different websites... :mad:

fasty

Even a select statement will update the RowsAffected property, a select on a table with no rows would at least set it to 0 so -1 implies that something's not quite right.

Can you show us the code where you send the query to the database? There might be a small change needed there.

dulpit

Ok, i tinkered with the code slightly and it's now working

What i did was read in from the database into a temporary string, and added this string into my array... Seems to work perfectly too...

command.CommandText = "select * from truckDetails";
connection.Open();
Reader = command.ExecuteReader();
int i = 0;
while (Reader.Read())
{
     string tempString = "";
     tempString += Reader.GetValue(0).ToString();
     myStringArray[i] = tempString;
     i++;
}