Dataadpater does not update database

I hope someone can help me.

My dataadapter will not update my database. Everything seems to work, and I get no errors of any kind. But my database is not being touched. I ran a sql profiler on it and there is no trace of my update. I can retrieve data just fine, but I cannot get it to update with my onclick event. Please just point me in the right direction, or any direction would also be cool.

Thank you.

Here is my code:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

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;

publicpartialclassWebAdmin_EditNews2 : System.Web.UI.Page

{

publicSqlConnection conn =newSqlConnection("Data Source=vnadbms01;initial catalog=vnawebdev; " +

"integrated security=true;persist security info=True;");

publicSqlDataAdapter da =newSqlDataAdapter();

publicDataSet ds =newDataSet();

publicDataTable dt =newDataTable();

protectedvoid Page_Load(object sender,EventArgs e)

{

if (!IsPostBack)

{

string MsgIDString = Session["MsgID"].ToString();

//Select Parameters:

SqlParameter SQLPMsgID =newSqlParameter("@.Msg_ID",SqlDbType.Int);

SQLPMsgID.Direction =ParameterDirection.Input;

SQLPMsgID.Value = MsgIDString;

da.SelectCommand =newSqlCommand();

//Set Select Commands

da.SelectCommand.Connection = conn;

da.SelectCommand.CommandText ="Messages_select";

da.SelectCommand.CommandType =CommandType.StoredProcedure;

da.SelectCommand.Parameters.Add(SQLPMsgID);

da.Fill(ds,"VNA_News");

FormView1.DataSource = ds;

FormView1.DataBind();

}

}

protectedvoid EditButton_Click(object sender,EventArgs e)

{

//Update parameters:

Label sqlprmMsgID = (Label)FormView1.FindControl("Msglbl");

SqlParameter SQLPMsgID2 =newSqlParameter("@.Msg_ID",SqlDbType.Int);

SQLPMsgID2.Direction =ParameterDirection.Input;

SQLPMsgID2.Value = sqlprmMsgID.Text;

TextBox sqlprmUsrID = (TextBox)FormView1.FindControl("User_IDTextBox");

SqlParameter SQLPDeptID =newSqlParameter("@.User_ID",SqlDbType.Int);

SQLPDeptID.Direction =ParameterDirection.Input;

SQLPDeptID.Value = sqlprmUsrID.Text;

TextBox sqlprmDept= (TextBox)FormView1.FindControl("Dept_IDTextBox");

SqlParameter SQLPUsrID =newSqlParameter("@.Dept_ID",SqlDbType.Int);

SQLPUsrID.Direction =ParameterDirection.Input;

SQLPUsrID.Value = sqlprmDept.Text;

TextBox sqlprmBody = (TextBox)FormView1.FindControl("Msg_bodyTextBox");

SqlParameter SQLPMsgBody =newSqlParameter("@.Msg_body",SqlDbType.Text);

SQLPMsgBody.Direction =ParameterDirection.Input;

SQLPMsgBody.Value = sqlprmBody.Text;

TextBox sqlprmMsgTitle = (TextBox)FormView1.FindControl("Msg_TitleTextBox");

SqlParameter SQLPMsgTitle =newSqlParameter("@.Msg_Title",SqlDbType.VarChar);

SQLPMsgTitle.Direction =ParameterDirection.Input;

SQLPMsgTitle.Value = sqlprmMsgTitle.Text;

TextBox sqlprmsts = (TextBox)FormView1.FindControl("stsTextBox");

SqlParameter SQLPMsgsts =newSqlParameter("@.sts",SqlDbType.Int);

SQLPMsgsts.Direction =ParameterDirection.Input;

SQLPMsgsts.Value = sqlprmsts.Text;

TextBox sqlprmexpdat = (TextBox)FormView1.FindControl("Exp_DatTextBox");

SqlParameter SQLPExpDat =newSqlParameter("@.Exp_Dat",SqlDbType.DateTime);

SQLPExpDat.Direction =ParameterDirection.Input;

SQLPExpDat.Value = sqlprmexpdat.Text;

//Set Update Commands

SqlConnection conn2 =newSqlConnection("Data Source=vnadbms01;initial catalog=vnawebdev; " +

"integrated security=true;persist security info=True;");

SqlDataAdapter dau =newSqlDataAdapter();

dau.UpdateCommand =newSqlCommand();

dau.UpdateCommand.Connection = conn2;

dau.UpdateCommand.CommandText ="Messages_Update";

dau.UpdateCommand.CommandType =CommandType.StoredProcedure;

dau.UpdateCommand.Parameters.Add(SQLPMsgID2);

dau.UpdateCommand.Parameters.Add(SQLPDeptID);

dau.UpdateCommand.Parameters.Add(SQLPMsgTitle);

dau.UpdateCommand.Parameters.Add(SQLPMsgBody);

dau.UpdateCommand.Parameters.Add(SQLPMsgsts);

dau.UpdateCommand.Parameters.Add(SQLPExpDat);

dau.UpdateCommand.Parameters.Add(SQLPUsrID);

dau.TableMappings.Add("VNA_News","VNA_News");

dau.Update(ds,"VNA_News");

I recommend you not to use SqlDataAdapter.Update to do data update to database.It is better to use SqlCommand.ExecuteNonQuery or SqlCommand.ExecuteScaler.If you want to do this,try to see this.

http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/UpdateDataFromDB.aspx

http://p2p.wrox.com/topic.asp?TOPIC_ID=7537

Wish this help you.

|||

Thank you for the info. After doing some reading I see why the dataadapter in a webform can be problematic. It sounds like it works great for windows forms. I am moving ahead with the sqlcommand.Executenonquery. That should hopefully do the trick. If not, I will try another method.

I spent way too much time on this. I should have been open to other methods for getting the job done. Reading also helps.

DataAdd narrowing conversion error

I am simply trying to take a date and add the value from another field
to it using the following expression:
=dateadd("d", " & Fields!new_tgddatefrom.Value & ",
Parameters!HolidayDate.Value)
new_tgddatefrom.Value is an int data type
HolidayDate.Value is a date
The expression works fine if I use an acutal number for the value to
add but when I use the field.value I get an error:
Overload resoution failed because no accessible 'DateAdd can be called
without a narrowing conversion.
Why or why is it so difficult to work with dates!?!? If anyone can
help me get this working I would sincerely appreciate it!
DebraTry =dateadd("d", Fields!new_tgddatefrom.Value, Parameters!HolidayDate.Value)
"Debralous" wrote:
> I am simply trying to take a date and add the value from another field
> to it using the following expression:
> =dateadd("d", " & Fields!new_tgddatefrom.Value & ",
> Parameters!HolidayDate.Value)
> new_tgddatefrom.Value is an int data type
> HolidayDate.Value is a date
>
> The expression works fine if I use an acutal number for the value to
> add but when I use the field.value I get an error:
> Overload resoution failed because no accessible 'DateAdd can be called
> without a narrowing conversion.
> Why or why is it so difficult to work with dates!?!? If anyone can
> help me get this working I would sincerely appreciate it!
> Debra
>|||William wrote:
> Try =dateadd("d", Fields!new_tgddatefrom.Value, Parameters!HolidayDate.Value)
>
That was the first thing I tried. While it doesn't error, it doesn't
actually add the number of days in Fields!new_tgddatefrom.Value, it
just shows the date which is in Parameters!HolidayDate.Value.
Any other suggestions?|||This is the proper function call:
= DateAdd(interval, number, date)
Are you sure you have valid datatypes and values for your parameters?
If the function is used in a report with multiple datasets or groupings, do
you need to declare a different scope for your function variables?
"Debralous" wrote:
> William wrote:
> > Try =dateadd("d", Fields!new_tgddatefrom.Value, Parameters!HolidayDate.Value)
> >
> That was the first thing I tried. While it doesn't error, it doesn't
> actually add the number of days in Fields!new_tgddatefrom.Value, it
> just shows the date which is in Parameters!HolidayDate.Value.
> Any other suggestions?
>

dataadapter.Update() OR ExecuteNonQuery()

Hy, again! I am at the begining of an application. I have some modules that insert, delete and update only one row at one table. My question is should I use dataadapter.Update() or ExecuteNonQuery(). I prefer ExecuteNonQuery because I want to build a class : DataLayer to implement my own InsertProcedure(), UpdateProcedure(),DeleteProcedure(). I want speed in my application, so which is the best: dataadapter.Update() OR ExecuteNonQuery(). Thank you!

Hi

If you are only intending on working with one row at a time (presumably using the primary key for the data you are working with) then it would be more efficient to use the ExecuteNonQuery method of the command object. The Update method of the DataAdapter object will attempt to update all changes to rows within a DataSet/DataTable.

HTH

|||You said: "Update method of the DataAdapter object will attempt to update ALL changes to rows within a DataSet/DataTable", even if I write: dataadapter.UpdateCommand="update TableName set field1 with ? for .... "?|||

Hi

If you specify the DataAdapter's UpdateCommand to update only one row then that will be similar to the ExecuteNonQuery method. I assumed by DataAdapter.Update that you were going to pass it a DataSet/DataTable so that all changed rows would be persisted to the Database.

To be honest though, the DataAdapter's UpdateCommand is a command object so therefore, to update a row you would have configured a DataAdapter and then configured a Command object when you could simply have configured a Command object only. However, if you already had the DataAdapter available to you then this wouldn't be so much of an issue. It really depends on the type of aproach you are taking, especially when considering a Data Access Layer.

HTH

|||Yes, you are right. With dataadapter the work is double. Thank you.

DataAdapter.Update updates zero records

I am having trouble getting an Update call to actually update records. The select statement is a stored procedure which is uses inner joins to link to property tables. The update, insert, and delete commands were generated by Visual Studio and only affect the primary table. So, to provide a simple example, I have a customer table with UID, Name, and LanguageID and a seperate table with LanguageID and LanguageDescription. The stored procedure can be used to populate a datagrid with all results (this works). The stored procedure also populates an edit page with one UID (this works). After the edit is completed, I attempt to update the dataset, which only has one row at this time, which shows that it has been modified. The Update modifies 0 rows and raises no exceptions. Is this because the update, insert, and delete statements do not match up one-to-one with the dataset? If so, what are my choices?I found the source of the problem. It was a result of a conflict elsewhere. So, if anyone else is trying to use a combination of stored procedures and normal sql queries, it will work fine, even if the statements do not match up perfectly with the dataset. The queries simply need to meet any requirements of the database.

DataAdapter.Update Method Question.

Hi,

I am trying to use DataAdapter.Update to save a file stream into SQl Express.

I have a dialog box that lets user select the file:

openFileDialog1.ShowDialog();

I want to put

openFileDialog1.OpenFile();

Into

this.documentTableAdapter.Update(this.docControllerAlphaDBDataSet.Document.DocumentColumn);

I am thinking that it might just be some syntax issue, but I looked online, and didn't find much answers.

Thanks,
Ke

Take a look on the links I posted here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=622943&SiteID=1

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

DataAdapter.Update does not work with DataSet.Merge!!!!

Hi Everybody,
I am Updating server database with data from client database, I am using mySQL Ver 4.1, I am unable to do that. What should I do to change the codes to make it work? I have also another method that do the opposite data from server updates the database on client side. I need help. There is no change in records of target database. I have saw some topics here at this forum. The problem is Connection to server database is ok, but when odAdapter.Update(ds,"<tablename>"); executes it returns value of 0 and on changes made to server database, it should have 12 rows. Advanced Thanks.

Code:

public int UpdateServerTable1(DataTable dtObj)
{
DataSet ds = new DataSet();
int iRes = 0;
try
{
this.sqlTextServer = "Select * From <tablename>";

OdbcDataAdapter odAdapter = new OdbcDataAdapter(this.sqlTextServer,this.odServerConn );

odAdapter.Fill(ds,"<tablename>");

ds.Merge(dtObj,true,MissingSchemaAction.Ignore);

OdbcCommandBuilder cmdBuild = new OdbcCommandBuilder(odAdapter);

iRes = odAdapter.Update(ds,"<tablename>");
}
catch (Exception ex)
{
ds.RejectChanges();
}
return iRes;
}

Regards,
denpsia

Hi,

Check the state of every row. Determine if the RowState of the rows in dtObj was changed to Unchanged. also try inspecting your UpdateCommand and InsertCommand see if the command builder generated the correct sql syntax...

cheers,

Paul June A. Domag

DataAdapter Wizard and rows not found

I am using MSDE and am trying to configure a dataAdapter. I generates a
couple of statements automatically, but I get an error message about a couple
of other statements. It says that I cannot generate those last statements
because it needs to associate a row with a table. If I am correct there are
now rows in MSDE only tables and columns. What can I do about configuring
MSDE so that that ado wizard can generate all the statements?
Spencer
hi Spencer,
Spencer H. Prue wrote:
> I am using MSDE and am trying to configure a dataAdapter. I generates
> a couple of statements automatically, but I get an error message
> about a couple of other statements. It says that I cannot generate
> those last statements because it needs to associate a row with a
> table. If I am correct there are now rows in MSDE only tables and
> columns. What can I do about configuring MSDE so that that ado wizard
> can generate all the statements?
I do think you missed something in the table mapping schema... and MSDE has
nothing to do with all that.. so I do think you'll have better luck asking
in the dotnet newsgroup hierarchy...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply