datasource wont update on delete

hi,

I have a dataadaptor which i use to fill a dataset. I can do this no problems. But if I delete a row from the dataset and update the adaptor again, nothing gets changed in my source database. Does anyone know why this is?

here's my code for my dataadaptor:
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("select * from tblExtRef", conn);
adapter.SelectCommand.Connection = conn;

SqlParameter parm;
SqlCommand cmd;
cmd = new SqlCommand("DELETE FROM tblextref WHERE extrefid = @.extrefid",conn);
parm = cmd.Parameters.Add("@.extrefid", SqlDbType.Int , 40, "extrefid");
parm.SourceColumn = "ExtRefid";
//parm.SourceVersion = DataRowVersion.Original;
//parm.Direction = ParameterDirection.InputOutput;
//SourceVersion = DataRowVersion.Original;
adapter.DeleteCommand = cmd;if you delete a row from the dataset/datatable, you just run the update on the adapter.

I don't think you need to create a delete command manually.

DataSet - Inserted row ID

I have a dataset that uses generated stored procedures to do its select, insert, update, delete.

I am inserting a row to that dataset, and after the update, using the ID of newly created row.

This worked just fine until I added triggers to some of the tables on my DB, and now, when I insert a row, the row's ID is not available after the update (it's 0)

Any idea what happened / what I have to do to fix this?

Thnx!

Hi,

The SQL Server uses SELECT SCOPE_IDENTITY() to get the last ID of the table. I'd like to know if you're using this to update your data.

Also, could you let me know what is the newly added trigger doing. It might be preventing the scope identity from returning.

Datagrid Delete

Hi I'm having a problem deleting rows from my datagrid. Basically I hit delete and a message box pops up and asks if Im sure I want to delete so I hit yes and then I get the following error -->Could not find stored procedure 'delete from SECTION_TBL where SECT_ID = @.SECT_ID'.

Is it my code thats wrong or is our test sql server that is the problem?

1<%@. Page Language="VB" EnableEventValidation="True" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>2<%@. import namespace="System" %>3<%@. import namespace="System.Data" %>4<%@. import namespace="System.Data.SqlClient" %>56<script language="VB" runat="server">78Dim sectionAs String9 Dim myconnectionAs SqlConnection10Dim mydaAs SqlDataAdapter11Dim dsAs DataSet1213Sub Page_Load(ByVal SourceAs Object,ByVal EAs EventArgs)14 BindData()15End Sub1617 Sub BindData()1819Dim strConnAs String ="server=fileserver; uid=xxx; pwd=xxx; database=NEW_CMS"20Dim sqlAs String ="Select * from SECTION_TBL"21 myconnection =New SqlConnection(strConn)22 myda =New SqlDataAdapter(sql, myconnection)23 ds =New DataSet24 myda.Fill(ds,"SECTION_TBL")25 sectList.DataSource = ds26 sectList.DataBind()2728End Sub2930 Private Sub sectList_ItemDataBound(ByVal senderAs Object,ByVal eAs DataGridItemEventArgs)Handles sectList.ItemDataBound3132Dim lAs LinkButton3334If e.Item.ItemType = ListItemType.ItemOr e.Item.ItemType = ListItemType.AlternatingItemThen35 l =CType(e.Item.Cells(0).FindControl("cmdDel"), LinkButton)36 l.Attributes.Add("onclick","return getconfirm();")37End If3839 End Sub4041 Sub sectList_DeleteCommand(ByVal sAs Object,ByVal eAs DataGridCommandEventArgs)4243Dim ConnectionStrAs String = ConfigurationManager.AppSettings("ConnStr")44Dim connAs SqlConnection45Dim cmdAs SqlCommand46Dim IdAs Integer4748 Id =CInt(e.Item.Cells(0).Text)49 conn =New SqlConnection("server=fileserver; uid=xxx; pwd=xxx; database=NEW_CMS")50 cmd =New SqlCommand("delete from SECTION_TBL where SECT_ID = @.SECT_ID", conn)51 cmd.CommandType = CommandType.StoredProcedure52 cmd.Parameters.Add("@.SECT_ID", SqlDbType.Int).Value = Id5354 cmd.Connection.Open()55 cmd.ExecuteNonQuery()56 cmd.Connection.Close()5758 DataBind()5960End Sub61626364</script>6566<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">6768<script language="javascript">6970function getconfirm()71{72if (confirm("Do you want to delete record?")==true)73return true;74else75return false;76}7778</script>7980<table cellpadding="2" cellspacing="2" width="760">81<tr>82<td>Sections</td>83</tr>84<tr>85<td>8687<asp:DataGrid OnDeleteCommand="sectList_DeleteCommand" ID="sectList" runat="server" DataKeyField="SECT_ID" AutoGenerateColumns="False">8889<Columns>9091<asp:BoundColumn DataField="SECT_ID" Visible="False" />9293<asp:HyperLinkColumn HeaderText="SECTION NAME" DataTextField="SECT_NAME" DataNavigateUrlField="SECT_ID" DataNavigateUrlFormatString="manageSection.aspx?SECT_ID={0}" />9495<asp:TemplateColumn>96<ItemTemplate>97<asp:LinkButton id="cmdDel" runat="server" Text="Delete" CommandName="Delete" CausesValidation="false" />98</ItemTemplate>99</asp:TemplateColumn>100101</Columns>102103</asp:DataGrid>104105</td>106</tr>107<tr>108<td></td>109</tr>110</table>111112</asp:Content>

Thanks in advance.

on line 51 you should use

CommandType.Text

|||

Brilliant - This works great

Thank you.

Databinding question

Hi,

I have a page created within VS 2005 which uses a detailsView with a SQLDataSource which has insert, edit and delete items allowed with it.

The problem is that if I delete a record I dont want to refresh the page as I want to set a label value to say item deleted. The problem then though is to select the item to delete I have a drop down which populates the details view on index change, but if I delete the item I cannot do a databind when its complete because it just binds to the existing dataset and does not do a fresh call on the database.

Is there a command I can run to refresh the dataset on click of the delete button?

Thanks

I think I would do databinding in a sub that does nothing else but the databinding. I normally do this in a sub called something like sub bindcontrols() or something like that. Then when I need to refresh the data I can just call that sub.|||

Thanks for your reply.

The problem is the databinding is handled by VS and I dont think I have a choice of where it runs.

|||try the following after deleting detailsView.databind() it should refresh the content Hope this helps

Database with empty name

I somehow managed to get a database object with an empty name into one of my instances of SQL Server. I can't delete it or otherwise work with it (I've tried renaming it so I could delete it) without getting an error message because of the empty name. I've tried these things (as well as a "Drop Database" query with no name, which I didn't expect to work and it didn't) from both the SQL 2000 and 2005 environments. Has anyone come across this before? I don't suppose this database is hurting anything but I'd still like to get rid of it. The objects in it make it appear to be a copy of the Master database - it also doesn't show up where I would expect it to in the Data folder for this instance.

Thanks,

Dave

Dave,

I never come across this problem. Whenever I have to drop or delete a database, I relatively get that done with ease. In case the database name is empty, which is very weird case, I will suggest you to try deleting using Enterprise manager. It would have been more easy to address your issue if you could specify what error message did you get when you tried renaming or using "Drop Database" query. But I am pretty sure you can delete your database using Enterprise Manager. However, you have to make sure before deleting your database that the database is not currently running. Most easy way to do that is to stop the Sql Server Service Manager from your task bar.

I hope it works.

Ujjwal Kaji

|||

Thanks for the reply. I tried deleting it in Enterprise Manager and get the same Error Message:

Error 21776: [SQL-DMO]The name '' was not found in the Databases collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.

Thanks for any insight into this.

-Dave

|||

Hi,

before messing with the system tables, try to do a:

1. DROP DATABASE []

2. sp_renamedb '','MyDatabasetoDelete'

or (posted by Tom Moreau)

3. (can vary to SQL server 2k5, because sysdatabases is sys.databases now)

sp_configure 'allow', 1
go
reconfigure with override
go
update sysdatabases
set
name = 'MyDB'
where
name = ''
go
sp_configure 'allow', 1
go
reconfigure with override
go

Stop and start SQL Server.

Check after each steps if the database is till existing, sometime SQl Server doesn′t know how to handle return code for this strange situation and gives back a weird error.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks for the reply. I guess somehow it was a system database because your 3rd suggestion worked perfectly. Very strange...

Thanks for your help.

Dave

|||Thank you for the post, option 3 solved my empty database name as well.

Database with empty name

I somehow managed to get a database object with an empty name into one of my instances of SQL Server. I can't delete it or otherwise work with it (I've tried renaming it so I could delete it) without getting an error message because of the empty name. I've tried these things (as well as a "Drop Database" query with no name, which I didn't expect to work and it didn't) from both the SQL 2000 and 2005 environments. Has anyone come across this before? I don't suppose this database is hurting anything but I'd still like to get rid of it. The objects in it make it appear to be a copy of the Master database - it also doesn't show up where I would expect it to in the Data folder for this instance.

Thanks,

Dave

Dave,

I never come across this problem. Whenever I have to drop or delete a database, I relatively get that done with ease. In case the database name is empty, which is very weird case, I will suggest you to try deleting using Enterprise manager. It would have been more easy to address your issue if you could specify what error message did you get when you tried renaming or using "Drop Database" query. But I am pretty sure you can delete your database using Enterprise Manager. However, you have to make sure before deleting your database that the database is not currently running. Most easy way to do that is to stop the Sql Server Service Manager from your task bar.

I hope it works.

Ujjwal Kaji

|||

Thanks for the reply. I tried deleting it in Enterprise Manager and get the same Error Message:

Error 21776: [SQL-DMO]The name '' was not found in the Databases collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.

Thanks for any insight into this.

-Dave

|||

Hi,

before messing with the system tables, try to do a:

1. DROP DATABASE []

2. sp_renamedb '','MyDatabasetoDelete'

or (posted by Tom Moreau)

3. (can vary to SQL server 2k5, because sysdatabases is sys.databases now)

sp_configure 'allow', 1
go
reconfigure with override
go
update sysdatabases
set
name = 'MyDB'
where
name = ''
go
sp_configure 'allow', 1
go
reconfigure with override
go

Stop and start SQL Server.

Check after each steps if the database is till existing, sometime SQl Server doesn′t know how to handle return code for this strange situation and gives back a weird error.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks for the reply. I guess somehow it was a system database because your 3rd suggestion worked perfectly. Very strange...

Thanks for your help.

Dave

|||Thank you for the post, option 3 solved my empty database name as well.

database user post extraction

please can anyone help me i have a ipb forum some how i accidently delete my post i have a old backup from 3 week ago how can i extract the post from the backup
NOTE: the only post that was deleted was post made by me none of the other member post was deleted
i did not want to restore the entire post table beacause i would loose post in the past 3 weeks
can anyone help me with a script or anything i can used to extract post of the post table where the poster id is 1
here is a sample

INSERT INTO `ibf_posts` VALUES (2, 0, NULL, 1, 'albertldee', 1, 1, '12.8.34.199'
INSERT INTO `ibf_posts` VALUES (3, 0, NULL, 4, '??-?', 1, 1, '207.44.154.35
INSERT INTO `ibf_posts` VALUES (4, 0, NULL, 6, 'demarcus', 1, 1, '69.142.131.149
INSERT INTO `ibf_posts` VALUES (5, 0, NULL, 7, 'gottasteu', 1, 1, '65.12.169.41

the post table has about 122000 line like above i just want to extract all the line that has albertldee like line number 1Try something like:
INSERT INTO ibf_posts
SELECT * FROM ibf_posts_backup
WHERE user_id = 'albertldee';
NOTE: Change user_id to the correct column name.

:D|||thanks but how do i set it up|||or can anyone tell me how can i setup a query to check a table and insert a line if it's not already exist|||Post a description of both the 'ibf_posts' and the backup table.
:confused:

Database user permission

Add a new user to a database and assign permission only to 2 tables in this database for select/update/insert/delete.

the user role is Public.

But when you login in this database with SQL Query Analyzer using this user account, in Object Browser (left side), this user can see all info same as dbo, such as table name, column, data types,... although this user can not access the data for others tables.

How to limit the this user view in Object Browser and only see the tables that the user have proper permission on?

With SQL Server 2000, you could NOT limit the user. They can 'see' all objects in the database.

With SQL Server 2005, the user can only see the objects in the schema(s) they have permissions for.

Are you using SQL 2000?

|||Thanks for reply. Yes, I use SQL Server 2000|||

Unfortunately, with SQL 2000, all users can see all database objects with the client tools.

There is nothing you can do about it -except upgrade to SQL 2005. With SQL 2005, you can keep things private and undisclosed.