System.Data.SQLite

I have tried to emulate the How To Write a Parameterized Query but am getting syntax errors "near @MbrID"  when executing them. I would appreciate instruction on how to properly do the query command text. Thanks for your help.

My relevant code follows:

    Private Sub WeeklyAttendance()
        Dim ServiceDate As String = dtpServiceDate.Value.Year.ToString + "-" + Format(dtpServiceDate.Value.Month, "00").ToString + "-" + Format(dtpServiceDate.Value.Day, "00").ToString
        Dim BaseInsertString As String = "INSERT INTO Attendance (Year, DateOfService, Present, MbrID ) VALUES ("
        Dim SuspectQuery As String = ""
        Dim strInsertString As String = ""
        Dim strUpdateDLACore As String = "UPDATE MbrData SET DateLastAttended = '" + ServiceDate + "' WHERE MbrID = @MbrID )"
        Dim strUpdateDLA As String = ""
        Dim intRecordsAffected As Integer = 0
        Dim intUpdateDLARecordsAffected As Integer = 0
        Dim cnnAttendance As SQLiteConnection = New SQLiteConnection(SqLiteConnection2)
        cnnAttendance.Open()
        Using cmdSetAttendance As SQLiteCommand = cnnAttendance.CreateCommand
            cmdSetAttendance.CommandText = BaseInsertString & dtpServiceDate.Value.Year.ToString & ",'" & ServiceDate + "', @PresentValue, @MbrID )"
            Dim PresentValueParameter As SQLiteParameter = New SQLiteParameter("@PresentValue")
            Dim MbrIDParameter As SQLiteParameter = New SQLiteParameter("@MbrID")
            cmdSetAttendance.Parameters.AddRange(New SQLiteParameter() {PresentValueParameter, MbrIDParameter})
            Using cmdUpdateDLA As SQLiteCommand = cnnAttendance.CreateCommand
                cmdUpdateDLA.CommandText = strUpdateDLACore
                cmdUpdateDLA.Parameters.Add(MbrIDParameter)
                Dim trans As SQLiteTransaction = cnnAttendance.BeginTransaction
                Try
                    For I As Integer = 0 To tvFamilies2.Nodes.Count - 1
                        For J As Integer = 0 To tvFamilies2.Nodes(I).Nodes.Count - 1
                            MbrIDParameter.Value = tvFamilies2.Nodes(I).Nodes(J).Tag
                            SuspectQuery = "UpdateDLA"
                            If tvFamilies2.Nodes(I).Nodes(J).Checked Then
                                'strInsertString = BaseInsertString & dtpServiceDate.Value.Year.ToString & ",'" & ServiceDate + "',1," + tvFamilies2.Nodes(I).Nodes(J).Tag.ToString + ")" + vbCrLf
                                'strUpdateDLA = strUpdateDLACore + tvFamilies2.Nodes(I).Nodes(J).Tag.ToString
                                'Dim cmdUpdateDLA As SQLiteCommand = New SQLiteCommand(strUpdateDLA, cnnAttendance)
                                PresentValueParameter.Value = 1
                                intUpdateDLARecordsAffected += cmdUpdateDLA.ExecuteNonQuery()
                                'intUpdateDLARecordsAffected += MbrDataTableAdapter.UpdateDLA(dtpServiceDate.Value, MbrIDParameter.Value.ToString)
                            Else
                                'strInsertString = BaseInsertString & dtpServiceDate.Value.Year.ToString & ",'" & ServiceDate + "',0," + tvFamilies2.Nodes(I).Nodes(J).Tag.ToString + ")"
                                'Dim cmdUpdateDLA As SQLiteCommand = New SQLiteCommand(strUpdateDLA, cnnAttendance)
                                PresentValueParameter.Value = 0
                                intUpdateDLARecordsAffected += cmdUpdateDLA.ExecuteNonQuery()
                            End If
                            'Dim cmdSetAttendance As SQLiteCommand = New SQLiteCommand(strInsertString, cnnAttendance)

                            SuspectQuery = "SetAttendance"
                            intRecordsAffected += cmdSetAttendance.ExecuteNonQuery()
                            'intRecordsAffected += AttendanceTableAdapter.InsertQuery(CType(dtpServiceDate.Value.Year, Long), CType(ServiceDate, Date), PresentValueParameter.Value, CType(MbrIDParameter.Value, Long))
                        Next
                    Next
                    trans.Commit()
                Catch ex As Exception
                    gMsg = "The " & SuspectQuery & " insert/update failed for the following reason: " & vbCrLf & ex.Message & vbCrLf
                    Select Case SuspectQuery
                        Case "UpdateDLA"
                            gMsg += cmdUpdateDLA.CommandText & vbCrLf & "PresentValueParameter = " & PresentValueParameter.Value.ToString & vbCrLf & " MbrID = " & MbrIDParameter.Value.ToString
                        Case "SetAttendance"
                            gMsg += cmdSetAttendance.CommandText & vbCrLf & " MbrID = " & MbrIDParameter.Value.ToString
                    End Select
                    MsgBox(gMsg)
                Finally
                    If trans IsNot Nothing Then trans.Dispose()
                End Try
            End Using
        End Using
        cnnAttendance.Close()
        cnnAttendance.Dispose()
        MsgBox("Records Affected = " & intRecordsAffected.ToString + vbCrLf + "MbrData Records Affected = " + intUpdateDLARecordsAffected.ToString)
    End Sub

rhencke,

 Thanks. That was it. Clearly not a game for a guy who can't read. You have saved my bacon again. I really appreciate it.

Here's an alternate version of your code.. what do you think? (edit, fixed typo)

(note.. I wrote this in notepad, so I can't guarantee no errors, but.. :)

Private Sub WeeklyAttendance()
    Const sqlMarkAttendance As String = "INSERT INTO Attendance (Year, DateOfService, Present, MbrID) " _
        & "VALUES (strftime("%Y", @DateLastAttended), @DateLastAttended, @Present, @MbrID); " _
        & "UPDATE MbrData SET DateLastAttended = @DateLastAttended WHERE MbrID = @MbrID;"

    Dim intRecordsAffected As Integer = 0

    Using con = New SQLiteConnection(SqLiteConnection2)
        con.Open()

        Using tran = con.BeginTransaction(), _
              markAttendance = con.CreateCommand(), _

            markAttendance.CommandText = sqlMarkAttendance
            Dim Present          = markAttendance.Parameters.Add("@Present", DbType.Boolean)
            Dim MbrID            = markAttendance.Parameters.Add("@MbrID", DbType.Int32)
            Dim DateLastAttended = markAttendance.Parameters.Add("@DateLastAttended", DbType.DateTime)

            DateLastAttended.Value = dtpServiceDate.Value.Date
            
            For Each node In tvFamilies2.Nodes
                For Each subNode In node.Nodes
                    MbrID.Value = subNode.Tag
                    Present.Value = subNode.Checked
                
                    intRecordsAffected += markAttendance.ExecuteNonQuery()
                Next
            Next
            
            trans.Commit()
        End Using
    End Using
End Sub

It is clearly more efficient. I had an error in the previously posted code in that the update command should be run only when the node is checked and not when it is not checked. With that modification (i.e., making two commands instead of one) your code is better. Thanks.