VS 2003 Master-Detail Issue

[adshocker]

Hi all,

Im trying to create a master-detail transaction where i can insert, edit and delete records.

here's what i have so far... just want someone to check if im on the right direction

[SIZE="2"]Private Function rpUpdate_Command()
 Dim v_Update As New OracleCommand("UPDATE rental_payments SET " & _
                                    "tran_id = :tran_id, " & _
                                    "cust_no = :cust_no, " & _
                                    "payment_mode = :payment_mode, " & _
                                    "paid_amt = :paid_amt, " & _
                                    "banc_cd = :banc_cd, " & _
                                    "card_type = :card_type, " & _
                                    "card_chk_no = :card_chk_no, " & _
                                    "paid_sw = :paid_sw " & _
                                    "WHERE tran_id = :tran_id", ORCL_CONN)
 With v_Update
  .Parameters.Add(":tran_id", OracleType.Number, 12, "tran_id")
	.Parameters.Add(":cust_no", OracleType.Number, 12, "cust_no")
	.Parameters.Add(":payment_mode", OracleType.VarChar, 15, "payment_mode")
	.Parameters.Add(":paid_amt", OracleType.Number, 12, "paid_amt")
	.Parameters.Add(":banc_cd", OracleType.VarChar, 20, "banc_cd")
	.Parameters.Add(":card_type", OracleType.VarChar, 20, "card_type")
	.Parameters.Add(":card_chk_no", OracleType.VarChar, 50, "card_chk_no")
	.Parameters.Add(":paid_sw", OracleType.VarChar, 5, "paid_sw")
 End With
 Return v_Update
End Function

Private Function rpInsert_Command()
 Dim v_Insert As New OracleCommand("INSERT INTO rental_payments " & _
                "(tran_id, cust_no, payment_mode, paid_amt, banc_cd, card_type, paid_sw, card_chk_no) " _
                & "VALUES (:tran_id, :cust_no, :payment_mode, :paid_amt, :banc_cd, :card_type, initcap(:paid_sw), :card_chk_no)", ORCL_CONN)
 With v_Insert
	.Parameters.Add(":tran_id", OracleType.Number, 12, "tran_id")
	.Parameters.Add(":cust_no", OracleType.Number, 12, "cust_no")
	.Parameters.Add(":payment_mode", OracleType.VarChar, 15, "payment_mode")
	.Parameters.Add(":paid_amt", OracleType.Number, 12, "paid_amt")
	.Parameters.Add(":banc_cd", OracleType.VarChar, 20, "banc_cd")
	.Parameters.Add(":card_type", OracleType.VarChar, 20, "card_type")
	.Parameters.Add(":card_chk_no", OracleType.VarChar, 50, "card_chk_no")
	.Parameters.Add(":paid_sw", OracleType.VarChar, 5, "paid_sw")
 End With
 Return v_Insert
End Function

Private Function rpDelete_Command()
 Dim v_Delete As New OracleCommand("DELETE from rental_payments " & _
 "WHERE tran_id = :tran_id", ORCL_CONN)

 With v_Delete
	.Parameters.Add(":tran_id", OracleType.Number, 12, "tran_id")
 End With
 Return v_Delete
End Function

Private Function rpSelect_Command()
 Dim v_Select As New OracleCommand("SELECT * FROM rental_payments ORDER BY tran_id", ORCL_CONN)
 Return v_Select
End Function

Private Function rvSelect_Command()
 Dim v_Select As New OracleCommand("SELECT * FROM rented_video WHERE tran_id = :tran_id", ORCL_CONN)

 With v_Select
	.Parameters.Add(":tran_id", dtRentalPayments.Columns("tran_id"))
 End With
 Return v_Select
End Function

Private Function rvUpdate_Command()
 Dim v_Update As New OracleCommand("UPDATE rented_video SET " & _
                                        "video_id = :video_id, " & _
                                        "cust_no = :cust_no, " & _
                                        "tran_id = :tran_id, " & _
                                        "date_rented = :date_rented, " & _
                                        "date_due = :date_due, " & _
                                        "returned = :returned, " & _
                                        "last_updated = :last_updated, " & _
                                        "rent_amt = :rent_amt " & _
                                        "WHERE video_id = :video_id " & _
                                        "AND cust_no = :cust_no " & _
                                        "AND tran_id = :tran_id", ORCL_CONN)
 With v_Update
	.Parameters.Add(":video_id", OracleType.Number, 6, "video_id")
	.Parameters.Add(":cust_no", OracleType.Number, 6, "cust_no")
	.Parameters.Add(":tran_id", OracleType.Number, 6, "tran_id")
	.Parameters.Add(":date_rented", OracleType.DateTime, vbShortDate, "date_rented")
	.Parameters.Add(":date_due", OracleType.DateTime, vbShortDate, "date_due")
	.Parameters.Add(":returned", OracleType.VarChar, 5, "returned")
	.Parameters.Add(":last_updated", OracleType.DateTime, vbShortDate, "last_updated")
	.Parameters.Add(":rent_amt", OracleType.Number, 12, "rent_amt")
 End With
 Return v_Update
End Function

Private Function rvInsert_Command()
 Dim v_Insert As New OracleCommand("INSERT INTO rented_video (video_id, cust_no, tran_id, " & _
 date_rented, date_due, last_updated, returned, rent_amt) " & _
 "VALUES (:video_id, :cust_no, :tran_id, :date_rented, :date_due, :last_updated, :returned, :rent_amt)", ORCL_CONN)

 With v_Insert
	.Parameters.Add(":video_id", OracleType.Number, 6, "video_id")
	.Parameters.Add(":cust_no", OracleType.Number, 6, "cust_no")
	.Parameters.Add(":tran_id", OracleType.Number, 6, "tran_id")
	.Parameters.Add(":date_rented", OracleType.DateTime, vbShortDate, "date_rented")
	.Parameters.Add(":date_due", OracleType.DateTime, vbShortDate, "date_due")
	.Parameters.Add(":returned", OracleType.VarChar, 5, "returned")
	.Parameters.Add(":last_updated", OracleType.DateTime, vbShortDate, "last_updated")
	.Parameters.Add(":rent_amt", OracleType.Number, 12, "rent_amt")
 End With
 Return v_Insert
End Function

Private Function rvDelete_Command()
 Dim v_Delete As New OracleCommand("DELETE FROM rented_video WHERE tran_id = :tran_id", ORCL_CONN)
 With v_Delete
	.Parameters.Add(":tran_id", dtRentalPayments.Columns("tran_id"))
 End With
 Return v_Delete
End Function

Private drRental As DataRelation
Private daRentalPayments As OracleDataAdapter
Private daRentedVideo As OracleDataAdapter
Private dtRentalPayments As DataTable
Private dtRentedVideo As DataTable
Private dsTransaction As DataSet

Private Sub Initialize_Data()
 dtRentalPayments = New DataTable
 dtRentedVideo = New DataTable
 dsTransaction = New DataSet

 daRentalPayments = Create_DataAdapter(rpSelect_Command, rpInsert_Command, rpDelete_Command, rpUpdate_Command)
 daRentedVideo = Create_DataAdapter(rvSelect_Command, rvInsert_Command, rvDelete_Command, rvUpdate_Command)

 daRentalPayments.Fill(dsTransaction, "rental_payments")
 daRentalPayments.Fill(dsTransaction, "rented_video")
 
 dtRentalPayments = dsTransaction.Tables("rental_payments")
 dtRentedVideo = dsTransaction.Tables("rented_video")

 drRental = New DataRelation("Rental", dtRentalPayments.Columns("tran_id"), dtRentedVideo.Columns("tran_id"))
 dsTransaction.Relations.Add(drRental)
End Sub

Private Sub Bind_Master()
 txtTranID.DataBindings.Add("Text", dtRentalPayments, "tran_id")
 txtCustNo.DataBindings.Add("Text", dtRentalPayments, "cust_no")
End Sub[/SIZE]

please help me... thanks.

 

[cjard]

It seems correct, but boy do I feel sorry for you having to write all that crap.. Werent you supposed to be upgrading to 2005? What you got there, I can do in about 8 clicks :/

 

[adshocker]

i already upgraded to VS 2005 a few days ago but i was having a little bit of a problem adjusting.
but when you put it this way, i thinks i'll be better off learning 2005. =)

thanks.

 

[cjard]

Data Sources window
Add Data Source
From Database
Set up the oracle connection
Pick which tables to include
Click Finish