Question asp.net vb can't update record using dataset

M

muupeetz

Member
Joined
Apr 30, 2009
Messages
5
Programming Experience
1-3
hello friends, i make my asp.net with vb 2005 as code behind file

i was able to retrieve the data using dataset from sql2005 table.. but when i update it using dataset the table always won't be updated :(

but if i insert the new data and then update it , it could. but with old data it can't


VB.NET: 
Imports TRAVEL
Imports System.Data.SqlClient
Imports System.Web.UI.WebControls
Imports Microsoft.VisualBasic
Imports System.Windows.Forms

Partial Class change
    Inherits System.Web.UI.Page
    Dim pvPlan, pvScheme, pvInsName1, pvinsName2 As String
    Dim pvSi1, pvSi2, pvSi3, pvSi4 As Integer
    Dim vDDL1 As String
    'Dim pvDest1, pvDest2, pvDest3, pvDest4, pvDest5 As String
    Dim ds As New DataSet
    Dim da As New SqlDataAdapter
    'Dim dt As New DataTable
    Dim cb As New SqlCommandBuilder

    Protected Sub formc_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles formc.Load
        'MsgBox(pvPN)
        getTblTravel()
    End Sub

Sub getTblTravel()
        Try
            'Dim pvDest1, pvDest2, pvDest3, pvDest4, pvDest5 As String
            'If con.State = ConnectionState.Closed Then
            '    con.Open()
            'End If
            'Dim cmd As New SqlCommand
            'cmd.Connection = con
            'cmd = New SqlCommand("insert into account([ac],[actype],[msid],[ms]) values ('" & Label9.Text & "','" & ComboBox3.SelectedValue & "','" & ComboBox1.SelectedValue & "','" & ComboBox2.SelectedValue & "')", myconnection)
            'cmd.CommandType = CommandType.Text
            'cmd.CommandText = "INSERT TRAVEL VALUES('" & _

            da = New SqlDataAdapter("SELECT * FROM TRAVEL WHERE PolNum ='" & pvPN & "'", con)
            cb = New SqlCommandBuilder(da)
            da.Fill(ds, "TRV")
            'dt = ds.Tables("TRV")
            For i As Integer = 0 To ds.Tables("TRV").Rows.Count - 1
                'pvDest1 = dt.Rows(i)(27)
                'pvDest2 = dt.Rows(i)(28)
                'pvDest3 = dt.Rows(i)(29)
                'pvDest4 = dt.Rows(i)(30)
                'pvDest5 = dt.Rows(i)(31)
                If ds.Tables("TRV").Rows(i)(13) = "--" Then
                    vInsYr2 = Nothing
                End If
                If ds.Tables("TRV").Rows(i)(14) = "--" Then
                    vInsYr3 = Nothing
                End If
                If ds.Tables("TRV").Rows(i)(15) = "--" Then
                    vInsYr4 = Nothing
                End If
                If ds.Tables("TRV").Rows(i)(26) = "ASEAN" Or ds.Tables("TRV").Rows(i)(26) = "Asean" Then
                    vIndexTeri = 0
                Else
                    vIndexTeri = 1
                End If

                txtPolNum.Text = ds.Tables("TRV").Rows(i)(0)
                rblPlan.SelectedValue = ds.Tables("TRV").Rows(i)(1)
                rblScheme.SelectedValue = ds.Tables("TRV").Rows(i)(2)
                txtSI1.Text = ds.Tables("TRV").Rows(i)(3)
                txtSI2.Text = ds.Tables("TRV").Rows(i)(4)
                txtSI3.Text = ds.Tables("TRV").Rows(i)(5)
                txtSI4.Text = ds.Tables("TRV").Rows(i)(6)
                txtInsName1.Text = ds.Tables("TRV").Rows(i)(8)
                txtInsName2.Text = ds.Tables("TRV").Rows(i)(9)
                txtInsName3.Text = ds.Tables("TRV").Rows(i)(10)
                txtInsName4.Text = ds.Tables("TRV").Rows(i)(11)
                CalPopIns1.SelectedDate = ds.Tables("TRV").Rows(i)(12)
                CalPopIns2.SelectedDate = vInsYr2
                CalPopIns3.SelectedDate = vInsYr3
                CalPopIns4.SelectedDate = vInsYr4
                txtInsPasp1.Text = ds.Tables("TRV").Rows(i)(16)
                txtInsPasp2.Text = ds.Tables("TRV").Rows(i)(17)
                txtInsPasp3.Text = ds.Tables("TRV").Rows(i)(18)
                txtInsPasp4.Text = ds.Tables("TRV").Rows(i)(19)
                txtAddr.Text = ds.Tables("TRV").Rows(i)(20)
                txtPhone.Text = ds.Tables("TRV").Rows(i)(21)
                txtNation.Text = ds.Tables("TRV").Rows(i)(22)
                txtBen.Text = ds.Tables("TRV").Rows(i)(23)
                txtRel.Text = ds.Tables("TRV").Rows(i)(24)
                txtOrigin.Text = ds.Tables("TRV").Rows(i)(25)
                DDlTeri.SelectedIndex = vIndexTeri
                pvDest1 = ds.Tables("TRV").Rows(i)(27)
                pvDest2 = ds.Tables("TRV").Rows(i)(28)
                pvDest3 = ds.Tables("TRV").Rows(i)(29)
                pvDest4 = ds.Tables("TRV").Rows(i)(30)
                pvDest5 = ds.Tables("TRV").Rows(i)(31)

                'FillComboBoxInt(ddlDest1i, "TRAVEL_COUNTRY")
                FillComboBoxInt(ddlDest2i, "TRAVEL_COUNTRY")
                'FillComboBoxInt(ddlDest3i, "TRAVEL_COUNTRY")
                'FillComboBoxInt(ddlDest4i, "TRAVEL_COUNTRY")
                'FillComboBoxInt(ddlDest5i, "TRAVEL_COUNTRY")

                retTblCountry(ddlDest1i, "TRAVEL_COUNTRY")
                'retTblCountry(ddlDest2i, "TRAVEL_COUNTRY")
                retTblCountry(ddlDest3i, "TRAVEL_COUNTRY")
                retTblCountry(ddlDest4i, "TRAVEL_COUNTRY")
                retTblCountry(ddlDest5i, "TRAVEL_COUNTRY")

                FillComboBoxAse(ddlDest1a, "TRAVEL_COUNTRY")
                FillComboBoxAse(ddlDest2a, "TRAVEL_COUNTRY")
                FillComboBoxAse(ddlDest3a, "TRAVEL_COUNTRY")
                FillComboBoxAse(ddlDest4a, "TRAVEL_COUNTRY")
                FillComboBoxAse(ddlDest5a, "TRAVEL_COUNTRY")
                If ds.Tables("TRV").Rows(i)(26) = "INTERNATIONAL" Or ds.Tables("TRV").Rows(i)(26) = "International" Then
                    pnlDestInt.Visible = True
                    pnlDestAsean.Visible = False
                    ddlDest1i.SelectedItem.Value = pvDest1
                    ddlDest1i.SelectedItem.Text = pvDest1
                    ddlDest3i.SelectedItem.Value = pvDest3
                    ddlDest3i.SelectedItem.Text = pvDest3
                    ddlDest4i.SelectedItem.Value = pvDest4
                    ddlDest4i.SelectedItem.Text = pvDest4
                    ddlDest5i.SelectedItem.Value = pvDest5
                    ddlDest5i.SelectedItem.Text = pvDest5
                Else
                    pnlDestAsean.Visible = True
                    pnlDestInt.Visible = False
                    ddlDest1a.SelectedValue = pvDest1
                    ddlDest2a.SelectedValue = pvDest2
                    ddlDest3a.SelectedValue = pvDest3
                    ddlDest4a.SelectedValue = pvDest4
                    ddlDest5a.SelectedValue = pvDest5
                End If
                CalPopStart.SelectedDate = ds.Tables("TRV").Rows(i)(32)
                CalPopEnd.SelectedDate = ds.Tables("TRV").Rows(i)(33)
                lblTrip.Text = ds.Tables("TRV").Rows(i)(34)
                CalPopIssue.SelectedDate = ds.Tables("TRV").Rows(i)(35)
                txtIll.Text = ds.Tables("TRV").Rows(i)(36)
                txtCur.Text = ds.Tables("TRV").Rows(i)(37)
                rblPackage.SelectedValue = ds.Tables("TRV").Rows(i)(38)
                txtPrem.Text = ds.Tables("TRV").Rows(i)(39)
                txtPolcost.Text = ds.Tables("TRV").Rows(i)(40)
                txtPremTot.Text = ds.Tables("TRV").Rows(i)(41)
                lblAge1.Text = ds.Tables("TRV").Rows(i)(42)
                lblAge2.Text = ds.Tables("TRV").Rows(i)(43)
                lblAge3.Text = ds.Tables("TRV").Rows(i)(44)
                lblAge4.Text = ds.Tables("TRV").Rows(i)(45)
                lblTravelAgen.Text = ds.Tables("TRV").Rows(i)(46)
                txtStafName.Text = ds.Tables("TRV").Rows(i)(47)
                txtHpBrand.Text = ds.Tables("TRV").Rows(i)(48)
                txtHpType.Text = ds.Tables("TRV").Rows(i)(49)
                txtCamBrand.Text = ds.Tables("TRV").Rows(i)(50)
                txtCamType.Text = ds.Tables("TRV").Rows(i)(51)
                txtNbBrand.Text = ds.Tables("TRV").Rows(i)(52)
                txtNbType.Text = ds.Tables("TRV").Rows(i)(53)
            Next

            con.Close()
        Catch ex As Exception
            MsgBox("getTblTravel() : " & ex.Message)
        End Try
    End Sub

Sub UpdateData()

        If txtPolNum.Text = "" Then
            MsgBox("Please Fill Policy Number", MsgBoxStyle.Exclamation, "NOTICE")
            txtPolNum.Focus()
            Exit Sub
        End If
        If txtStafName.Text = "" Then
            MsgBox("Please Fill Staff Name", MsgBoxStyle.Exclamation, "NOTICE")
            txtStafName.Focus()
            Exit Sub
        End If
        If txtInsName1.Text = "" Then
            MsgBox("Please Fill Insured Name 1 ", MsgBoxStyle.Exclamation, "NOTICE")
            txtStafName.Focus()
            Exit Sub
        End If
        If txtInsPasp1.Text = "" Then
            MsgBox("Please Fill Passport 1", MsgBoxStyle.Exclamation, "NOTICE")
            txtInsPasp1.Focus()
            Exit Sub
        End If
        If txtAddr.Text = "" Then
            MsgBox("Please Fill Address ", MsgBoxStyle.Exclamation, "NOTICE")
            txtStafName.Focus()
            Exit Sub
        End If
        If CalPopIns2.SelectedDate = "12:00:00 AM" Then
            pvInsDob2 = 0
        End If
        If CalPopIns3.SelectedDate = "12:00:00 AM" Then
            pvInsDob3 = 0
        End If
        If CalPopIns4.SelectedDate = "12:00:00 AM" Then
            pvInsDob4 = 0
        End If

        Try
            If MsgBox("Sure to Save?", MsgBoxStyle.YesNo, _
            "Confirmation") = MsgBoxResult.Yes Then
                'If con.State = ConnectionState.Closed Then
                '    con.Open()
                'End If

                For i As Integer = 0 To ds.Tables("TRV").Rows.Count - 1
                    If Trim(Microsoft.VisualBasic.Left(txtPolNum.Text, 1) = Microsoft.VisualBasic.Left(Trim(ds.Tables("TRV").Rows(i)(0)), 1)) Then

                        If CalPopIns2.SelectedDate = Nothing Then
                            vInsYr2 = "00:00:00 AM"
                        End If
                        If CalPopIns3.SelectedDate = Nothing Then
                            vInsYr3 = "00:00:00 AM"
                        End If
                        If CalPopIns4.SelectedDate = Nothing Then
                            vInsYr4 = "00:00:00 AM"
                        End If
                        If DDlTeri.SelectedValue = "Asean" Then
                            vIndexTeri = 0
                        Else
                            vIndexTeri = 1
                        End If

                        ds.Tables("TRV").Rows(i)(0) = txtPolNum.Text
                        ds.Tables("TRV").Rows(i)(1) = rblPlan.SelectedValue
                        ds.Tables("TRV").Rows(i)(2) = rblScheme.SelectedValue
                        ds.Tables("TRV").Rows(i)(3) = txtSI1.Text
                        ds.Tables("TRV").Rows(i)(4) = txtSI2.Text
                        ds.Tables("TRV").Rows(i)(5) = txtSI3.Text
                        ds.Tables("TRV").Rows(i)(6) = txtSI4.Text
                        ds.Tables("TRV").Rows(i)(8) = txtInsName1.Text
                        ds.Tables("TRV").Rows(i)(9) = txtInsName2.Text
                        ds.Tables("TRV").Rows(i)(10) = txtInsName3.Text
                        ds.Tables("TRV").Rows(i)(11) = txtInsName4.Text
                        ds.Tables("TRV").Rows(i)(12) = CalPopIns1.SelectedDate
                        ds.Tables("TRV").Rows(i)(13) = vInsYr2
                        ds.Tables("TRV").Rows(i)(14) = vInsYr3
                        ds.Tables("TRV").Rows(i)(15) = vInsYr4
                        ds.Tables("TRV").Rows(i)(16) = txtInsPasp1.Text
                        ds.Tables("TRV").Rows(i)(17) = txtInsPasp2.Text
                        ds.Tables("TRV").Rows(i)(18) = txtInsPasp3.Text
                        ds.Tables("TRV").Rows(i)(19) = txtInsPasp4.Text
                        ds.Tables("TRV").Rows(i)(20) = Trim(txtAddr.Text)
                        ds.Tables("TRV").Rows(i)(21) = txtPhone.Text
                        ds.Tables("TRV").Rows(i)(22) = txtNation.Text
                        ds.Tables("TRV").Rows(i)(23) = txtBen.Text
                        ds.Tables("TRV").Rows(i)(24) = txtRel.Text
                        ds.Tables("TRV").Rows(i)(25) = txtOrigin.Text
                        ds.Tables("TRV").Rows(i)(26) = DDlTeri.SelectedValue
                        ds.Tables("TRV").Rows(i)(27) = pvDest1
                        ds.Tables("TRV").Rows(i)(28) = pvDest2
                        ds.Tables("TRV").Rows(i)(29) = pvDest3
                        ds.Tables("TRV").Rows(i)(30) = pvDest4
                        ds.Tables("TRV").Rows(i)(31) = pvDest5
                        ds.Tables("TRV").Rows(i)(32) = CalPopStart.SelectedDate
                        ds.Tables("TRV").Rows(i)(33) = CalPopEnd.SelectedDate
                        ds.Tables("TRV").Rows(i)(34) = lblTrip.Text
                        ds.Tables("TRV").Rows(i)(35) = CalPopIssue.SelectedDate
                        ds.Tables("TRV").Rows(i)(36) = txtIll.Text
                        ds.Tables("TRV").Rows(i)(37) = txtCur.Text
                        ds.Tables("TRV").Rows(i)(38) = rblPackage.SelectedValue
                        ds.Tables("TRV").Rows(i)(39) = txtPrem.Text
                        ds.Tables("TRV").Rows(i)(40) = txtPolcost.Text
                        ds.Tables("TRV").Rows(i)(41) = txtPremTot.Text
                        ds.Tables("TRV").Rows(i)(42) = lblAge1.Text
                        ds.Tables("TRV").Rows(i)(43) = lblAge2.Text
                        ds.Tables("TRV").Rows(i)(44) = lblAge3.Text
                        ds.Tables("TRV").Rows(i)(45) = lblAge4.Text
                        ds.Tables("TRV").Rows(i)(46) = lblTravelAgen.Text
                        ds.Tables("TRV").Rows(i)(47) = txtStafName.Text
                        ds.Tables("TRV").Rows(i)(48) = txtHpBrand.Text
                        ds.Tables("TRV").Rows(i)(49) = txtHpType.Text
                        ds.Tables("TRV").Rows(i)(50) = txtCamBrand.Text
                        ds.Tables("TRV").Rows(i)(51) = txtCamType.Text
                        ds.Tables("TRV").Rows(i)(52) = txtNbBrand.Text
                        ds.Tables("TRV").Rows(i)(53) = txtNbType.Text
                        da.Update(ds.Tables("TRV"))
                    End If
                Next
               

                MsgBox("Data has been Updated..", MsgBoxStyle.Information, "NOTICE")
                lblMsg.Text = "Data has been Updated.."
               
            Else
                MsgBox("canceled  by user", MsgBoxStyle.Information, "NOTICE")
                btnSubmit.Enabled = True

            End If

                con.Close()
            getTblTravel()
                'System.Web.Security.FormsAuthentication.SignOut()
                'Response.Redirect("login.aspx")

            btnSubmit.Enabled = True
            btnPrint.Enabled = True


        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "")
        End Try

    End Sub
i wonder where's my fault.. :(
Thank you so much for any replies :D
 
Sort by date Sort by votes
Do you get an error message when you try to update or does nothing at all happen?

Did you put a break on your code and step through it to see what it is actually doing?
 
Upvote 0 Downvote

Similar threads

Zeusmax
  • Question
treeview problem, third level index error
Replies
13
Views
950
Zeusmax
Zeusmax
chacham
  • Question
Adding a handler that uses a variable, uses the value of the variable at event time
Replies
3
Views
529
JohnH
JohnH
V
  • Question
Question Not getting any value in cboSubCategory?
Replies
2
Views
927
jmcilhinney
jmcilhinney
I
  • Locked
  • Question
Generar reporte Excel desde un datagridview
Replies
1
Views
410
JohnH
JohnH
anybloodyid
  • Question
Resolved Store a variable
Replies
7
Views
692
jmcilhinney
jmcilhinney

Share this page

Latest posts

Back
Top