Printable View
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!!
Are there any triggers on the table?
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.
no there is no trigger on the table!!
here is SP
Code: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
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
and here is .asxp fileCode: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 connection class where i used getAllcompanies method to get all recordsCode:<%@ 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>
Code: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
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:
Thanks.Code:sp_helptrigger [TableName]
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.)
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
It seems to me that you are deleting every record in the table here
Code: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.Quote:
Originally Posted by brucevde
Thanks.
No she hasn't. She said she can enter them via enterprise manager but they've dissapeared after she runs the Sproc.Quote:
But she says she can't enter records manually via Enterprise Manager
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.
Yep, that was the one:bigyello:Quote:
This seems to be what you were thinking of