Updating SQL2005 table containing timestamp column

Guest · Feb 6, 2006

I hope I have the right forum for this question. I have an existing Windows
application which uses a SQL Server database and stored procedures. I am now
developing a web application to use the same database.

The original Update and Delete SP's all use a timestamp for concurreny
checking.

I am trying to use the same Update SP from my sqlDataSource but I keep
getting the following error:

Operand type clash: sql_variant is incompatible with timestamp

Can anyone help please?

Kevin Yu [MSFT] · Feb 7, 2006

Hi Mal,

Do you mean that when you use the same SP to do the update in
sqlDataSource, the Operand type clash error was generated? If yes, could
you show me the SP. If the SP has paramters, can you show me how the
parameters were passed?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Guest · Feb 7, 2006

SP is as follows:
CREATE PROCEDURE [dbo].[updatecustomer]
@customerid int,
@customername char(40),
@address1 char(30),
@address2 char(30),
@town char(30),
@county int,
@postcode char(10),
@country int,
@phone char(20),
@fax char(20),
@web char(40),
@assignedto int,
@details text,
@lastupdated datetime,
@updateby char(30),
@timestamp timestamp
as
update customer set customername = @customername, address1 =
@address1,address2 = @address2, town = @town,
county = @county, postcode = @postcode,country = @country, phone =
@phone,
fax = @fax, web = @web, assignedto = @assignedto, details = @details,
updateby = @updateby, lastupdated = @lastupdated
where customerid = @customerid
and timestamp = @timestamp

Parameters are passed using a sqlDataSource using this update procedure as
the update method

Parameters are bound in a textbox on a FormView control as Bind("timestamp")

Kevin Yu [MSFT] · Feb 8, 2006

Hi Mal,

I tested it on my machine with a timestamp field checking concurrency, and
it works fine. Does this still occurs if you recreate the sqlDataSource?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Guest · Feb 9, 2006

Sorry I didnt quite get the last answer right, I have an object datasource
which has a business class called Customer, this is in a class file in my
App_Code folder. i have set up data methods within the class which I use in
the ObjectDataSource as follows:

I think that maybe the timestamp property is not correct:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

/// <summary>
/// Summary description for Customer
/// </summary>
public class Customer
{
private int _CustomerID, _County, _Country, _AssignedTo;
private string _CustomerName, _Address1, _Address2, _Town, _PostCode,
_Phone, _Fax, _Web, _Details, _UpdateBy, _CurrentUserName;
private DateTime _LastUpdated;
private byte[] _TimeStamp;

public Customer()
{

}

#region Get & Set Methods
public string CurrentUserName
{
get { return _CurrentUserName.Trim(); }
set { _CurrentUserName = value; }
}

public string CustomerName
{
get { return _CustomerName.Trim(); }
set {_CustomerName = value; }
}

public string Address1
{
get { return _Address1.Trim(); }
set { _Address1 = value; }
}

public string Address2
{
get { return _Address2.Trim(); }
set { _Address2 = value; }
}

public string Town
{
get { return _Town.Trim(); }
set { _Town = value; }
}

public string PostCode
{
get { return _PostCode.Trim(); }
set { _PostCode = value; }
}

public string Phone
{
get { return _Phone.Trim(); }
set { _Phone = value; }
}

public string Fax
{
get { return _Fax.Trim(); }
set { _Fax = value; }
}

public string Web
{
get { return _Web.Trim(); }
set { _Web = value; }
}

public string Details
{
get { return _Details.Trim(); }
set { _Details = value; }
}

public string UpdateBy
{
get { return _UpdateBy; }
set { _UpdateBy = value; }
}

public int CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}

public int County
{
get { return _County; }
set { _County = value; }
}

public int Country
{
get { return _Country; }
set { _Country = value; }
}

public int AssignedTo
{
get { return _AssignedTo; }
set { _AssignedTo = value; }
}

public DateTime LastUpdated
{
get { return _LastUpdated; }
set { _LastUpdated = value; }
}

#endregion

#region Select, Insert, Update, Delete Methods
public System.Data.DataSet Select(Int32 customerID)
{
try
{
DataSet ds = new DataSet();

SqlHelper.FillDataset(ConfigurationManager.ConnectionStrings["ToolkitServer"].ConnectionString,
CommandType.StoredProcedure,
"getcustomer", ds, new string[] { "customer" },
new SqlParameter("@customerid",
customerID));
this.CustomerID =
Convert.ToInt32(ds.Tables[0].Rows[0]["customerid"]);
this.CustomerName =
ds.Tables[0].Rows[0]["customername"].ToString();
this.Address1 = ds.Tables[0].Rows[0]["address1"].ToString();
this.Address2 = ds.Tables[0].Rows[0]["address2"].ToString();
this.Town = ds.Tables[0].Rows[0]["town"].ToString();
this.County = Convert.ToInt32(ds.Tables[0].Rows[0]["county"]);
this.PostCode = ds.Tables[0].Rows[0]["postcode"].ToString();
this.Country =
Convert.ToInt32(ds.Tables[0].Rows[0]["country"]);
this.Phone = ds.Tables[0].Rows[0]["phone"].ToString();
this.Fax = ds.Tables[0].Rows[0]["fax"].ToString();
this.Web = ds.Tables[0].Rows[0]["web"].ToString();
this.Details = ds.Tables[0].Rows[0]["details"].ToString();
this.LastUpdated =
Convert.ToDateTime(ds.Tables[0].Rows[0]["lastupdated"].ToString());
this.UpdateBy = ds.Tables[0].Rows[0]["updateby"].ToString();
(byte[])ds.Tables[0].Rows[0]["timestamp"].ToString();
return ds;
}
catch
{
return null;
}

}

public void Insert(Customer c)
{
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["ToolkitServer"].ConnectionString);
SqlCommand cmd = new SqlCommand("insertcustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@customername", c.CustomerName);
cmd.Parameters.AddWithValue("@address1", c.Address1);
cmd.Parameters.AddWithValue("@address2", c.Address2);
cmd.Parameters.AddWithValue("@town", c.Town);
cmd.Parameters.AddWithValue("@county", c.County);
cmd.Parameters.AddWithValue("@postcode", c.PostCode);
cmd.Parameters.AddWithValue("@country", c.Country);
cmd.Parameters.AddWithValue("@phone", c.Phone);
cmd.Parameters.AddWithValue("@fax", c.Fax);
cmd.Parameters.AddWithValue("@web", c.Web);
cmd.Parameters.AddWithValue("@assignedto", c.AssignedTo);
cmd.Parameters.AddWithValue("@details", c.Details);
cmd.Parameters.AddWithValue("@lastupdated", DateTime.Now);
cmd.Parameters.AddWithValue("@updateby", c.UpdateBy);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

}

public void Update(Customer c)
{
try
{
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["ToolkitServer"].ConnectionString);
SqlCommand cmd = new SqlCommand("updatecustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@customerid", c.CustomerID);
cmd.Parameters.AddWithValue("@customername", c.CustomerName);
cmd.Parameters.AddWithValue("@address1", c.Address1);
cmd.Parameters.AddWithValue("@address2", c.Address2);
cmd.Parameters.AddWithValue("@town", c.Town);
cmd.Parameters.AddWithValue("@county", c.County);
cmd.Parameters.AddWithValue("@postcode", c.PostCode);
cmd.Parameters.AddWithValue("@country", c.Country);
cmd.Parameters.AddWithValue("@phone", c.Phone);
cmd.Parameters.AddWithValue("@fax", c.Fax);
cmd.Parameters.AddWithValue("@web", c.Web);
cmd.Parameters.AddWithValue("@assignedto", c.AssignedTo);
cmd.Parameters.AddWithValue("@details", c.Details);
cmd.Parameters.AddWithValue("@timestamp", c.TimeStamp);
cmd.Parameters.AddWithValue("@lastupdated", DateTime.Now);
cmd.Parameters.AddWithValue("@updateby", c.UpdateBy);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
}
}

public void Delete(Customer c)
{
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["ToolkitServer"].ConnectionString);
SqlCommand cmd = new SqlCommand("deletecustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@customerid", c.CustomerID);
cmd.Parameters.AddWithValue("@timestamp", c.TimeStamp);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}

#endregion
}

Kevin Yu [MSFT] · Feb 12, 2006

Hi Mal,

From the code snippet, I see that you are adding the parameters without
type and size. This migth be the cause of the thrown exception. Would you
try to specify the type and size for all the parameters? Use
SqlDbType.Timestamp for the timestamp field.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Guest · Feb 12, 2006

Would that be in the form:

cmd.Parameters.Add("@customerid", SqlDbType.Int);
cmd.Parameters["@Customerid"].Value = c.CustomerID;

etc, etc

Guest · Feb 12, 2006

Am I correct in declaring _Timestamp to be byte[] in my class, if so what
would I use to convert it back when I do a select from the database from the
returned dataset into the _Timestamp variable?

Kevin Yu [MSFT] · Feb 13, 2006

Hi Mal,

The TimeStamp type in SQL server is automatically mapped into byte[] in
NET framework.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconmappingnetdataproviderdatatypestonetframeworkdatatypes.asp

But for parameters, they also have their types. As you stated in your last
post in the cmd.Parameters.Add, we can specify the type of it. Also, if we
specify the type, the size should also be specified.

cmd.Parameters.Add("@Customerid", SqlDbType.Int, 4);
cmd.Parameters["@Customerid"].Value = c.CustomerID;

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Guest · Feb 13, 2006

Thanks I will try this, my only problem now is more just plain old c# which I
dont know well enough yet.

How do I get the value from my recordset to the timestamp byte[] object?

TimeStamp = dsCustomer.Tables[0].Rows[0]["timestamp"];

obviously this wont work because its not the right type.

John Bailo · Feb 13, 2006

Mal said:
Thanks I will try this, my only problem now is more just plain old c# which I
dont know well enough yet.

How do I get the value from my recordset to the timestamp byte[] object?

TimeStamp = dsCustomer.Tables[0].Rows[0]["timestamp"];

obviously this wont work because its not the right type.

DateTime TimeStamp = (DateTime) dsCustomer.Tables[0].Rows[0]["timestamp"];

Kevin Yu [MSFT] · Feb 14, 2006

Hi Mal,

If it is being converted to byte[], it has to be

Byte[] TimeStamp = (Byte[]) dsCustomer.Tables[0].Rows[0]["timestamp"];

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Guest · Feb 14, 2006

I must be missing something here in the way I have implemented the class and
object data source. If I update the formview and press the update button the
update code produces an exception of procedure expected the parameter
@timestamp but was not supplied:

When I looked at all the other properties declared within the class none of
them are populated.

This is my class which is assigned to the objectdatasource on the form and
the objectdatasource is assigned to the formview:

private int _CustomerID, _County, _Country, _AssignedTo;
private string _CustomerName, _Address1, _Address2, _Town, _PostCode,
_Phone, _Fax, _Web, _Details, _UpdateBy, _CurrentUserName;
private DateTime _LastUpdated;
private byte[] _TimeStamp;

public Customer()
{

}

#region Get & Set Methods
public byte[] TimeStamp
{
get { return _TimeStamp; }
set { _TimeStamp = value; }
}

public string CurrentUserName
{
get { return _CurrentUserName.Trim(); }
set { _CurrentUserName = value; }
}

public string CustomerName
{
get { return _CustomerName.Trim(); }
set { _CustomerName = value; }
}

public string Address1
{
get { return _Address1.Trim(); }
set { _Address1 = value; }
}

public string Address2
{
get { return _Address2.Trim(); }
set { _Address2 = value; }
}

public string Town
{
get { return _Town.Trim(); }
set { _Town = value; }
}

public string PostCode
{
get { return _PostCode.Trim(); }
set { _PostCode = value; }
}

public string Phone
{
get { return _Phone.Trim(); }
set { _Phone = value; }
}

public string Fax
{
get { return _Fax.Trim(); }
set { _Fax = value; }
}

public string Web
{
get { return _Web.Trim(); }
set { _Web = value; }
}

public string Details
{
get { return _Details.Trim(); }
set { _Details = value; }
}

public string UpdateBy
{
get { return _UpdateBy; }
set { _UpdateBy = value; }
}

public int CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}

public int County
{
get { return _County; }
set { _County = value; }
}

public int Country
{
get { return _Country; }
set { _Country = value; }
}

public int AssignedTo
{
get { return _AssignedTo; }
set { _AssignedTo = value; }
}

public DateTime LastUpdated
{
get { return _LastUpdated; }
set { _LastUpdated = value; }
}

#endregion

#region Select, Insert, Update, Delete Methods
public System.Data.DataSet Select(Int32 customerID)
{
try
{
DataSet ds = new DataSet();

SqlHelper.FillDataset(ConfigurationManager.ConnectionStrings["ToolkitServer"].ConnectionString,
CommandType.StoredProcedure,
"getcustomer", ds, new string[] { "customer" },
new SqlParameter("@customerid",
customerID));
this.CustomerID =
Convert.ToInt32(ds.Tables[0].Rows[0]["customerid"]);
this.CustomerName =
ds.Tables[0].Rows[0]["customername"].ToString();
this.Address1 = ds.Tables[0].Rows[0]["address1"].ToString();
this.Address2 = ds.Tables[0].Rows[0]["address2"].ToString();
this.Town = ds.Tables[0].Rows[0]["town"].ToString();
this.County = Convert.ToInt32(ds.Tables[0].Rows[0]["county"]);
this.PostCode = ds.Tables[0].Rows[0]["postcode"].ToString();
this.Country = Convert.ToInt32(ds.Tables[0].Rows[0]["country"]);
this.Phone = ds.Tables[0].Rows[0]["phone"].ToString();
this.Fax = ds.Tables[0].Rows[0]["fax"].ToString();
this.Web = ds.Tables[0].Rows[0]["web"].ToString();
this.Details = ds.Tables[0].Rows[0]["details"].ToString();
this.TimeStamp = (Byte[])ds.Tables[0].Rows[0]["timestamp"];
this.LastUpdated =
Convert.ToDateTime(ds.Tables[0].Rows[0]["lastupdated"].ToString());
this.UpdateBy = ds.Tables[0].Rows[0]["updateby"].ToString();
return ds;
}
catch
{
return null;
}

}

public void Insert(Customer c)
{
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["ToolkitServer"].ConnectionString);
SqlCommand cmd = new SqlCommand("insertcustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@customername", c.CustomerName);
cmd.Parameters.AddWithValue("@address1", c.Address1);
cmd.Parameters.AddWithValue("@address2", c.Address2);
cmd.Parameters.AddWithValue("@town", c.Town);
cmd.Parameters.AddWithValue("@county", c.County);
cmd.Parameters.AddWithValue("@postcode", c.PostCode);
cmd.Parameters.AddWithValue("@country", c.Country);
cmd.Parameters.AddWithValue("@phone", c.Phone);
cmd.Parameters.AddWithValue("@fax", c.Fax);
cmd.Parameters.AddWithValue("@web", c.Web);
cmd.Parameters.AddWithValue("@assignedto", c.AssignedTo);
cmd.Parameters.AddWithValue("@details", c.Details);
cmd.Parameters.AddWithValue("@timestamp", c.TimeStamp);
cmd.Parameters.AddWithValue("@lastupdated", DateTime.Now);
cmd.Parameters.AddWithValue("@updateby", c.UpdateBy);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

}

public void Update(Customer c)
{
try
{
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["ToolkitServer"].ConnectionString);
SqlCommand cmd = new SqlCommand("updatecustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@customerid", SqlDbType.Int);
cmd.Parameters["@customerid"].Value = c.CustomerID;
cmd.Parameters.Add("@customername", SqlDbType.NVarChar);
cmd.Parameters["@customername"].Value = c.CustomerName;
cmd.Parameters.Add("@address1", SqlDbType.NVarChar);
cmd.Parameters["@address1"].Value = c.Address1;
cmd.Parameters.Add("@address2", SqlDbType.NVarChar);
cmd.Parameters["@address2"].Value = c.Address2;
cmd.Parameters.Add("@town", SqlDbType.NVarChar);
cmd.Parameters["@town"].Value = c.Town;
cmd.Parameters.Add("@county", SqlDbType.Int);
cmd.Parameters["@county"].Value = c.County;
cmd.Parameters.Add("@postcode", SqlDbType.NVarChar);
cmd.Parameters["@postcode"].Value = c.PostCode;
cmd.Parameters.Add("@country", SqlDbType.Int);
cmd.Parameters["@country"].Value = c.Country;
cmd.Parameters.Add("@phone", SqlDbType.NVarChar);
cmd.Parameters["@phone"].Value = c.Phone;
cmd.Parameters.Add("@fax", SqlDbType.NVarChar);
cmd.Parameters["@fax"].Value = c.Fax;
cmd.Parameters.Add("@web", SqlDbType.NVarChar);
cmd.Parameters["@web"].Value = c.Web;
cmd.Parameters.Add("@assignedto", SqlDbType.Int);
cmd.Parameters["@assignedto"].Value = c.AssignedTo;
cmd.Parameters.Add("@details", SqlDbType.NVarChar);
cmd.Parameters["@details"].Value = c.Details;
cmd.Parameters.Add("@timestamp", SqlDbType.Timestamp);
cmd.Parameters["@timestamp"].Value = c.TimeStamp;
cmd.Parameters.Add("@lastupdated", SqlDbType.DateTime);
cmd.Parameters["@lastupdated"].Value = DateTime.Now;
cmd.Parameters.Add("@updateby", SqlDbType.NVarChar);
cmd.Parameters["@updateby"].Value = c.UpdateBy;

con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
}
}

public void Delete(Customer c)
{
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["ToolkitServer"].ConnectionString);
SqlCommand cmd = new SqlCommand("deletecustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@customerid", c.CustomerID);
cmd.Parameters.AddWithValue("@timestamp", c.TimeStamp);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
--
Mal Ball

Kevin Yu said:
Hi Mal,

If it is being converted to byte[], it has to be

Byte[] TimeStamp = (Byte[]) dsCustomer.Tables[0].Rows[0]["timestamp"];

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Kevin Yu [MSFT] · Feb 15, 2006

Hi Mal,

I tried your code on my machine but cannot repro the problem. Could you
post your page with only one formview control here?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Guest · Feb 15, 2006

Sorry Kevin I am not sure how to do this, do I just cut and paste the code
from both aspx and aspx.cs or is there a way of uploading?

Do you want me to create a new form with these elements in it, I cannot send
the actual form because I am also using Infragistics web objects as well. The
FormView is on an Infragistics Web Tab object and the form is also based on a
master page.

Kevin Yu [MSFT] · Feb 16, 2006

Hi Mal,

Try to simplify the page with only the FormView. If with only the FormView
on that page, this problem can be reproduced, please copy and paste the
aspx and aspx.cs file here. Thank you!

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Guest · Feb 18, 2006

aStill getting error update Procedure of Function 'updatecustomer' expects
parameter '@timestamp', which was not supplied.

Having looked at timestamp when it is added as a paremeter in the update
statement it is set to null, however if I look at the timestamp paremeter
when I do the initial select it is populated.

Simple form with objectdatasource and formview below, no code in aspx.cs
file at the moment. I will send the update Customer.cs class separately
because too many characters for message.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
DataObjectTypeName="Customer"
DeleteMethod="Delete" InsertMethod="Insert"
SelectMethod="Select" TypeName="Customer"
UpdateMethod="Update">
<SelectParameters>
<asp

arameter DefaultValue="1" Name="customerID"
Type="Int32" />
</SelectParameters>
</asp:ObjectDataSource>

</div>
<asp:FormView ID="CustomerFormView" runat="server"
DataSourceID="ObjectDataSource1" Height="218px" Style="position: static"
Width="825px" DataKeyNames="CustomerID">
<EditItemTemplate>
<table style="width: 813px; position: static">
<tr>
<td style="width: 86px; height:
24px;" valign="middle">
<asp:Label ID="Label14"
runat="server" Style="position: static" Text="Customer ID:"></asp:Label></td>
<td style="width: 154px; height:
24px;" valign="middle">
<asp:Label ID="customeridLabel1"
runat="server" Text='<%# Eval("customerid") %>' style="position: static">
</asp:Label></td>
<td style="width: 100px; height:
24px;" valign="top">
<asp:Label ID="Label28"
runat="server" Style="position: static" Text="Notes:"></asp:Label></td>
<td style="width: 100px; height:
24px;">
<asp:Label ID="UserName"
runat="server" Style="position: relative" Text='<%# User.Identity.Name %>'
Visible="False"></asp:Label>
<asp:Label ID="TimeStamp"
runat="server" Style="left: 4px; position: relative; top: 0px"
Text='<%# Eval("timestamp")
%>'></asp:Label></td>
</tr>
<tr>
<td style="width: 86px"
valign="middle">
<asp:Label ID="Label17"
runat="server" Style="position: static" Text="Name:"></asp:Label></td>
<td style="width: 154px"
valign="middle">
<asp:TextBox ID="customernameTextBox"
runat="server" Text='<%# Bind("customername") %>' style="position: static"
Width="269px"></asp:TextBox>
</td>
<td colspan="2" rowspan="8"
valign="top" align="left">
<asp:TextBox ID="detailsTextBox"
runat="server" Text='<%# Bind("details") %>' style="position: static"
Height="201px" TextMode="MultiLine" Width="416px"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 86px"
valign="middle">
<asp:Label ID="Label18"
runat="server" Style="position: static" Text="Address:"></asp:Label></td>
<td style="width: 154px"
valign="middle">
<asp:TextBox ID="address1TextBox"
runat="server" Text='<%# Bind("address1") %>' style="position: static"
Width="148px"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 86px"
valign="middle">
</td>
<td style="width: 154px"
valign="middle">
<asp:TextBox ID="address2TextBox"
runat="server" Text='<%# Bind("address2") %>' style="position: static">
</asp:TextBox></td>
</tr>
<tr>
<td style="width: 86px"
valign="middle">
<asp:Label ID="Label19"
runat="server" Style="position: static" Text="Town:"></asp:Label></td>
<td style="width: 154px"
valign="middle">
<asp:TextBox ID="townTextBox" runat="server"
Text='<%# Bind("town") %>' style="position: static">
</asp:TextBox></td>
</tr>
<tr>
<td style="width: 86px"
valign="middle">
<asp:Label ID="Label20"
runat="server" Style="position: static" Text="County:"></asp:Label></td>
<td style="width: 154px"
valign="middle">
<asp:TextBox ID="TextBox2"
runat="server" Style="position: relative" Text='<%# Eval("county")
%>'></asp:TextBox></td>
</tr>
<tr>
<td style="width: 86px"
valign="middle">
<asp:Label ID="Label21"
runat="server" Style="position: static" Text="Post Code:"></asp:Label></td>
<td style="width: 154px"
valign="middle">
<asp:TextBox ID="postcodeTextBox"
runat="server" Text='<%# Bind("postcode") %>' style="position: static"
Width="95px"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 86px"
valign="middle">
<asp:Label ID="Label22"
runat="server" Style="position: static" Text="Country:"></asp:Label></td>
<td style="width: 154px"
valign="middle">
<asp:TextBox ID="TextBox3"
runat="server" Style="position: relative" Text='<%# Eval("country")
%>'></asp:TextBox></td>
</tr>
<tr>
<td style="width: 86px"
valign="middle">
<asp:Label ID="Label23"
runat="server" Style="position: static" Text="Phone:"></asp:Label></td>
<td style="width: 154px"
valign="middle">
<asp:TextBox ID="phoneTextBox"
runat="server" Text='<%# Bind("phone") %>' style="position: static">
</asp:TextBox></td>
</tr>
<tr>
<td style="width: 86px"
valign="middle">
<asp:Label ID="Label25"
runat="server" Style="position: static" Text="Web:"></asp:Label></td>
<td style="width: 154px"
valign="middle">
<asp:TextBox ID="webTextBox" runat="server"
Text='<%# Bind("web") %>' style="position: static">
</asp:TextBox></td>
<td style="width: 100px"
valign="middle">
<asp:Label ID="Label24"
runat="server" Style="position: static" Text="Fax:"></asp:Label></td>
<td style="width: 100px"
valign="middle">
<asp:TextBox ID="TextBox1"
runat="server" Style="position: relative" Text='<%# Bind("fax") %>'>
</asp:TextBox>
<asp:Button ID="Button2"
runat="server" CommandName="Update" Style="left: 29px; position: relative;
top: 0px" Text="Update"
/></td>
</tr>
</table>
</EditItemTemplate>
<InsertItemTemplate>
<table style="width: 814px; position:
relative">
<tr>
<td style="width: 74px">
<asp:Label ID="Label26"
runat="server" Style="position: relative" Text="Name:"></asp:Label></td>
<td style="width: 100px">
<asp:TextBox ID="customernameTextBox"
runat="server" Text='<%# Bind("customername") %>' style="position: relative"
Width="251px"></asp:TextBox></td>
<td style="width: 100px">
<asp:Label ID="Label37"
runat="server" Style="position: relative" Text="Notes:"></asp:Label></td>
<td style="width: 100px">
<asp:Label ID="Label38"
runat="server" Style="position: relative" Text="<%# User.Identity.Name %>"
Visible="False"></asp:Label></td>
</tr>
<tr>
<td style="width: 74px">
<asp:Label ID="Label27"
runat="server" Style="position: relative" Text="Address:"></asp:Label></td>
<td style="width: 100px">
<asp:TextBox ID="address1TextBox"
runat="server" Text='<%# Bind("address1") %>' style="position: relative">
</asp:TextBox></td>
<td colspan="2" rowspan="8"
valign="top">
<asp:TextBox ID="detailsTextBox"
runat="server" Text='<%# Bind("details") %>' style="position: relative">
</asp:TextBox></td>
</tr>
<tr>
<td style="width: 74px">
</td>
<td style="width: 100px">
<asp:TextBox ID="address2TextBox"
runat="server" Text='<%# Bind("address2") %>' style="position: relative">
</asp:TextBox></td>
</tr>
<tr>
<td style="width: 74px">
<asp:Label ID="Label30"
runat="server" Style="position: relative" Text="Town:"></asp:Label></td>
<td style="width: 100px">
<asp:TextBox ID="townTextBox" runat="server"
Text='<%# Bind("town") %>' style="position: relative">
</asp:TextBox></td>
</tr>
<tr>
<td style="width: 74px">
<asp:Label ID="Label31"
runat="server" Style="position: relative" Text="County:"></asp:Label></td>
<td style="width: 100px">
<asp:TextBox ID="countyTextBox"
runat="server" Text='<%# Eval("county") %>' style="position:
relative"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 74px">
<asp:Label ID="Label32"
runat="server" Style="position: relative" Text="Post Code:"></asp:Label></td>
<td style="width: 100px">
<asp:TextBox ID="postcodeTextBox"
runat="server" Text='<%# Bind("postcode") %>' style="position: relative"
Width="92px"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 74px; height:
25px;">
<asp:Label ID="Label33"
runat="server" Style="position: relative" Text="Country:"></asp:Label></td>
<td style="width: 100px; height:
25px;">
<asp:TextBox ID="countryTextBox"
runat="server" Text='<%# Eval("country") %>' style="position:
relative"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 74px">
<asp:Label ID="Label34"
runat="server" Style="position: relative" Text="Phone:"></asp:Label></td>
<td style="width: 100px">
<asp:TextBox ID="phoneTextBox"
runat="server" Text='<%# Bind("phone") %>' style="position: relative">
</asp:TextBox></td>
</tr>
<tr>
<td style="width: 74px">
<asp:Label ID="Label35"
runat="server" Style="position: relative" Text="Fax:"></asp:Label></td>
<td style="width: 100px">
<asp:TextBox ID="faxTextBox" runat="server"
Text='<%# Bind("fax") %>' style="position: relative">
</asp:TextBox></td>
</tr>
<tr>
<td style="width: 74px">
<asp:Label ID="Label36"
runat="server" Style="position: relative" Text="Web:"></asp:Label></td>
<td style="width: 100px">
<asp:TextBox ID="webTextBox" runat="server"
Text='<%# Bind("web") %>' style="position: relative">
</asp:TextBox></td>
<td style="width: 100px">
<asp:Label ID="TimeStamp"
runat="server" ForeColor="Black" Style="position: relative"
Text='<%# Eval("timestamp")
%>'></asp:Label></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 74px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
<asp:Button ID="Button1"
runat="server" CommandName="Update" Style="position: relative"
Text="Update" /></td>
<td style="width: 100px">
</td>
</tr>
</table>
<br />
</InsertItemTemplate>
<ItemTemplate>
<table style="width: 800px; position: static">
<tr>
<td style="width: 93px" align="left"
valign="middle">
<asp:Label ID="Label3"
runat="server" Style="position: static" Text="Customer ID:"></asp:Label></td>
<td style="width: 194px"
align="left" valign="middle">
<asp:Label ID="customeridLabel"
runat="server" Style="position: static" Text='<%# Eval("customerid")
%>'></asp:Label></td>
<td style="width: 100px" align="left">
<asp:Label ID="Label13"
runat="server" Style="position: static" Text="Details:"></asp:Label></td>
<td style="width: 100px" align="left">
<asp:Label ID="assignedtoLabel"
runat="server" Style="position: static" Text='<%# Eval("assignedto")
%>'></asp:Label></td>
</tr>
<tr>
<td style="width: 93px" align="left"
valign="middle">
<asp:Label ID="Label4"
runat="server" Style="position: static" Text="Name:"></asp:Label></td>
<td style="width: 194px"
align="left" valign="middle">
<asp:Label
ID="customernameLabel" runat="server" Style="position: static" Text='<%#
Eval("customername") %>' ForeColor="Black"></asp:Label></td>
<td align="left" colspan="2"
rowspan="8" valign="top">
<asp:Label ID="detailsLabel"
runat="server" Style="position: static" Text='<%# Eval("details") %>'
ForeColor="Black"></asp:Label></td>
</tr>
<tr>
<td style="width: 93px" align="left"
valign="middle">
<asp:Label ID="Label5"
runat="server" Style="position: static" Text="Address:"></asp:Label></td>
<td style="width: 194px"
align="left" valign="middle">
<asp:Label ID="address1Label"
runat="server" Style="position: static" Text='<%# Eval("address1") %>'
ForeColor="Black"></asp:Label></td>
</tr>
<tr>
<td style="width: 93px; height:
21px;" align="left" valign="middle">
</td>
<td style="width: 194px; height:
21px;" align="left" valign="middle">
<asp:Label ID="address2Label"
runat="server" Style="position: static" Text='<%# Bind("address2") %>'
ForeColor="Black"></asp:Label></td>
</tr>
<tr>
<td style="width: 93px" align="left"
valign="middle">
<asp:Label ID="Label6"
runat="server" Style="position: static" Text="Town:"></asp:Label></td>
<td style="width: 194px"
align="left" valign="middle">
<asp:Label ID="townLabel"
runat="server" Style="position: static" Text='<%# Eval("town") %>'
ForeColor="Black"></asp:Label></td>
</tr>
<tr>
<td style="width: 93px" align="left"
valign="middle">
<asp:Label ID="Label7"
runat="server" Style="position: static" Text="County:"></asp:Label></td>
<td style="width: 194px"
align="left" valign="middle">
<asp:Label ID="Label1"
runat="server" ForeColor="Black" Style="position: relative"
Text='<%# Eval("county")
%>'></asp:Label></td>
</tr>
<tr>
<td style="width: 93px" align="left"
valign="middle">
<asp:Label ID="Label8"
runat="server" Style="position: static" Text="Post Code:"></asp:Label></td>
<td style="width: 194px"
align="left" valign="middle">
<asp:Label ID="postcodeLabel"
runat="server" Style="position: static" Text='<%# Eval("postcode") %>'
ForeColor="Black"></asp:Label></td>
</tr>
<tr>
<td style="width: 93px; height:
21px" align="left" valign="middle">
<asp:Label ID="Label9"
runat="server" Style="position: static" Text="Country:"></asp:Label></td>
<td style="width: 194px; height:
21px" align="left" valign="middle">
<asp:Label ID="Label2"
runat="server" ForeColor="Black" Style="position: relative"
Text='<%# Eval("country")
%>'></asp:Label></td>
</tr>
<tr>
<td style="width: 93px" align="left"
valign="middle">
<asp:Label ID="Label10"
runat="server" Style="position: static" Text="Phone:"></asp:Label></td>
<td style="width: 194px"
align="left" valign="middle">
<asp:Label ID="phoneLabel"
runat="server" Style="position: static" Text='<%# Eval("phone") %>'
ForeColor="Black"></asp:Label></td>
</tr>
<tr>
<td style="width: 93px" align="left"
valign="middle">
<asp:Label ID="Label11"
runat="server" Style="position: static" Text="Fax:"></asp:Label></td>
<td style="width: 194px"
align="left" valign="middle">
<asp:Label ID="faxLabel"
runat="server" Style="position: static" Text='<%# Eval("fax") %>'
ForeColor="Black"></asp:Label></td>
<td style="width: 100px" align="left">
<asp:Button ID="Button3"
runat="server" Style="position: relative" Text="Button" /></td>
<td style="width: 100px" align="left">
<asp:Button ID="Button4"
runat="server" CommandName="Edit" Style="position: relative"
Text="Edit" /></td>
</tr>
<tr>
<td style="width: 93px" align="left"
valign="middle">
<asp:Label ID="Label12"
runat="server" Style="position: static" Text="Web:"></asp:Label></td>
<td style="width: 194px"
align="left" valign="middle">
<asp:Label ID="webLabel"
runat="server" Style="position: static" Text='<%# Eval("web") %>'
ForeColor="Black"></asp:Label></td>
<td style="width: 100px" align="left">
</td>
<td style="width: 100px" align="left">
<asp:Label ID="Label29"
runat="server" ForeColor="Black" Style="position: relative"
Text='<%# Eval("timestamp")
%>'></asp:Label></td>
</tr>
<tr>
<td style="width: 93px" align="left"
valign="middle">
<asp:Label ID="Label15"
runat="server" Style="position: static" Text="Last Updated:"></asp:Label></td>
<td style="width: 194px"
align="left" valign="middle">
<asp:Label ID="lastupdatedLabel"
runat="server" Style="position: static" Text='<%# Eval("lastupdated") %>'
ForeColor="Black"></asp:Label></td>
<td style="width: 100px"
align="left" valign="middle">
<asp:Label ID="Label16"
runat="server" Style="position: static" Text="Update By:"></asp:Label></td>
<td style="width: 100px"
align="left" valign="middle">
<asp:Label ID="updatebyLabel"
runat="server" Style="position: static" Text='<%# Eval("updateby") %>'
ForeColor="Black"></asp:Label></td>
</tr>
</table>
</ItemTemplate>
</asp:FormView>

</form>
</body>
</html>

Guest · Feb 18, 2006

Kevin

Here is the Customer class that I have set up after your suggestions about
the timestamp property

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

/// <summary>
/// Summary description for Customer
/// </summary>
public class Customer
{
private int _CustomerID, _County, _Country, _AssignedTo;
private string _CustomerName, _Address1, _Address2, _Town, _PostCode,
_Phone, _Fax, _Web, _Details, _UpdateBy, _CurrentUserName;
private DateTime _LastUpdated;
private byte[] _TimeStamp;

public Customer()
{

}

#region Get & Set Methods
public byte[] TimeStamp
{
get { return _TimeStamp; }
set { _TimeStamp = value; }
}

public string CurrentUserName
{
get { return _CurrentUserName.Trim(); }
set { _CurrentUserName = value; }
}

public string CustomerName
{
get { return _CustomerName.Trim(); }
set { _CustomerName = value; }
}

public string Address1
{
get { return _Address1.Trim(); }
set { _Address1 = value; }
}

public string Address2
{
get { return _Address2.Trim(); }
set { _Address2 = value; }
}

public string Town
{
get { return _Town.Trim(); }
set { _Town = value; }
}

public string PostCode
{
get { return _PostCode.Trim(); }
set { _PostCode = value; }
}

public string Phone
{
get { return _Phone.Trim(); }
set { _Phone = value; }
}

public string Fax
{
get { return _Fax.Trim(); }
set { _Fax = value; }
}

public string Web
{
get { return _Web.Trim(); }
set { _Web = value; }
}

public string Details
{
get { return _Details.Trim(); }
set { _Details = value; }
}

public string UpdateBy
{
get { return _UpdateBy; }
set { _UpdateBy = value; }
}

public int CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}

public int County
{
get { return _County; }
set { _County = value; }
}

public int Country
{
get { return _Country; }
set { _Country = value; }
}

public int AssignedTo
{
get { return _AssignedTo; }
set { _AssignedTo = value; }
}

public DateTime LastUpdated
{
get { return _LastUpdated; }
set { _LastUpdated = value; }
}

#endregion

#region Select, Insert, Update, Delete Methods
public System.Data.DataSet Select(Int32 customerID)
{
try
{
DataSet ds = new DataSet();

SqlHelper.FillDataset(ConfigurationManager.ConnectionStrings["ToolkitServer"].ConnectionString,
CommandType.StoredProcedure,
"getcustomer", ds, new string[] { "customer" },
new SqlParameter("@customerid",
customerID));
this.CustomerID =
Convert.ToInt32(ds.Tables[0].Rows[0]["customerid"]);
this.CustomerName =
ds.Tables[0].Rows[0]["customername"].ToString();
this.Address1 = ds.Tables[0].Rows[0]["address1"].ToString();
this.Address2 = ds.Tables[0].Rows[0]["address2"].ToString();
this.Town = ds.Tables[0].Rows[0]["town"].ToString();
this.County = Convert.ToInt32(ds.Tables[0].Rows[0]["county"]);
this.PostCode = ds.Tables[0].Rows[0]["postcode"].ToString();
this.Country = Convert.ToInt32(ds.Tables[0].Rows[0]["country"]);
this.Phone = ds.Tables[0].Rows[0]["phone"].ToString();
this.Fax = ds.Tables[0].Rows[0]["fax"].ToString();
this.Web = ds.Tables[0].Rows[0]["web"].ToString();
this.Details = ds.Tables[0].Rows[0]["details"].ToString();
this.TimeStamp = (Byte[])ds.Tables[0].Rows[0]["timestamp"];
this.LastUpdated =
Convert.ToDateTime(ds.Tables[0].Rows[0]["lastupdated"].ToString());
this.UpdateBy = ds.Tables[0].Rows[0]["updateby"].ToString();
return ds;
}
catch
{
return null;
}

}

public void Insert(Customer c)
{
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["ToolkitServer"].ConnectionString);
SqlCommand cmd = new SqlCommand("insertcustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@customername", c.CustomerName);
cmd.Parameters.AddWithValue("@address1", c.Address1);
cmd.Parameters.AddWithValue("@address2", c.Address2);
cmd.Parameters.AddWithValue("@town", c.Town);
cmd.Parameters.AddWithValue("@county", c.County);
cmd.Parameters.AddWithValue("@postcode", c.PostCode);
cmd.Parameters.AddWithValue("@country", c.Country);
cmd.Parameters.AddWithValue("@phone", c.Phone);
cmd.Parameters.AddWithValue("@fax", c.Fax);
cmd.Parameters.AddWithValue("@web", c.Web);
cmd.Parameters.AddWithValue("@assignedto", c.AssignedTo);
cmd.Parameters.AddWithValue("@details", c.Details);
cmd.Parameters.AddWithValue("@timestamp", c.TimeStamp);
cmd.Parameters.AddWithValue("@lastupdated", DateTime.Now);
cmd.Parameters.AddWithValue("@updateby", c.UpdateBy);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

}

public void Update(Customer c)
{
try
{
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["ToolkitServer"].ConnectionString);
SqlCommand cmd = new SqlCommand("updatecustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@customerid", SqlDbType.Int);
cmd.Parameters["@customerid"].Value = c.CustomerID;
cmd.Parameters.Add("@customername", SqlDbType.NVarChar);
cmd.Parameters["@customername"].Value = c.CustomerName;
cmd.Parameters.Add("@address1", SqlDbType.NVarChar);
cmd.Parameters["@address1"].Value = c.Address1;
cmd.Parameters.Add("@address2", SqlDbType.NVarChar);
cmd.Parameters["@address2"].Value = c.Address2;
cmd.Parameters.Add("@town", SqlDbType.NVarChar);
cmd.Parameters["@town"].Value = c.Town;
cmd.Parameters.Add("@county", SqlDbType.Int);
cmd.Parameters["@county"].Value = c.County;
cmd.Parameters.Add("@postcode", SqlDbType.NVarChar);
cmd.Parameters["@postcode"].Value = c.PostCode;
cmd.Parameters.Add("@country", SqlDbType.Int);
cmd.Parameters["@country"].Value = c.Country;
cmd.Parameters.Add("@phone", SqlDbType.NVarChar);
cmd.Parameters["@phone"].Value = c.Phone;
cmd.Parameters.Add("@fax", SqlDbType.NVarChar);
cmd.Parameters["@fax"].Value = c.Fax;
cmd.Parameters.Add("@web", SqlDbType.NVarChar);
cmd.Parameters["@web"].Value = c.Web;
cmd.Parameters.Add("@assignedto", SqlDbType.Int);
cmd.Parameters["@assignedto"].Value = c.AssignedTo;
cmd.Parameters.Add("@details", SqlDbType.NVarChar);
cmd.Parameters["@details"].Value = c.Details;
cmd.Parameters.Add("@timestamp", SqlDbType.Timestamp);
cmd.Parameters["@timestamp"].Value = c.TimeStamp;
cmd.Parameters.Add("@lastupdated", SqlDbType.DateTime);
cmd.Parameters["@lastupdated"].Value = DateTime.Now;
cmd.Parameters.Add("@updateby", SqlDbType.NVarChar);
cmd.Parameters["@updateby"].Value = c.UpdateBy;

con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
}
}

public void Delete(Customer c)
{
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["ToolkitServer"].ConnectionString);
SqlCommand cmd = new SqlCommand("deletecustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@customerid", c.CustomerID);
cmd.Parameters.AddWithValue("@timestamp", c.TimeStamp);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}

#endregion

}

Kevin Yu [MSFT] · Feb 20, 2006

Hi Mal,

In the aspx page, the the ObjectDataSource's update command parameter was
not provided. It hasn't been bound to the correct object, since the update
method has an argument with customer type. You can click on the
ObjectDataSource and add the parameter in the property window for the
UpdateParameters property. HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Guest · Feb 20, 2006

Sorry Kevin, not quite sure what I need to use as the parameter. I am
assuming that you mean the update command is expecting a Customer object but
I am not passing this, is this correct? If so, what do I need to put into the
update parameters box for Value, and parameter source.

My appologies but as a newbie to Web Development I am on a steep learning
curve.

Updating SQL2005 table containing timestamp column

Guest

Kevin Yu [MSFT]

Guest

Kevin Yu [MSFT]

Guest

Kevin Yu [MSFT]

Guest

Guest

Kevin Yu [MSFT]

Guest

John Bailo

Kevin Yu [MSFT]

Guest

Kevin Yu [MSFT]

Guest

Kevin Yu [MSFT]

Guest

Guest

Kevin Yu [MSFT]

Guest