p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 3.5 Professionals (http://p2p.wrox.com/forumdisplay.php?f=352)
-   -   Pass parameters to stored procedure from GridView (http://p2p.wrox.com/showthread.php?t=72640)

Dmitriy February 9th, 2009 09:47 AM

Pass parameters to stored procedure from GridView
 
Hi,

I have a GridVeiw control on my WEB page which is populated from the "standard" SQL 2005 membership database. This control has the DELETE column to delete selected user. I am using "dbo.aspnet_Users_DeleteUser" stored procedure to do this action. This procedure has the "signature":
Code:

ALTERPROCEDURE [dbo].[aspnet_Users_DeleteUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@TablesToDeleteFrom int,
@NumTablesDeletedFrom intOUTPUT

.........
........

and returns an Integer value.

When I try to delete a row from the grid, I am getting error:

Procedure or function aspnet_Users_DeleteUser has too many arguments specified.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Procedure or function aspnet_Users_DeleteUser has too many arguments specified.
My question is: how to specify and pass parameters to the stored procedure in my case?

Thanks in advance

-Dmitriy

alliancejhall February 9th, 2009 10:35 AM

Please post more code...
 
Can you post the code you are using in your codebehind and aspx page?

Dmitriy February 9th, 2009 10:49 AM

Pass parameters to stored procedure from GridView
 
This is my Code behind:

Code:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
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.Xml.Linq;
namespace LibraryFileUpload.Restricted
{
publicpartialclassUsersList : System.Web.UI.Page
{
privatestring sErrorMessage = null;
protectedvoid Page_Load(object sender, EventArgs e)
{
}
protectedvoid usersGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
Session[
"SelectedUserName"] = usersGridView.Rows[e.RowIndex].Cells[1].Text;
}
catch (Exception en)
{
sErrorMessage = en.Message;
Session[
"Message"] = "ERROR inside the usersGridView_RowDeleting() function. " + sErrorMessage + " Contact customer support,please. ";
Response.Redirect(
"ErrorForm.aspx", false);
}
}
protectedvoid LoginStatus2_LoggedOut(object sender, EventArgs e)
{
Session.Clear();
Session.Abandon();
}
protectedvoid SqlDataSource_Users_Deleting(object sender, SqlDataSourceCommandEventArgs e)
{
if (Session["SelectedUserName"] != null)
{
e.Command.Parameters[
"@ApplicationName"].Value = "/";
e.Command.Parameters[
"@UserName"].Value = (String)Session["SelectedUserName"];
e.Command.Parameters[
"@TablesToDeleteFrom"].Value = 5;
e.Command.Parameters[
"@NumTablesDeletedFrom"].Value = 0;
}
}
protectedvoid usersGridView_RowDeleted(object sender, GridViewDeletedEventArgs e)
{
if (e.Exception != null)
{
e.ExceptionHandled =
true;
sErrorMessage = e.Exception.Message;
Session[
"Message"] = "ERROR inside the usersGridView_RowDeleted() function. " + sErrorMessage + " Contact customer support,please. ";
Response.Redirect(
"ErrorForm.aspx", false);
}
}
}
}

And this is my form:

Code:

<%@PageLanguage="C#"MasterPageFile="~/Restricted/LibraryFileUpload4.Master"AutoEventWireup="true"CodeBehind="UsersList.aspx.cs"Inherits="LibraryFileUpload.Restricted.UsersList"Title="Untitled Page" %>
<asp:ContentID="Content1"ContentPlaceHolderID="head"runat="server">
<styletype="text/css">
.styleTitle
{
font-weight: bold;
text-decoration: underline;
height: 80px;
}
.style7
{
font-weight: bold;
text-decoration: underline;
height: 43px;
}
.styleWaitMessage
{
font-weight: bold;
color: Blue;
}
</style>
</
asp:Content>
<
asp:ContentID="Content2"ContentPlaceHolderID="ContentPlaceHolder1"runat="server">
<%--<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>--
%>
<tablestyle="width: 100%;">
<tr>
<tdalign="center">
<tablestyle="width: 100%;">
<trvalign="top">
<tdalign="center"class="style7">
<fontcolor="#336699"size="4">Users List Screen</font>
</td>
</tr>
<tr>
<tdalign="center">
<br/>
<asp:UpdateProgressID="UpdateProgress1"runat="server">
<ProgressTemplate>
<divclass="styleWaitMessage">
<imgsrc="../Images/spinner3-bluey.gif"/> Processing ... Wait, please.
</div>
</ProgressTemplate>
</asp:UpdateProgress>
</td>
</tr>
<trclass="style6">
<tdalign="center">
<asp:GridViewID="usersGridView"runat="server"AllowPaging="True"AllowSorting="True"BorderStyle="Double"BorderWidth="2px"CellPadding="4"CellSpacing="2"EnableSortingAndPagingCallbacks="True"Font-Bold="True"ForeColor="#333333"Width="346px"AutoGenerateColumns="False"DataSourceID="SqlDataSource_Users"onrowdeleting="usersGridView_RowDeleting"DataKeyNames="UserName"onrowdeleted="usersGridView_RowDeleted">
<FooterStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>
<RowStyleBackColor="#F7F6F3"ForeColor="#333333"/>
<Columns>
<asp:CommandFieldButtonType="Button"ShowDeleteButton="True"/>
<asp:BoundFieldDataField="UserName"HeaderText="UserName"SortExpression="UserName"/>
<asp:BoundFieldDataField="Email"HeaderText="Email"SortExpression="Email"/>
<asp:BoundFieldDataField="CreateDate"HeaderText="CreateDate"SortExpression="CreateDate"/>
</Columns>
<PagerStyleBackColor="#284775"ForeColor="White"HorizontalAlign="Center"/>
<SelectedRowStyleBackColor="#E2DED6"Font-Bold="True"ForeColor="#333333"/>
<HeaderStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>
<EditRowStyleBackColor="#999999"/>
<AlternatingRowStyleBackColor="White"ForeColor="#284775"/>
</asp:GridView>
<asp:SqlDataSourceID="SqlDataSource_Users"runat="server"ConnectionString="<%$ ConnectionStrings:MyFileUploadDBConnectionString %>"SelectCommand="SELECT aspnet_Users.UserName, aspnet_Membership.Email, aspnet_Membership.CreateDate FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId"DeleteCommandType="StoredProcedure"DeleteCommand="dbo.aspnet_Users_DeleteUser"ondeleting="SqlDataSource_Users_Deleting">
<DeleteParameters><asp:ParameterDirection="ReturnValue"Name="RETURN_VALUE"Type="Int32"DefaultValue=""/>
<asp:ParameterName="ApplicationName"Type="String"DefaultValue=""/>
<asp:ParameterDefaultValue=""Name="UserName"Type="String"></asp:Parameter>
<asp:ParameterDefaultValue=""Name="TablesToDeleteFrom"Type="Int32"/>
<asp:ParameterDefaultValue=""Direction="InputOutput"Name="NumTablesDeletedFrom"Type="Int32"/>
</DeleteParameters>
</asp:SqlDataSource>
</td>
</tr>
</table></td>
<tdalign="right"valign="top"width="50px">
<asp:LoginStatusID="LoginStatus2"runat="server"Height="31px"ToolTip="User Login/Logout"Width="46px"onloggedout="LoginStatus2_LoggedOut"/>
</td>
</tr>
</table>
<%--</ContentTemplate>
</asp:UpdatePanel> --
%>
</asp:Content>

Should I specify the returned value as well?

gbianchi February 9th, 2009 11:01 AM

Hi there..

I can see why you have the error. I don't understand why you are doing such a mess with a direct delete (or there is something I'm missing?)

Code:

<DeleteParameters><asp:ParameterDirection="ReturnValue"Name="RETURN_VALUE"Type="Int32"DefaultValue=""/>
<asp:ParameterName="ApplicationName"Type="String"DefaultValue=""/>
<asp:ParameterDefaultValue=""Name="UserName"Type="String"></asp:Parameter>
<asp:ParameterDefaultValue=""Name="TablesToDeleteFrom"Type="Int32"/>
<asp:ParameterDefaultValue=""Direction="InputOutput"Name="NumTablesDeletedFrom"Type="Int32"/>
</DeleteParameters>

Or I'm missing something (and if that happens I'm terrible sorry) or you are passing 5 parameters to the SP?

alliancejhall February 9th, 2009 11:04 AM

Noticed that too...
 
I was just about to post the same thing...

Looks like you are duplicating your output... remove this line right here and then try
<asp:ParameterDirection="ReturnValue"Name="RETURN_ VALUE"Type="Int32"DefaultValue=""/>

Dmitriy February 9th, 2009 11:17 AM

My mistake...
 
Gentlemen,
Thank you very much. I do not know how I included this returned parameter on the form? Thanks for your input again!

-Dmitriy [:(!]

Lee Dumond February 9th, 2009 11:19 AM

I am just really curious as to why one wouldn't just use the methods from the Membership classes to do this.

Dmitriy February 9th, 2009 11:27 AM

Yes,
I've tryed to use code like this:

Code:

protectedvoid usersGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
Membership.DeleteUser(usersGridView.Rows[e.RowIndex].Cells[1].Text);
Response.Redirect("Administration.aspx", false);
}
catch (Exception en)
{
sErrorMessage = en.Message;
Session[
"Message"] = "ERROR inside the usersGridView_RowDeleting() function. " + sErrorMessage + " Contact customer support,please. ";
Response.Redirect(
"ErrorForm.aspx", false);
}
}



But it is giving me an error and does not update the grid?

alliancejhall February 9th, 2009 11:50 AM

Well...
 
what's the error it's giving you?

Lee Dumond February 9th, 2009 11:59 AM

Yes, what is the error?

And, by the way, GridViews don't update themselves. You have to rebind them when the underlying data changes.

priti2010 July 14th, 2011 04:09 AM

how to delete records in gridview calling a stored procedure
 
myconnection.ConnectionString = sqlConStr
If myconnection.State <> ConnectionState.Open Then myconnection.Open()
Dim mycommand As SqlCommand
Dim row As GridViewRow = DirectCast(DataGridOdin.Rows(e.RowIndex), GridViewRow)
Dim lbldeleteID As Label = DirectCast(row.FindControl("lblid"), Label)
mycommand = New SqlCommand("sp_ServiceDel", myconnection)
mycommand.CommandType = CommandType.StoredProcedure
'mycommand = New SqlCommand("delete tbl_Service where Service_ID=" + lbldeleteID.Text & "", myconnection)
MsgBox("Do you want to Delete", MsgBoxStyle.OkCancel, "Confirmation")
If (MsgBoxResult.Ok) Then
Dim a As Integer
a = mycommand.ExecuteNonQuery()
MsgBox("Record Deleted")
myconnection.Close()
Bindgrid()

Imar July 14th, 2011 05:29 AM

Ouch....

Quote:

mycommand = New SqlCommand("delete tbl_Service where Service_ID=" + lbldeleteID.Text & "", myconnection)
In case anyone sees the above post: don't copy this code as is as it's open to SQL injection. If I enter

0 OR 1=1

lin the bldeleteID control, all your records are gone. Ooops.

Also, MsgBox is a Win Forms concept, not an ASP.NET concept and it won't work as intended.

Cheers,

Imar

priti2010 July 14th, 2011 06:23 AM

not able to delete the particular selected records
 
Protected Sub DataGridOdin_RowDeleting1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles DataGridOdin.RowDeleting
Try
myconnection.ConnectionString = sqlConStr
If myconnection.State <> ConnectionState.Open Then myconnection.Open()
Dim mycommand As SqlCommand
mycommand = New SqlCommand("sp_ServiceDel", myconnection)
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Parameters.Add("@Service_ID", SqlDbType.Int).Value = e.RowIndex
MsgBox("Do you want to Delete", MsgBoxStyle.OkCancel, "Confirmation")
If (MsgBoxResult.Ok) Then
Dim a As Integer
a = mycommand.ExecuteNonQuery
MsgBox("Record Deleted")
myconnection.Close()
Bindgrid()
End If
Catch ex As Exception
Response.Write(ex.Message)
End Try

End Sub

Imar July 14th, 2011 06:45 AM

Better, but MsgBox is still a desktop thing, while this forum category and the original post deals with ASP.NET....

Imar

priti2010 July 14th, 2011 06:52 AM

Quote:

Originally Posted by Imar (Post 274302)
Better, but MsgBox is still a desktop thing, while this forum category and the original post deals with ASP.NET....

Imar

even i m working in asp.net but vb as backend and i m new to programming
if i could get some idea about it

Imar July 14th, 2011 07:18 AM

Idea about what? I thought you were answering the original post? Can you elaborate?

Imar

priti2010 July 14th, 2011 07:34 AM

Please help
 
Quote:

Originally Posted by Imar (Post 274304)
Idea about what? I thought you were answering the original post? Can you elaborate?

Imar

how i can delete or update a select record using stored procedure in gridview
i think my code is not proper so its not working properly

priti2010 July 14th, 2011 07:35 AM

Quote:

Originally Posted by Imar (Post 274304)
Idea about what? I thought you were answering the original post? Can you elaborate?

Imar

do you want to go through my full page so that you can give me an idea

Imar July 14th, 2011 07:39 AM

And what is not working? Do you get an error and if so, which one? Also, did you ditch the MsgBox stuff?

How does your stored procedure look? Is it set up to delete an item by its ID? It looks like you're not passing the record's ID, but the relative index in the GridView instead. You may want to query the GridView's DataKeys collection to get the underlying item ID.

Google knows more on this: http://www.google.com/#sclient=psy&h...w=1920&bih=959

If all this doesn't help, please post your relevant code and a clear problem description, or we're not able to help you out much.

Cheers,

Imar

Imar July 14th, 2011 07:41 AM

Quote:

do you want to go through my full page so that you can give me an idea
We probably don't want to see the full page, but a trimmed down version showing just the code necessary to explain and / or reproduce the problem.

Cheers,

Imar

priti2010 July 14th, 2011 07:46 AM

Protected Sub DataGridOdin_RowDeleting1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles DataGridOdin.RowDeleting


Try
myconnection.ConnectionString = sqlConStr
If myconnection.State <> ConnectionState.Open Then myconnection.Open()
Dim mycommand As SqlCommand
mycommand = New SqlCommand("sp_ServiceDel", myconnection)
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Parameters.Add("@Service_ID", SqlDbType.Int).Value = e.RowIndex

MsgBox("Do you want to Delete", MsgBoxStyle.OkCancel, "Confirmation")
If (MsgBoxResult.Ok) Then

Dim a As Integer
a = mycommand.ExecuteNonQuery
MsgBox("Record Deleted")
myconnection.Close()
Bindgrid()
End If

Catch ex As Exception
Response.Write(ex.Message)
End Try

End Sub

Protected Sub DataGridOdin_RowUpdating1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles DataGridOdin.RowUpdating
Try
Dim mycommand As SqlCommand
myconnection.ConnectionString = sqlConStr
'If myconnection.State <> ConnectionState.Open Then myconnection.Open()
mycommand = New SqlCommand("sp_ServiceUpdt", myconnection)
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Parameters.Add("@Service_ID", SqlDbType.Int).Value = e.RowIndex
mycommand.Parameters.Add("@Service_Name", SqlDbType.NChar, 100).Value = txtServiceName.Text.Trim
mycommand.Parameters.Add("@NSE", SqlDbType.Bit).Value = chkExchange.Text
mycommand.Parameters.Add("@BSE", SqlDbType.Bit).Value = chkExchange.Text
mycommand.Parameters.Add("@NSE_FO", SqlDbType.Bit).Value = chkExchange.Text
mycommand.Parameters.Add("@MCX", SqlDbType.Bit).Value = chkExchange.Text
mycommand.Parameters.Add("@MCX_SX", SqlDbType.Bit).Value = chkExchange.Text
mycommand.Parameters.Add("@NCDEX", SqlDbType.Bit).Value = chkExchange.Text
mycommand.Parameters.Add("@NSE_SX", SqlDbType.Bit).Value = chkExchange.Text
mycommand.Parameters.Add("@Manager_1", SqlDbType.Bit).Value = chkServer.Text
mycommand.Parameters.Add("@Manager_2", SqlDbType.Bit).Value = chkServer.Text
mycommand.Parameters.Add("@MIS_101", SqlDbType.Bit).Value = chkServer.Text
mycommand.Parameters.Add("@MIS_102", SqlDbType.Bit).Value = chkServer.Text
mycommand.Connection = myconnection
MsgBox("Do you want to Update", MsgBoxStyle.OkCancel, "Confirmation")
If (MsgBoxResult.Ok) Then
MsgBox("Record Updated")
DataGridOdin.EditIndex = -1
Bindgrid()
End If
Catch ex As Exception
Response.Write(ex.Message)
End Try

End Sub


This is my code, i m not getting any errors while debugging this code
only the records are not deleting proper and the update its wont update the gridview

Imar July 14th, 2011 09:02 AM

Code:


while (stillInPostedSampleCode)
{
  You cannot use MsgBox in a Web App
}

Code:


while (notPosted)
{
  How does your stored procedure look?
  Is it set up to delete an item by its ID?
  It looks like you're not passing the record's ID, but the relative index in the GridView  instead.
  You may want to query the GridView's DataKeys collection to get the underlying item ID.
  Please post your relevant code and a clear problem description
}

In other words: you have to help us help you. I am not psychic, don't know how your GridView looks like, where it gets its data from, what happens when you debug the code, whether or not the code even compiles, what you see when you step through it with the debugger, what you know or don't know about GridView.DataKeys, etc etc etc.

Without all that, I don't understand how you think we can help you. It *looks* like you're trying to delete a record with an *ID* that matches the *index* of the record in the GridVew which is unlikely to work, but it's hard to say without relevant information.

Cheers,

Imar

priti2010 July 15th, 2011 03:43 AM

Please let me know how to solved this error
 
I have modify my code but now i am getting a error "Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index "
Protected Sub DataGridOdin_RowDeleting1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles DataGridOdin.RowDeleting
Try
Dim Service_ID As [String]
Service_ID = DataGridOdin.DataKeys(e.RowIndex).Value.ToString()
mycommand = New SqlCommand("sp_ServiceDel", myconnection)
myconnection.Open()
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Parameters.Add("@Service_ID", SqlDbType.Int).Value = Convert.ToInt32(Service_ID)
mycommand.ExecuteReader()
myconnection.Close()
Bindgrid()

Catch ex As Exception
Response.Write(ex.Message)
End Try


End Sub

---my stored procedure is as follows--
CREATE PROCEDURE [dbo].[sp_ServiceDel]
-- Add the parameters for the stored procedure here
@Service_ID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DELETE tbl_Service from tbl_Service
where Service_ID = @Service_ID

END

Imar July 15th, 2011 04:01 AM

We're a bit stuck in a loop here.... I cannot really help you if you're not providing the right information. So, to be absolutely clear on what I need to help you:

1. Please post the code of your GridView
2. Post the code that assigns the data to the GridView
3. Did you set a DataKey on the GridView?
4. What do you get when you debug your code and look at the ID you're trying to delete? Does it have a value? What does "DataGridOdin.DataKeys(e.RowIndex).Value.ToString( )" give you when you debug? And does DataGridOdin.DataKeys contain any data?

Cheers,

Imar

priti2010 July 15th, 2011 04:39 AM

Please find the detail
 
no i have not set any datakeys
please let me know how to set a datakey value
and in my rowindex the e value is 0
Service_ID = DataGridOdin.DataKeys(e.RowIndex).Value.ToString()
and exception error is show that index value is out of range....
my datagird table look as this
my design contains a textbox and 2 checkboxlist which accepts only the bits values
Service_ID Service_Name NSE BSE NSE_FO MCX MCX_SX NCDEX NSE_SX Manager_1 Manager_2 MIS_101 MIS_102
1 jdjkd 1 1 1 1 1 1 1 1 1 1 1
2 bgftht 1 1 1 1 0 1 0 1 0 1 0
3 hgthf 1 1 1 0 1 1 1 1 1 0 1
4 fgdgd 1 1 1 1 1 1 1 1 1 0 1
and this is how i am binding a data
Public Function GetData() As DataTable
Try
myconnection.ConnectionString = sqlConStr
If myconnection.State <> ConnectionState.Open Then myconnection.Open()
mycommand = New SqlCommand("sp_Service", myconnection)
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Connection = myconnection
pnlview.Visible = True
DataGridOdin.Visible = True
datareader = mycommand.ExecuteReader
da = New DataTable("tbl_Service")
da.Load(datareader)
DataGridOdin.DataSource = da
DataGridOdin.DataBind()

Catch ex As Exception
Response.Write(ex.Message)
End Try
Return da

End Function

priti2010 July 15th, 2011 04:54 AM

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<link href="Styles/Site1.css" rel="stylesheet" type="text/css" />
<head runat="server">

<title></title>


<style type="text/css">
.style1
{
text-align: center;
}
.style2
{
text-align: right;
width: 410px;
}
.style5
{
text-align: center;
}
.style6
{
height: 178px;
}
</style>
</head>
<body>

<form id="form1" runat="server">
<div>
<table id ="TABLE" width ="100%" >
<tr class ="subheader" >
<td colspan="3" class="style1"> <strong>
ODIN SERVER CHECK LIST</strong></td>
</tr >
<tr>
<td class="style2" >
Service Name :&nbsp;&nbsp; </td>
<td colspan="2">
<asp:TextBox ID="txtServiceName" runat="server" AutoCompleteType="Disabled"></asp:TextBox>&nbsp;&nbsp;
<font color = "red" /><strong >* </strong>&nbsp;
<asp:RequiredFieldValidator ID = "Validator" runat = "server" ControlToValidate = "txtServiceName"
ErrorMessage = "Required Entry !!!!">
</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style2">
Exchange :&nbsp;&nbsp; </td>
<td colspan="2">
<asp:CheckBoxList ID="chkExchange" runat="server" RepeatDirection="horizontal"
RepeatColumns= 4 >
<asp:ListItem Value = "1">NSE</asp:ListItem>
<asp:ListItem Value = "2">BSE</asp:ListItem>
<asp:ListItem Value = "3">NSE_FO</asp:ListItem>
<asp:ListItem Value = "4">MCX</asp:ListItem>
<asp:ListItem Value = "5">MCX_SX</asp:ListItem>
<asp:ListItem Value = "6">NCDEX</asp:ListItem>
<asp:ListItem Value = "7">NSE_SX</asp:ListItem>
</asp:CheckBoxList>
</td>
</tr>
<tr>
<td class="style2">
Server :&nbsp;&nbsp; </td>
<td colspan="2">
<asp:CheckBoxList ID="chkServer" runat="server" RepeatDirection= "horizontal" RepeatColumns= 2 Width = 400 >
<asp:ListItem Value = "11">Manager_1</asp:ListItem>
<asp:ListItem Value = "12">Manager_2</asp:ListItem>
<asp:ListItem Value = "13">MIS_101</asp:ListItem>
<asp:ListItem Value = "14">MIS_102</asp:ListItem>
</asp:CheckBoxList>
</td>
</tr>
<tr class ="subheader">
<td class="style5">
<asp:Button ID="btnShow" runat="server" Text="Show" CssClass = "button"
CausesValidation="False"/>

</td>

<td class="style1">
<asp:Button ID="btnAdd" runat="server" CssClass="button" Text="Add" CausesValidation="True" />
</td>

<td class="style1">
<asp:Button ID="btnExit" runat="server" Text="Exit" CssClass = "button"/>
</td>

</tr>
<tr >
<td class="style1" colspan="3" >
<asp:Label ID="lblCount" runat="server" Text="lblCount" CssClass="labelMessage"
Visible="False" ></asp:Label>
</td>

</tr>
<tr>
<td colspan="3" class="style6" >
<asp:Panel ID="pnlview" runat="server" ScrollBars="Both" Width="100%"
HorizontalAlign="Center" Visible="False">
<asp:GridView ID="DataGridOdin" runat="server"
CellPadding="3" Height="157px" Width="99%"
AllowSorting="True" AllowPaging = "True" BackColor="White" BorderColor="#CCCCCC"
BorderStyle="None" BorderWidth="1px" HorizontalAlign="Center"
UseAccessibleHeader="False" AutoGenerateColumns="False"
AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" >
<PagerStyle HorizontalAlign ="Left" BackColor="White" ForeColor="#000066" />
<PagerSettings Position = "TopAndBottom"
FirstPageText = "Go to First Page" LastPageText = "Go to Last Page" />

<Columns>

<asp:BoundField DataField="Service_ID" HeaderText="Service_ID"
InsertVisible="False" ReadOnly="True" SortExpression="Service_ID" />
<asp:BoundField DataField="Service_Name" HeaderText="Service_Name"
SortExpression="Service_Name" />
<asp:CheckBoxField DataField="NSE" HeaderText="NSE" SortExpression="NSE" />
<asp:CheckBoxField DataField="BSE" HeaderText="BSE" SortExpression="BSE" />
<asp:CheckBoxField DataField="NSE_FO" HeaderText="NSE_FO"
SortExpression="NSE_FO" />
<asp:CheckBoxField DataField="MCX" HeaderText="MCX" SortExpression="MCX" />
<asp:CheckBoxField DataField="MCX_SX" HeaderText="MCX_SX"
SortExpression="MCX_SX" />
<asp:CheckBoxField DataField="NCDEX" HeaderText="NCDEX"
SortExpression="NCDEX" />
<asp:CheckBoxField DataField="NSE_SX" HeaderText="NSE_SX"
SortExpression="NSE_SX" />
<asp:CheckBoxField DataField="Manager_1" HeaderText="Manager_1"
SortExpression="Manager_1" />
<asp:CheckBoxField DataField="Manager_2" HeaderText="Manager_2"
SortExpression="Manager_2" />
<asp:CheckBoxField DataField="MIS_101" HeaderText="MIS_101"
SortExpression="MIS_101" />
<asp:CheckBoxField DataField="MIS_102" HeaderText="MIS_102"
SortExpression="MIS_102" />
</Columns>
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
</asp:Panel>
</td>
</tr>
</table>
</div>

</form>
</body>
</html>
This is my aspx page code to refer

Imar July 15th, 2011 04:56 AM

Quote:

1. Please post the code of your GridView
http://msdn.microsoft.com/en-us/libr....datakeys.aspx

You assign the primary key of your data record (which I assume is Service_ID) to the DataKeys property of the GridView. This way, the GridView can link the underlying record ID to the item's index so you can retrieve it again when you try to delete an item.

All this would be much quicker if you just posted the code I've been asking for for the past 27 posts.... ;-)

Cheers,

Imar

priti2010 July 18th, 2011 05:41 AM

how to delete gridview records
 
Protected Sub GridviewOdin_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridviewOdin.RowDeleting
Try

myconnection.ConnectionString = sqlConStr
mycommand = New SqlCommand("sp_ServiceDel", myconnection)
myconnection.Open()
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Parameters.Add("@Service_ID", SqlDbType.Int).Value = e.RowIndex
mycommand.ExecuteReader()
myconnection.Close()
Bindgrid()

Catch ex As Exception
Response.Write(ex.Message)
End Try

End Sub

my stored procedure
CREATE PROCEDURE [dbo].[sp_ServiceDel]
-- Add the parameters for the stored procedure here
@Service_ID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DELETE tbl_Service from tbl_Service
where Service_ID = @Service_ID

select * from tbl_Service

END
i am not able to get the proper rowindex to delete the record

Imar July 18th, 2011 05:49 AM

I am not sure why you think that posting the same, or similar code over and over again is going to help....

Your GridView needs a DataKeyNames set in order to keep track of the primary keys of your data objects. Without that, it's not going to work. So:

1. Go here: http://msdn.microsoft.com/en-us/libr...akeynames.aspx

2. Read about the DataKeyNames

3. Fix your GridView and try again.

If it keeps failing, post *all relevant* code; e.g. the code for the GridView and the code / markup that assigns data to it....

Imar

priti2010 July 18th, 2011 05:59 AM

Dim Service_ID As [String]
Service_ID = GridviewOdin.DataKeys(e.RowIndex).Value.ToString()
myconnection.ConnectionString = sqlConStr
mycommand = New SqlCommand("sp_ServiceDel", myconnection)
myconnection.Open()
mycommand.CommandType = CommandType.StoredProcedure
'mycommand.Parameters.Add("@Service_ID", SqlDbType.Int).Value = e.RowIndex
mycommand.Parameters.Add("@Service_ID", SqlDbType.Int, "Service_ID").Value = Convert.ToInt32(Service_ID)

mycommand.ExecuteReader()
myconnection.Close()
Bindgrid()

if i used this way i am gettina a error like"Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index "
and even i set a datakeynames in aspx page is gives me and error
what is the method that i can delete the particular selected records

Imar July 18th, 2011 06:04 AM

Why do you keep posting the same code over and over again?

Quote:

what is the method that i can delete the particular selected records
Did you follow the three steps I mentioned above?

Imar

priti2010 July 18th, 2011 06:40 AM

to update in gridview
 
hey thanks dr i did all the procedures to said now its working can u just tell me how can i update the checkbox list and the textbox by using the same datakeys how can my checkbox list will get update if there are around 5 to 6 checkbox in a list
please help me out

priti2010 July 18th, 2011 07:40 AM

not able to update gridview
 
Protected Sub GridviewOdin_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridviewOdin.RowUpdating
Try
Dim Service_ID As String
Service_ID = GridviewOdin.DataKeys(e.RowIndex).Value.ToString
myconnection.ConnectionString = sqlConStr
mycommand = New SqlCommand("sp_ServiceUpdt", myconnection)
myconnection.Open()
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Parameters.Add("@Service_ID", SqlDbType.Int).Value = Convert.ToInt32(Service_ID)
mycommand.Parameters.Add("@Service_Name", SqlDbType.NChar, 100).Value = txtServiceName.Text
mycommand.Parameters.Add("@NSE", SqlDbType.Bit).Value = Convert.ToBoolean(chkExchange.SelectedItem.Value.T oString)
mycommand.Parameters.Add("@BSE", SqlDbType.Bit).Value = Convert.ToBoolean(chkExchange.SelectedItem.Value.T oString)
mycommand.Parameters.Add("@NSE_FO", SqlDbType.Bit).Value = Convert.ToBoolean(chkExchange.SelectedItem.Value.T oString)
mycommand.Parameters.Add("@MCX", SqlDbType.Bit).Value = Convert.ToBoolean(chkExchange.SelectedItem.Value.T oString)
mycommand.Parameters.Add("@MCX_SX", SqlDbType.Bit).Value = Convert.ToBoolean(chkExchange.SelectedItem.Value.T oString)
mycommand.Parameters.Add("@NCDEX", SqlDbType.Bit).Value = Convert.ToBoolean(chkExchange.SelectedItem.Value.T oString)
mycommand.Parameters.Add("@NSE_SX", SqlDbType.Bit).Value = Convert.ToBoolean(chkExchange.SelectedItem.Value.T oString)
mycommand.Parameters.Add("@Manager_1", SqlDbType.Bit).Value = Convert.ToBoolean(chkServer.SelectedItem.Value.ToS tring)
mycommand.Parameters.Add("@Manager_2", SqlDbType.Bit).Value = Convert.ToBoolean(chkServer.SelectedItem.Value.ToS tring)
mycommand.Parameters.Add("@MIS_101", SqlDbType.Bit).Value = Convert.ToBoolean(chkServer.SelectedItem.Value.ToS tring)
mycommand.Parameters.Add("@MIS_102", SqlDbType.Bit).Value = Convert.ToBoolean(chkServer.SelectedItem.Value.ToS tring)


mycommand.ExecuteReader()

myconnection.Close()
GridviewOdin.EditIndex = -1
Bindgrid()

Catch ex As Exception
Response.Write(ex.Message)
End Try
End Submy stored procedure is as follows--
CREATE PROCEDURE sp_ServiceUpdt
@Service_ID int,
@Service_Name nchar(100),
@NSE bit,
@BSE bit,
@NSE_FO bit,
@MCX bit,
@MCX_SX bit,
@NCDEX bit,
@NSE_SX bit,
@Manager_1 bit,
@Manager_2 bit,
@MIS_101 bit,
@MIS_102 bit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
update tbl_Service
set
Service_Name =@Service_Name,
NSE = @NSE,
BSE = @BSE,
NSE_FO =@NSE_FO,
MCX= @MCX,
MCX_SX = @MCX_SX,
NCDEX = @NCDEX,
NSE_SX = @NSE_SX,
Manager_1 = @Manager_1,
Manager_2= @Manager_2,
MIS_101=@MIS_101,
MIS_102=@MIS_102
where Service_ID = @Service_ID

select * from tbl_Service
end
i am getting a error that String was not recognized as a valid Boolean.

Imar July 18th, 2011 10:49 AM

I am sorry, but I don't think I can help you anymore. I realy tried hard, but asking the same questions over and over and over and over and over again (post relevant code, provide a clear problem description, tell us what the debugger tells you, tell us where the code crashes, post the relevant code, explain what you're doing, post the relevant code) gets boring after a while.

Sorry it didn't work out.

Imar

priti2010 July 20th, 2011 04:43 AM

how can i avoid duplicate records to get add or update in a table
 
how can i make a stored procedure to check the duplicate records present or not while inserting and updating records

priti2010 July 20th, 2011 08:28 AM

CREATE PROCEDURE sp_ServiceRcds
-- Add the parameters for the stored procedure here
(@Service_Name nchar(100),
@Otputs nchar(50) output)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @result nchar(50)
IF EXISTS (select * from tbl_Service where Service_Name = @Service_Name)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET @result = 'Records Already exits'
END
ELSE
BEGIN
INSERT into tbl_Service(Service_Name) values(@Service_Name)
-- Insert statements for procedure here
SET @result ='Records Added'
END
END
this is my stored procedure now how can i co-relate with the other insert procedure and update procedure to avoid duplicacy of records

priti2010 July 21st, 2011 12:47 AM

duplicates records
 
how can i call the procedure to check the duplicate records present in table while adding or updating the table

priti2010 August 2nd, 2011 04:52 AM

to avoid duplicate records
 
can any one tell me how can i avoid duplicate records not to save in gridview and if match is found show error of duplicates record found

priti2010 August 5th, 2011 03:07 AM

Check Duplicate Record while adding new records
 
to check duplicate record while entering new record

Dim myconnection as New SqlConnection
Dim str As String = "SELECT COUNT(*) FROM <tablename> WHERE LTRIM(RTRIM(parameter))='" & textbox1.Text.Trim & "'"
mycommand = New SqlCommand(str, myconnection)
Dim mCount As Integer = mycommand.ExecuteScalar()

If mCount = 0 Then
write insert query
else
Label1.text= "Duplicate match"
end if

priti2010 August 10th, 2011 07:37 AM

error while updating same records
 
Dim str As String
str = String.Empty
Dim Service_Name As String
Dim colindex As Integer = 1
Service_Name = DirectCast(GridviewOdin.Rows(e.RowIndex).FindContr ol("Service_Name"), TextBox).Text
str = "SELECT COUNT(*) FROM tbl_Service WHERE LTRIM(RTRIM(Service_Name))='" & Service_Name & "'"
mycommand = New SqlCommand(str, myconnection)
Dim mCount As Integer = mycommand.ExecuteScalar()
'myconnection.Close()

If mCount = 0 Then
update procedure
else
MsgBox("Duplicate Service Found", MsgBoxStyle.OkOnly, "")
end if
This above is my query i cant update the grid with the same record present in the grid but if i update new records it will check and update but existing records cannot be update in the same row
how can i solve this error


All times are GMT -4. The time now is 10:35 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.