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.

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

DataAdapter Update Problems

Hi, I am populating a datagrid with data from one sql table, I add additional columns and then wish to write it back out to another table. I have written the following code to acheive this, however it keeps throwing the follwing exception - "Update requites a valid UpdateComman when passed a DataRow collection with modified rows"

I've looked and I can see what the problem is, can anybody help please?

Private Sub FillData()'SQL connectionMe.sqlconnection =New System.Data.SqlClient.SqlConnectionMe.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")Dim sqlAs String If Request("action") ="gen"Then sql ="select item from tblPickList where GroupName ='xxx'AND Category = '" & Category2 & "' ORDER BY item ASC" ElseIf Request("action") = "fav" Then sql = "select identifiertext as item from tblfavourites where HRID =" & Session("hrid") & " and type = 3 ORDER BY identifiertext ASC" ElseIf Request("action") = "spec" Then sql = "select item from tblPickList where GroupName ='" & GroupID & "' AND Category = '" & Category2 & "' ORDER BY item ASC"End If ds =New DataSet da =New SqlClient.SqlDataAdapter(sql, sqlconnection)Try sqlconnection.Open() da.Fill(ds,"tblPickListTemp") sqlconnection.Close()Catch exAs Exception sqlconnection.Close()End Try ds.Tables(0).Columns.Add("HRID") ds.Tables(0).Columns.Add("Flag")'may possibly add a favourites column here at a later date to ensure no duplicationDim drAs DataRowFor Each drIn ds.Tables("tblPickListTemp").Rows dr("HRID") = Session("hrid") dr("Flag") = 0Next DgrdFavourites.DataSource = ds DgrdFavourites.DataBind()End Sub Public Sub CreateTempRecords()'SQL connectionMe.sqlconnection =New System.Data.SqlClient.SqlConnectionMe.sqlcommand =New System.Data.SqlClient.SqlCommandMe.da =New SqlClient.SqlDataAdapterMe.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")Dim sqlAs String sql ="Insert into tblPickListTemp (Item, HRID, Flag) values (@.Item, @.HRID, @.Flag)" sqlcommand.CommandText = sql sqlcommand.Connection = sqlconnection sqlcommand.Parameters.Add("@.Item","Item") sqlcommand.Parameters.Add("@.HRID","HRID") sqlcommand.Parameters.Add("@.Flag","Flag") da.InsertCommand = sqlcommand da.Update(ds.Tables(0))End Sub

Hello Naimh,

Your program retrieves records from the database, you modify them and try to update the records in the database.

In that case you need to add an UpdateCommand to your SqlDataAdapter.

The Update statement will than handle new rows and modified rows.

Jeroen Molenaar.

|||I thought that was what I was doing with the da.InsertCommand on the 2nd to last line.I'm not sure I've really got to grips with the dataadapter syntax, could you show me please?|||

The InsertCommand you use is correct. But you are not adding new rows to the table, so you need also the UpdateCommand.

sql = "Update tblPickList Set HRID = @.HRID, Flag = @.Flag where Item = @.Item"

// Add the parameters here ...

sqlcommand.CommandText = sql

da.UpdateCommand = sqlcommand

// etc.

Hope you get it working now.

Jeroen Molenaar.

|||

jeroenm:

The InsertCommand you use is correct. But you are not adding new rows to the table, so you need also the UpdateCommand.

Okay really confused now, as that is exactly what I want to do, I populate the dataset from one table, manipulate it in the dataset and then Insert entirely new row's into a completely different table, erk! I shall give your suggestion a try but I have to fess up to being none the wiser.|||

jeroenm:

sql?= "Update tblPickListTemp Set HRID = @.HRID, Flag = @.Flag where Item = @.Item"

See now this is never going to work for me, tblPickListTemp ( the table I want to insert rows into) at the start of this process is empty, I cannot update and set fields to new values when no records currently exist.Ah well back to the drawing board, thanks anyway.|||

Do you have an AcceptChanges statement somewhere before the data is saved to the database?

When you modify the dataset after AcceptChanges, the tableadapter thinks it needs an Update statement in stead of an Insert statement.

Jeroen Molenaar.

|||No I am afraid I don't, all the code is in the snippet above. It's most confusing. I have also ensured that the primary key is set in the recipient table, which is another common problem which throws this error. I am at a real loss and almost at the point of scrapping the whole thing and taking a different approach, it's just that if I could have got this to work it would have been less trips to the Db and a lot less spaghetti code.|||

You can add some ASP.NET code. Perhaps this gives another clue.

|||

<form id="Form1" method="post" runat="server"><TABLE id="Table2" cellSpacing="0" cellPadding="0" width="280" align="center" border="0"><TR height="24"><TD class="fntmed" style="WIDTH: 280px" background="../images/header.jpg">         PickList</TD></TR><TR><TD class="fntnorm" style="WIDTH: 280px; HEIGHT: 36px"><IMG height="6" src="../images/6pixspacer.gif"><br><asp:image id="imgLine" runat="server" Width="260px" ImageUrl="../Images/line.jpg"></asp:image></TD></TR><TR><TD><div style="OVERFLOW: auto; WIDTH: 270px; POSITION: static; HEIGHT: 190px"><asp:DataGrid id="DgrdFavourites" autogeneratecolumns="False" BackColor="#ffffff" showheader="False"OnItemCommand="DgrdFavourites_ItemCommand" runat="server" Width="256px" Height="25px" BorderWidth="0"CssClass="fntsmall" AlternatingItemStyle-CssClass="fntsmall"><AlternatingItemStyle BackColor="#e2eaee" /><Columns><asp:BoundColumn DataField="Item" /><asp:templatecolumn ItemStyle-HorizontalAlign="Right"><ItemTemplate><asp:button CssClass="buttonlike" Text="Select" commandname="Select" runat="server" ID="btnSelect" /></ItemTemplate></asp:templatecolumn><asp:templatecolumn ItemStyle-HorizontalAlign="Right"><ItemTemplate><asp:button CssClass="buttonlike" Text="Favourite" runat="server" commandname="Add" ID="btnAdd" /></ItemTemplate></asp:templatecolumn></Columns></asp:DataGrid></div></TD></TR></TABLE><asp:Label ID="oops" Runat="server"></asp:Label></form></body>

That the entirety of the front end code, basically at page load inside a "If not Is Postback" the Filldata() method is invoked followed by the CreateTempData() method. Not a lot to it really.|||

Hello Naimh,

Sorry, but I've been moving you into the wrong direction. My first idea was correct, but I didn't read your code properly.

When you call FillData the dataset ds is filled from the table tblPickList. In FillData you add 2 columns and modify all rows with the session HRID. At this stage your rows have the status Modified in the dataset ds. (This generates the error message when calling da.Update())

Before "DgrdFavourites.DataSource = ds" you can enter the command ds.AcceptChanges(). All modifications are accepted and no update statement will be generated by the dataadapter.

The second thing is make some changes to CreateTempRecords. Create a new dataset dstemp as a copy of ds. After that insert the rows from ds into the new dataset dstemp, for example with a foreach construction. Now the new dataset is ready for the insert statement that you have written in CreateTempRecords(). Change da.Update(ds.Tables(0)) into da.Update(dstemp.Tables(0)).

Now your code should do the job!

Jeroen Molenaar.

DataAdapter does NOT load relations from DB

Why DataAdapter does NOT load relations from DB ?

the relations are made using SQL SERVER MANAGEMENT

but when a fill a datatable using dataadapter the relations are not loaded .

why ? what is the solution to this problem ?

Because it doesn't, nor would it be appropriate for it to. Your code may want to set up different relationships than those enforced by the database. Or setup up no relationships. If you want the relationships in your code, then you'll need to add them yourself.

DataAdapter always returns 0 or empty rows

Good day,

I just like to ask if anybody has experienced getting empty rows from SQL data adapter? I'm using SQL Server 2005. Problem is when the sql is run on Query Analyzer it retrieves a number of rows but when used in my application it returns 0 or empty rows.

I thought the connection is not the problem since I got my columns right. Below is my code snippet.

Thanks!

conststring COMMAND_TEXT =@."select distinct somefield as matchcode, count(somefield) "

+

"as recordcount from filteredaccount where StateCode = 0 group by somefield having count(somefield) > 1";SqlDataAdapter adapter =newSqlDataAdapter(COMMAND_TEXT, connection);DataTable dt =newDataTable(sometablename);

adapter.Fill(dt);

Hi,

I might be wrong, but don't you need a 'Return dt;' at the end of all this?

Hope this helps.

Paul

|||

You can not write aggregate function like count in where condition. You need to use subquery.

conststring COMMAND_TEXT =@."select distinct matchcode,recordcount from
(select distinct somefield as matchcode, count(somefield)as recordcount from filteredaccount where StateCode = 0 group by somefield ) As mytbl
whererecordcount > 1

|||Thanks for the replies. I will try your recommended SQL update, my question now is why my SQL is returning rows when run on Query Analyzer but not on the web application?|||

Hi,

The reason is that in your code you are returning nothing! Personally, I didn't find anything wrong with the original query, it worked fine in my tests. I was using an ObjectDataSource calling an object and my function was pretty much identical to yours - except it had a Return statement and mine is in VB rather than C#. However, the code is:

' Initialise the DataTableDim dadAs New SqlDataAdapter("SELECT DISTINCT LastName AS Matchcode, COUNT(Lastname) AS RecordCount " _&"FROM Contacts GROUP BY LastName HAVING COUNT(LastName)>1", _conString)' Create a DataTableDim dtAs New DataTable()' Populate the DataTabledad.Fill(dt)' Return resultsReturn dt

This worked without any issues on my system. By the way, the function returns a DataTable (i.e. Function nameOfFunction() As DataTable).

Hope this helps.

Paul

|||

Hi Paul,

I do return the datatable I just didn't put it there, sorry about that. I put logs after the adapter.fill and checked columns.count and rows.count.

columns were ok - 2 columns but rows.count is always zero.

I'm wondering if anybody has experienced the same, maybe a bug/ setting in SQL server 2005 or ADO.NET that I can check?

Thanks.

|||

Hi,

If I'm understanding correctly, the problem isn't the returning of your data table, but is in giving you a rows.count value? If so, I've just tried it on my code without any ptoblems. The adapted code looks like this:

' Initialise the DataTableDim dadAs New SqlDataAdapter("SELECT DISTINCT LastName AS Matchcode, COUNT(Lastname) AS RecordCount " _ &"FROM Contacts GROUP BY LastName HAVING COUNT(LastName)>1", _conString)' Create a DataTableDim dtAs New DataTable()' Populate the DataTable dad.Fill(dt)' Log result & Return log.Write("Columns & Rows","Test Grid","Columns= " & dt.Columns.Count.ToString &" Rows= " & dt.Rows.Count.ToString, 0,"Test","test")Return dt

If you can't get this to work (I'm sure your logging code won't be the same as mine, but hopefully you get the idea), you could try writing to your log file from the OnDataBound event. Strangely, if you have AutoGenerateColumns set to true, the column count returns zero here, but the row count is OK. This problem is fixed by ensuring you have set out the columns in your GridView.

I hope this (at last!) helps sort your problem. If not, I suggest you post all of the relevant code.

Cheers,

Paul

|||

Thanks Paul. The problem is my datatable when filled always has zero rows. While the SQL query when run on Query Analyzer returns a number of rows.

I'm sure that there's nothing wrong with my logging.

Anyway, I kind of implemented a workaround, my SQL selects from a view.. what I did was to select directly from the table instead. Hence, i believe my SQL view is somewhat corrupt or problematic.

Thanks again for the replies.

What bothers me though is ADO.NET does not fire an exception or anything... it just does not fills the datatable...

|||

Hi,

Do you mean that you can't get the return with a number of rows by selecting from the view in QA ? Try to select from the table directly and fill your datatable with the DataAdapter , then check if you can get a number of rows. If you can, the problem should be caused by the view you use. So what you should do is to rebuild the view and check the selecting in QA first. My personal view is that there should not be anything wrong with your DataAdapter or DataTable. Thanks.

|||

Yes that's what I did I select directly from the table rather than from the view. It is working fine now.

My SQL select from view run on Query Analyzer works fine returns a number of rows

BUT my SQL select from view run on my application always returns 0 rows.

What's strange is that I didn't get any errors when filling up my datatable, rows are just zeroed out.

|||

Hi,

i. You may open your SQL Profiler and start a trace. In the meantime, pls run your application and show us the textdata in the profiler.

ii. Pls show us the whole code and including your view in your database.

Thanks.

|||

Thanks Michael appreciate your suggestions... below is the whole method I'll try the SQL Profiler... what filter should I use to catch the sql statement?

public

DataTable RetrieveAllGroups(int entityTypeCode)

{

const

string COMMAND_TEXT =@."select distinct somefield as matchcode, count(somefield) "

+

"as recordcount from filteredaccount where StateCode = 0 group by somefield having count(somefield) > 1 order by recordcount desc, matchcode asc";

SqlConnection connection = GetDataConnection();

if

(connection !=null)

{

SqlDataAdapter adapter =new SqlDataAdapter(COMMAND_TEXT, connection);

DataTable dt =

new DataTable(entityTableName);

adapter.Fill(dt);

return dt;

}

}

DataAdapter - SELECT Statement - items in last 30 days

I'm using DataList to return vales stored in an SQL database, of which one of the fields contains the date the record was added.

I am trying to fill the dataset with items only from the last 30 days.
I've tried a few different ways, but all the database rows are returned.

What is the WHERE clause I sholud use to do this??

ThanksTry with the following SQL statement, i belive it should work.

select * from <tablename> where datediff(day, <columnname>, getdate()) < 30

Hope it solves your issue.|||Thanks very much, it worked a treat