Read Data from .dbf File with OdbcConnection

Question

I am using visual studio 2010 on Win 7. I want to read a .dbf file and get the minimum value of a selected column.
Here is what I have:

System.Data.Odbc.OdbcConnection oConn = new System.Data.Odbc.OdbcConnection();
oConn.ConnectionString = @"Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=" + ImportDirPath + ";Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
oConn.Open();

// Update time
string nowTime = DateTime.Now.ToString("HHmmss");
oCmd.CommandText = @"UPDATE " + tableName + " SET HQCJBS = " + nowTime + " WHERE HQZQDM = ?";
oCmd.Parameters.AddWithValue("row2", "000000");
oCmd.ExecuteNonQuery();


string query = "SELECT MIN(" + colName + ") FROM " + tableName + " WHERE HQZQDM <> 000000";
OdbcDataAdapter da = new OdbcDataAdapter(query, oConn);
DataSet ds = new DataSet();
da.Fill(ds);

Suppose colName and tableName are correct. I have two question.

Two Questions:
When the code da.Fill(ds); is hit, I got an error data type mismatch in criteria expression access, What is wrong?
After I get the minimum value from the database, how can I input it into the memory as a double, for example double min = ds.Tables[0];

It looks like 'HQZQDM' field is not of numeric type. This may cause the data type mismatch error. — Oleg
– Oleg, Commented Jun 24, 2015 at 6:01

DRapp · Accepted Answer · 2015-06-24 13:34:09Z

Your update command should apply TWO parameters... one for the set, another for the where clause. Use the "?" place-holder for each respectively and add the parameters in the same order as they appear in the query.

string nowTime = DateTime.Now.ToString("HHmmss");
oCmd.CommandText = @"UPDATE " + tableName + " SET HQCJBS = ? WHERE HQZQDM = ?";
oCmd.Parameters.AddWithValue("setParm", nowTime );
oCmd.Parameters.AddWithValue("whereParm, "000000");
oCmd.ExecuteNonQuery();

For your select MIN() query, it appears your WHERE criteria column is a string and by having the literal numbers without quotes is applying it as a numeric... Again, stick with "?" parameters

OdbcCommand getMinCmd = new OdbcCommand("", oConn);
getMinCmd.CommandText = "SELECT MIN(" + colName 
       + ") FROM " + tableName + " WHERE HQZQDM <> ?";
getMinCmd.Parameters.AddWithValue("whereParm, "000000");

OdbcDataAdapter da = new OdbcDataAdapter(getMinCmd);
DataSet ds = new DataSet();
da.Fill(ds);

Finally, to get the value OUT of the retrieved query into memory, you need to get the row of the table (only 1 record result set, zero-based index) and column-0 too. Since you did not assign a column name, you don't know the column and just go with 0-index...

int lowestValue = (int)ds.Tables[0].Rows[0][0];

Think of the above as the hierarchy...

ds
  tables[0]
    rows[0]
      [column 0]
    rows[1]  -- but your query would only have one row anyhow
      [column 0]
  tables[1]  -- if your query had multiple queries, this might be available

but at least it shows where the pieces are.

Now, if you change your query slightly to something like

select MIN( colName ) as MyMinValue ...

then your reference would be where you explicitly name the column from the row retrieved

int lowestValue = (int)ds.Tables[0].Rows[0]["MyMinValue"];

Collectives™ on Stack Overflow

Read Data from .dbf File with OdbcConnection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related