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 Try

The 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 Try

any ideas

thanks
M

Well you forgot to run the cmd2.
Just before the Catch enter:

cmd2.ExecuteNonQuery

Edited 5 Years Ago by adam_k: n/a

This question has already been answered. Start a new discussion instead.