Wrox Programmer Forums
Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
|
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 21st, 2003, 12:02 PM
Authorized User
 
Join Date: Jun 2003
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default Updating Database w/Dataset -Failure

Dear Users why will my Database not update from this code. I fill my Dataset, which updates my DataGrid1, but after I click on a selected row it should update my database.

Why does this not work?

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Dim NTID As String
        Dim X As Long
        Dim Y As Long
        NTID = txtNTID.Text

        Dim Direct As String = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\DATA\ACCESS\13FloorEquipment.mdb"
        Dim strSQL As String = "SELECT [ID], [ItemName], [StartDate],[EndDate],[NTID] FROM(Requests) "
        strSQL = strSQL & "Where(([NTID]='" & NTID & "'));"

        Dim conn As OleDbConnection = New OleDbConnection(Direct)

        Dim Xa As OleDbDataAdapter

        Xa = New OleDbDataAdapter(strSQL, conn)
        conn.Open()

        Dim Xs As New DataSet()
        Xa.Fill(Xs, "Requests")

        '***********************
        'Dim dbTable As DataTable = Xs.Tables("Requests")
        Dim iLoop As Integer
        Dim iRow As Integer = 0
        Dim myRow As DataRow
        'Loop through the dataset
        iLoop = Xs.Tables("Requests").Rows.Count + 1
        'iLoop = Xs.Tables(0).Rows(iRow).Item(0) 'Xs.Tables("Requests").Rows.Count(+1)

        'load row
        myRow = Xs.Tables("Requests").Rows(iRow)

        'get selected row from table Vmake

        iRow = DataGrid1.CurrentCell.RowNumber
        If iRow > 0 Then
            'Remove selected row from Table Xs

            Xs.Tables("Requests").Rows.RemoveAt(iRow)


            'update Dataset

            Xa.Update(Xs, "Requests")
            Xs.AcceptChanges()


        Else

        End If



        '****************************



        'Xa.Fill(Xs, "Requests")
        'Call DatasetUpDate()
        DataGrid1.DataSource = Xs.Tables("Requests").DefaultView
        conn.Close()

    End Sub
 
Old November 25th, 2003, 06:41 PM
Authorized User
 
Join Date: Jun 2003
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am getting error message?? WHY

System.Data.OleDb.OleDbException' occurred in system.data.dll


From this code which returns Items from my Datagrid and tries to hard "DELETE" my Database.

Private Sub DataGrid_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.DoubleClick

        If Lost = 0 Then
            GoTo Quit
        End If
        Dim Irow As Integer
        Dim TRow As Integer
        Dim myRow As DataRow

        Irow = DataGrid1.Item(DataGrid1.CurrentCell)
        MsgBox(Irow)
        TRow = DataGrid1.CurrentCell.RowNumber
        MsgBox(TRow)


        Dim Link As String = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\DATA\ACCESS\13FloorEquipment.mdb"
        Dim ObjConn As New OleDbConnection(Link)
        Dim RstrSQL As String
        RstrSQL = "DELETE [ID],[Itemname],[StartDate],[Enddate] FROM (Requests) WHERE (([ID]='" & Irow & "'))"
        Dim ObjCom As New OleDbCommand(RstrSQL, ObjConn)
        MsgBox(RstrSQL)
        ObjConn.Open()

        ObjCom.ExecuteNonQuery()


        'Xs.Tables("Requests").Rows(0).Delete()
        ObjConn.Close()

Quit:
    End Sub
 
Old April 20th, 2006, 05:25 PM
Registered User
 
Join Date: Apr 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to vijaye7 Send a message via Yahoo to vijaye7
Default

Kenneth, I am also facing the same problem of DataAdapter not updating the database using Dataset. If you found whatz the problem, Can you please reply me too.???

 
Old April 21st, 2006, 05:34 AM
Authorized User
 
Join Date: Apr 2006
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

problem must be with dataset rows state

 
Old April 24th, 2006, 04:50 AM
Authorized User
 
Join Date: Feb 2006
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi guys,
following is the coding where i got error. when i update the modified rows in a datagrid the it shows error

"The timeout period elapsed prior to completion of the operation or the server is not responding"

please help me out i have treied a lot but got no success



Private Sub UpdateAvailRecord_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            sqlcon.Open()
            Adapt.SelectCommand = Cmd
            Cmd.Connection = sqlcon
            Cmd.CommandText = "Select * from targetpath with (NOLOCK)"
            Cmd.CommandType = CommandType.Text
            Adapt.TableMappings.Add("TablePath", "TabPath")
            Adapt.Fill(DtSet, "TabPath")
            Cmd.Cancel()
            If TelNumber.Length > 0 And Email.Length > 0 Then
                If Old = 0 Then
                    Cmd.CommandText = "select candidateid,resumecode,name,contactnumber,Email,ex perience,skills,category,Location,lastactivitydate bycandidate,resumetext,updatedon from candidatedetails with (NOLOCK) where contactnumber like '%" & TelNumber.Trim.Replace("'", "''").ToString & "%' or email like '%" & Email.Trim.Replace("'", "''").ToString & "%'"
                Else
                    Cmd.CommandText = "select candidateid,resumecode,name,contactnumber,Email,ex perience,skills,category,Location,lastactivitydate bycandidate,resumetext,updatedon from candidatedetailsold with (NOLOCK) where contactnumber like '%" & TelNumber.Trim.Replace("'", "''").ToString & "%' or email like '%" & Email.Trim.Replace("'", "''").ToString & "%'"
                End If
            ElseIf TelNumber.Length > 0 And Email.Trim.Length = 0 Then
                If Old = 0 Then
                    Cmd.CommandText = "select candidateid,resumecode,Name,contactnumber,Email,ex perience,skills,category,Location,lastactivitydate bycandidate,resumetext,updatedon from candidatedetails with (NOLOCK) where contactnumber like '%" & TelNumber.Trim.Replace("'", "''").ToString & "%'"
                Else
                    Cmd.CommandText = "select candidateid,resumecode,Name,contactnumber,Email,ex perience,skills,category,Location,lastactivitydate bycandidate,resumetext,updatedon from candidatedetailsold with (NOLOCK) where contactnumber like '%" & TelNumber.Trim.Replace("'", "''").ToString & "%'"
                End If
            ElseIf TelNumber.Length = 0 And Email.Trim.Length > 0 Then
                If Old = 0 Then
                    Cmd.CommandText = "select candidateid,resumecode,name,contactnumber,Email,ex perience,skills,category,Location,lastactivitydate bycandidate,resumetext,updatedon from candidatedetails with (NOLOCK) where email like '%" & Email.Trim.Replace("'", "''").ToString & "%'"
                Else
                    Cmd.CommandText = "select candidateid,resumecode,name,contactnumber,Email,ex perience,skills,category,Location,lastactivitydate bycandidate,resumetext,updatedon from candidatedetailsold with (NOLOCK) where email like '%" & Email.Trim.Replace("'", "''").ToString & "%'"
                End If
            End If
            'Query = Cmd.CommandText
            Cmd.CommandType = CommandType.Text
            Adapt.TableMappings.Add("Table", "UpdateRec")
            Adapt.Fill(DtSet, "UpdateRec")
            Cmd.Cancel()
            sqlcon.Close()
            DataGrid.SetDataBinding(DtSet, "UpdateRec")
            Dim DgTabStyle As New DataGridTableStyle
            DgTabStyle.MappingName = "UpdateRec"

            Dim DgTxtCol As New DataGridTextBoxColumn
            DgTxtCol.MappingName = "ResumeCode"
            DgTxtCol.HeaderText = "Resume Code"
            DgTxtCol.ReadOnly = True
            DgTabStyle.GridColumnStyles.Add(DgTxtCol)

            DgTxtCol = New DataGridTextBoxColumn
            DgTxtCol.MappingName = "Name"
            DgTxtCol.HeaderText = "Name"
            DgTxtCol.ReadOnly = True
            DgTabStyle.GridColumnStyles.Add(DgTxtCol)

            DgTxtCol = New DataGridTextBoxColumn
            DgTxtCol.MappingName = "Contactnumber"
            DgTxtCol.HeaderText = "Contact Number"
            DgTabStyle.GridColumnStyles.Add(DgTxtCol)

            DgTxtCol = New DataGridTextBoxColumn
            DgTxtCol.MappingName = "Email"
            DgTxtCol.HeaderText = "Email-Id"
            DgTabStyle.GridColumnStyles.Add(DgTxtCol)

            DgTxtCol = New DataGridTextBoxColumn
            DgTxtCol.MappingName = "Experience"
            DgTxtCol.HeaderText = "Experience"
            DgTabStyle.GridColumnStyles.Add(DgTxtCol)

            DgTxtCol = New DataGridTextBoxColumn
            DgTxtCol.MappingName = "Skills"
            DgTxtCol.HeaderText = "Skills"
            DgTabStyle.GridColumnStyles.Add(DgTxtCol)

            DgTxtCol = New DataGridTextBoxColumn
            DgTxtCol.MappingName = "Category"
            DgTxtCol.HeaderText = "Category"
            DgTabStyle.GridColumnStyles.Add(DgTxtCol)

            DgTxtCol = New DataGridTextBoxColumn
            DgTxtCol.MappingName = "Location"
            DgTxtCol.HeaderText = "Location"
            DgTabStyle.GridColumnStyles.Add(DgTxtCol)

            DgTxtCol = New DataGridTextBoxColumn
            DgTxtCol.MappingName = "LastActivitydatebycandidate"
            DgTxtCol.HeaderText = "Access Date"
            DgTabStyle.GridColumnStyles.Add(DgTxtCol)

            DgTxtCol = New DataGridTextBoxColumn
            DgTxtCol.MappingName = "Resumetext"
            DgTxtCol.HeaderText = "Resume"
            DgTabStyle.GridColumnStyles.Add(DgTxtCol)

            DgTxtCol = New DataGridTextBoxColumn
            DgTxtCol.MappingName = "updatedon"
            DgTxtCol.HeaderText = "Last Updated"
            DgTabStyle.GridColumnStyles.Add(DgTxtCol)
            DataGrid.TableStyles.Add(DgTabStyle)
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End Try
    End Sub

    Private Sub UpdateAvailRecord_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        DtSet.Clear()
        DtSet = Nothing
        Adapt.Dispose()
        Me.Dispose()
    End Sub
    Private Sub BtnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click
        If DtSet.HasChanges(DataRowState.Modified) Then
            Try
                If TxtExcelFile.Text.Length > 0 Then
                    If Old = 0 Then
                        File.Copy(TxtExcelFile.Text.Trim.ToString, DtSet.Tables("TabPath").Rows(0).Item("Path").ToStr ing & "\" & TxtExcelFile.Text.Trim.Substring(TxtExcelFile.Text .LastIndexOf("\") + 1), True)
                    Else
                        File.Copy(TxtExcelFile.Text.Trim.ToString, DtSet.Tables("TabPath").Rows(0).Item("Path").ToStr ing.Substring(0, InStrRev(DtSet.Tables("TabPath").Rows(0).Item("Pat h").ToString, "\", , CompareMethod.Text) - 1) & "\uploadold\" & TxtExcelFile.Text.Trim.Substring(TxtExcelFile.Text .LastIndexOf("\") + 1), True)
                    End If
                Else
                    MessageBox.Show("Please Choose The New Resume File", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                    Exit Sub
                End If
                'Dim Adpt As New SqlDataAdapter(Query, ConStr)
                'Adpt.TableMappings.Add("Table", "UpdateRec")
                Dim CmdBuild As SqlCommandBuilder = New SqlCommandBuilder(Adapt)
                'Adpt.UpdateCommand = CmdBuild.GetUpdateCommand
                Adapt.UpdateCommand = CmdBuild.GetUpdateCommand
                'Dim DSet As DataSet
                'DSet = DtSet.GetChanges(DataRowState.Modified)
                'Call Adpt.Update(DSet)
                Adapt.Update(DtSet.Tables("UpdateRec").GetChanges( DataRowState.Modified))
                DtSet.AcceptChanges()
                MsgBox("Database Updated")
                TxtExcelFile.Text = ""
                Me.Dispose()
            Catch Ex As Exception
                MsgBox(Ex.ToString)
            End Try
        End If
    End Sub





 
Old April 24th, 2006, 04:54 AM
Authorized User
 
Join Date: Feb 2006
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

my database is used in web application as well as in desktop application by several users.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating Database from Dataset hexOffender VB Databases Basics 0 October 30th, 2006 12:40 PM
updating a dataset hexOffender VB.NET 2002/2003 Basics 1 September 28th, 2006 05:13 PM
Updating Datagrid From Dataset Issue hannahforinton C# 2 May 30th, 2006 12:19 PM
problem in updating in dataset vohra_vikas2004 ADO.NET 1 June 28th, 2005 05:26 AM
NullReferenceException updating a DataSet rows yoord BOOK: Beginning ASP.NET 1.0 7 September 1st, 2004 03:49 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.