4

This is my code:

OleDbConnection connection = new OleDbConnection(
   "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\Offline.accdb;Persist Security Info=False");
connection.Open();
OleDbCommand command = connection.CreateCommand();
command.CommandText = "SELECT DISTINCT B.* FROM BlankFormSubmissions B, Actions A WHERE A.FormName = 'FindingNemo' AND B.ProcessName = A.ProcessName AND B.ActionName = A.ActionName AND B.ID = 12 OR B.ID = 13 OR B.ID = 14 ORDER BY B.ID";
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    string xml = (string)reader["XML"];
    // ... do something with xml
}

The column, "XML", is an Access Database table column, of type memo.

The value of xml always contains only the first characters of the XML. I'm guessing it's the first 256 characters. Obviously, I need all of the characters in the string.

Anyone know how to fix this?

Thanks :)

Tony Toews
  • 7,850
  • 1
  • 22
  • 27
Stephen Oberauer
  • 5,237
  • 6
  • 53
  • 75
  • Just a guess - have you tried crafting the select statement so the XML column is last? In some of the older database systems, the large columns had to be last in the select list. – RQDQ Mar 01 '11 at 13:43
  • Thanks. I gave that a try, but unfortunately still get the same problem :( – Stephen Oberauer Mar 01 '11 at 14:12

2 Answers2

6

The problem could be the memo field itself;

Memo fields can't be used in Aggregate Arguments ( like Max, Var, Sum etc. ) If used in 'Group By' totals in a query only the first 255 characters are returned. 'Having' and 'Where' clauses in Group Aggregate functions also return only the first 255 chars However, using 'First' or 'Last' arguments return the full length of the string.

Is this the entire SQL statement?

Johnny DropTables
  • 656
  • 5
  • 15
  • The poster isn't doing aggregation... "SELECT * FROM BlankFormSubmissions" – RQDQ Mar 01 '11 at 13:48
  • Although you don't appear to be using aggregates, it might be worth trying to change to a text field and checking it. – Johnny DropTables Mar 01 '11 at 13:49
  • Sorry guys... I thought my SQL statement would have nothing to do with the problem, so I summarized it to make my code easier to read. I actually found that when I use the simpler SQL, it does work, so Johnny may actually be right. – Stephen Oberauer Mar 01 '11 at 14:03
  • I've edited my code above and put in the actual SQL statement that's causing the problem. – Stephen Oberauer Mar 01 '11 at 14:04
  • @Johnny DropTables - I can't revoke my downvote unless you edit the question. Can you please do that do I can upvote? – RQDQ Mar 01 '11 at 14:09
  • The "DISTINCT" was causing the problem. I voted the answer up one... so I hope that cancels out the down vote... not too sure how these points work. – Stephen Oberauer Mar 01 '11 at 14:17
  • 3
    You seriously have to ask yourself if you have a schema error if you think you need to include a memo field in a SELECT DISTINCT. – David-W-Fenton Mar 04 '11 at 03:51
0

this way;)

 OleDbCommand sqlcmdCommand1 = new OleDbCommand("select stmt", sqlconConnection);
        sqlcmdCommand1.CommandType = CommandType.Text;
        try
        {
            sqlconConnection.Open();
            OleDbDataReader sdaResult = sqlcmdCommand1.ExecuteReader();
            myclass a = new myclass();
            while (sdaResult.Read())
            {

               a.i = sdaResult.GetString(2);
               or 
               int i = sdaResult.GetString(2)); 
              // 2 is the index of your column, in general start from 0;'
            }

if this do not work , i mean if you the value of my_memo_value is null then: create a class in which you get and set value of string and int. and then use it here Like

Myclass {
Public int i{get;set}
}
safi
  • 3,636
  • 8
  • 24
  • 37