none
Help needed with vb.net vs access RRS feed

  • Question

  • Function fntZoeken(ByVal strCommand As String, ByVal strField As String)
            subOpenConnection()
    
            ' 2. Maak het commando
            Dim cmd As OleDbCommand
            Dim strCmd As String = strCommand
    
            cmd = New OleDbCommand(strCmd, cnn)
    
            ' 3. Haal gegevens op
            Dim res As New List(Of String)
            Dim strItem As String
            Dim rd As OleDbDataReader
            'MessageBox.Show(strCmd)
            'MessageBox.Show(strField)
    
    
            rd = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    
                If strField.Substring(3, 3) = "Lev" Then
                    cmbLeverancierZoeken.Items.Clear()
                    If rd.HasRows Then
                        Do While rd.Read
                            strItem = CStr(rd(strField))
                            res.Add(strItem)
                            cmbLeverancierZoeken.Items.Add(strItem)
                        Loop
                    End If
                    cmbLeverancierZoeken.DroppedDown = True
                ElseIf strField.Substring(3, 3) = "Kla" Then
    
                    If TabControl1.SelectedTab Is tcKlanten Then
                        cmbKlantZoeken.Items.Clear()
                        If rd.HasRows Then
                            Do While rd.Read
                                strItem = CStr(rd(strField))
                                res.Add(strItem)
                                cmbKlantZoeken.Items.Add(strItem)
                            Loop
                        End If
                        cmbKlantZoeken.DroppedDown = True
                    ElseIf TabControl1.SelectedTab Is tcBestellingen Then
    
                        If strCbSelect = "KlantNaam" Then
                            cmbBestelKlantNaam.Items.Clear()
                            If rd.HasRows Then
                                Do While rd.Read
                                    strItem = CStr(rd(strField))
                                    res.Add(strItem)
                                    cmbBestelKlantNaam.Items.Add(strItem)
                                Loop
                            End If
                            cmbBestelKlantNaam.DroppedDown = True
                    ElseIf strCbSelect = "KlantVoornaam" Then
                        cmbBestelKlantVoornaam.Items.Clear()
                        If rd.HasRows Then
                            Do While rd.Read
                                strItem = CStr(rd(strField))
                                res.Add(strItem)
                                cmbBestelKlantVoornaam.Items.Add(strItem)
                            Loop
                        End If
                        cmbBestelKlantVoornaam.DroppedDown = True
    
                    End If
                ElseIf strField.Substring(3, 3) = "Pro" Then
                    If TabControl1.SelectedTab Is tcProducten Then
                        cmbProductZoeken.Items.Clear()
                        If rd.HasRows Then
                            Do While rd.Read
                                strItem = CStr(rd(strField))
                                res.Add(strItem)
                                cmbProductZoeken.Items.Add(strItem)
                            Loop
                        End If
                        cmbProductZoeken.DroppedDown = True
    
                    ElseIf TabControl1 Is tcBestellingen Then
                        If strCbSelect = "ProductNaam" Then
                            If rd.HasRows Then
                                Do While rd.Read
                                    strItem = CStr(rd(strField))
                                    res.Add(strItem)
                                    cmbBestelProduct.Items.Add(strItem)
                                Loop
                            End If
                            cmbBestelProduct.DroppedDown = True
                        ElseIf strCbSelect = "ProductPrijs" Then
                            If rd.HasRows Then
                                Do While rd.Read
                                    strItem = CStr(rd(strField))
                                    res.Add(strItem)
                                    txtBestelPrijsPerStuk.Text = CStr(strItem)
                                Loop
                            End If
                            cmbBestelProduct.DroppedDown = True
                        End If
                    End If
                End If
            End If

    'This gives an error on the line: rd = cmd.ExecuteReader(CommandBehavior.CloseConnection) when clicked on

    cmbBestelKlantVoornaam
    Private Sub cbBestelKlantNaam_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbBestelKlantNaam.Click
            Dim strCommand As String
            Dim strField As String
            strCbSelect = "KlantNaam"
            strCommand = "SELECT DISTINCT fldKlantNaam FROM tblKlanten ORDER BY fldKlantNaam"
            strField = "fldKlantNaam"
            fntZoeken(strCommand, strField)
        End Sub
    
    
        Private Sub cbBestelKlantVoornaam_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbBestelKlantVoornaam.Click
            Dim strCommand As String
            Dim strField As String
            strCbSelect = "KlantVoornaam"
            strCommand = "'SELECT DISTINCT fldKlantVoornaam FROM tblKlanten WHERE fldKlantNaam=" & CStr(cmbBestelKlantNaam.SelectedItem) & "'"
            'MessageBox.Show(strCommand)
            strCommand = strCommand.Substring(1, (strCommand.Length - 2))
            'MessageBox.Show(strCommand)
            strField = "fldKlantVoornaam"
            fntZoeken(strCommand, strField)
        End Sub


    Wednesday, May 30, 2012 5:44 PM

Answers

  • Hi robrechtleus,

    I guess this issue is caused by the command string and I would suggest that you should use the OleDbCommand.Parameters to hold its parameter. In this way, your application would be more safety and easy to maintain.

    Consider into this issue, you should regenerate the strCommand with the place holder of '?' and add CStr(cmbBestelKlantNaam.SelectedItem) to the strCommand.Parameters property.

    Here is a link for you which you could go through with its code snippet to solve this issue:

    Hope this could help you.

    Best Regard


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, June 12, 2012 7:02 AM
    Moderator

All replies

  • My guess is that the following

    strCommand = "'SELECT DISTINCT fldKlantVoornaam FROM tblKlanten WHERE fldKlantNaam=" & CStr(cmbBestelKlantNaam.SelectedItem

    Should be

    strCommand = "SELECT DISTINCT fldKlantVoornaam FROM tblKlanten WHERE fldKlantNaam='" & CStr(cmbBestelKlantNaam.SelectedItem) & "'"
    Which is why using parameters would be best to use here.


    KSG

    Wednesday, May 30, 2012 7:28 PM
  • Hi robrechtleus,

    I guess this issue is caused by the command string and I would suggest that you should use the OleDbCommand.Parameters to hold its parameter. In this way, your application would be more safety and easy to maintain.

    Consider into this issue, you should regenerate the strCommand with the place holder of '?' and add CStr(cmbBestelKlantNaam.SelectedItem) to the strCommand.Parameters property.

    Here is a link for you which you could go through with its code snippet to solve this issue:

    Hope this could help you.

    Best Regard


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, June 12, 2012 7:02 AM
    Moderator