Hi I have managed to sort out my date issue however although I have used the same code format on of my tables is not being updated and I am not getting any error msg so not sure why it will not work
this is my code
cmd1 = conn.CreateCommand
cmd1.CommandText = "INSERT INTO tenant(t_id, firstName, lastName, email, phone, dob, rentPaid, rent, dateIn, deposit, service, rentDueDate, property, notes, existing) VALUES(@t_id, @firstName, @lastName, @Email, @phone, @dob, @rentPaid, @rent, @dateIn, @deposit, @service, @rentDueDate, @property, @notes, @existing)"
' Add Parameters to Command Parameters collection
cmd1.Parameters.Add(New SqlParameter("@t_id", SqlDbType.VarChar, 10))
cmd1.Parameters("@t_id").Value = TextBoxTenantId.Text
cmd1.Parameters.Add(New SqlParameter("@firstName", SqlDbType.VarChar, 20))
cmd1.Parameters("@firstName").Value = TextBoxFirstName.Text
cmd1.Parameters.Add(New SqlParameter("@lastName", SqlDbType.VarChar, 20))
cmd1.Parameters("@lastName").Value = TextBoxLastName.Text
cmd1.Parameters.Add(New SqlParameter("@email", SqlDbType.VarChar, 250))
cmd1.Parameters("@email").Value = MaskedEditBoxEmail.Text
cmd1.Parameters.Add(New SqlParameter("@phone", SqlDbType.Decimal, 18, 0))
cmd1.Parameters("@phone").Value = TextBoxPhone.Text
cmd1.Parameters.Add(New SqlParameter("@dob", SqlDbType.DateTime))
cmd1.Parameters("@dob").Value = DateTimePickerDob.Value
cmd1.Parameters.Add(New SqlParameter("@rentPaid", SqlDbType.Decimal, 18, 0))
cmd1.Parameters("@rentPaid").Value = MaskedEditBoxRentPaid.Text
cmd1.Parameters.Add(New SqlParameter("@rent", SqlDbType.Decimal, 18, 0))
cmd1.Parameters("@rent").Value = TextBoxRent.Text
cmd1.Parameters.Add(New SqlParameter("@dateIn", SqlDbType.DateTime))
cmd1.Parameters("@dateIn").Value = DateTimePickerDateIn.Value
cmd1.Parameters.Add(New SqlParameter("@deposit", SqlDbType.Decimal, 18, 0))
cmd1.Parameters("@deposit").Value = MaskedEditBoxDeposit.Text
cmd1.Parameters.Add(New SqlParameter("@service", SqlDbType.Decimal, 18, 0))
cmd1.Parameters("@service").Value = MaskedEditBoxService.Text
cmd1.Parameters.Add(New SqlParameter("@rentDueDate", SqlDbType.VarChar, 2))
cmd1.Parameters("@rentDueDate").Value = TextBoxRentDue.Text
cmd1.Parameters.Add(New SqlParameter("@property", SqlDbType.VarChar, 10))
cmd1.Parameters("@property").Value = DropDownList1.Text
cmd1.Parameters.Add(New SqlParameter("@notes", SqlDbType.Text))
cmd1.Parameters("@notes").Value = TextBoxNotes.Text
cmd1.Parameters.Add(New SqlParameter("@existing", SqlDbType.Bit, 0))
cmd1.Parameters("@existing").Value = True
'update payments
Dim connPaString As String = My.Settings.strConn
Dim connPa As New SqlConnection(connPaString)
Try
connPa.Open()
cmd2 = conn.CreateCommand
cmd2.CommandText = "INSERT INTO payments(paid, firstName, lastName, propRef, t_id) VALUES(@paid, @firstName, @lastName, @propRef, @t_id)"
' Add Parameters to Command Parameters collection
cmd2.Parameters.Add(New SqlParameter("@paid", SqlDbType.Decimal, 18, 0))
cmd2.Parameters("@paid").Value = RadMaskedEditBoxRentPaid.Text
cmd2.Parameters.Add(New SqlParameter("@firstName", SqlDbType.VarChar, 20))
cmd2.Parameters("@firstName").Value = TextBoxFirstName.Text
cmd2.Parameters.Add(New SqlParameter("@lastName", SqlDbType.VarChar, 20))
cmd2.Parameters("@lastName").Value = TextBoxLastName.Text
cmd2.Parameters.Add(New SqlParameter("@propRef", SqlDbType.VarChar, 10))
cmd2.Parameters("@propRef").Value = DropDownList1.Text
cmd2.Parameters.Add(New SqlParameter("@t_id", SqlDbType.VarChar, 10))
cmd2.Parameters("@t_id").Value = TextBoxTenantId.Text
Catch ex As SqlException
MessageBox.Show("Error while updating record on table..." & ex.Message, "Update Records")
Finally
connPa.Close()
End Try
'update property table
Dim connUpString As String = My.Settings.strConn
Dim connUp As New SqlConnection(connUpString)
Try
connUp.Open()
Dim updateSql As String = "UPDATE property " & "SET available = @available " & "WHERE propRef = @propRef"
Dim cmdUp As New SqlCommand(updateSql, connUp)
cmdUp.Parameters.Add("@available", SqlDbType.Bit, 0)
cmdUp.Parameters.Add("@propRef", SqlDbType.VarChar, 10, "propRef")
cmdUp.Parameters("@available").Value = 0
cmdUp.Parameters("@propRef").Value = Me.RadDropDownList1.Text
cmdUp.ExecuteNonQuery()
Catch ex As SqlException
MessageBox.Show("Error while updating record on table..." & ex.Message, "Update Records")
Finally
connUp.Close()
End Try
check = cmd1.ExecuteReader.RecordsAffected()
If check > 0 Then
MsgBox("Tennant " & Trim(TextBoxFirstName.Text) & " " & Trim(TextBoxLastName.Text) & " successfully added", MsgBoxStyle.OkOnly, "Message :")
Else
MsgBox("Tennant " & Trim(TextBoxFirstName.Text) & Trim(TextBoxLastName.Text) & " Failure not added", MsgBoxStyle.OkOnly, "Message :")
End If
'Refresh_Form()
End If
Catch ex As Exception
MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
Finally
conn.Close()
End TryThe first insert works and so does the update for some reason this does not work
Dim connPaString As String = My.Settings.strConn
Dim connPa As New SqlConnection(connPaString)
Try
connPa.Open()
cmd2 = conn.CreateCommand
cmd2.CommandText = "INSERT INTO payments(paid, firstName, lastName, propRef, t_id) VALUES(@paid, @firstName, @lastName, @propRef, @t_id)"
' Add Parameters to Command Parameters collection
cmd2.Parameters.Add(New SqlParameter("@paid", SqlDbType.Decimal, 18, 0))
cmd2.Parameters("@paid").Value = RadMaskedEditBoxRentPaid.Text
cmd2.Parameters.Add(New SqlParameter("@firstName", SqlDbType.VarChar, 20))
cmd2.Parameters("@firstName").Value = TextBoxFirstName.Text
cmd2.Parameters.Add(New SqlParameter("@lastName", SqlDbType.VarChar, 20))
cmd2.Parameters("@lastName").Value = TextBoxLastName.Text
cmd2.Parameters.Add(New SqlParameter("@propRef", SqlDbType.VarChar, 10))
cmd2.Parameters("@propRef").Value = DropDownList1.Text
cmd2.Parameters.Add(New SqlParameter("@t_id", SqlDbType.VarChar, 10))
cmd2.Parameters("@t_id").Value = TextBoxTenantId.Text
Catch ex As SqlException
MessageBox.Show("Error while updating record on table..." & ex.Message, "Update Records")
Finally
connPa.Close()
End Tryany ideas
thanks
M
