none
The Code Run Well but It Can't Update DataBase..Update. RRS feed

  • Question

  • Hi,

    Please I need somme help here,

    This  Code Run Well but It Can't  Update DataBase..Update.

     Private Sub Totaux1()
            Try
                Using cn As New OleDb.OleDbConnection With
                    {
                        .ConnectionString = Builder.ConnectionString
                    }
                    Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
    
                        cmd.CommandText = "Select Sum (IIf(Clmn1 Is Null, 0,Clmn1)) As Sum1,
                                            Sum (IIf(Colmn2 Is Null, 0,Colmn2)) As Sum2,
                                            Sum (IIf(Clmn3 Is Null,0,Clmn3)) As Sum3,
                                            FROM  Table1"
    
                        cn.Open()
                        Dim Rdr As OleDb.OleDbDataReader = cmd.ExecuteReader
                        If Rdr.HasRows Then
                            While Rdr.Read
    
                                Dim Sum1 As Integer = Rdr.Item("Sum1")
                                Dim Sum2 As Integer = Rdr.Item("Sum2")
                                Dim Sum3 As Integer = Rdr.Item("Sum3")
                        
                                 cmd.CommandText = "Update Table2 Set TotalClmn1=@d2,TotalClmn2=@d3,TotalClmn3=@d3,
                                                     Where Total=@d1"
                                cmd.Parameters.AddWithValue("@d1","ToTal1")
                                cmd.Parameters.AddWithValue("@d2", Rdr.Item("Sum1"))
                                cmd.Parameters.AddWithValue("@d3", Rdr.Item("Sum2"))
                                cmd.Parameters.AddWithValue("@d4", Rdr.Item("Sum3"))
                                cmd.ExecuteNonQuery()
                                Dgw2.Refresh()
                                cmd.CommandText = "Update Table3 Set Totaux=@d2 Where ToTal=@d1"
                                cmd.Parameters.AddWithValue("@d1", "Total1")
                                cmd.Parameters.AddWithValue("@d2", Rdr.Item("Sum3"))    
                                cmd.ExecuteNonQuery()
                                Dgw2.Refresh()
                            End While
                            If Not Rdr Is Nothing Then
                                Rdr.Close()
                            End If
                            Exit Sub
                        End If
                         Rdr.Close()
                     
                    End Using
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    So Thank you very Much .


    • Edited by Bajtitou Thursday, March 7, 2019 6:18 AM
    Wednesday, March 6, 2019 11:02 PM

Answers

  • Hello,

    Here is a bare bones example (where I would use my connection class or my connection class in a NuGet package as in my signature)

    • Note what I use reader.Get... rather than Reader.Item.
    • How I'm asserting if the ExecuteNonQuery was successful or not and report in a MessageBox.
    • Removal of the while, if the intent was to update multiple rows let's try just one first.
    • The use of ? for parameters as named parameters are only a benefit to the programmer and have not meaning to MS-Access.
    • In code read my comment about the parameter using ToTal1.
    • If you still have issues you could upload your project to Microsoft OneDrive in a .Zip file and I will take a look at it this morning but first try what I have suggested.
    • Remember I don't have your database so everything I presented could not be tested.
    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form1
    
    End Class
    Public Class DataOperations
        Private builder As OleDbConnectionStringBuilder
    
        Public Sub New()
            builder = New OleDbConnectionStringBuilder With
                {
                    .Provider = "Microsoft.ACE.OLEDB.12.0",
                    .DataSource = Path.Combine(Application.StartupPath, "ED_OISEAUX.accdb")
                }
        End Sub
        Private Sub Totaux1()
    
            Dim upDateResult As Integer = 0
    
            Try
                Using cn As New OleDbConnection With
                    {
                        .ConnectionString = builder.ConnectionString
                    }
    
                    Using cmd As New OleDbCommand With {.Connection = cn}
    
                        cmd.CommandText = "SELECT Sum (IIf(Clmn1 Is Null, 0,Clmn1)) As Sum1,
                                            Sum (IIf(Colmn2 Is Null, 0,Colmn2)) As Sum2,
                                            Sum (IIf(Clmn3 Is Null,0,Clmn3)) As Sum3,
                                            FROM  Table1"
    
                        cn.Open()
    
                        Dim reader As OleDbDataReader = cmd.ExecuteReader
    
                        If reader.HasRows Then
    
                            reader.Read()
    
                            Dim sum1 As Integer = reader.GetInt32(0)
                            Dim sum2 As Integer = reader.GetInt32(1)
                            Dim sum3 As Integer = reader.GetInt32(2)
    
                            cmd.CommandText = "UPDATE Table2 " &
                                              "SET " &
                                              "TotalClmn1 = @d2," &
                                              "TotalClmn1 = @d3," &
                                              "TotalClmn1 = @d3, " &
                                              "WHERE Total=@d1"
    
                            cmd.Parameters.AddWithValue("?", sum1)
                            cmd.Parameters.AddWithValue("?", sum2)
                            cmd.Parameters.AddWithValue("?", sum2)
    
                            '
                            ' In the following parameter, is the value going to be "ToTal1"
                            ' or is the value suppose to be something else as you have this
                            ' code this parameter value is literally ToTal1 I would suspect
                            ' this is not correct.
                            '
                            cmd.Parameters.AddWithValue("?", "ToTal1")
    
    
                            cmd.Parameters.AddWithValue("@d4", sum3)
    
                            upDateResult = cmd.ExecuteNonQuery()
    
                            If upDateResult <> 1 Then
                                MessageBox.Show("Not updated")
                                Exit Sub
                            Else
                                MessageBox.Show("Updated")
                            End If
    
                            cmd.Parameters.Clear()
    
                            cmd.CommandText = "Update Table3 Set Totaux= @d2 Where ToTal = @d1"
    
                            cmd.Parameters.AddWithValue("@d2", sum3)
    
                            '
                            ' Same as above where I commented
                            '
                            cmd.Parameters.AddWithValue("@d1", "Total1")
    
                            upDateResult = cmd.ExecuteNonQuery()
    
                            If upDateResult = 1 Then
                                MessageBox.Show("Updated")
                            Else
                                MessageBox.Show("Not updated")
                            End If
    
                        Else
                            MessageBox.Show("No records match condition")
                        End If
                    End Using
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error",
                                MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Bajtitou Friday, March 8, 2019 7:30 AM
    Thursday, March 7, 2019 12:57 PM
    Moderator
  • Hi,
    here is a working well demo with your code:

    Module Module1
      Sub Main()
        Try
          Dim c As New Demo
          c.Execute()
        Catch ex As Exception
          Console.WriteLine(ex.ToString)
        End Try
        Console.WriteLine("Continue enter key")
        Console.ReadKey()
      End Sub
      Friend Class Demo
    
        Private cnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb;Persist Security Info=True"
    
        Friend Sub Execute()
          CreateDBs()
          Totaux1()
        End Sub
    
        Private Sub CreateDBs()
          Using cn As New OleDb.OleDbConnection(cnString)
            cn.Open()
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
              ' delete previous version
              cmd.CommandText = "DROP Table Table1"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              cmd.CommandText = "DROP Table Table2"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              cmd.CommandText = "DROP Table Table3"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              ' Create Tables
              cmd.CommandText = "CREATE Table Table1([Clmn1] Integer,[Colmn2] Integer,[Clmn3] Integer)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "CREATE Table Table2([Total] VARCHAR(255),[TotalClmn1] Integer,[TotalClmn2] Integer,[TotalClmn3] Integer)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "CREATE Table Table3([ToTal] VARCHAR(255),[Totaux] Integer)"
              cmd.ExecuteNonQuery()
              ' Insert values
              cmd.CommandText = "INSERT INTO Table1([Clmn1],[Colmn2],[Clmn3]) VALUES(1,2,3)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "INSERT INTO Table1([Clmn1],[Colmn2],[Clmn3]) VALUES(1,2,3)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "INSERT INTO Table1([Clmn1],[Colmn2],[Clmn3]) VALUES(1,2,3)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "INSERT INTO Table2([Total],[TotalClmn1]) VALUES(""Total1"",0)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "INSERT INTO Table3([Total],[Totaux]) VALUES(""Total1"",0)"
              cmd.ExecuteNonQuery()
            End Using
          End Using
        End Sub
        Private Sub Totaux1()
          Using cn As New OleDb.OleDbConnection(cnString)
            cn.Open()
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
              cmd.CommandText = "Select Sum(IIf(Clmn1 Is Null, 0,Clmn1)) As Sum1,
                                        Sum(IIf(Colmn2 Is Null, 0,Colmn2)) As Sum2,
                                        Sum(IIf(Clmn3 Is Null,0,Clmn3)) As Sum3
                                        FROM Table1"
              Dim Sum1 As Integer = 0
              Dim Sum2 As Integer = 0
              Dim Sum3 As Integer = 0
              Dim Rdr As OleDb.OleDbDataReader = cmd.ExecuteReader
              If Rdr.HasRows Then
                While Rdr.Read
                  Sum1 = CType(Rdr.Item("Sum1"), Integer)
                  Sum2 = CType(Rdr.Item("Sum2"), Integer)
                  Sum3 = CType(Rdr.Item("Sum3"), Integer)
                End While
              End If
              Rdr.Close()
              cmd.CommandText = "Update Table2 Set TotalClmn1=?,TotalClmn2=?,TotalClmn3=?
                                                     Where Total=?"
              cmd.Parameters.AddWithValue("@d2", Sum1)
              cmd.Parameters.AddWithValue("@d3", Sum2)
              cmd.Parameters.AddWithValue("@d4", Sum3)
              cmd.Parameters.AddWithValue("@d1", "Total1")
              cmd.ExecuteNonQuery()
              'Dgw2.Refresh()
              cmd.Parameters.Clear()
              cmd.CommandText = "Update Table3 Set Totaux=? Where Total=?"
              cmd.Parameters.AddWithValue("@d2", Sum3)
              cmd.Parameters.AddWithValue("@d1", "Total1")
              cmd.ExecuteNonQuery()
              'Dgw2.Refresh()
            End Using
          End Using
        End Sub
      End Class
    End Module


    --
    Viele Grüsse / Best Regards
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Friday, March 8, 2019 6:50 AM

All replies

  • Hello,

    The first thing I noticed is that in the first update statement @d1 parameter is not in order. OleDb data provider working with Access database the parameters are in ordinal position.

    Here is what to try along with clearing the parameters between each update. I changed the parm order.

    Private Sub Totaux1() Try Using cn As New OleDb.OleDbConnection With { .ConnectionString = Builder.ConnectionString } Using cmd As New OleDb.OleDbCommand With {.Connection = cn} cmd.CommandText = "Select Sum (IIf(Clmn1 Is Null, 0,Clmn1)) As Sum1, Sum (IIf(Colmn2 Is Null, 0,Colmn2)) As Sum2, Sum (IIf(Clmn3 Is Null,0,Clmn3)) As Sum3, FROM Table1" cn.Open() Dim Rdr As OleDb.OleDbDataReader = cmd.ExecuteReader If Rdr.HasRows Then While Rdr.Read Dim Sum1 As Integer = Rdr.Item("Sum1") Dim Sum2 As Integer = Rdr.Item("Sum2") Dim Sum3 As Integer = Rdr.Item("Sum3") cmd.CommandText = "Update Table2 Set TotalClmn1=@d2,TotalClmn1=@d3,TotalClmn1=@d3, Where Total=@d1" cmd.Parameters.AddWithValue("@d2", Rdr.Item("Sum1")) cmd.Parameters.AddWithValue("@d3", Rdr.Item("Sum2")) cmd.Parameters.AddWithValue("@d3", Rdr.Item("Sum2")) cmd.Parameters.AddWithValue("@d1","ToTal1") cmd.Parameters.AddWithValue("@d4", Rdr.Item("Sum3")) cmd.ExecuteNonQuery()

    cmd.Parameters.Clear()

    Dgw2.Refresh() cmd.CommandText = "Update Table3 Set Totaux=@d2 Where ToTal=@d1" cmd.Parameters.AddWithValue("@d2", Rdr.Item("Sum3")) cmd.Parameters.AddWithValue("@d1", "Total1") cmd.ExecuteNonQuery() Dgw2.Refresh() End While If Not Rdr Is Nothing Then Rdr.Close() End If Exit Sub End If Rdr.Close() End Using End Using Catch ex As Exception MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub



    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Wednesday, March 6, 2019 11:17 PM
    Moderator
  • Unity.Abstractionsis the datadase file located? If it is located in C:\program files directory with the program.exe, then the O/S is going to block any writing to the file.
    Thursday, March 7, 2019 3:46 AM
  • Hi,
    using oledb the parameters in command-object must be in the same order as in the sql.

    …
                                 cmd.CommandText = "Update Table2 Set TotalClmn1=?,TotalClmn1=?,TotalClmn1=?,
                                                     Where Total=?"
                                cmd.Parameters.AddWithValue("@d2", Rdr.Item("Sum1"))
                                cmd.Parameters.AddWithValue("@d3", Rdr.Item("Sum2"))
                                cmd.Parameters.AddWithValue("@d4", Rdr.Item("Sum3"))
                                cmd.Parameters.AddWithValue("@d1","ToTal1")
    …


    Please, use OPTION STRICT ON.


    --
    Viele Grüsse / Best Regards
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks



    Thursday, March 7, 2019 6:05 AM
  • Hello,

    The first thing I noticed is that in the first update statement @d1 parameter is not in order. OleDb data provider working with Access database the parameters are in ordinal position.

    Here is what to try along with clearing the parameters between each update. I changed the parm order.

    Hi,

    Thanks for your answer,

    I tried with your suggestion but it is not updated.

    So thankyou very much.

    Thursday, March 7, 2019 7:19 AM
  • Unity.Abstractionsis the datadase file located? If it is located in C:\program files directory with the program.exe, then the O/S is going to block any writing to the file.

    Hi ,

    Thank you for your answer ,

    here is The Path Of Data base:

    C:\Users\Desktop\Application\Application\bin\Debug\DataBase.accdb

    When I save data It save normally without problem.

    THank you very much.

    Thursday, March 7, 2019 7:24 AM
  • Hi,
    using oledb the parameters in command-object must be in the same order as in the sql.

    …
                                 cmd.CommandText = "Update Table2 Set TotalClmn1=?,TotalClmn1=?,TotalClmn1=?,
                                                     Where Total=?"
                                cmd.Parameters.AddWithValue("@d2", Rdr.Item("Sum1"))
                                cmd.Parameters.AddWithValue("@d3", Rdr.Item("Sum2"))
                                cmd.Parameters.AddWithValue("@d4", Rdr.Item("Sum3"))
                                cmd.Parameters.AddWithValue("@d1","ToTal1")
    …

    Hi, 

    Thank you for answer,

    I do that but it is not Updating .

    so thank you very Much.

    Thursday, March 7, 2019 7:27 AM
  • Hi,

    It may be because there is no data in the table:

    If Rdr.HasRows Then
    ...
    else
    MsgBox("No value in the datatable")
    End if

    Best  Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 7, 2019 8:59 AM
  • Hi,

    It may be because there is no data in the table:

    If Rdr.HasRows Then
    ...
    else
    MsgBox("No value in the datatable")
    End if

    Best  Regards,

    Alex

    Hi,

    Thank you very much ,

    But there is data in the table.

    Best Regards .

    Thursday, March 7, 2019 9:32 AM
  • Okay, can you change 

    cmd.ExecuteNonQuery 

    to

    Dim Result As Integer = cmd.ExecuteNonQuery()

    Then

    MessageBox.Show(Result.ToString())

    If the variable Result is 1 the UPDATE worked, otherwise the UPDATE failed. Add to this, if Result is 1 and you run the project again without seeing results it's possible that if the database is in the project try selecting the database, select properties, select property "Copy to Output Directory" and if the value is "Copy always" this means each time you run the project it copies over the last one in the Bin folder so change to "Copy If Newer" and try the operation again.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 7, 2019 10:05 AM
    Moderator
  • Okay, can you change 

    cmd.ExecuteNonQuery 

    to

    Dim Result As Integer = cmd.ExecuteNonQuery()

    Then

    MessageBox.Show(Result.ToString())

    If the variable Result is 1 the UPDATE worked, otherwise the UPDATE failed. Add to this, if Result is 1 and you run the project again without seeing results it's possible that if the database is in the project try selecting the database, select properties, select property "Copy to Output Directory" and if the value is "Copy always" this means each time you run the project it copies over the last one in the Bin folder so change to "Copy If Newer" and try the operation again.

    Hi, 

    I do the change But, No Message no result.

    I give this information :

    1/ The Database It is in The Bin folder;\bin\Debug\DataBase.accdb

    Module ModuleDeConnection
        Public Builder As New OleDb.OleDbConnectionStringBuilder With
           {
             .Provider = "Microsoft.ACE.OLEDB.12.0",
             .DataSource = IO.Path.Combine(Application.StartupPath, "ED_OISEAUX.accdb")
           }
    End Module

    2/ App cofig:

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
        <configSections>
        </configSections>
        <connectionStrings/>
        <startup>
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.1"/>
        </startup>
    </configuration>
    CapDbase

    Thank you very much .

    Thursday, March 7, 2019 12:02 PM
  • Hello,

    Here is a bare bones example (where I would use my connection class or my connection class in a NuGet package as in my signature)

    • Note what I use reader.Get... rather than Reader.Item.
    • How I'm asserting if the ExecuteNonQuery was successful or not and report in a MessageBox.
    • Removal of the while, if the intent was to update multiple rows let's try just one first.
    • The use of ? for parameters as named parameters are only a benefit to the programmer and have not meaning to MS-Access.
    • In code read my comment about the parameter using ToTal1.
    • If you still have issues you could upload your project to Microsoft OneDrive in a .Zip file and I will take a look at it this morning but first try what I have suggested.
    • Remember I don't have your database so everything I presented could not be tested.
    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form1
    
    End Class
    Public Class DataOperations
        Private builder As OleDbConnectionStringBuilder
    
        Public Sub New()
            builder = New OleDbConnectionStringBuilder With
                {
                    .Provider = "Microsoft.ACE.OLEDB.12.0",
                    .DataSource = Path.Combine(Application.StartupPath, "ED_OISEAUX.accdb")
                }
        End Sub
        Private Sub Totaux1()
    
            Dim upDateResult As Integer = 0
    
            Try
                Using cn As New OleDbConnection With
                    {
                        .ConnectionString = builder.ConnectionString
                    }
    
                    Using cmd As New OleDbCommand With {.Connection = cn}
    
                        cmd.CommandText = "SELECT Sum (IIf(Clmn1 Is Null, 0,Clmn1)) As Sum1,
                                            Sum (IIf(Colmn2 Is Null, 0,Colmn2)) As Sum2,
                                            Sum (IIf(Clmn3 Is Null,0,Clmn3)) As Sum3,
                                            FROM  Table1"
    
                        cn.Open()
    
                        Dim reader As OleDbDataReader = cmd.ExecuteReader
    
                        If reader.HasRows Then
    
                            reader.Read()
    
                            Dim sum1 As Integer = reader.GetInt32(0)
                            Dim sum2 As Integer = reader.GetInt32(1)
                            Dim sum3 As Integer = reader.GetInt32(2)
    
                            cmd.CommandText = "UPDATE Table2 " &
                                              "SET " &
                                              "TotalClmn1 = @d2," &
                                              "TotalClmn1 = @d3," &
                                              "TotalClmn1 = @d3, " &
                                              "WHERE Total=@d1"
    
                            cmd.Parameters.AddWithValue("?", sum1)
                            cmd.Parameters.AddWithValue("?", sum2)
                            cmd.Parameters.AddWithValue("?", sum2)
    
                            '
                            ' In the following parameter, is the value going to be "ToTal1"
                            ' or is the value suppose to be something else as you have this
                            ' code this parameter value is literally ToTal1 I would suspect
                            ' this is not correct.
                            '
                            cmd.Parameters.AddWithValue("?", "ToTal1")
    
    
                            cmd.Parameters.AddWithValue("@d4", sum3)
    
                            upDateResult = cmd.ExecuteNonQuery()
    
                            If upDateResult <> 1 Then
                                MessageBox.Show("Not updated")
                                Exit Sub
                            Else
                                MessageBox.Show("Updated")
                            End If
    
                            cmd.Parameters.Clear()
    
                            cmd.CommandText = "Update Table3 Set Totaux= @d2 Where ToTal = @d1"
    
                            cmd.Parameters.AddWithValue("@d2", sum3)
    
                            '
                            ' Same as above where I commented
                            '
                            cmd.Parameters.AddWithValue("@d1", "Total1")
    
                            upDateResult = cmd.ExecuteNonQuery()
    
                            If upDateResult = 1 Then
                                MessageBox.Show("Updated")
                            Else
                                MessageBox.Show("Not updated")
                            End If
    
                        Else
                            MessageBox.Show("No records match condition")
                        End If
                    End Using
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error",
                                MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Bajtitou Friday, March 8, 2019 7:30 AM
    Thursday, March 7, 2019 12:57 PM
    Moderator
  • Hello,

    Here is a bare bones example (where I would use my connection class or my connection class in a NuGet package as in my signature)


    Hi,

    thank you very much,

    I tried the Example and recommandation.

    and I get this error :

    Cast

    Specified cast is not valid.


    So thank you.

    Thursday, March 7, 2019 5:42 PM
  • Hello,

    Here is a bare bones example (where I would use my connection class or my connection class in a NuGet package as in my signature)


    Hi,

    thank you very much,

    I tried the Example and recommandation.

    and I get this error :

    Cast

    Specified cast is not valid.


    So thank you.

    What line?

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 7, 2019 5:49 PM
    Moderator

  • What line?


    Hi, 

    Here:cast

    Dim sum1 As Integer = rdr.GetInt32(3)

    Thank you. 


    • Edited by Bajtitou Thursday, March 7, 2019 6:39 PM
    Thursday, March 7, 2019 6:35 PM
  • That means you need to type it as whatever field type this is so we know it's not an integer which means you need to look at the column definition to see what type that column is then use the appropriate Get method e.g. if decimal use GetDecimal etc.  

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 7, 2019 6:56 PM
    Moderator

  • Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form1
    
    End Class
    Public Class DataOperations
        Private builder As OleDbConnectionStringBuilder
    
        Public Sub New()
            builder = New OleDbConnectionStringBuilder With
                {
                    .Provider = "Microsoft.ACE.OLEDB.12.0",
                    .DataSource = Path.Combine(Application.StartupPath, "ED_OISEAUX.accdb")
                }
        End Sub
        Private Sub Totaux1()
    
            Dim upDateResult As Integer = 0
    
            Try
                Using cn As New OleDbConnection With
                    {
                        .ConnectionString = builder.ConnectionString
                    }
    
                    Using cmd As New OleDbCommand With {.Connection = cn}
    
                        cmd.CommandText = "SELECT Sum (IIf(Clmn1 Is Null, 0,Clmn1)) As Sum1,
                                            Sum (IIf(Colmn2 Is Null, 0,Colmn2)) As Sum2,
                                            Sum (IIf(Clmn3 Is Null,0,Clmn3)) As Sum3,
                                            FROM  Table1"
    
                        cn.Open()
    
                        Dim reader As OleDbDataReader = cmd.ExecuteReader
    
                        If reader.HasRows Then
    
                            reader.Read()
    
                            Dim sum1 As Integer = reader.GetInt32(0)
                            Dim sum2 As Integer = reader.GetInt32(1)
                            Dim sum3 As Integer = reader.GetInt32(2)
    
                            cmd.CommandText = "UPDATE Table2 " &
                                              "SET " &
                                              "TotalClmn1 = @d2," &
                                              "TotalClmn1 = @d3," &
                                              "TotalClmn1 = @d3, " &
                                              "WHERE Total=@d1"
    
                            cmd.Parameters.AddWithValue("?", sum1)
                            cmd.Parameters.AddWithValue("?", sum2)
                            cmd.Parameters.AddWithValue("?", sum2)
    
                            '
                            ' In the following parameter, is the value going to be "ToTal1"
                            ' or is the value suppose to be something else as you have this
                            ' code this parameter value is literally ToTal1 I would suspect
                            ' this is not correct.
                            '
                            cmd.Parameters.AddWithValue("?", "ToTal1")
    
    
                            cmd.Parameters.AddWithValue("@d4", sum3)
    
                            upDateResult = cmd.ExecuteNonQuery()
    
                            If upDateResult <> 1 Then
                                MessageBox.Show("Not updated")
                                Exit Sub
                            Else
                                MessageBox.Show("Updated")
                            End If
    
                            cmd.Parameters.Clear()
    
                            cmd.CommandText = "Update Table3 Set Totaux= @d2 Where ToTal = @d1"
    
                            cmd.Parameters.AddWithValue("@d2", sum3)
    
                            '
                            ' Same as above where I commented
                            '
                            cmd.Parameters.AddWithValue("@d1", "Total1")
    
                            upDateResult = cmd.ExecuteNonQuery()
    
                            If upDateResult = 1 Then
                                MessageBox.Show("Updated")
                            Else
                                MessageBox.Show("Not updated")
                            End If
    
                        Else
                            MessageBox.Show("No records match condition")
                        End If
                    End Using
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error",
                                MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    End Class


    Hi,

    Do the reader  still open , 

    If not Where or when it will be close?

    Because after many test the Table2 is Updated but not Table3, so Problem with reader .

    Thank you.


    • Edited by Bajtitou Thursday, March 7, 2019 9:17 PM
    Thursday, March 7, 2019 8:55 PM
  • So close the reader after each ExecuteNonQuery but if that was an issue you would jump to the catch and you have never indicated this.

    Now here is a warning, we have past 15 replies, about at this point the only true way of assisting is to get a copy of your database to really assist.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 7, 2019 11:20 PM
    Moderator
  • Hi,
    here is a working well demo with your code:

    Module Module1
      Sub Main()
        Try
          Dim c As New Demo
          c.Execute()
        Catch ex As Exception
          Console.WriteLine(ex.ToString)
        End Try
        Console.WriteLine("Continue enter key")
        Console.ReadKey()
      End Sub
      Friend Class Demo
    
        Private cnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb;Persist Security Info=True"
    
        Friend Sub Execute()
          CreateDBs()
          Totaux1()
        End Sub
    
        Private Sub CreateDBs()
          Using cn As New OleDb.OleDbConnection(cnString)
            cn.Open()
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
              ' delete previous version
              cmd.CommandText = "DROP Table Table1"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              cmd.CommandText = "DROP Table Table2"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              cmd.CommandText = "DROP Table Table3"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              ' Create Tables
              cmd.CommandText = "CREATE Table Table1([Clmn1] Integer,[Colmn2] Integer,[Clmn3] Integer)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "CREATE Table Table2([Total] VARCHAR(255),[TotalClmn1] Integer,[TotalClmn2] Integer,[TotalClmn3] Integer)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "CREATE Table Table3([ToTal] VARCHAR(255),[Totaux] Integer)"
              cmd.ExecuteNonQuery()
              ' Insert values
              cmd.CommandText = "INSERT INTO Table1([Clmn1],[Colmn2],[Clmn3]) VALUES(1,2,3)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "INSERT INTO Table1([Clmn1],[Colmn2],[Clmn3]) VALUES(1,2,3)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "INSERT INTO Table1([Clmn1],[Colmn2],[Clmn3]) VALUES(1,2,3)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "INSERT INTO Table2([Total],[TotalClmn1]) VALUES(""Total1"",0)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "INSERT INTO Table3([Total],[Totaux]) VALUES(""Total1"",0)"
              cmd.ExecuteNonQuery()
            End Using
          End Using
        End Sub
        Private Sub Totaux1()
          Using cn As New OleDb.OleDbConnection(cnString)
            cn.Open()
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
              cmd.CommandText = "Select Sum(IIf(Clmn1 Is Null, 0,Clmn1)) As Sum1,
                                        Sum(IIf(Colmn2 Is Null, 0,Colmn2)) As Sum2,
                                        Sum(IIf(Clmn3 Is Null,0,Clmn3)) As Sum3
                                        FROM Table1"
              Dim Sum1 As Integer = 0
              Dim Sum2 As Integer = 0
              Dim Sum3 As Integer = 0
              Dim Rdr As OleDb.OleDbDataReader = cmd.ExecuteReader
              If Rdr.HasRows Then
                While Rdr.Read
                  Sum1 = CType(Rdr.Item("Sum1"), Integer)
                  Sum2 = CType(Rdr.Item("Sum2"), Integer)
                  Sum3 = CType(Rdr.Item("Sum3"), Integer)
                End While
              End If
              Rdr.Close()
              cmd.CommandText = "Update Table2 Set TotalClmn1=?,TotalClmn2=?,TotalClmn3=?
                                                     Where Total=?"
              cmd.Parameters.AddWithValue("@d2", Sum1)
              cmd.Parameters.AddWithValue("@d3", Sum2)
              cmd.Parameters.AddWithValue("@d4", Sum3)
              cmd.Parameters.AddWithValue("@d1", "Total1")
              cmd.ExecuteNonQuery()
              'Dgw2.Refresh()
              cmd.Parameters.Clear()
              cmd.CommandText = "Update Table3 Set Totaux=? Where Total=?"
              cmd.Parameters.AddWithValue("@d2", Sum3)
              cmd.Parameters.AddWithValue("@d1", "Total1")
              cmd.ExecuteNonQuery()
              'Dgw2.Refresh()
            End Using
          End Using
        End Sub
      End Class
    End Module


    --
    Viele Grüsse / Best Regards
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Friday, March 8, 2019 6:50 AM
  • So close the reader after each ExecuteNonQuery but if that was an issue you would jump to the catch and you have never indicated this.

    Now here is a warning, we have past 15 replies, about at this point the only true way of assisting is to get a copy of your database to really assist.

    Hi,

    I add a close reader to this line and here the problem is solved.

    cast

    So thank you very Much for assistance and help.

    Friday, March 8, 2019 7:29 AM
  • Hi,
    here is a working well demo with your code:

    Module Module1
      Sub Main()
        Try
          Dim c As New Demo
          c.Execute()
        Catch ex As Exception
          Console.WriteLine(ex.ToString)
        End Try
        Console.WriteLine("Continue enter key")
        Console.ReadKey()
      End Sub
      Friend Class Demo
    
        Private cnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb;Persist Security Info=True"
    
        Friend Sub Execute()
          CreateDBs()
          Totaux1()
        End Sub
    
        Private Sub CreateDBs()
          Using cn As New OleDb.OleDbConnection(cnString)
            cn.Open()
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
              ' delete previous version
              cmd.CommandText = "DROP Table Table1"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              cmd.CommandText = "DROP Table Table2"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              cmd.CommandText = "DROP Table Table3"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              ' Create Tables
              cmd.CommandText = "CREATE Table Table1([Clmn1] Integer,[Colmn2] Integer,[Clmn3] Integer)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "CREATE Table Table2([Total] VARCHAR(255),[TotalClmn1] Integer,[TotalClmn2] Integer,[TotalClmn3] Integer)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "CREATE Table Table3([ToTal] VARCHAR(255),[Totaux] Integer)"
              cmd.ExecuteNonQuery()
              ' Insert values
              cmd.CommandText = "INSERT INTO Table1([Clmn1],[Colmn2],[Clmn3]) VALUES(1,2,3)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "INSERT INTO Table1([Clmn1],[Colmn2],[Clmn3]) VALUES(1,2,3)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "INSERT INTO Table1([Clmn1],[Colmn2],[Clmn3]) VALUES(1,2,3)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "INSERT INTO Table2([Total],[TotalClmn1]) VALUES(""Total1"",0)"
              cmd.ExecuteNonQuery()
              cmd.CommandText = "INSERT INTO Table3([Total],[Totaux]) VALUES(""Total1"",0)"
              cmd.ExecuteNonQuery()
            End Using
          End Using
        End Sub
        Private Sub Totaux1()
          Using cn As New OleDb.OleDbConnection(cnString)
            cn.Open()
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
              cmd.CommandText = "Select Sum(IIf(Clmn1 Is Null, 0,Clmn1)) As Sum1,
                                        Sum(IIf(Colmn2 Is Null, 0,Colmn2)) As Sum2,
                                        Sum(IIf(Clmn3 Is Null,0,Clmn3)) As Sum3
                                        FROM Table1"
              Dim Sum1 As Integer = 0
              Dim Sum2 As Integer = 0
              Dim Sum3 As Integer = 0
              Dim Rdr As OleDb.OleDbDataReader = cmd.ExecuteReader
              If Rdr.HasRows Then
                While Rdr.Read
                  Sum1 = CType(Rdr.Item("Sum1"), Integer)
                  Sum2 = CType(Rdr.Item("Sum2"), Integer)
                  Sum3 = CType(Rdr.Item("Sum3"), Integer)
                End While
              End If
              Rdr.Close()
              cmd.CommandText = "Update Table2 Set TotalClmn1=?,TotalClmn2=?,TotalClmn3=?
                                                     Where Total=?"
              cmd.Parameters.AddWithValue("@d2", Sum1)
              cmd.Parameters.AddWithValue("@d3", Sum2)
              cmd.Parameters.AddWithValue("@d4", Sum3)
              cmd.Parameters.AddWithValue("@d1", "Total1")
              cmd.ExecuteNonQuery()
              'Dgw2.Refresh()
              cmd.Parameters.Clear()
              cmd.CommandText = "Update Table3 Set Totaux=? Where Total=?"
              cmd.Parameters.AddWithValue("@d2", Sum3)
              cmd.Parameters.AddWithValue("@d1", "Total1")
              cmd.ExecuteNonQuery()
              'Dgw2.Refresh()
            End Using
          End Using
        End Sub
      End Class
    End Module


    Hi,

    Thank you very much sir  Peter Fleischer for your assistance ,

    I will work on your example. 

    thank you .

    Friday, March 8, 2019 7:37 AM
  • Hi,
    there are 3 things to consider (in OleDb):

    • OleDbConnection suports on only one open SQL Reader at the same time for one connection.
    • placeholder for parameters are question marks (?)
    • the parameters must be in the order of occurrence in the SQL

    --
    Viele Grüsse / Best Regards
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Proposed as answer by Peter Fleischer Friday, March 8, 2019 9:00 AM
    • Marked as answer by Bajtitou Friday, March 8, 2019 4:55 PM
    • Unmarked as answer by Bajtitou Friday, March 8, 2019 4:59 PM
    Friday, March 8, 2019 8:19 AM
  • Hi,
    there are 3 things to consider (in OleDb):

    • OleDbConnection suports on only one open SQL Reader at the same time for one connection.
    • placeholder for parameters are question marks (?)
    • the parameters must be in the order of occurrence in the SQL

    Hi,

    thank you Very Much  Sir.

    Best Regards.

    Friday, March 8, 2019 8:53 AM