Oledb: Select after insert fails

  • Thread starter Thread starter vidar
  • Start date Start date
V

vidar

We're having some problems with our 3-tier application. On one ASP.Net
page, we're reading data from one database, and inserts the data into
another database. After the insert statement has run, we select the
same data, and create and object that is used in the business layer.

If we use the same connection object for both the insert and select, it
works. But if we use seperate connections, it always fail, but it never
fails on the same record.

It isn't timing out as it only takes 2-4 seconds before it fails. If it
fails, and we execute the same SQL manually a few seconds later, it
works. It seems like the data isn't written to the database straight
away.

Any clues what might cause this?

The select that fails:

using(OleDbDataReader dr = new OleDbCommand(sql.ToString(),
cn).ExecuteReader()) {
if(dr.Read()) {
row = buildDataValues(dr, dataValueDefData);
} else {
throw new MissingFieldException("Missing data. ");
}
}
 
Sorry, bad wording on my part.. It doesn't fail, it just fails to
return the data I just inserted. If I try the same SQL a few seconds
later, it works. Seems like a delay in writing the data to the database
when using two database connections.

Regards,

Vidar
 
I've managed to solve the problem, but I don't understand why the new
code works and the old fail.. If someone could explain it I would be
grateful:

I changed the code from

public int CreateNode(int contentClassDefId, int moduleId, int siteId,
int parentId) {
using(OleDbConnection cn = new OleDbConnection(_connectionString)) {
cn.Open();
return createNode(contentClassDefId, moduleId, siteId, parentId, cn);
}
}


to

public int CreateNode(int contentClassDefId, int moduleId, int siteId,
int parentId) {
return createNode(contentClassDefId, moduleId, siteId, parentId);
}

and opened the connection in createNode instead(and used try catch
finally instead of using):

OleDbConnection cn = null;
try{
cn = new OleDbConnection(_connectionString);
cn.Open();
OleDbCommand cmd = new OleDbCommand(sql.ToString(), cn);
cmd.ExecuteNonQuery();
}catch(Exception e){

}finally{
cn.Close();
}


Could it be the using(OleDbConnection cn = new
OleDbConnection(_connectionString)) { that doesn't work? Or is it that
I passed the database connection as a parameter to the method?
 
Back
Top