none
cmdText maybe in accessible due to its protection RRS feed

  • Question

  • I am trying to do an Update with the following code but I am getting an error message "cmdtext may be inaccessible "

     Dim cmd As New OleDb.OleDbCommand
            Dim conn As New OleDb.OleDbConnection
            'Object To Use As SQL Query
            conn.ConnectionString = getConnection()
            conn.Open()

    Using cmd = New OleDbCommand(cmdText, conn) cmd.CommandText = "Update Student set Programtbl= ?,Leveltbl= ?,where StudentID=? cmd.Parameters.AddWithValue("@P1", cboProgram.Text) cmd.Parameters.AddWithValue("@P2", cboLevel.Text) cmd.ExcuteNonQuery()



    Friday, December 8, 2017 8:08 PM

Answers

  • Hi,

    Been out. Here is an example where I created a class that represents information to pass to the update method.

    The class

    Public Class Student
        Public Property Identifier As Integer
        Public Property ProgramTable As String
        Public Property Level As String
        '
        ' Add as many properties as needed
        '
    End Class

    Revised class from before

    Public Class SampleOperations1
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = "Your path to the database and database name goes here"
        }
        Public Property HasError As Boolean
        Public Property Exception As Exception
        Public Function UpdateStudent(ByVal pStudent As Student) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
            HasError = False
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText =
                            <SQL>
                                UPDATE Student SET Programtbl = @Programtbl,Leveltbl = @pLeveltbl
                                WHERE StudentID = @Identifier
                            </SQL>.Value
    
                        cmd.Parameters.AddWithValue("@Programtbl", pStudent.ProgramTable)
                        cmd.Parameters.AddWithValue("@Leveltbl", pStudent.Level)
                        cmd.Parameters.AddWithValue("@Identifier", pStudent.Identifier)
    
                        cn.Open()
    
                        ' when Affected returns 1, one row was affected
                        ' when Affected returns >1 more than one row was affected
                        ' when Affected is -1, the record was not located in pStudentID
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                HasError = True
                Success = False
                Me.Exception = Exception
            End Try
    
            Return Success
    
        End Function
    
    End Class
    

    Usage, ignore the fact I used a code module, you would do this from a form, a code module or another class.

    Public Module Demo
        Public Sub Example()
            Dim student As New Student With {.Identifier = 1, .Level = "Second", .ProgramTable = "Master"}
            Dim ops As New SampleOperations1
            If ops.UpdateStudent(student) Then
                ' success
            Else
                ' failed
            End If
        End Sub
    End Module
    So when I setup properties you would use your TextBox and ComboBox to set properties.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by alobi Tuesday, December 12, 2017 1:54 PM
    Saturday, December 9, 2017 7:57 PM
    Moderator
  • Hi alobi,

    According to your description, you just want to update  Access database date.

    Firstly you used cmdText in oledbcommand, but you didn't show cmdText here. 

    Secondly I find there are three parameters in update sentence, but you just replace two parameters.

    Thirdly if you use cmd = New OleDbCommand(cmdText, conn), now  cmd.CommandText =cmdText, so you don't need to set cmd.CommandText . Please see the following two wording.

    One:

     Try
                Using cnn = New OleDbConnection(constring)
                    Dim cmd As New OleDb.OleDbCommand
                    cnn.Open()
                    cmd.Connection = cnn
                    Dim cmdText As String
                    cmdText = "UPDATE inventory SET ID=@ID, BRAND=@Brand,SPECIFICATION=@specs,STATUS = @stat WHERE ID=@ID"
                    cmd.CommandText = cmdText
                    cmd.Parametes.AddWithValue("@ID", Me.txtstdID.Text)
                    cmd.Parametes.AddWithValue("@Brand", Me.cboBrand.Text)
                    cmd.Parametes.AddWithValue("@specs", Me.txtDescription.Text)
                    cmd.Parametes.AddWithValue("@stat", strStat)
                    cmd.ExecuteNonQuery()
                End Using
                'refresh data in list
                RefreshData()
                'clear form
                Me.btnClear.PerformClick()
    
            Catch(x As Exception)
             MessageBox.Show(x.Message)
            End Try

    Two:

     Dim cmdText As String
            Try
                Using cnn = New OleDbConnection(constring)
                    cnn.Open()
                    cmdText = "UPDATE inventory SET ID=@ID, BRAND=@Brand,SPECIFICATION=@specs,STATUS = @stat WHERE ID=@ID"
                    Dim cmd As New OleDb.OleDbCommand(cmdText, cnn)
    
                    cmd.Parametes.AddWithValue("@ID", Me.txtstdID.Text)
                    cmd.Parametes.AddWithValue("@Brand", Me.cboBrand.Text)
                    cmd.Parametes.AddWithValue("@specs", Me.txtDescription.Text)
                    cmd.Parametes.AddWithValue("@stat", strStat)
                    cmd.ExecuteNonQuery()
                End Using
                'refresh data in list
                RefreshData()
                'clear form
                Me.btnClear.PerformClick()
    
            Catch(x As Exception)
             MessageBox.Show(x.Message)
            End Try
    

    Best Regards,

    Cherry


    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.

    • Marked as answer by alobi Tuesday, December 12, 2017 1:53 PM
    Monday, December 11, 2017 3:05 AM
    Moderator

All replies

  •  So where in your code have you declared the 'cmdText' variable?  If it is not at the class level or within the sub or function that your code snippet is in,  then it is inaccessible.

     For an error like this,  it helps if you post all the relative code.  Like the complete sub or function, and where and how each variable involved is declared and/or passed to the code.


    If you say it can`t be done then i`ll try it

    • Edited by IronRazerz Friday, December 8, 2017 10:17 PM
    Friday, December 8, 2017 8:11 PM
  • You should not use global variables in regards to your connection and command, instead create, use, dispose as shown below in a data class. Create an instance of the class e.g. Dim ops As NewSampleOperations1. Then call UpdateStudent passing in values, in this case from cboProgram.Text and cboLevel.text. If the update was successful then the method returns True, if fails most likely an error happened and you can get the error via ops.Exception.Message.

    Public Class SampleOperations1
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = "Your path to the database and database name goes here"
        }
        Public Property HasError As Boolean
        Public Property Exception As Exception
        Public Function UpdateStudent(ByVal pStudentID As Integer, ByVal pProgramtbl As String, ByVal pLeveltbl As String) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
            HasError = False
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText =
                            <SQL>
                                UPDATE Student SET Programtbl = @Programtbl,Leveltbl = @pLeveltbl
                                WHERE StudentID = @Identifier
                            </SQL>.Value
    
                        cmd.Parameters.AddWithValue("@Programtbl", pProgramtbl)
                        cmd.Parameters.AddWithValue("@Leveltbl", pLeveltbl)
                        cmd.Parameters.AddWithValue("@Identifier", pStudentID)
    
                        cn.Open()
    
                        ' when Affected returns 1, one row was affected
                        ' when Affected returns >1 more than one row was affected
                        ' when Affected is -1, the record was not located in pStudentID
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                HasError = True
                Success = False
                Me.Exception = Exception
            End Try
    
            Return Success
    
        End Function
    
    End Class
    


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, December 8, 2017 10:07 PM
    Moderator
  • Hi Karen,

    Thank you so much for the advice above. It is highly appreciated. I have one more question though. In a situation where there are more than 20 textboxes in a form should I pass all the value through the brackets as shown below? "UpdateStudent(ByVal pStudentID As Integer, ByVal pProgramtbl As String, ByVal pLeveltbl As String) As Boolean"

    • Edited by alobi Saturday, December 9, 2017 4:39 PM
    Saturday, December 9, 2017 4:38 PM
  • Hi,

    Been out. Here is an example where I created a class that represents information to pass to the update method.

    The class

    Public Class Student
        Public Property Identifier As Integer
        Public Property ProgramTable As String
        Public Property Level As String
        '
        ' Add as many properties as needed
        '
    End Class

    Revised class from before

    Public Class SampleOperations1
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = "Your path to the database and database name goes here"
        }
        Public Property HasError As Boolean
        Public Property Exception As Exception
        Public Function UpdateStudent(ByVal pStudent As Student) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
            HasError = False
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText =
                            <SQL>
                                UPDATE Student SET Programtbl = @Programtbl,Leveltbl = @pLeveltbl
                                WHERE StudentID = @Identifier
                            </SQL>.Value
    
                        cmd.Parameters.AddWithValue("@Programtbl", pStudent.ProgramTable)
                        cmd.Parameters.AddWithValue("@Leveltbl", pStudent.Level)
                        cmd.Parameters.AddWithValue("@Identifier", pStudent.Identifier)
    
                        cn.Open()
    
                        ' when Affected returns 1, one row was affected
                        ' when Affected returns >1 more than one row was affected
                        ' when Affected is -1, the record was not located in pStudentID
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                HasError = True
                Success = False
                Me.Exception = Exception
            End Try
    
            Return Success
    
        End Function
    
    End Class
    

    Usage, ignore the fact I used a code module, you would do this from a form, a code module or another class.

    Public Module Demo
        Public Sub Example()
            Dim student As New Student With {.Identifier = 1, .Level = "Second", .ProgramTable = "Master"}
            Dim ops As New SampleOperations1
            If ops.UpdateStudent(student) Then
                ' success
            Else
                ' failed
            End If
        End Sub
    End Module
    So when I setup properties you would use your TextBox and ComboBox to set properties.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by alobi Tuesday, December 12, 2017 1:54 PM
    Saturday, December 9, 2017 7:57 PM
    Moderator
  • Thanks, I will go put it together and get back to you. Thanks for your time
    Sunday, December 10, 2017 11:08 AM
  • Hi Karen I am getting a couple of red wiggle lines on OleDbConnectionStringBuilder and OleDbConnection saying that both are not defined
    Sunday, December 10, 2017 5:27 PM
  • Hi Karen I am getting a couple of red wiggle lines on OleDbConnectionStringBuilder and OleDbConnection saying that both are not defined

     So did you try holding your mouse over the 'red wiggle line' to see if it suggests a fix for the problems?  Normally it will.  That is the first step to take when you see an error like this in the code.  My guess would be that it will suggest for you to add the Import statement to the top your code...

    Imports System.Data.OleDb
    
    Public Class Form1
    
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = "Your path to the database and database name goes here"
        }
    
    End Class


    If you say it can`t be done then i`ll try it

    • Edited by IronRazerz Sunday, December 10, 2017 5:43 PM
    Sunday, December 10, 2017 5:40 PM
  • Hi alobi,

    According to your description, you just want to update  Access database date.

    Firstly you used cmdText in oledbcommand, but you didn't show cmdText here. 

    Secondly I find there are three parameters in update sentence, but you just replace two parameters.

    Thirdly if you use cmd = New OleDbCommand(cmdText, conn), now  cmd.CommandText =cmdText, so you don't need to set cmd.CommandText . Please see the following two wording.

    One:

     Try
                Using cnn = New OleDbConnection(constring)
                    Dim cmd As New OleDb.OleDbCommand
                    cnn.Open()
                    cmd.Connection = cnn
                    Dim cmdText As String
                    cmdText = "UPDATE inventory SET ID=@ID, BRAND=@Brand,SPECIFICATION=@specs,STATUS = @stat WHERE ID=@ID"
                    cmd.CommandText = cmdText
                    cmd.Parametes.AddWithValue("@ID", Me.txtstdID.Text)
                    cmd.Parametes.AddWithValue("@Brand", Me.cboBrand.Text)
                    cmd.Parametes.AddWithValue("@specs", Me.txtDescription.Text)
                    cmd.Parametes.AddWithValue("@stat", strStat)
                    cmd.ExecuteNonQuery()
                End Using
                'refresh data in list
                RefreshData()
                'clear form
                Me.btnClear.PerformClick()
    
            Catch(x As Exception)
             MessageBox.Show(x.Message)
            End Try

    Two:

     Dim cmdText As String
            Try
                Using cnn = New OleDbConnection(constring)
                    cnn.Open()
                    cmdText = "UPDATE inventory SET ID=@ID, BRAND=@Brand,SPECIFICATION=@specs,STATUS = @stat WHERE ID=@ID"
                    Dim cmd As New OleDb.OleDbCommand(cmdText, cnn)
    
                    cmd.Parametes.AddWithValue("@ID", Me.txtstdID.Text)
                    cmd.Parametes.AddWithValue("@Brand", Me.cboBrand.Text)
                    cmd.Parametes.AddWithValue("@specs", Me.txtDescription.Text)
                    cmd.Parametes.AddWithValue("@stat", strStat)
                    cmd.ExecuteNonQuery()
                End Using
                'refresh data in list
                RefreshData()
                'clear form
                Me.btnClear.PerformClick()
    
            Catch(x As Exception)
             MessageBox.Show(x.Message)
            End Try
    

    Best Regards,

    Cherry


    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.

    • Marked as answer by alobi Tuesday, December 12, 2017 1:53 PM
    Monday, December 11, 2017 3:05 AM
    Moderator