Pass parameters to stored procedure from GridView

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

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

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 :   </td>
<td colspan="2">
<asp:TextBox ID="txtServiceName" runat="server" AutoCompleteType="Disabled"></asp:TextBox>  
<font color = "red" /><strong >* </strong> 
<asp:RequiredFieldValidator ID = "Validator" runat = "server" ControlToValidate = "txtServiceName"
ErrorMessage = "Required Entry !!!!">
</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style2">
Exchange :   </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 :   </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

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