Help with dataset update back to sql server database

D

Devhead

I'm trying to update my dataset back to a table named 'CaseProcedures' in
the sql server DB. I'm getting the following exception when i attempt to run
the line:

SDA.Update(dsChanges,"CaseProcedure");

Exception:

Server was unable to process request ---> Update unable to find table
mapping['CaseProcedure'] or DataTable 'CaseProcedure'

This is strange because i define this in the update statement above. Here's
is my code in its entirety. It is located in a web service:

[WebMethod(true)]

public DataSet UpdateCaseProcedure(DataSet dsChanges)

{

//Get the connection string from web.config file

string TmpConn = ConfigurationSettings.AppSettings["connstring"];

SqlConnection SConn = new SqlConnection(TmpConn);

SqlDataAdapter SDA = new System.Data.SqlClient.SqlDataAdapter();

DataView dv = new DataView(dsChanges.Tables[0]);

//update Case Procedure Table with added rows

dv.RowStateFilter = DataViewRowState.Added;


//StepNbr,Action,ExpectedResult,ActualResult,CaseProcedureStatus.CaseProcedureStatus
Status,Comments, CaseProcedure.CaseProcedureStatus_id,
CaseProcedure.CaseProcedure_id

if (dv.Count > 0)

{

//build insert command

SqlCommand insCmd = new SqlCommand(

"insert into CaseProcedure
(StepNbr,Action,ExpectedResult,ActualResult,Comments)
values(@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments)",SConn);

//@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments,@CaseProcedureStatus_id

insCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

insCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

insCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

insCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16, "ActualResult");

insCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

SDA.InsertCommand = insCmd;

}


//update Case Procedure Table with modified rows

dv.RowStateFilter = DataViewRowState.ModifiedOriginal;

if (dv.Count > 0)

{

//build update command

SqlCommand upCmd = new SqlCommand(

"update CaseProcedure set
StepNbr=@StepNbr,Action=@Action,ExpectedResult=@ExpectedResult,ActualResult=@ActualResult,Comments=@Comments
where CaseProcedure_id=@CaseProcedure_id",SConn);

upCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

upCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

upCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

upCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16, "ActualResult");

upCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

upCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.UpdateCommand = upCmd;

}

//update Case Procedure Table with deleted rows

dv.RowStateFilter = DataViewRowState.Deleted;

if (dv.Count > 0)

{

//build delete command

SqlCommand delCmd = new SqlCommand(

"delete from CaseProcedure where CaseProcedure_id=@CaseProcedure_id",SConn);

delCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.DeleteCommand = delCmd;

}

dv.RowStateFilter = DataViewRowState.None;

SDA.Update(dsChanges,"CaseProcedure"); //this is where the exception
happens


return dsChanges;

}
 
J

Jason L James

I do not see where you have filled your dataset. Have you given the
tables in the dataset names?

When you call

DataView dv = new DataView(dsChanges.Tables[0]);

you use the table's index with the collect, not its name.

Calling

SDA.Update(dsChanges,"CaseProcedure");

suggusts that "CaseProcedure" is the name of the table in the DS,
not the SQL DB?

What happening if you try

SDA.Update(dsChanges.Tables(0));

Good luck,

Jason.


I'm trying to update my dataset back to a table named 'CaseProcedures' in
the sql server DB. I'm getting the following exception when i attempt to run
the line:

SDA.Update(dsChanges,"CaseProcedure");

Exception:

Server was unable to process request ---> Update unable to find table
mapping['CaseProcedure'] or DataTable 'CaseProcedure'

This is strange because i define this in the update statement above. Here's
is my code in its entirety. It is located in a web service:

[WebMethod(true)]

public DataSet UpdateCaseProcedure(DataSet dsChanges)

{

//Get the connection string from web.config file

string TmpConn = ConfigurationSettings.AppSettings["connstring"];

SqlConnection SConn = new SqlConnection(TmpConn);

SqlDataAdapter SDA = new System.Data.SqlClient.SqlDataAdapter();

DataView dv = new DataView(dsChanges.Tables[0]);

//update Case Procedure Table with added rows

dv.RowStateFilter = DataViewRowState.Added;


//StepNbr,Action,ExpectedResult,ActualResult,CaseProcedureStatus.CaseProcedureStatus
Status,Comments, CaseProcedure.CaseProcedureStatus_id,
CaseProcedure.CaseProcedure_id

if (dv.Count > 0)

{

//build insert command

SqlCommand insCmd = new SqlCommand(

"insert into CaseProcedure
(StepNbr,Action,ExpectedResult,ActualResult,Comments)
values(@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments)",SConn);

//@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments,@CaseProcedureStatus_id

insCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

insCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

insCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

insCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16, "ActualResult");

insCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

SDA.InsertCommand = insCmd;

}


//update Case Procedure Table with modified rows

dv.RowStateFilter = DataViewRowState.ModifiedOriginal;

if (dv.Count > 0)

{

//build update command

SqlCommand upCmd = new SqlCommand(

"update CaseProcedure set
StepNbr=@StepNbr,Action=@Action,ExpectedResult=@ExpectedResult,ActualResult=@ActualResult,Comments=@Comments
where CaseProcedure_id=@CaseProcedure_id",SConn);

upCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

upCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

upCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

upCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16, "ActualResult");

upCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

upCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.UpdateCommand = upCmd;

}

//update Case Procedure Table with deleted rows

dv.RowStateFilter = DataViewRowState.Deleted;

if (dv.Count > 0)

{

//build delete command

SqlCommand delCmd = new SqlCommand(

"delete from CaseProcedure where CaseProcedure_id=@CaseProcedure_id",SConn);

delCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.DeleteCommand = delCmd;

}

dv.RowStateFilter = DataViewRowState.None;

SDA.Update(dsChanges,"CaseProcedure"); //this is where the exception
happens


return dsChanges;

}
Click to expand...
 
C

Cor Ligthert [MVP]

DevHead,

We see a lot of code.

It is intended to update the dataset dsChanges.

What is that?

Cor
 
D

Devhead

OK setting SDA.Update(dsChanges.Tables[0]) seem to save my data but the
other fields were getting truncated.

Devhead said:
I'm trying to update my dataset back to a table named 'CaseProcedures' in
the sql server DB. I'm getting the following exception when i attempt to
run the line:

SDA.Update(dsChanges,"CaseProcedure");

Exception:

Server was unable to process request ---> Update unable to find table
mapping['CaseProcedure'] or DataTable 'CaseProcedure'

This is strange because i define this in the update statement above.
Here's is my code in its entirety. It is located in a web service:

[WebMethod(true)]

public DataSet UpdateCaseProcedure(DataSet dsChanges)

{

//Get the connection string from web.config file

string TmpConn = ConfigurationSettings.AppSettings["connstring"];

SqlConnection SConn = new SqlConnection(TmpConn);

SqlDataAdapter SDA = new System.Data.SqlClient.SqlDataAdapter();

DataView dv = new DataView(dsChanges.Tables[0]);

//update Case Procedure Table with added rows

dv.RowStateFilter = DataViewRowState.Added;


//StepNbr,Action,ExpectedResult,ActualResult,CaseProcedureStatus.CaseProcedureStatus
Status,Comments, CaseProcedure.CaseProcedureStatus_id,
CaseProcedure.CaseProcedure_id

if (dv.Count > 0)

{

//build insert command

SqlCommand insCmd = new SqlCommand(

"insert into CaseProcedure
(StepNbr,Action,ExpectedResult,ActualResult,Comments)
values(@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments)",SConn);

//@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments,@CaseProcedureStatus_id

insCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

insCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

insCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

insCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16,
"ActualResult");

insCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

SDA.InsertCommand = insCmd;

}


//update Case Procedure Table with modified rows

dv.RowStateFilter = DataViewRowState.ModifiedOriginal;

if (dv.Count > 0)

{

//build update command

SqlCommand upCmd = new SqlCommand(

"update CaseProcedure set
StepNbr=@StepNbr,Action=@Action,ExpectedResult=@ExpectedResult,ActualResult=@ActualResult,Comments=@Comments
where CaseProcedure_id=@CaseProcedure_id",SConn);

upCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

upCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

upCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

upCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16, "ActualResult");

upCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

upCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.UpdateCommand = upCmd;

}

//update Case Procedure Table with deleted rows

dv.RowStateFilter = DataViewRowState.Deleted;

if (dv.Count > 0)

{

//build delete command

SqlCommand delCmd = new SqlCommand(

"delete from CaseProcedure where
CaseProcedure_id=@CaseProcedure_id",SConn);

delCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.DeleteCommand = delCmd;

}

dv.RowStateFilter = DataViewRowState.None;

SDA.Update(dsChanges,"CaseProcedure"); //this is where the exception
happens


return dsChanges;

}
Click to expand...
 
J

Jason James

DevHead,

are the field definitions in your dataset the same as those in the DB?
Have you imported the schema before importing the data?

myDA.FillSchema(myDS, SchemaType.Source)

Is it only the text fields that are getting truncated?

Let us know,

Jason

OK setting SDA.Update(dsChanges.Tables[0]) seem to save my data but the
other fields were getting truncated.

Devhead said:
I'm trying to update my dataset back to a table named 'CaseProcedures' in
the sql server DB. I'm getting the following exception when i attempt to
run the line:

SDA.Update(dsChanges,"CaseProcedure");

Exception:

Server was unable to process request ---> Update unable to find table
mapping['CaseProcedure'] or DataTable 'CaseProcedure'

This is strange because i define this in the update statement above.
Here's is my code in its entirety. It is located in a web service:

[WebMethod(true)]

public DataSet UpdateCaseProcedure(DataSet dsChanges)

{

//Get the connection string from web.config file

string TmpConn = ConfigurationSettings.AppSettings["connstring"];

SqlConnection SConn = new SqlConnection(TmpConn);

SqlDataAdapter SDA = new System.Data.SqlClient.SqlDataAdapter();

DataView dv = new DataView(dsChanges.Tables[0]);

//update Case Procedure Table with added rows

dv.RowStateFilter = DataViewRowState.Added;


//StepNbr,Action,ExpectedResult,ActualResult,CaseProcedureStatus.CaseProcedureStatus
Status,Comments, CaseProcedure.CaseProcedureStatus_id,
CaseProcedure.CaseProcedure_id

if (dv.Count > 0)

{

//build insert command

SqlCommand insCmd = new SqlCommand(

"insert into CaseProcedure
(StepNbr,Action,ExpectedResult,ActualResult,Comments)
values(@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments)",SConn);

//@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments,@CaseProcedureStatus_id

insCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

insCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

insCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

insCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16,
"ActualResult");

insCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

SDA.InsertCommand = insCmd;

}


//update Case Procedure Table with modified rows

dv.RowStateFilter = DataViewRowState.ModifiedOriginal;

if (dv.Count > 0)

{

//build update command

SqlCommand upCmd = new SqlCommand(

"update CaseProcedure set
StepNbr=@StepNbr,Action=@Action,ExpectedResult=@ExpectedResult,ActualResult=@ActualResult,Comments=@Comments
where CaseProcedure_id=@CaseProcedure_id",SConn);

upCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

upCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

upCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

upCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16, "ActualResult");

upCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

upCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.UpdateCommand = upCmd;

}

//update Case Procedure Table with deleted rows

dv.RowStateFilter = DataViewRowState.Deleted;

if (dv.Count > 0)

{

//build delete command

SqlCommand delCmd = new SqlCommand(

"delete from CaseProcedure where
CaseProcedure_id=@CaseProcedure_id",SConn);

delCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.DeleteCommand = delCmd;

}

dv.RowStateFilter = DataViewRowState.None;

SDA.Update(dsChanges,"CaseProcedure"); //this is where the exception
happens


return dsChanges;

}
Click to expand...
Click to expand...
 
D

Devhead

Well it appears that it is truncated after 16 characters. I set up the
parameters as text size 16 as is in the SQL server table definition but it
apparently is treating it like char(16). hmmmm, that seems strange.

Jason James said:
DevHead,

are the field definitions in your dataset the same as those in the DB?
Have you imported the schema before importing the data?

myDA.FillSchema(myDS, SchemaType.Source)

Is it only the text fields that are getting truncated?

Let us know,

Jason

OK setting SDA.Update(dsChanges.Tables[0]) seem to save my data but the
other fields were getting truncated.

Devhead said:
I'm trying to update my dataset back to a table named 'CaseProcedures'
in
the sql server DB. I'm getting the following exception when i attempt to
run the line:

SDA.Update(dsChanges,"CaseProcedure");

Exception:

Server was unable to process request ---> Update unable to find table
mapping['CaseProcedure'] or DataTable 'CaseProcedure'

This is strange because i define this in the update statement above.
Here's is my code in its entirety. It is located in a web service:

[WebMethod(true)]

public DataSet UpdateCaseProcedure(DataSet dsChanges)

{

//Get the connection string from web.config file

string TmpConn = ConfigurationSettings.AppSettings["connstring"];

SqlConnection SConn = new SqlConnection(TmpConn);

SqlDataAdapter SDA = new System.Data.SqlClient.SqlDataAdapter();

DataView dv = new DataView(dsChanges.Tables[0]);

//update Case Procedure Table with added rows

dv.RowStateFilter = DataViewRowState.Added;


//StepNbr,Action,ExpectedResult,ActualResult,CaseProcedureStatus.CaseProcedureStatus
Status,Comments, CaseProcedure.CaseProcedureStatus_id,
CaseProcedure.CaseProcedure_id

if (dv.Count > 0)

{

//build insert command

SqlCommand insCmd = new SqlCommand(

"insert into CaseProcedure
(StepNbr,Action,ExpectedResult,ActualResult,Comments)
values(@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments)",SConn);

//@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments,@CaseProcedureStatus_id

insCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

insCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

insCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

insCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16,
"ActualResult");

insCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

SDA.InsertCommand = insCmd;

}


//update Case Procedure Table with modified rows

dv.RowStateFilter = DataViewRowState.ModifiedOriginal;

if (dv.Count > 0)

{

//build update command

SqlCommand upCmd = new SqlCommand(

"update CaseProcedure set
StepNbr=@StepNbr,Action=@Action,ExpectedResult=@ExpectedResult,ActualResult=@ActualResult,Comments=@Comments
where CaseProcedure_id=@CaseProcedure_id",SConn);

upCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

upCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

upCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

upCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16,
"ActualResult");

upCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

upCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.UpdateCommand = upCmd;

}

//update Case Procedure Table with deleted rows

dv.RowStateFilter = DataViewRowState.Deleted;

if (dv.Count > 0)

{

//build delete command

SqlCommand delCmd = new SqlCommand(

"delete from CaseProcedure where
CaseProcedure_id=@CaseProcedure_id",SConn);

delCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.DeleteCommand = delCmd;

}

dv.RowStateFilter = DataViewRowState.None;

SDA.Update(dsChanges,"CaseProcedure"); //this is where the exception
happens


return dsChanges;

}
Click to expand...
Click to expand...
Click to expand...
 
D

Devhead

i set the size of textbox field parameters to the Max length of the
Richtextbox on my winform(2147483647) and that seemed to work but i don't
know if that is best practice or not???? it seems that textbox size should
be set up like the sql server text size of 16 but i'm not too sure about
that. would like a better understanding about this.........Thanks.

Devhead said:
Well it appears that it is truncated after 16 characters. I set up the
parameters as text size 16 as is in the SQL server table definition but it
apparently is treating it like char(16). hmmmm, that seems strange.

Jason James said:
DevHead,

are the field definitions in your dataset the same as those in the DB?
Have you imported the schema before importing the data?

myDA.FillSchema(myDS, SchemaType.Source)

Is it only the text fields that are getting truncated?

Let us know,

Jason

OK setting SDA.Update(dsChanges.Tables[0]) seem to save my data but the
other fields were getting truncated.

I'm trying to update my dataset back to a table named 'CaseProcedures'
in
the sql server DB. I'm getting the following exception when i attempt
to
run the line:

SDA.Update(dsChanges,"CaseProcedure");

Exception:

Server was unable to process request ---> Update unable to find table
mapping['CaseProcedure'] or DataTable 'CaseProcedure'

This is strange because i define this in the update statement above.
Here's is my code in its entirety. It is located in a web service:

[WebMethod(true)]

public DataSet UpdateCaseProcedure(DataSet dsChanges)

{

//Get the connection string from web.config file

string TmpConn = ConfigurationSettings.AppSettings["connstring"];

SqlConnection SConn = new SqlConnection(TmpConn);

SqlDataAdapter SDA = new System.Data.SqlClient.SqlDataAdapter();

DataView dv = new DataView(dsChanges.Tables[0]);

//update Case Procedure Table with added rows

dv.RowStateFilter = DataViewRowState.Added;


//StepNbr,Action,ExpectedResult,ActualResult,CaseProcedureStatus.CaseProcedureStatus
Status,Comments, CaseProcedure.CaseProcedureStatus_id,
CaseProcedure.CaseProcedure_id

if (dv.Count > 0)

{

//build insert command

SqlCommand insCmd = new SqlCommand(

"insert into CaseProcedure
(StepNbr,Action,ExpectedResult,ActualResult,Comments)
values(@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments)",SConn);

//@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments,@CaseProcedureStatus_id

insCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

insCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

insCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

insCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16,
"ActualResult");

insCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

SDA.InsertCommand = insCmd;

}


//update Case Procedure Table with modified rows

dv.RowStateFilter = DataViewRowState.ModifiedOriginal;

if (dv.Count > 0)

{

//build update command

SqlCommand upCmd = new SqlCommand(

"update CaseProcedure set
StepNbr=@StepNbr,Action=@Action,ExpectedResult=@ExpectedResult,ActualResult=@ActualResult,Comments=@Comments
where CaseProcedure_id=@CaseProcedure_id",SConn);

upCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

upCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

upCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

upCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16,
"ActualResult");

upCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

upCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.UpdateCommand = upCmd;

}

//update Case Procedure Table with deleted rows

dv.RowStateFilter = DataViewRowState.Deleted;

if (dv.Count > 0)

{

//build delete command

SqlCommand delCmd = new SqlCommand(

"delete from CaseProcedure where
CaseProcedure_id=@CaseProcedure_id",SConn);

delCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.DeleteCommand = delCmd;

}

dv.RowStateFilter = DataViewRowState.None;

SDA.Update(dsChanges,"CaseProcedure"); //this is where the
exception
happens


return dsChanges;

}
Click to expand...
Click to expand...
Click to expand...
 
J

Jason L James

If the field length on the DB is only 16 chars then setting the
textbox.MaxLength = 16 is a good way of ensure that only
16 characters get entered. This could also be checked by using
the validating method of the form or control.

I'm not sure if the string in the textbox has an end of string
character appended to it, so you might like to increase the size
of the field in the DB to 17 or 18, or reduce the MaxLength
property of the textbox.

How many characters are getting truncated? Is it consistent? How
are you setting up the field length in the dataset?

When I am creating fields with several alphanumeric characters in them
I tend to use the nvarchar datatype:

nVarChar(16) in your case.

Have you tried that? You could then change the update to type:

System.Data.SqlDbType.NVarChar, 16

Hope that helps and good luck. Let us know.

Jason

i set the size of textbox field parameters to the Max length of the
Richtextbox on my winform(2147483647) and that seemed to work but i don't
know if that is best practice or not???? it seems that textbox size should
be set up like the sql server text size of 16 but i'm not too sure about
that. would like a better understanding about this.........Thanks.

Devhead said:
Well it appears that it is truncated after 16 characters. I set up the
parameters as text size 16 as is in the SQL server table definition but it
apparently is treating it like char(16). hmmmm, that seems strange.

Jason James said:
DevHead,

are the field definitions in your dataset the same as those in the DB?
Have you imported the schema before importing the data?

myDA.FillSchema(myDS, SchemaType.Source)

Is it only the text fields that are getting truncated?

Let us know,

Jason

OK setting SDA.Update(dsChanges.Tables[0]) seem to save my data but the
other fields were getting truncated.

I'm trying to update my dataset back to a table named 'CaseProcedures'
in
the sql server DB. I'm getting the following exception when i attempt
to
run the line:

SDA.Update(dsChanges,"CaseProcedure");

Exception:

Server was unable to process request ---> Update unable to find table
mapping['CaseProcedure'] or DataTable 'CaseProcedure'

This is strange because i define this in the update statement above.
Here's is my code in its entirety. It is located in a web service:

[WebMethod(true)]

public DataSet UpdateCaseProcedure(DataSet dsChanges)

{

//Get the connection string from web.config file

string TmpConn = ConfigurationSettings.AppSettings["connstring"];

SqlConnection SConn = new SqlConnection(TmpConn);

SqlDataAdapter SDA = new System.Data.SqlClient.SqlDataAdapter();

DataView dv = new DataView(dsChanges.Tables[0]);

//update Case Procedure Table with added rows

dv.RowStateFilter = DataViewRowState.Added;


//StepNbr,Action,ExpectedResult,ActualResult,CaseProcedureStatus.CaseProcedureStatus
Status,Comments, CaseProcedure.CaseProcedureStatus_id,
CaseProcedure.CaseProcedure_id

if (dv.Count > 0)

{

//build insert command

SqlCommand insCmd = new SqlCommand(

"insert into CaseProcedure
(StepNbr,Action,ExpectedResult,ActualResult,Comments)
values(@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments)",SConn);

//@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments,@CaseProcedureStatus_id

insCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

insCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

insCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

insCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16,
"ActualResult");

insCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

SDA.InsertCommand = insCmd;

}


//update Case Procedure Table with modified rows

dv.RowStateFilter = DataViewRowState.ModifiedOriginal;

if (dv.Count > 0)

{

//build update command

SqlCommand upCmd = new SqlCommand(

"update CaseProcedure set
StepNbr=@StepNbr,Action=@Action,ExpectedResult=@ExpectedResult,ActualResult=@ActualResult,Comments=@Comments
where CaseProcedure_id=@CaseProcedure_id",SConn);

upCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

upCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

upCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

upCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16,
"ActualResult");

upCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

upCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.UpdateCommand = upCmd;

}

//update Case Procedure Table with deleted rows

dv.RowStateFilter = DataViewRowState.Deleted;

if (dv.Count > 0)

{

//build delete command

SqlCommand delCmd = new SqlCommand(

"delete from CaseProcedure where
CaseProcedure_id=@CaseProcedure_id",SConn);

delCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.DeleteCommand = delCmd;

}

dv.RowStateFilter = DataViewRowState.None;

SDA.Update(dsChanges,"CaseProcedure"); //this is where the
exception
happens


return dsChanges;

}
Click to expand...
Click to expand...
Click to expand...
 
D

Devhead

the text field in the SQL Server database is defined as text 16, the
default. i don't think that means text with 16 characters. i might be wrong
on that. anyway, all text fields on my winform was truncating to 16
characters so i increased the size of my update parameter to the max length
of text box on winform, which in this case was 2147483647. It seems that the
update parameters with text fields defined with size of 16 treats them like
char(13) whereas SQL Server text field size 16 do not.

Jason L James said:
If the field length on the DB is only 16 chars then setting the
textbox.MaxLength = 16 is a good way of ensure that only
16 characters get entered. This could also be checked by using
the validating method of the form or control.

I'm not sure if the string in the textbox has an end of string
character appended to it, so you might like to increase the size
of the field in the DB to 17 or 18, or reduce the MaxLength
property of the textbox.

How many characters are getting truncated? Is it consistent? How
are you setting up the field length in the dataset?

When I am creating fields with several alphanumeric characters in them
I tend to use the nvarchar datatype:

nVarChar(16) in your case.

Have you tried that? You could then change the update to type:

System.Data.SqlDbType.NVarChar, 16

Hope that helps and good luck. Let us know.

Jason

i set the size of textbox field parameters to the Max length of the
Richtextbox on my winform(2147483647) and that seemed to work but i don't
know if that is best practice or not???? it seems that textbox size should
be set up like the sql server text size of 16 but i'm not too sure about
that. would like a better understanding about this.........Thanks.

Devhead said:
Well it appears that it is truncated after 16 characters. I set up the
parameters as text size 16 as is in the SQL server table definition but
it
apparently is treating it like char(16). hmmmm, that seems strange.

DevHead,

are the field definitions in your dataset the same as those in the DB?
Have you imported the schema before importing the data?

myDA.FillSchema(myDS, SchemaType.Source)

Is it only the text fields that are getting truncated?

Let us know,

Jason

OK setting SDA.Update(dsChanges.Tables[0]) seem to save my data but the
other fields were getting truncated.

I'm trying to update my dataset back to a table named
'CaseProcedures'
in
the sql server DB. I'm getting the following exception when i attempt
to
run the line:

SDA.Update(dsChanges,"CaseProcedure");

Exception:

Server was unable to process request ---> Update unable to find table
mapping['CaseProcedure'] or DataTable 'CaseProcedure'

This is strange because i define this in the update statement above.
Here's is my code in its entirety. It is located in a web service:

[WebMethod(true)]

public DataSet UpdateCaseProcedure(DataSet dsChanges)

{

//Get the connection string from web.config file

string TmpConn = ConfigurationSettings.AppSettings["connstring"];

SqlConnection SConn = new SqlConnection(TmpConn);

SqlDataAdapter SDA = new System.Data.SqlClient.SqlDataAdapter();

DataView dv = new DataView(dsChanges.Tables[0]);

//update Case Procedure Table with added rows

dv.RowStateFilter = DataViewRowState.Added;


//StepNbr,Action,ExpectedResult,ActualResult,CaseProcedureStatus.CaseProcedureStatus
Status,Comments, CaseProcedure.CaseProcedureStatus_id,
CaseProcedure.CaseProcedure_id

if (dv.Count > 0)

{

//build insert command

SqlCommand insCmd = new SqlCommand(

"insert into CaseProcedure
(StepNbr,Action,ExpectedResult,ActualResult,Comments)
values(@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments)",SConn);

//@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments,@CaseProcedureStatus_id

insCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

insCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

insCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

insCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16,
"ActualResult");

insCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

SDA.InsertCommand = insCmd;

}


//update Case Procedure Table with modified rows

dv.RowStateFilter = DataViewRowState.ModifiedOriginal;

if (dv.Count > 0)

{

//build update command

SqlCommand upCmd = new SqlCommand(

"update CaseProcedure set
StepNbr=@StepNbr,Action=@Action,ExpectedResult=@ExpectedResult,ActualResult=@ActualResult,Comments=@Comments
where CaseProcedure_id=@CaseProcedure_id",SConn);

upCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

upCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

upCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

upCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16,
"ActualResult");

upCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

upCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.UpdateCommand = upCmd;

}

//update Case Procedure Table with deleted rows

dv.RowStateFilter = DataViewRowState.Deleted;

if (dv.Count > 0)

{

//build delete command

SqlCommand delCmd = new SqlCommand(

"delete from CaseProcedure where
CaseProcedure_id=@CaseProcedure_id",SConn);

delCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.DeleteCommand = delCmd;

}

dv.RowStateFilter = DataViewRowState.None;

SDA.Update(dsChanges,"CaseProcedure"); //this is where the
exception
happens


return dsChanges;

}
Click to expand...
Click to expand...
Click to expand...
 
J

Jason James

Devhead,

I'm not sure why you are seeing the truncation that you are, but I
would suggest trying to change the datatype of the filed to
nvarchar(50). This will store up to 50 uni-code chars. I've had
not problems with it or seen any truncation.

This is the definition of the text datatype that you are using:

<B>text</B>

Variable-length non-Unicode data in the code page of the server and
with a maximum length of 2^31-1 (2,147,483,647) characters. When the
server code page uses double-byte characters, the storage is still
2,147,483,647 bytes. Depending on the character string, the storage
size may be less than 2,147,483,647 bytes.

Give it a go and let me know how you get on.

Good luck,

Jason.

the text field in the SQL Server database is defined as text 16, the
default. i don't think that means text with 16 characters. i might be wrong
on that. anyway, all text fields on my winform was truncating to 16
characters so i increased the size of my update parameter to the max length
of text box on winform, which in this case was 2147483647. It seems that the
update parameters with text fields defined with size of 16 treats them like
char(13) whereas SQL Server text field size 16 do not.

Jason L James said:
If the field length on the DB is only 16 chars then setting the
textbox.MaxLength = 16 is a good way of ensure that only
16 characters get entered. This could also be checked by using
the validating method of the form or control.

I'm not sure if the string in the textbox has an end of string
character appended to it, so you might like to increase the size
of the field in the DB to 17 or 18, or reduce the MaxLength
property of the textbox.

How many characters are getting truncated? Is it consistent? How
are you setting up the field length in the dataset?

When I am creating fields with several alphanumeric characters in them
I tend to use the nvarchar datatype:

nVarChar(16) in your case.

Have you tried that? You could then change the update to type:

System.Data.SqlDbType.NVarChar, 16

Hope that helps and good luck. Let us know.

Jason

i set the size of textbox field parameters to the Max length of the
Richtextbox on my winform(2147483647) and that seemed to work but i don't
know if that is best practice or not???? it seems that textbox size should
be set up like the sql server text size of 16 but i'm not too sure about
that. would like a better understanding about this.........Thanks.

Well it appears that it is truncated after 16 characters. I set up the
parameters as text size 16 as is in the SQL server table definition but
it
apparently is treating it like char(16). hmmmm, that seems strange.

DevHead,

are the field definitions in your dataset the same as those in the DB?
Have you imported the schema before importing the data?

myDA.FillSchema(myDS, SchemaType.Source)

Is it only the text fields that are getting truncated?

Let us know,

Jason

OK setting SDA.Update(dsChanges.Tables[0]) seem to save my data but the
other fields were getting truncated.

I'm trying to update my dataset back to a table named
'CaseProcedures'
in
the sql server DB. I'm getting the following exception when i attempt
to
run the line:

SDA.Update(dsChanges,"CaseProcedure");

Exception:

Server was unable to process request ---> Update unable to find table
mapping['CaseProcedure'] or DataTable 'CaseProcedure'

This is strange because i define this in the update statement above.
Here's is my code in its entirety. It is located in a web service:

[WebMethod(true)]

public DataSet UpdateCaseProcedure(DataSet dsChanges)

{

//Get the connection string from web.config file

string TmpConn = ConfigurationSettings.AppSettings["connstring"];

SqlConnection SConn = new SqlConnection(TmpConn);

SqlDataAdapter SDA = new System.Data.SqlClient.SqlDataAdapter();

DataView dv = new DataView(dsChanges.Tables[0]);

//update Case Procedure Table with added rows

dv.RowStateFilter = DataViewRowState.Added;


//StepNbr,Action,ExpectedResult,ActualResult,CaseProcedureStatus.CaseProcedureStatus
Status,Comments, CaseProcedure.CaseProcedureStatus_id,
CaseProcedure.CaseProcedure_id

if (dv.Count > 0)

{

//build insert command

SqlCommand insCmd = new SqlCommand(

"insert into CaseProcedure
(StepNbr,Action,ExpectedResult,ActualResult,Comments)
values(@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments)",SConn);

//@StepNbr,@Action,@ExpectedResult,@ActualResult,@Comments,@CaseProcedureStatus_id

insCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

insCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

insCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

insCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16,
"ActualResult");

insCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

SDA.InsertCommand = insCmd;

}


//update Case Procedure Table with modified rows

dv.RowStateFilter = DataViewRowState.ModifiedOriginal;

if (dv.Count > 0)

{

//build update command

SqlCommand upCmd = new SqlCommand(

"update CaseProcedure set
StepNbr=@StepNbr,Action=@Action,ExpectedResult=@ExpectedResult,ActualResult=@ActualResult,Comments=@Comments
where CaseProcedure_id=@CaseProcedure_id",SConn);

upCmd.Parameters.Add("@StepNbr", SqlDbType.Int, 4, "StepNbr");

upCmd.Parameters.Add("@Action", SqlDbType.Text, 16, "Action");

upCmd.Parameters.Add("@ExpectedResult", SqlDbType.Text, 16,
"ExpectedResult");

upCmd.Parameters.Add("@ActualResult", SqlDbType.Text, 16,
"ActualResult");

upCmd.Parameters.Add("@Comments", SqlDbType.Text, 16, "Comments");

upCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.UpdateCommand = upCmd;

}

//update Case Procedure Table with deleted rows

dv.RowStateFilter = DataViewRowState.Deleted;

if (dv.Count > 0)

{

//build delete command

SqlCommand delCmd = new SqlCommand(

"delete from CaseProcedure where
CaseProcedure_id=@CaseProcedure_id",SConn);

delCmd.Parameters.Add("@CaseProcedure_id", SqlDbType.Int, 4,
"CaseProcedure_id");

SDA.DeleteCommand = delCmd;

}

dv.RowStateFilter = DataViewRowState.None;

SDA.Update(dsChanges,"CaseProcedure"); //this is where the
exception
happens


return dsChanges;

}
Click to expand...
Click to expand...
Click to expand...
 

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

c# sql update question 1
update call not working 2
SQL Server dataset read from database A and write to database B 2
How to get xml based dataset to update an SQL server database? 6
How to Sync Dataset to Database on SQL Server? Help! 5
How to delete a SQL Server record (descending from another record of the same table) with a C# appli 6

Top