locked
C# oleDBDataAdapter and results that contain a Dash (-) RRS feed

  • Question

  • User-433253235 posted

    I am running a query on an Access 2000 database from my C# code.  Whenever any part of my results contain a dash (-) then my adapater will not fill and will just show no rows.  So what I did was use the Replace function in the Access Query.  Of course once I did this then I received an error from ASP, because the Jet engine does not know what to do with Replace. 

    So my question is how do I get this data to fill my adapater if it sometimes contains a dash, but I can't use the Replace function in the Access query?  My current C# code is below.  Thanks for your help. 

    DataSet myDataSet1 = new DataSet();

    OleDbConnection con = new OleDbConnection();
    string constring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + myDB.ToString();
    con.ConnectionString = constring;
    con.Open();

    OleDbDataAdapter myAdapter = new OleDbDataAdapter("myquery", con);
    myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
    myAdapter.SelectCommand.Parameters.Add("mydate", OleDbType.VarChar).Value = myDateToQuery;
    myAdapter.Fill(myDataSet1);

    Thursday, April 29, 2010 12:48 PM

Answers

  • User-1199946673 posted

    Yes the second one will not work, because of the replace function! But the first one might run, but most likely it will return 0 records! You're using the Like operator, and the* as the wildcard character. This will work in Access, but in Jet, the wildcard charater is % (like in SQL SERVER).

    In Jet

    [description] Like "*something else*"

    is the same as

    [description] = "*something else*"

    Instead, use

    [description] Like "%something else%"

    Also I se you're using

    [date_entered] like [mydate]

    note that the like operator only works for strings, not for datevalues so if it is a date fields this will not work either. So I think you need to modify your SQL statement first before jumping to conclusion that moght nit be the case!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 29, 2010 5:21 PM

All replies

  • User-1199946673 posted

    Whenever any part of my results contain a dash (-) then my adapater will not fill and will just show no rows.
     

    I don't understand what you mean by this?

    So what I did was use the Replace function in the Access Query.  Of course once I did this then I received an error from ASP, because the Jet engine does not know what to do with Replace.

    That's correct If I try to follow you, I think you mean that you a column with datatype text (or memo), and it value contain a dash?

     

    Thursday, April 29, 2010 1:01 PM
  • User-433253235 posted

    I don't understand what you mean by this?

    Meaning that whenever my resulting data does not contain a dash my adapter fills properly.  But if the resulting data contains a dash then my adapater treats it as if there was no data returned.

    That's correct If I try to follow you, I think you mean that you a column with datatype text (or memo), and it value contain a dash?


    Yes.

    Thursday, April 29, 2010 1:42 PM
  • User-1199946673 posted

    Can you show the SQL of "myquery"? 

    Thursday, April 29, 2010 1:46 PM
  • User-433253235 posted

    Can you show the SQL of "myquery"? 



    This is what I currently have:

    SELECT format([this date],'mmddyyyy')+"|"+[Btc] & "|" & [Type] & "|" [Description]  & "|" & [Amount] AS Expr1, [that date]
    FROM myTable
    WHERE [type]="something" And [description] like "*something else*" and [date_entered] like [mydate]
    ORDER BY [that date]
    


    I tried this and it works fine directly from Access, but gives me an error from C# when trying to run it, because the Jet engine does not understand Replace:

    SELECT format([this date],'mmddyyyy')+"|"+[Btc] & "|" & [Type] & "|" Replace([Description], "-", " ")  & "|" & [Amount] AS Expr1, [that date]
    FROM myTable
    WHERE [type]="something" And [description] like "*something else*" and [date_entered] like [mydate]
    ORDER BY [that date]


    Thursday, April 29, 2010 2:35 PM
  • User-1199946673 posted

    Yes the second one will not work, because of the replace function! But the first one might run, but most likely it will return 0 records! You're using the Like operator, and the* as the wildcard character. This will work in Access, but in Jet, the wildcard charater is % (like in SQL SERVER).

    In Jet

    [description] Like "*something else*"

    is the same as

    [description] = "*something else*"

    Instead, use

    [description] Like "%something else%"

    Also I se you're using

    [date_entered] like [mydate]

    note that the like operator only works for strings, not for datevalues so if it is a date fields this will not work either. So I think you need to modify your SQL statement first before jumping to conclusion that moght nit be the case!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 29, 2010 5:21 PM
  • User-433253235 posted

    Yes the second one will not work, because of the replace function! But the first one might run, but most likely it will return 0 records! You're using the Like operator, and the* as the wildcard character. This will work in Access, but in Jet, the wildcard charater is % (like in SQL SERVER).

    In Jet

    [description] Like "*something else*"

    is the same as

    [description] = "*something else*"

    Instead, use

    [description] Like "%something else%"

    Also I se you're using

    [date_entered] like [mydate]

    note that the like operator only works for strings, not for datevalues so if it is a date fields this will not work either. So I think you need to modify your SQL statement first before jumping to conclusion that moght nit be the case!

    The date part works totally fine.  But you were right on the money with the * and %.  Once I changed it to percent I was able to return data.  Thank you very much for your help. 

    Friday, April 30, 2010 9:28 AM