Solved

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

Posted on 2012-12-30
4
419 Views
Last Modified: 2013-01-02
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
0
Comment
Question by:GaiaGia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 9

Expert Comment

by:chwong67
ID: 38731867
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

0
 
LVL 9

Accepted Solution

by:
chwong67 earned 380 total points
ID: 38731870
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

0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 120 total points
ID: 38732082
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.
0
 

Author Comment

by:GaiaGia
ID: 38736285
Thank you very much both for pointing out my problem, I am done now.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question