decimal numbers c# and sql server

[Stropher]

Hi!

I just want to update a column in a table having a value of a decimal
number.
Then I tried this via sql-query analyzer

update tablepost set preis = 0.71 where idnumber = 50
It worked well.

Then in my c# program, I tried the same: but first of all

command = "update tablepost set preis = 0.71 where idnumber = 50";
//I passed it as a paramter for a given method:
This also functioned well and stored the right value.

Then I tried these:
decimal preis = 0.71;

command = "update tablepost set preis = " + this.preis + " where
idnumber = 50";

It did not compile, then I added:
command = "update tablepost set preis = convert(decimal(28,2)," +
this.preis + ")where idnumber = 50";

It compiled, but instead of 0.71, it stored 0, when I give 32.8 it
stores 32.

In customized regional numbers I have:
Decimal symbol: ','
No. of digits after decimal: '.'

And for the tablepost - preis, I do have
decimal (as datatype), 28 and 2 for accuracy and decimal places
respectively.

Can anyone give me some tips, how to go about this...
Lest I forget, I can select from tables returning back decimal numbers
without any problem

Thanks in advance.
Stropher

 

[gmiley]

try this:

decimal preis = 0.71m;

It might be that it is somewhere trying, or actually converting it to a
double. Appending your literal with 'm', as in the above example
prevents that from happening.

 

[Stropher]

Thanks Gmiley for your quick answer,
I have tried it, but it could not go through complaining of not having
'm' defined

 

[gmiley]

Could you provide the code for the block where this all takes place?

starting with the variable definition and setting, and the sql string
set, to the execution of the sql string?

 

[Stropher]

Oh thanks, here:

private DataTable tblResult;
private void btnImport_Click(object sender, EventArgs e)
{
int articleID = 0;
decimal preis = 0;
decimal dek = 0;
string command = "";

//created and returned articleID
//...

//Extract the Article numbers from the excel file

//Use the article numbers to select their preises from the
respective tables
command = "SELECT art_id as ArticleID, eg_preis as Preis
from article inner join boughtarticles on
art_id = bgt_id WHERE art_aktive = 1";

tblResult = this.dal.SelectStatementsData(command); //call
the function and get datatable in return

if (tblResult != null)
{
foreach (DataRow dr in tblResult.Rows)
{
if (dr[0] != null)
articleID = Convert.ToInt32(dr[0]);
if (dr[1] != null)
preis = Decimal.Parse(dr[1].ToString()); //here
I get sth. like 0.71, 32.80, etc

//create and insert into a new row of tablepost
this.tablepostID =
this.dal.CreateStatements(insertCommandText);
if (tablepostID > 0)
{
updateCommandText = "update tablepost set
articleID = " + articleID + ", Preis = convert
(decimal(28,2)," + preis + ") where tablepostID
= " + this.tablepostID.ToString();

//Update the Table and indicate whether
successful or not
updateSuccessful =
this.dal.UpdateStatements(updateCommandText);

if (!updateSuccessful)
MessageBox.Show("UpdateTablepost NOT
successful!\n " + dal.ExceptionMsg);
}
}//end of foreach (...)

dataGVDisplay.DataSource = tblResult; //display it on
the grid table
//...
}//end of if (tableResult ...)
else
MessageBox.Show(dal.ExceptionMsg);
}
//have to convert to decimal, b/c what I get with select is sth. like
0,71, and what I send to the db (its what works or what the db accepts)
in return is 0.71 and when I take look at the table it stores 0,71
//the problem of german and english languages...: If I enter 0.71 (no
problem), but as a parameter preis having 0.71 no way.

---------------------------------------//another class DAL for the
connections to the db
private string exeptionMsg;
public string ExceptionMsg
{
get
{
return this.exceptionMsg;
}

set
{
this.exceptionMsg = value;
}
}

public bool UpdateStatements(string updateCommandText)
{

SqlCommand command = new SqlCommand(updateCommandText);
command.Connection = connection;
bool rowsAffected = false;
try
{
connection.Open();

int affectedRows = command.ExecuteNonQuery();

if (affectedRows > 0)
rowsAffected = true;
}
catch (SqlException ex)
{
exceptionMsg = "PROBLEM by UpdateStatements (Update
failed) " + ex.Message;
}
finally
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
return rowsAffected;
}

 

[Stropher]

has no one had such a problem before?..
still searching for help....

Thanks

 

[Stropher]

This was the solution to my problem....
Thanks to all for your contributions

double price = 0;

SqlParameter parPrice = new SqlParameter("@price", SqlDbType.Float);
parPrice.Value = price;

commandText = "update Article set price = @price where ArticleID = " +
articleID.ToString();
this.command.Parameters.Insert(0, parPreis);
this.command.Connection = this.connection;
this.connection.Open();

int affectedRows = this.command.ExecuteNonQuery();