Failed to convert parameter value from a Binary to a Byte[]. -- SqlDataSource w/ SQL Timestamp parameter

Question

First, unfortunately I can't change the database and looking for a "strapped for time" solution. I realize what I'm asking for isn't best practice, but enough disclaimers...

MS SQL Server
C# / .NET 4.xx
WebForms
dbo.MyTable -- apps are not allowed to access tables directly
series of dbo.MyTable_[OP] CRUD stored procs (_GET, _INSERT, _UPDATE, etc)
MyTable has a Timestamp column called "ts"

The CRUD procs, where appropriate, all include a input/output parameter of @ts to avoid concurrency issues.

I'm using DevExpress WebForms GridView - works perfectly, not the issue.

The GridView is using the MS SqlDataSource. Its select command is calling the MyTable_List proc. Its insert command it calling the MyTable_Insert proc. Both of these work flawlessly.

Updating is not. Been up and done the web trying to figure out if this is even possible. So update is basically this:

SQL:

CREATE PROC MyTable_Update
  @key int
  @name varchar(100)
  @ts timestamp output
AS
  IF (SELECT TOP 1 1 FROM MyTable WHERE key = @key and ts = @ts) IS NULL 
BEGIN
   RAISEERROR('someone else changed this, reload, try again, 00, 00);
   RETURN;
END
--[simple update the table logic]
SELECT @ts = ts FROM MyTable WHERE key = @key

The datasource:

<asp:SqlDataSource id="ds" runat="server" ... OnUpdating="ds_Updating" UpdateCommand="MyTable_UPDATE" UpdateCommandType="StoredProcedure">
  <UpdateParameters>
    <asp:Parameter Name="key" Type="Int32" />
    <asp:Parameter Name="name" Type="String" />
    <asp:Parameter Direction="InputOutput" Name="ts" Type="String" DefaultValue="0" />  <!-- i know, bear with me -->
   </UpdateParameters>
 </asp:SqlDataSource>

The C#:

protected void myGrid_StartRowEditing(object sender, [DevEx].ASPxStartRowEditingEventArgs e)
{  
  //get the timestamp value from the db for this row being edited
  int key = (Int32)myGrid.GetRowValuesByKeyValue(e.EditingKeyValue, "key");
  var ts = db.MyTable_Get(key).First().ts; //actually getting the ts from the database
  Session["MyTable_ts"] = ts;
}

protected void ds_Updating(object sender, SqlDataSourceCommandEventArgs e)
{ 
  //override what was set in the aspx
  var p = e.Command.Parameters["@ts"];
  p.DbType = Binary;
  p.Direction = ParameterDirection.InputOutput;
  p.Size = 8;
  var ts = (System.Data.Linq.Binary)Session["MyTable_ts"];
  p.Value = ts;
}

Finally, when I start edit on a row in the grid, StartRowEditing fires, saves the timestamp value from the db into the session var as expected (obviously for the format in .NET is different that SQL). When I try to commit the change (click "update" on the DevEx grid editor"), ts_Updating fires off, all the code completes with no exceptions. Then when the SqlDataSource fires the actual update, it throws "Failed to convert parameter value from a Binary to a Byte[]." to the DevEx grid and reports that through the grid's UI.

How can I (is it even possible?), to shuffle the db timestamp value into a session, then send it through the SqlDataSource update parameter?

Again, I realize I could overcome this by writing out all the CSLA or even just using ObjectDataSource and writing out all the update logic, but I'm pressed for time for a few grids that I just need to get done and move on -- bigger fish in the project to fry.

THANK YOU!

The cast '(System.Data.Linq.Binary)Session["MyTable_ts"]' looks wrong to me, can you try casting to byte[]? — Menahem, Jan 11 '19 at 07:54
What is the type of `db.MyTable_Get(key).First().ts`? What version of MSSQL are you using? — Ian Kemp, Jan 11 '19 at 09:41
@IanKemp - the type for db.MyTable_Get(key).First().ts is System.Data.Linq.Binary — Ted Krapf, Jan 11 '19 at 14:53

score 1 · Answer 1 · answered Jan 11 '19 at 15:31

Figured it out -- was stupid simple and I overlooked it. System.Data.Linq.Binary's ToArray() will cast to a Byte[]. Adding .ToArray() to ts on the last line of the function will convert to the appropriate type that the SqlDataSource can communicate back to the DB, allowing the Timestamp value to match, informing the db there isn't a concurrency issue.

protected void ds_Updating(object sender, SqlDataSourceCommandEventArgs e)
{ 
  //override what was set in the aspx
  var p = e.Command.Parameters["@ts"];
  p.DbType = Binary;
  p.Direction = ParameterDirection.InputOutput;
  p.Size = 8;
  var ts = (System.Data.Linq.Binary)Session["MyTable_ts"];
  p.Value = ts.ToArray();  //Binary must be cast to an Array (of Byte)
}

Failed to convert parameter value from a Binary to a Byte[]. -- SqlDataSource w/ SQL Timestamp parameter

1 Answers1