[RESOLVED] Record Deleted automatically from sql server 2000

[ERUM]

I have strange kind of problem with my sql server.i entered some records(manually) via an Enterprise manager .

but as soon as i closed the table and execute stored procedure to get all records from table it does not show me any record .and all things vanish from there



any help!!

[RSINGH]

Are there any triggers on the table?

[FunkyDexter]

Add the record again.
Right Click on the table in object explorer and click 'open table' (if you used this window to insert the record close it and reopen it)
Is your record there?

If it isn't then RSINGH is probably right - check for triggers and constraints on the table (constraints should be unneccessary as it would refuse the insert if one was broken but check anyway to be sure).
If it is there then post your sproc. We'll need to see your code before we've got any hope of identifying the problem.

[ERUM]

no there is no trigger on the table!!


here is SP

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER  procedure getAllcompanies

as select * from records


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[ERUM]

i have just find out one more thing in my code(asp.net) that when ever i bind the grid with records from database it deleted at that time (if i test my application couple of time)!!
in my humble opinion i think i should provide u all stuff involve in the code level

Public Class DisplayRecords
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents dg2 As System.Web.UI.WebControls.DataGrid

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Dim cn As New Connection


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not Page.IsPostBack Then
            Me.dg2.DataSource = cn.getAllCompanies()
            Me.dg2.DataBind()

        End If
        
    End Sub

    Protected Sub dg2_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dg2.ItemCommand
        'Dim iid As Integer = Convert.ToInt32(e.Item.Cells(0).Text) 'Grab the ID from the hidden column   
        'cn.DeleteCompanyByS_NO(iid)

        'Me.dg2.DataSource = cn.getAllCompanies()
        'Me.dg2.DataBind()
        'Response.Write(iid)

    End Sub


    Protected Sub dg2_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dg2.ItemDataBound
        Dim l As LinkButton
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
            l = CType(e.Item.Cells(4).FindControl("cmdDel"), LinkButton)
            l.Attributes.Add("onclick", "return confirm('Are you sure you want to delete this item?');")
            Dim iid As Integer = Convert.ToInt32(e.Item.Cells(0).Text) 'Grab the ID from the hidden column   
            cn.DeleteCompanyByS_NO(iid)

            'ds = 
            Me.dg2.DataSource = cn.getAllCompanies()
            Me.dg2.DataBind()
            Response.Write(iid)

        End If
    End Sub

    'Sub dg2_PageChanger(ByVal Source As Object, _
    'ByVal E As DataGridPageChangedEventArgs)
    '    ' Set the CurrentPageIndex before binding the grid 
    '    dg2.CurrentPageIndex = E.NewPageIndex
    '    Me.dg2.DataSource = cn.getAllCompanies()
    '    Me.dg2.DataBind()
    'End Sub

  
    Protected Sub dg2_ItemCreated(ByVal sender As Object, ByVal e As DataGridItemEventArgs)
        Dim l As LinkButton
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
            l = CType(e.Item.Cells(4).FindControl("cmdDel"), LinkButton)
            l.Attributes.Add("onclick", "return confirm('Are you sure you want to delete this item?');")

        End If
        'If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
        '    Dim lb As LinkButton = TryCast(e.Item.FindControl("lbDelete"), LinkButton)


        '    lb.OnClientClick = "if(!confirm('are you sure to delete this item?')){return false;}"
        'End If
    End Sub


End Class

and here is .asxp file

<%@ Register TagPrefix="uc1" TagName="Header" Src="Header.ascx" %>
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="DisplayRecords.aspx.vb" Inherits="DATA_ENTRY_FORM_DEVELOPMENT.DisplayRecords"%>
<%@ Register TagPrefix="uc1" TagName="NavigationPanel" Src="NavigationPanel.ascx" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
	<HEAD>
		<title>DispalyRecords</title>
		<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
		<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
		<meta name="vs_defaultClientScript" content="JavaScript">
		<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
	</HEAD>
	<body MS_POSITIONING="GridLayout">
		<form id="Form1" method="post" runat="server">
			<TABLE align="left" id="Table1" cellSpacing="0" cellPadding="1" width="800" border="0">
				<TR>
					<TD align="center" colspan="2">
						<uc1:Header id="Header1" runat="server"></uc1:Header></TD>
				</TR>
				<TR>
					<TD style="WIDTH: 112px; HEIGHT: 27px">
						<uc1:NavigationPanel id="NavigationPanel1" runat="server"></uc1:NavigationPanel></TD>
					<TD style="HEIGHT: 27px">
						<asp:DataGrid runat="server" ID="dg2" DataKeyField="S_NO" AutoGenerateColumns="false" BorderWidth="1px"
							BorderStyle="None" BorderColor="#E7E7FF" AllowPaging="true" PagerStyle-Mode="NumericPages"
							PageSize="1" Width="674px"   OnItemCommand=dg2_ItemCommand>
							<Columns>
								<asp:BoundColumn DataField="s_no" ReadOnly="true" HeaderText="S.No" HeaderStyle-Width="10" HeaderStyle-Font-Bold="true"></asp:BoundColumn>
								<asp:BoundColumn DataField="comp_name" HeaderText="CompanyName" HeaderStyle-Font-Bold="True"></asp:BoundColumn>
								<asp:BoundColumn DataField="URL" HeaderText="URL" HeaderStyle-Font-Bold="True"></asp:BoundColumn>
								<asp:BoundColumn DataField="Email" HeaderText="Email" HeaderStyle-Font-Bold="True"></asp:BoundColumn>
								<asp:TemplateColumn HeaderText="Picture" HeaderStyle-Font-Bold="True">
									<ItemTemplate>
										<asp:Image runat="server" ID="img1" />
									</ItemTemplate>
								</asp:TemplateColumn>
								<asp:TemplateColumn HeaderText=Delete>              
<HeaderStyle Width="75px" />              
<ItemStyle HorizontalAlign="Center"/>              
<ItemTemplate>
 <asp:linkbutton  CausesValidation="false"  id="cmdDel" runat="server">Delete</asp:linkbutton>             
</ItemTemplate>     
</asp:TemplateColumn>

							
								<asp:TemplateColumn HeaderText="Edit" HeaderStyle-Font-Bold="True">
									<ItemTemplate>
										<a href='Edit.aspx?s_no=<%# DataBinder.Eval(Container.DataItem, "s_no" )%>'>Edit</a>
									</ItemTemplate>
								</asp:TemplateColumn>
							</Columns>
						</asp:DataGrid></TD>
				</TR>
				<TR>
					<TD style="WIDTH: 112px"></TD>
					<TD></TD>
				</TR>
			</TABLE>
		</form>
	</body>
</HTML>

and here is connection class where i used getAllcompanies method to get all records

Imports System.Data.SqlClient
Imports System.data
Imports Microsoft.VisualBasic
Imports System.IO

Public Class Connection
    Dim strConnString1 As String
    Dim cn1 As SqlConnection

    Function getAllCompanies() As DataSet
        Dim strConnString As String

        Dim cn As SqlConnection
        cn = New SqlConnection
        strConnString += "Password=" + System.Configuration.ConfigurationSettings.AppSettings("Password") + ";"
        strConnString += "Persist Security Info=" + System.Configuration.ConfigurationSettings.AppSettings("Persist Security Info") + ";"
        strConnString += "User Id=" + System.Configuration.ConfigurationSettings.AppSettings("User ID") + ";"
        strConnString += "Initial Catalog=" + System.Configuration.ConfigurationSettings.AppSettings("Initial Catalog") + ";"
        strConnString += "Data Source=" + System.Configuration.ConfigurationSettings.AppSettings("Data Source") + ""
        cn.ConnectionString = strConnString
        cn.Open()
        Dim cmd As SqlCommand = cn.CreateCommand()
        Dim ds As New DataSet
        Dim adpt As New SqlDataAdapter(cmd)
        With cmd
            .CommandText = "getAllcompanies"
            .CommandType = CommandType.StoredProcedure
            adpt.Fill(ds, "Records")
        End With
        'dim 
        'Dim rdr As SqlDataReader = cmd.ExecuteReader() 
        'Return rdr
        getAllCompanies = ds
    End Function

end Class

[usamaalam]

If you can't enter record via Enterprise Manager, then no need to check your ASP code. There must be a trigger or constraint. You can check trigger like this:

sp_helptrigger [TableName]

Thanks.

[FunkyDexter]

Just a thought, VS can be set to take a copy of a database when you run your code in debug mode. The idea is that, this way, you can work over your production database without actually affecting it (because you're actually affecting the copy).

The side affect is that, when you run your code a second time, a new copy is taken so none of the records you entered first time will be retained. It sounds like that may be your problem. You can change that setting so it persists the data but I'm afraid I can't remember what the setting is (I looked for an old post where this was discussed but can't find it.)

[si_the_geek]

This seems to be what you were thinking of (link from jmc's signature):
http://msdn.microsoft.com/en-us/libr...89(VS.80).aspx

[brucevde]

It seems to me that you are deleting every record in the table here

    Protected Sub dg2_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dg2.ItemDataBound
        Dim l As LinkButton
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
            l = CType(e.Item.Cells(4).FindControl("cmdDel"), LinkButton)
            l.Attributes.Add("onclick", "return confirm('Are you sure you want to delete this item?');")
            Dim iid As Integer = Convert.ToInt32(e.Item.Cells(0).Text) 'Grab the ID from the hidden column   
            cn.DeleteCompanyByS_NO(iid)

            'ds = 
            Me.dg2.DataSource = cn.getAllCompanies()
            Me.dg2.DataBind()
            Response.Write(iid)

        End If
    End Sub

[usamaalam]

It seems to me that you are deleting every record in the table here

    Protected Sub dg2_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dg2.ItemDataBound
        Dim l As LinkButton
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
            l = CType(e.Item.Cells(4).FindControl("cmdDel"), LinkButton)
            l.Attributes.Add("onclick", "return confirm('Are you sure you want to delete this item?');")
            Dim iid As Integer = Convert.ToInt32(e.Item.Cells(0).Text) 'Grab the ID from the hidden column   
            cn.DeleteCompanyByS_NO(iid)

            'ds = 
            Me.dg2.DataSource = cn.getAllCompanies()
            Me.dg2.DataBind()
            Response.Write(iid)

        End If
    End Sub

But she says she can't enter records manually via Enterprise Manager so I think the problem must be somewhere on the database level.

Thanks.

[FunkyDexter]

But she says she can't enter records manually via Enterprise Manager

No she hasn't. She said she can enter them via enterprise manager but they've dissapeared after she runs the Sproc.

Erum, are you running the sproc directly from enterprise manager or are you running it from your ASP? If the latter then Bruce has almost certainly identified your problem.

This seems to be what you were thinking of

Yep, that was the one