Delete from database in Code Behind

[MasterChief]

I am trying to learn how to delete multple items in a database using
the code behind file. I would just like somebody to tell me what is
wrong with my code. I am new to connecting to the database through a
code-behind. I have a gridview called gridview1. When the user checks a
bunch of plans then should be able to hit delete and for each checked
box it should delete the plan and update the gridview1.

Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Configuration

Partial Class _Default
Inherits System.Web.UI.Page
Protected WithEvents gvName As System.Web.UI.WebControls.GridView
Protected Function GetPlanURL(ByVal plan_id As Int32) As String
Dim url As String
url = "~/EditItem.aspx?plan_id=" + plan_id.ToString()
Return url
End Function

Protected Sub Delete_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Delete.Click
Dim CheckBox As CheckBox
Dim KeyId As Int32
Dim strConnection As String =
System.Configuration.ConfigurationManager.AppSettings("draftinglogConnectionString1")
Dim objConnection As New SqlConnection(strConnection)

For Each GridViewRow As GridViewRow In GridView1.Rows
CheckBox = CType(GridViewRow.FindControl("chkDelete"),
CheckBox)
If CheckBox.Checked = True Then
KeyId =
CType(GridView1.DataKeys.Item(GridViewRow.RowIndex).Value, Int32)

Dim strSql As String = "delete from plans WHERE
plans.plan_id=" + KeyId.ToString()
Dim objCommand As New SqlCommand(strSql, objConnection)
objConnection.Open()
gvName.DataSource = objCommand.ExecuteReader()
gvName.DataBind()
objConnection.Close()
End If
Next
End Sub
End Class

 

[P. Yanzick]

Hello,

I am not at a system where I can play with your code, but I believe that
part of the issue is you are using ExecuteReader... Try using
ExecuteNonQuery instead, and also note it won't return a DataSource...

Also, you may want to reconsider where you have the DataBind statement. It
probably doesn't need to be done on every row deletion.

Thanks
Paul

 

[MasterChief]

Well right now where I am getting an error is
Dim strConnection As String =
System.Configuration.ConfigurationManager.AppSettings.Item("draftinglogConnectionString1").ToString()
It says Object Reference not set to an instance of an object.
My web.config file looks like
<connectionStrings>
<add name="draftinglogConnectionString1" connectionString="Data
Source=x\SQLEXPRESS;Initial Catalog=draftinglog;Persist Security
Info=True;User ID=x;Password=x" providerName="System.Data.SqlClient"/>
</connectionStrings>

It is like strConnection can take the configuration from my web.config
file. Is there something I am missing. I have declared
Imports System.Configuration at the top of my .vb file.

Any help would be greatly appreciated.

 

[P. Yanzick]

Try changing

Dim strConnection As String =
System.Configuration.ConfigurationManager.AppSettings.Item("draftinglogConnectionString1").ToString()to Dim strConnection As String = ConfigurationSettings.AppSettings("draftinglogConnectionString1").ToString()This should work for you."MasterChief" <[email protected]> wrote in messageWell right now where I am getting an error is> Dim strConnection As String =>System.Configuration.ConfigurationManager.AppSettings.Item("draftinglogConnectionString1").ToString()> It says Object Reference not set to an instance of an object.> My web.config file looks like> <connectionStrings>> <add name="draftinglogConnectionString1" connectionString="Data> Source=x\SQLEXPRESS;Initial Catalog=draftinglog;Persist Security> Info=True;User ID=x;Password=x" providerName="System.Data.SqlClient"/>> </connectionStrings>>> It is like strConnection can take the configuration from my web.config> file. Is there something I am missing. I have declared> Imports System.Configuration at the top of my .vb file.>> Any help would be greatly appreciated.>

 

[MasterChief]

It is still giving the error. The reason I did
System.Configuration.ConfigurationManager.AppSettings is because when I
do ConfigurationSettings.AppSettings the compiler tells me it is
obsolete and I should be using the method I have been trying to use. I
have looked at so many different examples and they all match what I am
trying to do and how I am going about it.