Cannot map compute column in DB to Dataset

[Guest]

Hi,

I'm a VB .NET developer, so I have problem when select data from table that
have some column is computed column.

There is an error like a below statement:
"The column mapping from SourceTable 'MyColumn' failed because the
DataColumn 'MyColumn' is a computed column."

I have check that data on above column, I found that it was updated with
DataTable that have column use Expression property.

1. What should I do with this trouble? Should I change the way to update
data with computed column? How?

2. Is there any solution to query data and mapping above column to my dataset?

Thanks,

KPH

 

[Grzegorz Danowski]

Hi,

I'm a VB .NET developer, so I have problem when select data from table
that
have some column is computed column.

There is an error like a below statement:
"The column mapping from SourceTable 'MyColumn' failed because the
DataColumn 'MyColumn' is a computed column."

I have made small snippet to test using computed column.
1. I made new table on sql server:
Create Table Orders (
IdOrd Int Identity(1,1) Primary Key,
OrdName Varchar(50),
Quantity Int,
Price Money,
Value As (Quantity * Price)
)
2. I created new windows form project (c#, but I suppose vb.net will be the
same),
3. I selected sql server and then my new table on server explorer tag, then
I pulled it on form (making in the way sqlConnection1 and sqlDataAdapter1),
4. I generated new typed data set basing on above data adapter (dataSet11),
5. I put new DataGrid on form (set DataSource to dataSet11), and two button
(btnLoad and btnSave),
6. I put new events procedures for the buttons:
private void btnLoad_Click(object sender,
System.EventArgs e)
{
this.dataSet11.Clear();
this.sqlDataAdapter1.Fill(this.dataSet11.Orders);
}

private void btnSave_Click(object sender,
System.EventArgs e)
{
this.sqlDataAdapter1.Update(this.dataSet11.Orders);
this.dataSet11.AcceptChanges();
}
7. And in this moment I don't see any problem: there are visible all columns
(as well Value column), and the computed column is actualized after save
data.
I confess that I'm some surprised: I did'n expect that computed column from
SQL Server would be automaticly actualized in DataSet - I thought that It
would be necessary to manual set expression column in DataTable.

I hope that it helps you.

Regards,
Grzegorz

 

[Grzegorz Danowski]

U¿ytkownik "Grzegorz Danowski" <[email protected]> napisa³ w
wiadomo¶ci (...)

I confess that I'm some surprised: I did'n expect that computed column
from SQL Server would be automaticly actualized in DataSet - I thought
that It would be necessary to manual set expression column in DataTable.

Because data adapter UpdateCommand pull down data from server:
UPDATE
Orders
SET
OrdName = @OrdName, Quantity = @Quantity, Price = @Price
WHERE
IdOrd = @Original_IdOrd
AND
(OrdName = @Original_OrdName
OR
@Original_OrdName IS NULL
AND OrdName IS NULL)
AND
(Price = @Original_Price
OR
@Original_Price IS NULL
AND Price IS NULL)
AND
(Quantity = @Original_Quantity
OR
@Original_Quantity IS NULL
AND Quantity IS NULL);

--select data again
SELECT
IdOrd, OrdName, Quantity, Price, Value
FROM
Orders
WHERE
IdOrd = @IdOrd

And maybe more efficient way is to make expression column in dataset.

Regards,
Grzegorz

 

[Guest]

Hi

Thank you for your reply.

It's quite hard to explain why I do that, I create a column of table on
database with int32 datatype but I do not create a computed column (like your
example) when deploy database. I use data in column of DataTable object to
store data and a formular doesn't fix, it can be change all the time depends
on formular of another table.

When I update in the database I use above DataTable object. In the DataTable
object has a column use Expression property. I don't know what happen when
update with this column.

When I fill data in the DataSet object, there is an error occured?

KPH

 

[Grzegorz Danowski]

It's quite hard to explain why I do that, I create a column of table on
database with int32 datatype but I do not create a computed column (like
your
example) when deploy database. I use data in column of DataTable object to
store data and a formular doesn't fix, it can be change all the time
depends
on formular of another table.

When I update in the database I use above DataTable object. In the
DataTable
object has a column use Expression property. I don't know what happen when
update with this column.

I misunderstand something. Would you like to update database with data
calculated in your expression column on client application? If not, you
should simply remowe out the expression column from your
update/insert/select commands.
Regards,
Grzegorz

 

[Guest]

Yes, I would like to update data from client data to database.

Why I have problem when update data with Expression property of column?

What is the root cause of this situation?

KPH

 

[Grzegorz Danowski]

Yes, I would like to update data from client data to database.

Why I have problem when update data with Expression property of column?

What is the root cause of this situation?

Well, I know it is imposible using DataAdapter object, but you can do it in
this way (unfortunely a bit more writing):

public class MyTest
{
private SqlConnection myConn = new SqlConnection(
"server=(local);database=testy;" +
"Integrated Security=SSPI;");
private DataTable myDt = new DataTable("Orders");
private SqlDataAdapter myDa;

public MyTest()
{
myDa = new SqlDataAdapter(
"Select IdOrd, OrdName, Quantity, Price " +
"From Orders2", myConn);
}

public void LoadData()
{
myDa.Fill(myDt);
//autoincrement
myDt.Columns["IdOrd"].AutoIncrement = true;
//expression column
myDt.Columns.Add("Value",
typeof(System.Decimal), "Quantity * Price");
}

public void AddSomeData()
{
DataRow dr = myDt.NewRow();
dr["OrdName"] = "bread";
dr["Quantity"] = 2;
dr["Price"] = 3;
myDt.Rows.Add(dr);
}

public void UpdateDb()
{
//first inserted row
SqlCommand myInsCom = GetInsCommand();
SqlCommand myUpdCom;// = GetUpdCommand();

myConn.Open();

foreach(DataRow dr in myDt.Select("", "" ,
DataViewRowState.Added))
{
//settings parameters value
myInsCom.Parameters["@OrdN"].Value = dr["OrdName"];
myInsCom.Parameters["@Quant"].Value = dr["Quantity"];
myInsCom.Parameters["@Price"].Value = dr["Price"];
myInsCom.Parameters["@Val"].Value = dr["Value"];

//fire insert
myInsCom.ExecuteNonQuery();
myDt.AcceptChanges();
}

//then updated rows etc...
foreach(DataRow dr in myDt.Select("", "" ,
DataViewRowState.ModifiedCurrent
| DataViewRowState.ModifiedOriginal))
{
//settings parameters value

//myUpdCom.ExecuteNonQuery();
}
myConn.Close();
}
private SqlCommand GetInsCommand()
{
SqlCommand insCom;
insCom = new SqlCommand(
"Insert Into Orders2 (OrdName, Quantity, Price, Value) " +
"Values (@OrdN, @Quant, @Price, @Val)", myConn);
//define parameters
insCom.Parameters.Add("@OrdN", SqlDbType.VarChar, 50);
insCom.Parameters.Add("@Quant", SqlDbType.Int, 0);
insCom.Parameters.Add("@Price", SqlDbType.Money, 50);
insCom.Parameters.Add("@Val", SqlDbType.Money, 50);
return insCom;
}
}

But I'm not sure if updating database in so way (by data computed in
expression column) is really correct idea.

Regards,
Grzegorz

Ps.
Actual Order table:
Create Table Orders (
IdOrd Int Identity(1,1) Primary Key,
OrdName Varchar(50),
Quantity Int,
Price Money,
Value Money
)

 

[Grzegorz Danowski]

Yes, I would like to update data from client data to database.

Why I have problem when update data with Expression property of column?

What is the root cause of this situation?

Well, I know it is imposible using DataAdapter object, but you can do it in
this way (unfortunely a bit more writing):

public class MyTest
{
private SqlConnection myConn = new SqlConnection(
"server=(local);database=testy;" +
"Integrated Security=SSPI;");
private DataTable myDt = new DataTable("Orders");
private SqlDataAdapter myDa;

public MyTest()
{
myDa = new SqlDataAdapter(
"Select IdOrd, OrdName, Quantity, Price " +
"From Orders2", myConn);
}

public void LoadData()
{
myDa.Fill(myDt);
//autoincrement
myDt.Columns["IdOrd"].AutoIncrement = true;
//expression column
myDt.Columns.Add("Value",
typeof(System.Decimal), "Quantity * Price");
}

public void AddSomeData()
{
DataRow dr = myDt.NewRow();
dr["OrdName"] = "bread";
dr["Quantity"] = 2;
dr["Price"] = 3;
myDt.Rows.Add(dr);
}

public void UpdateDb()
{
//first inserted row
SqlCommand myInsCom = GetInsCommand();
SqlCommand myUpdCom;// = GetUpdCommand();

myConn.Open();

foreach(DataRow dr in myDt.Select("", "" ,
DataViewRowState.Added))
{
//settings parameters value
myInsCom.Parameters["@OrdN"].Value = dr["OrdName"];
myInsCom.Parameters["@Quant"].Value = dr["Quantity"];
myInsCom.Parameters["@Price"].Value = dr["Price"];
myInsCom.Parameters["@Val"].Value = dr["Value"];

//fire insert
myInsCom.ExecuteNonQuery();
}

//then updated rows etc...
foreach(DataRow dr in myDt.Select("", "" ,
DataViewRowState.ModifiedCurrent
| DataViewRowState.ModifiedOriginal))
{
//settings parameters value

//myUpdCom.ExecuteNonQuery();
}

//delete on finish...

myConn.Close();
myDt.AcceptChanges();
}


private SqlCommand GetInsCommand()
{
SqlCommand insCom;
insCom = new SqlCommand(
"Insert Into Orders2 (OrdName, Quantity, Price, Value) " +
"Values (@OrdN, @Quant, @Price, @Val)", myConn);
//define parameters
insCom.Parameters.Add("@OrdN", SqlDbType.VarChar, 50);
insCom.Parameters.Add("@Quant", SqlDbType.Int, 0);
insCom.Parameters.Add("@Price", SqlDbType.Money, 50);
insCom.Parameters.Add("@Val", SqlDbType.Money, 50);
return insCom;
}
}

But I'm not sure if updating database in so way (by data computed in
expression column) is really correct idea.

Regards,
Grzegorz

Ps.
Actual Order table:
Create Table Orders (
IdOrd Int Identity(1,1) Primary Key,
OrdName Varchar(50),
Quantity Int,
Price Money,
Value Money
)

 

[Grzegorz Danowski]

Yes, I would like to update data from client data to database.

Why I have problem when update data with Expression property of column?

What is the root cause of this situation?

Well, I know it is imposible using DataAdapter object, but you can do it
in
this way (unfortunely a bit more writing):
(...)
public void UpdateDb()
{
//first inserted row
SqlCommand myInsCom = GetInsCommand();
SqlCommand myUpdCom;// = GetUpdCommand();

myConn.Open();

foreach(DataRow dr in myDt.Select("", "" ,
DataViewRowState.Added))
{
//settings parameters value
myInsCom.Parameters["@OrdN"].Value = dr["OrdName"];

(...)

And another idea:
Instead of using expression column use ColumnChanged event to calculate
value of your column, for example:
private void myDt_ColumnChanged(object sender, DataColumnChangeEventArgs
e)
{
if(e.Column.ColumnName != "Value")
{
DataRow dr = e.Row;
if(dr.IsNull("Price") || dr.IsNull("Quantity"))
dr["Value"] = DBNull.Value;
else
dr["Value"] = (System.Decimal)dr["Price"] * (int)dr["Quantity"];
}
}

And then you can use simple updating logic (by DataAdapter).
The method has also so advantage, that user first load from db previously
computed column (maybe calculated on other principles then current) but he
see so data as in db. If he change something, recalculation will be done on
actual principles.

However, the best way is ussing TSQL and triggers.

Regards,
Grzegorz