CheckBoxList SelectedValue problems

Help! I have a checkboxlist that when you check a box it should delete all the entries in the database that having a matching MassID. It isn't working! It was working on one web server, but when I copied and pasted it onto the clients server it quit working. Here is my code on the for the checkboxlist:




<asp:CheckBoxList ID="CheckBoxList1" runat="server" DataTextField="Ref" DataValueField="MassID" Font-Names="Verdana" Font-Size="9pt" AutoPostBack="True">


</asp:CheckBoxList>





Here is the code on the backend:



Imports System.Data.OleDb


Imports System.Data


Imports System.Data.SqlClient


Imports system.IO


Partial Class Newer_AdminAddEvent


Inherits System.Web.UI.Page


Dim dbConn As String = "Data Source=mssql09.discountasp.net;Initial Catalog=DB_261536_annbransom;Persist Security Info=True;User ID=DB_261536_annbransom_user;Password=****"


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


'must be logged in


If Session("logger") <> True Then


Response.Redirect("Default.aspx")


End If


Dim sqlConn As SqlConnection = New SqlConnection(dbConn)


Dim sqlCmd As SqlCommand = New System.Data.SqlClient.SqlCommand()


Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter


Dim dtDel As New DataTable


Dim SelectStatement As String


SelectStatement = "SELECT DISTINCT MassID, Title, Ref, DateRange, StartYear, StartMonth, StartDay From Ohio_Events WHERE Date >= Current_Timestamp Order by StartYear Desc, StartMonth Desc, StartDay Desc"


With sqlCmd


.Connection = sqlConn


.CommandType = CommandType.Text


.CommandText = SelectStatement


End With


sqlConn.Open()


sqlAdapter.SelectCommand = sqlCmd


sqlAdapter.Fill(dtDel)


sqlConn.Close()


CheckBoxList1.DataSource = dtDel


CheckBoxList1.DataBind()


End Sub


Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click


Dim sqlConn As SqlConnection = New SqlConnection(dbConn)


Dim sqlCmd As SqlCommand = New System.Data.SqlClient.SqlCommand()


Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter


Dim dt As New DataTable


Dim SelectStatement As String


SelectStatement = "Select Top 1 EventID From Ohio_Events order by EventID desc"


With sqlCmd


.Connection = sqlConn


.CommandType = CommandType.Text


.CommandText = SelectStatement


End With


sqlConn.Open()


sqlAdapter.SelectCommand = sqlCmd


sqlAdapter.Fill(dt)


sqlConn.Close()


Dim Title As String


Dim Descrip As String


Dim StartDate As Date


Dim EndDate As Date


Dim MassID As Integer


Dim Location As String


Dim DateRange As String


Dim Ref As String


Dim StartDay As String


Dim StartMonth As String


Dim StartYear As String


Location = RadioButtonList1.SelectedValue


If dt.Rows.Count <> 0 Then


MassID = dt.Rows(0).Item(0) + 1


Else


MassID = 1


End If





Title = txtTitle.Text


Title = Replace(Title, "'", "''")


Descrip = txtDescription.Text


Descrip = Replace(Descrip, "'", "''")


StartDate = txtStartDate.Text


EndDate = txtEndDate.Text


If StartDate <> EndDate Then


DateRange = txtStartDate.Text &amp; "-" &amp; txtEndDate.Text


Else


DateRange = txtStartDate.Text


End If


Ref = DateRange &amp; "" &amp; Title


StartDay = StartDate.Day


StartMonth = StartDate.Month


StartYear = StartDate.Year





Dim EDate As Date


EDate = StartDate


Dim NewDate As Date


While EDate.Date <= EndDate.Date


With sqlCmd


.Connection = sqlConn


.CommandType = CommandType.Text


.CommandText = "Insert into Ohio_Events (Date, Title, Description, MassID, Location, DateRange, Ref, StartDay, StartMonth, StartYear) values ('" &amp; EDate &amp; "', '" &amp; Title &amp; "', '" &amp; Descrip &amp; "', '" &amp; MassID &amp; "', '" &amp; Location &amp; "', '" &amp; DateRange &amp; "', '" &amp; Ref &amp; "', '" &amp; StartDay &amp; "', '" &amp; StartMonth &amp; "', '" &amp; StartYear &amp; "')"


.Connection.Open()


.ExecuteNonQuery()


.Connection.Close()


End With


NewDate = DateAdd("d", 1, EDate)


EDate = NewDate


End While


Response.Redirect("AdminAddEvent.aspx")


End Sub





Protected Sub CheckBoxList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckBoxList1.SelectedIndexChanged


Dim sqlConn As SqlConnection = New SqlConnection(dbConn)


Dim sqlCmd As SqlCommand = New System.Data.SqlClient.SqlCommand()


With sqlCmd


.Connection = sqlConn


.CommandType = CommandType.Text


.CommandText = "delete from Ohio_Events Where MassID = '" &amp; CheckBoxList1.SelectedValue &amp; "'"


.Connection.Open()


.ExecuteNonQuery()


.Connection.Close()


End With


Response.Redirect("AdminAddEvent.aspx")


End Sub


End Class


Any help would be greatly appreciated.





Thanks!

 

What happens if you try to test the same query directly on the data server?
(Using the Test query options...)

 

It works just fine on the data server. This is a weird one...

 

I grew up in Detroit...Classic UofM/Buckeye rivalry may be keeping me from seeing the problem, but...


Have you tried creating a page output for testing that will show you if the MassID is returning for the CheckBoxList1.SelectedValue?


All the best,


Mark Wisecarver

 

Well, I work for an Ohio company but I am located in Lexington, KY, home of theundisputed basketball legends The Wildcats. No contest...


Anyway, I did make the page try and display the massID on a label when the checkboxlist index changed, but it doesn't display anything.

 

Cool conversation...FYI, I reside in N.E. Tennessee now but am still a UofM fan.


My three children were all born in TN so they're Vol fans, go figure eh?


Back to the problem at hand...


I think this would be a nice way to test out the data updates via Ajax controls.


Have you considered that? At least so you can see the values without posting?

 

You should only rebind the data of the CheckBoxList if it's not a postback. Otherwise, you'll lose the selected value.

Aristotle

DiscountASP.NET
www.DiscountASP.NET