DataSet rows being deleted, but after the update , the sql database is not updated. The de

Stepping thru the code with the debugger shows the dataset rows being deleted.

After executing the code, and getting to the page presentation. Then I stop the debug and start the

page creation process again ( Page_Load ). The database still has the original deleted dataset rows.

Adding rows works, then updating works fine, but deleting rows, does not seem to work.

The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.

cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId);

rowCountDb = cDependChildDs._ClientDependentChild.Count;

for (row = 0; row < rowCountDb; row++)
{

dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0];
dr_dependentChild.Delete();


//cDependChildDs._ClientDependentChild.Rows.RemoveAt(0);

//cDependChildDs._ClientDependentChild.Rows.Remove(0);
/* update the Client Process Table Adapter*/
// cDependChildTA.Update(cDependChildDs._ClientDependentChild);
// cDependChildTA.Update(cDependChildDs._ClientDependentChild);

}

/* zero rows in the DataSet at this point */

/* update the Child Table Adapter */
cDependChildTA.Update(cDependChildDs._ClientDependentChild);

Hi,

You should use AcceptChanges method after using delete in order to update the data. The following link may be helpful to you.

http://msdn2.microsoft.com/en-us/library/ms233823(VS.80).aspx

Thanks.

Dataset Data is not the same as Database Data

Hi,

Thanks in advance. I have a problem in my dataset. Today morning we updated some of the descriptions to our parameter tables. But these are not getting updated to our reports. I checked the dataset. Even that shows the same values. How do I go about this? Please help. Thank You.

Regards,

Das.

You might have turned on caching on the reports. Reports execution can be cached, which is mostly done in environments where data retrieval would take a tremendous time comparing to the benefit of having realtime / cached data. Look in the properties of the report. Caching of the report should be disabled, otherwise the reportserver will take a copy from the Report Server Temp database to render the report.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Thanks for the reply. I tried to find how to disable the cache in report and dataset properties. But I couldn't find any option. Could you please tell me where can I find the option? Thanks for your help. Bye.

Regards,

Das.

DataGrid updation problem !

Hello all
i am trying to update the database with the DataSet (Updated from the DataGrid) but it is giving the error as
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."
the code is as follows

public DataSet bindControlToGrid(DataSet dataSet)
{
SqlConnection objConn= null;
SqlCommand objComm = null;
try
{
objConn = new SqlConnection(@."User ID=sa;Password=sa;database=ExtraNet;server=EAGLES\EAGLES");
SqlDataAdapter objAdapter=new SqlDataAdapter();
objAdapter.SelectCommand= new SqlCommand("SELECT sUserID,sUserName FROM tblUser WHERE dUserDeActivated IS NULL ORDER BY sUserName,nUsrSerialNo",objConn);
SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(objAdapter);
objConn.Open();
DataSet objdataSet= new DataSet("ko");
string d = (dataSet.Tables[0].Rows[0].ItemArray.GetValue(0).ToString());
objAdapter.Fill(objdataSet);
objAdapter.Update(dataSet);
objAdapter.Dispose();
}
catch(Exception ex)
{
string s= ex.Message;
}
finally
{
//objComm.Dispose();
objConn.Close();
}
return dataSet;
}

kindly helpGot The Answere

sUserID was not declared as primary ker

Thanks all

Database Version 539 vs. 611 (using SMO)

Hi
The number you are referring to is the internal version number of the
internal database schema. It generally gets updated between major releases.
To see this in action, attach or restore a SQL Server 2000 DB in 2005 using
T-SQL code in Management Studio. You will notice towards the end of the
operation, multiple messages showing the DB being upgraded one revision at a
time to the latest release.
There is no guarantee that between hotfixes or service packs the DB is not
upgraded. 611 is SQL Server 2005 RTM.
Querying the server for it's version number is more reliable.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eH3R7kXlGHA.3816@.TK2MSFTNGP02.phx.gbl...
>I am using SMO in my .NET application to determine the version of a
>database (2000 or 2005). When I get the version of a SQL 2000 database, it
>returns 539. When I get the version of a SQL 2005 database, it returns 611.
> My question is this. Can I safely assume that a value of 539 always means
> the database is a SQL 2000 database, and can I safely assume that a value
> of 611 always means a SQL 2005 database?
> Also, does anyone know what '539' and '611' refer to?
> Thanks, Amos.
>Hi,
wait a minute, you queried for the wrong property. Guess you queried
for something like the Build Number. Use the following peroperty on SMO
to get the information:
Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
SQL2k5
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||I am using SMO in my .NET application to determine the version of a database
(2000 or 2005). When I get the version of a SQL 2000 database, it returns
539. When I get the version of a SQL 2005 database, it returns 611.
My question is this. Can I safely assume that a value of 539 always means
the database is a SQL 2000 database, and can I safely assume that a value of
611 always means a SQL 2005 database?
Also, does anyone know what '539' and '611' refer to?
Thanks, Amos.|||Hi
The number you are referring to is the internal version number of the
internal database schema. It generally gets updated between major releases.
To see this in action, attach or restore a SQL Server 2000 DB in 2005 using
T-SQL code in Management Studio. You will notice towards the end of the
operation, multiple messages showing the DB being upgraded one revision at a
time to the latest release.
There is no guarantee that between hotfixes or service packs the DB is not
upgraded. 611 is SQL Server 2005 RTM.
Querying the server for it's version number is more reliable.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eH3R7kXlGHA.3816@.TK2MSFTNGP02.phx.gbl...
>I am using SMO in my .NET application to determine the version of a
>database (2000 or 2005). When I get the version of a SQL 2000 database, it
>returns 539. When I get the version of a SQL 2005 database, it returns 611.
> My question is this. Can I safely assume that a value of 539 always means
> the database is a SQL 2000 database, and can I safely assume that a value
> of 611 always means a SQL 2005 database?
> Also, does anyone know what '539' and '611' refer to?
> Thanks, Amos.
>|||Hi,
wait a minute, you queried for the wrong property. Guess you queried
for something like the Build Number. Use the following peroperty on SMO
to get the information:
Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
SQL2k5
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||Jens,
Fantastic! Thanks very much.
Amos.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1150928369.273988.134490@.g10g2000cwb.googlegroups.com...
> Hi,
> wait a minute, you queried for the wrong property. Guess you queried
> for something like the Build Number. Use the following peroperty on SMO
> to get the information:
> Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
> SQL2k5
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Jens,
Fantastic! Thanks very much.
Amos.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1150928369.273988.134490@.g10g2000cwb.googlegroups.com...
> Hi,
> wait a minute, you queried for the wrong property. Guess you queried
> for something like the Build Number. Use the following peroperty on SMO
> to get the information:
> Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
> SQL2k5
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Database UPDATE problem

Just a question. Im really

wondering why my sample database program wont update. It will say

that it has updated the actual database but when i double check, it

didnt. I am not using the command builder coz i want my own sql

codes. When I test using the query builder, i will see the data being

updated or added but when i use my own program, it really wont.

Actually i have this one sample database program that updates ok. I

just dont know why I cant do it again. Im really pissed off thinking

abt it the whole night.

I ommitted the INSERT and

DELETE commands since they dont work too. here is my program. PLS

HELP.

using

System;

using

System.Collections.Generic;

using

System.ComponentModel;

using

System.Data;

using

System.Data.SqlClient;

using

System.Drawing;

using

System.Text;

using

System.Windows.Forms;

namespace

test3_database

{

public

partial class

Form1 : Form

{

public

Form1()

{

InitializeComponent();

}

private

void Form1_Load(object

sender, EventArgs e)

{

da_conn.Fill(ds_addressbook1, "book1");

}

private

void

book1BindingNavigatorSaveItem_Click(object

sender, EventArgs e)

{

SqlCommand

sql_UPDATE = new SqlCommand(

"UPDATE

book1 SET "

+ "Name

= @.txtName, "

+ "Mobile

= @.txtMobile, "

+

"Landline = @.txtLandline, "

+ "Note

= @.txtNote"

+ "WHERE

"

+ "(Name

= @.OldName) AND "

+ "(Mobile

= @.OldMobile) AND "

+

"(Landline = @.OldLandline) AND "

+ "(Note

= @.OldNote)");

//

assigning connection

sql_UPDATE.Connection

= conn;

//

UPDATE parameters

sql_UPDATE.Parameters.Add("@.txtName",

SqlDbType.NChar, 30, "Name");

sql_UPDATE.Parameters.Add("@.txtMobile",

SqlDbType.NChar, 12, "Mobile");

sql_UPDATE.Parameters.Add("@.txtLandline",

SqlDbType.NChar, 12, "Landline");

sql_UPDATE.Parameters.Add("@.txtNote",

SqlDbType.NChar, 200, "Note");

sql_UPDATE.Parameters.Add("@.OldName",

SqlDbType.NChar, 30,

"Name").SourceVersion =

DataRowVersion.Original;

sql_UPDATE.Parameters.Add("@.OldMobile",

SqlDbType.NChar, 12,

"Mobile").SourceVersion =

DataRowVersion.Original;

sql_UPDATE.Parameters.Add("@.OldLandline",

SqlDbType.NChar, 12,

"Landline").SourceVersion =

DataRowVersion.Original;

sql_UPDATE.Parameters.Add("@.OldNote",

SqlDbType.NChar, 200,

"Note").SourceVersion =

DataRowVersion.Original;

//

assigning UPDATE command

da_conn.UpdateCommand

= sql_UPDATE;

//

update attempt

try

{

da_conn.Update(ds_addressbook1);

MessageBox.Show("update

ok!");

}

catch

{

MessageBox.Show("update

ERROR!");

}

}

}

}

what exactly is your result, a messagebox that displays "update ERROR!"? You should add error handling to capture the error returned by sql server.|||i get "update ok!" message. and i wonder why because when u check the databse it is not updated.

thanks for the reply |||

Run profiler or debug to see what Update statement is fired againt the server, perhaps you have some condition why didn′t sort out the specified rows you wanted to update. It could be that 0 rows are being returned /getting updated but the Update is still valid and executed. Perhaps you have the chance to check the sql_update.Parameters["@.RowCount"].Value as well ?!

HTH, Jens Suessmeyer.

|||i set no condition to my proram. ill try your suggestion. thanks so much

how do u run that profile? im sorry. im very new to visual studio and sqlserver. im just a student.|||

Look in this post,there are some walkthroughs:

http://groups.google.de/group/microsoft.public.sqlserver.tools/browse_frm/thread/cceb1ebec48370b

HTH, JensSuessmeyer.

|||SQL Express does not have Profiler. any alternative?

thanks.|||first i thank God for what happened last night.

second to all of

you guys, thank you for all your inputs. i found the bug. it was

hiding. hehehe. all your inputs have helped me finding it.

again, THANK YOU GUYS:

cgraus

JocularJoe

Jay Hickerson MS

KraGiE

Greg Yvkoff

Jens Suessmeyer

Database trigger to run managed C# code

Hi there,

Values in my database need to updated periodically. The code, upon starting the application, queries the database and stores the values in the Application collection. This is to avoid making a database call everytime the values are needed (increases performance). The drawback is that changes to the database values are not updated in the code.

How can I create a database trigger that will update the C# Application colllection whenever a table value is updated?

The following article gave me some good insight on how to integrate the .NET CLR with SQL Server 2005.

http://msdn2.microsoft.com/en-us/library/ms345136.aspx

I can now create triggers to run managed C# code. However, the CLR is limited because I cannot reference the System.Web.HttpApplicationState object or anything to do with HTTP requests; so I can't update my Application collection.

However, the CLR does let me access System.Web.Services. So I should be able to make a web service call to update the variables. More to come on this....

|||

I solved my issue. Seems like I was going down the wrong path. The SqlCacheDependency class is what I needed. I can set up a cache dependency on a SQL table so that when values in that table change the associated cache value will be invalid.

Here's a good article on SqlCacheDependency ->http://www.ondotnet.com/pub/a/dotnet/2005/01/17/sqlcachedependency.html?page=last

This increases performance significantly! Awesome!