ASP.NET
ASP.NET
Visual Basic.NET
Visual Basic.NET
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Failed to update and save Checkbox controls action to a field of SQL database table

Hi Gurus,

my web app page has 3 checkbox and 1 text field, I need to submit values of these controls by the button control, and update a field in the SQL Server 2005 database, by matching a field called [File Number] .

I put my code below, no error when debugging, but no data saved in the data table as well.

Please advise.

Protected Sub Button1AfterReadLetter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1AfterReadLetter.Click

Dim Username2 As String = Request.QueryString("Val")

Dim DBconn As New SqlClient.SqlConnection("Data Source=Myserver;Initial Catalog=MyDB;Integrated Security=True;")
Dim DBcmd As New SqlClient.SqlCommand
Dim DBAdap As New SqlClient.SqlDataAdapter
Dim DS As New DataSet

DBconn.Open()
Try
DBcmd = New SqlClient.SqlCommand("update MyTable set EnglishCheckbox =@eCheckbox where [File Number] = @File Number", DBconn)
DBcmd = New SqlClient.SqlCommand("update MyTable set SpanishCheckbox=@sCheckbox where [File Number] = @File Number", DBconn)
DBcmd = New SqlClient.SqlCommand("update MyTable set OtherLanguageCBox=@oCheckbox where [File Number] = @File Number", DBconn)
DBcmd = New SqlClient.SqlCommand("update MyTable set languageName=@LanguageName where [File Number] = @File Number", DBconn)

DBcmd.Parameters.Add("@eCheckbox", SqlDbType.Char).Value = CheckBox1.Checked
DBcmd.Parameters.Add("@sCheckbox", SqlDbType.Char).Value = CheckBox2.Checked
DBcmd.Parameters.Add("@oCheckbox", SqlDbType.Char).Value = CheckBox3.Checked
DBcmd.Parameters.Add("@LanguageName", SqlDbType.VarChar).Value = TextBox1.Text

Catch exp As Exception
Response.Write(exp)
End Try
DBcmd.Dispose()
DBconn.Close()
DBconn = Nothing

Response.Redirect("WebForm2.aspx?val2=" + Username2)

End Sub

GaiaGiaAsked: 2012-12-30

Who is Participating?

LVL 9

chwong67Commented: 2012-12-30

improved code:

    Protected Sub Button1AfterReadLetter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1AfterReadLetter.Click


        Dim Username2 As String = Request.QueryString("Val")


        Dim DBconn As New SqlClient.SqlConnection("Data Source=Myserver;Initial Catalog=MyDB;Integrated Security=True;")
        Dim DBcmd As New SqlClient.SqlCommand
        Dim DBAdap As New SqlClient.SqlDataAdapter
        Dim DS As New DataSet

        DBconn.Open()
        Try
            DBcmd = New SqlClient.SqlCommand("update MyTable set EnglishCheckbox =@eCheckbox, SpanishCheckbox=@sCheckbox, OtherLanguageCBox=@oCheckbox, languageName=@LanguageName where [File Number] = @File Number", DBconn)
            DBcmd.Parameters.Add("@eCheckbox", SqlDbType.Char).Value = CheckBox1.Checked
            DBcmd.Parameters.Add("@sCheckbox", SqlDbType.Char).Value = CheckBox2.Checked
            DBcmd.Parameters.Add("@oCheckbox", SqlDbType.Char).Value = CheckBox3.Checked
            DBcmd.Parameters.Add("@LanguageName", SqlDbType.VarChar).Value = TextBox1.Text
DBcmd.ExecuteNonQuery()
        Catch exp As Exception
            Response.Write(exp)
        End Try
        DBcmd.Dispose()
        DBconn.Close()
        DBconn = Nothing

        Response.Redirect("WebForm2.aspx?val2=" + Username2)

    End Sub

Open in new window

LVL 9

chwong67Commented: 2012-12-30

Your dmcmd must split. latest string will overwrite all.

Protected Sub Button1AfterReadLetter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1AfterReadLetter.Click


        Dim Username2 As String = Request.QueryString("Val")


        Dim DBconn As New SqlClient.SqlConnection("Data Source=Myserver;Initial Catalog=MyDB;Integrated Security=True;")
        Dim DBcmd As New SqlClient.SqlCommand
        Dim DBcmd1 As New SqlClient.SqlCommand
        Dim DBcmd2 As New SqlClient.SqlCommand
        Dim DBcmd3 As New SqlClient.SqlCommand
        Dim DBAdap As New SqlClient.SqlDataAdapter
        Dim DS As New DataSet

        DBconn.Open()
        Try
            DBcmd1 = New SqlClient.SqlCommand("update MyTable set EnglishCheckbox =@eCheckbox where [File Number] = @File Number", DBconn)
            DBcmd2 = New SqlClient.SqlCommand("update MyTable  set SpanishCheckbox=@sCheckbox where [File Number] = @File Number", DBconn)
            DBcmd3 = New SqlClient.SqlCommand("update  MyTable  set OtherLanguageCBox=@oCheckbox where [File Number] = @File Number", DBconn)
            DBcmd = New SqlClient.SqlCommand("update MyTable set languageName=@LanguageName where [File Number] = @File Number", DBconn)

            DBcmd1.Parameters.Add("@eCheckbox", SqlDbType.Char).Value = CheckBox1.Checked
            DBcmd2.Parameters.Add("@sCheckbox", SqlDbType.Char).Value = CheckBox2.Checked
            DBcmd3.Parameters.Add("@oCheckbox", SqlDbType.Char).Value = CheckBox3.Checked
            DBcmd.Parameters.Add("@LanguageName", SqlDbType.VarChar).Value = TextBox1.Text

        Catch exp As Exception
            Response.Write(exp)
        End Try
        DBcmd.Dispose()
        DBconn.Close()
        DBconn = Nothing

        Response.Redirect("WebForm2.aspx?val2=" + Username2)

    End Sub

Open in new window

LVL 40

Jacques Bourgeois (James Burger)PresidentCommented: 2012-12-31

First, the DataAdapter and the DataSet are useless. You do not use them, don't you? You don't need them.

And chwong67 gave you the solution, without explaining. First you do not need a command for each field, you can do everything in only one command.

And there was one very important missing line in your code, line 19 in chwong67 code, the line where he executes the command. Simply creating a command does not execute it.