-
rkulp
  - Joined on 06-28-2008
- Rural Coffee County, Tennessee
- Posts 73
|
Two Date Inserts -- One works and one fails -- Why? (SOLVED)
I have two insert routines to insert a record into each of two tables. The date field is typed as "Date." The first insert (new member) works while the second one (record attendance) fails. In fact, the VS2010 designer won't let me put in the date type saying it is not a DBType. It also won't take "yyyy-mm-dd" format saying it won't convert to integer. So, what do I do? The code is below. Public Sub InsertNewFamilyMember(ByRef LastName As String, ByRef FirstName As String, ByRef MI As String, ByRef Telephone As String, ByRef SpecialCondition As String, ByRef DLA As Date, ByRef Active As Boolean, ByRef FamilyID As Integer)
Dim SQLParam As New SQLiteParameter("ReturnID", SqlDbType.Int)Dim cnMember As SQLiteConnection = New SQLiteConnection(My.Settings.conCAT)
cnMember.Open()
Const InsertNewMember As String = "INSERT INTO MbrData (LastName, FirstName,MI,Telephone,SpecialCondition, DateLastAttended, Active,FamilyID) VALUES (@LastName, @FirstName,@MI,@Telephone,@SpecialCondition, @DateLastAttended, @Active,@FamilyID); SELECT last_insert_rowid() as NewMbrID"
TryDim cmdNewMember As SQLiteCommand = New SQLiteCommand(InsertNewMember, cnMember)
cmdNewMember.CommandType = CommandType.TextcmdNewMember.Parameters.AddWithValue("@LastName", LastName)
cmdNewMember.Parameters.AddWithValue( "@FirstName", FirstName)cmdNewMember.Parameters.AddWithValue("@MI", MI)
cmdNewMember.Parameters.AddWithValue( "@Telephone", Telephone)cmdNewMember.Parameters.AddWithValue("@SpecialCondition", SpecialCondition)
cmdNewMember.Parameters.AddWithValue( "@DateLastAttended", DLA)cmdNewMember.Parameters.AddWithValue("@Active", 1)cmdNewMember.Parameters.AddWithValue("@FamilyID", CurrentFamilyID)
NewMbrID = cmdNewMember.ExecuteScalar()
cmdNewMember.Parameters.Clear()
Catch ex As ExceptionMsgBox("The following error occurred adding the new family member: " & vbCrLf + ex.Message)
End Try
Try
If Not IsDBNull(DLA) ThenDim ServiceDate As String = DLA.Year.ToString + "-" + DLA.Month.ToString + "-" + DLA.Day.ToString
Dim InsertNewAttendanceRecord As String = "INSERT INTO Attendance (Year,DateOfService,Present,MbrID) VALUES (@Year,@DateOfService,@Present,@MemberID)"Dim cmdNewAttendanceRecord As SQLiteCommand = New SQLiteCommand(InsertNewAttendanceRecord, cnMember)
With cmdNewAttendanceRecord.CommandType = CommandType.Text
.Parameters.Add( "@Year", DLA.Year).Parameters.Add("@DateOfService", ServiceDate)
.Parameters.Add( "@Present", 1).Parameters.Add("@MbrID", NewMbrID)
End With
cmdNewAttendanceRecord.ExecuteNonQuery()
cmdNewAttendanceRecord.Parameters.Clear()
End If
Catch ex As ExceptionMsgBox("The following error occurred adding the attendance record: " + vbCrLf + ex.Message)
FinallyfrmMain.AttendanceTableAdapter.FillByMbrID(frmMain.CATDataSet.Attendance, New System.Nullable(Of Integer)(NewMbrID))
frmMain.MbrDataTableAdapter.FillByFamilyID(frmMain.CATDataSet.MbrData, New System.Nullable(Of Long)(CType(CurrentFamilyID, Long)))frmMain.FamilyDataTableAdapter.FillByFamilyID(frmMain.CATDataSet.FamilyData, New System.Nullable(Of Integer)(CType(CurrentFamilyID, Integer)))
End Try
End Sub
|
|