Updating SQL2005 table containing timestamp column

G

Guest

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?
 
K

Kevin Yu [MSFT]

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."
 
G

Guest

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")
 
K

Kevin Yu [MSFT]

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."
 
G

Guest

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
}
 
K

Kevin Yu [MSFT]

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."
 
G

Guest

Would that be in the form:

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

etc, etc
 
G

Guest

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?
 
K

Kevin Yu [MSFT]

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."
 
G

Guest

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.
 
J

John Bailo

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.
Click to expand...

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

Kevin Yu [MSFT]

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."
 
G

Guest

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."
Click to expand...
 
K

Kevin Yu [MSFT]

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."
 
G

Guest

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.
 
K

Kevin Yu [MSFT]

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."
 
G

Guest

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:parameter 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>
 
G

Guest

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

}
 
K

Kevin Yu [MSFT]

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."
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL2005 2
sql_variant is incompatible with xml 1
Timestamp 1
Optimistic Concurrency and TimeStamp 4
Linq, stored procedure with timestamp output column 1
Insert or update record with vba and ado 2
Using a Timestamp field to implement optimistic concurrency 2
dbdirect methods with timestamp columns 2

Top