SQL TimeStamp in DataViewGrid

[JerryShaw]

Well I guess it is my turn to ask a question, since most of the time I spend answering questions on DaniWeb.

The Problem: DataViewGrid throws a "System.ArgumentException parameter is not valid..."
when the dataset contains an SQL 2005 TimeStamp type column.

The error occurs in System.Drawing.FromStream... da da da
It is because a TimeStamp is a Byte[8] unique value that my grid thinks is an image.

I am creating a type of Query Analyzer form. A simple select * from mytable is given to an adapter...

QueryDataSet.Tables.Clear();
                SqlDataAdapter adapter = new SqlDataAdapter(Query, conn);
                adapter.Fill(QueryDataSet,"Table");
                DataResultsGrid.DataSource = QueryDataSet.Tables[0];
                QueryBindingSource = new BindingSource(QueryDataSet, "Table");
                DataResultsGrid.DataSource = QueryBindingSource;

The dataset reads the schema when I set its datasource to this table, later I set it to the bindingsource to get it hooked. I can pass any SQL statement and it works fine... until the schema contains a TimeStamp column and the datagrid is scrolled to the right where the timerstamp column would be visible.. then this exception is thrown for each row.

Any ideas ?

// Jerry

[Ramy Mahrous]

Sorry Jerry I was out Cairo last 4 days, I didn't read your question else now so binary columns by default in gridview read as image, in your scenario VS tries to convert your binary data (timestamp) to image so it fails so it throws exception!!! try to change the gridview control in you timestamp column to text data..

[JerryShaw]

Hi Ramy,

Hope your trip to Cairo was all good.
As for the TimeStamp DataGridView column issue. I found that if the AutoGenerateColumns property is true (default), then the column will get bound, and you cannot do anything with it, other than drop that column, and add it back manually as a DataGridTextBoxColumn.
The Microsoft C# team says that once a column has been added to a grid, its type can not be changed. You have to delete it, and add it back as the other type. There is one caveat to this. You can exchange type between Combo and Text classes. Can't change between Image (aka Byte[]) and TextBox columnn types.

Once in the TextBoxColumn type, then you can to handle it in the CellFormatting event handler. Concat the bytes to hex into the args. e.Value property.

To just prevent the grid from blowing up, you can create a handler for the DataError event. Interestingly you don't need to add code to this handler. Its presence is all that is needed.

Obviously setting the Visible property to false also works, but the idea was to show all column values in a readonly grid.

So... (after all that hot wind), I am going to tackle creating all the columns at runtime based on the dataset, and schema.
BTW: I finally ran across a code snipet that allows you to find out what the REAL sql type is for a DataTable column. If you ever need to know how, drop me a line and I will show you the code. Bascially there are 30 properties for each column in an SQL pumped DataSet. Came in handly since the DataSet treats an Image, Blob and TimeStamp all as Byte arrays. I needed to know exactly what SqlType I was dealing with.

Anyway thanks for looking in on me,
Jerry

[Ramy Mahrous]

Thanks for your great explanation Jerry although you're the one who asks :) sure show the line of code it'd help me if I were in your place, Thanks again Jerry!

[work.caglar]

Hi, Jerry, I'm having the same problem. my value is 0x000000003DD56F2A type timestamp and I'm having trouble. I wrote code to share the solution, but it didn't work. What should I do.

thank you.