[size=2][/size][size=2][color=#0000ff]Private[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] Form1_Load([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] [/size][size=2][color=#0000ff]MyBase[/color][/size][size=2].Load
[/size][size=2][color=#008000]' Step 1, retrieve and set Last UID used.
[/color][/size][size=2]ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\video\videostore.mdb; Persist Security Info=False"
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] Query1 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Select MAX(Title_Ref) from Videos"
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DBConn1 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection(ConnString)
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DBCmd1 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(Query1, DBConn1)
DBConn1.Open()
LabelLuid.Text = [/size][size=2][color=#0000ff]Ctype[/color][/size][size=2](DBCmd1.ExecuteScalar(),[/size][size=2][color=#0000ff]String[/color][/size][size=2])
DBConn1.Close()
LabelNuid.Text = [/size][size=2][color=#0000ff]CStr[/color][/size][size=2]([/size][size=2][color=#0000ff]CInt[/color][/size][size=2](LabelLuid.Text) + 1)
[/size][size=2][color=#008000]' End Step 1
[/color][/size][size=2][/size][size=2][color=#008000]' Step 2, retreive the Category listing from Categories table
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] Query2 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Select DISTINCT Rental_Category from Rental_Categories"
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DBConn2 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection(ConnString)
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DBCmd2 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(Query2, DBConn2)
DBConn2.Open()
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] myReader2 [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataReader
myReader2 = DBCmd2.ExecuteReader()
[/size][size=2][color=#0000ff]While[/color][/size][size=2] myReader2.Read
CatCBox.Items.Add(myReader2.GetValue(0))
[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]While
[/color][/size][size=2]myReader2.Close()
DBConn2.Close()
CatCBox.Items.Add("Select Category")
CatCBox.SelectedText = "Select Category"
[/size][size=2][color=#008000]' End Step 2
[/color][/size][size=2][/size][size=2][color=#008000]' Step 3, retreive the Ratings
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] Query3 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Select DISTINCT Category FROM Videos"
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DBConn3 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection(ConnString)
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DBCmd3 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(Query3, DBConn3)
DBConn3.Open()
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] myReader3 [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataReader
myReader3 = DBCmd3.ExecuteReader()
[/size][size=2][color=#0000ff]While[/color][/size][size=2] myReader3.Read
RateCBox.Items.Add(myReader3.GetString(0))
[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]While
[/color][/size][size=2]myReader3.Close()
DBConn3.Close()
RateCBox.Items.Add("Select Rating")
RateCBox.SelectedText = "Select Rating"
[/size][size=2][color=#008000]' End step 3
[/color][/size][size=2][/size][size=2][color=#008000]' Step 4 Retreive Genres
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] Query4 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "SELECT DISTINCT Genre FROM Videos"
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DBConn4 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection(ConnString)
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DBCmd4 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(Query4, DBConn4)
DBConn4.Open()
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] myReader4 [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataReader
myReader4 = DBCmd4.ExecuteReader()
[/size][size=2][color=#0000ff]While[/color][/size][size=2] myReader4.Read
GenreCBox.Items.Add(myReader4.GetValue(0))
[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]While
[/color][/size][size=2]myReader4.Close()
DBConn4.Close()
GenreCBox.Items.Add("Select Genre")
GenreCBox.SelectedText = "Select Genre"
[/size][size=2][color=#008000]'End step 4
[/color][/size][size=2]FormatCBox.Items.Add("Select Format")
FormatCBox.SelectedText = "Select Format"
[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub
[/color][/size][size=2][/size][size=2][color=#0000ff]Public[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] TestFields()
TResult = [/size][size=2][color=#0000ff]True
[/color][/size][size=2][/size][size=2][color=#0000ff]If[/color][/size][size=2] IsNothing(TextBoxMnum.Text) [/size][size=2][color=#0000ff]Then[/color][/size][size=2] TResult = [/size][size=2][color=#0000ff]False
[/color][/size][size=2][/size][size=2][color=#0000ff]If[/color][/size][size=2] TextBoxMname.Text [/size][size=2][color=#0000ff]Like[/color][/size][size=2] "Enter Movie Title Here" [/size][size=2][color=#0000ff]Then[/color][/size][size=2] TResult = [/size][size=2][color=#0000ff]False
[/color][/size][size=2][/size][size=2][color=#0000ff]If[/color][/size][size=2] CatCBox.Text [/size][size=2][color=#0000ff]Like[/color][/size][size=2] "Select Category" [/size][size=2][color=#0000ff]Then[/color][/size][size=2] TResult = [/size][size=2][color=#0000ff]False
[/color][/size][size=2][/size][size=2][color=#0000ff]If[/color][/size][size=2] RateCBox.Text [/size][size=2][color=#0000ff]Like[/color][/size][size=2] "Select Rating" [/size][size=2][color=#0000ff]Then[/color][/size][size=2] TResult = [/size][size=2][color=#0000ff]False
[/color][/size][size=2][/size][size=2][color=#0000ff]If[/color][/size][size=2] GenreCBox.Text [/size][size=2][color=#0000ff]Like[/color][/size][size=2] "Select Genre" [/size][size=2][color=#0000ff]Then[/color][/size][size=2] TResult = [/size][size=2][color=#0000ff]False
[/color][/size][size=2][/size][size=2][color=#0000ff]If[/color][/size][size=2] FormatCBox.Text [/size][size=2][color=#0000ff]Like[/color][/size][size=2] "Select Format" [/size][size=2][color=#0000ff]Then[/color][/size][size=2] TResult = [/size][size=2][color=#0000ff]False
[/color][/size][size=2][/size][size=2][color=#0000ff]If[/color][/size][size=2] IsNothing(TextBoxMcost.Text) [/size][size=2][color=#0000ff]Then[/color][/size][size=2] TextBoxMcost.Text = "0"
[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub
[/color][/size][size=2][/size][size=2][color=#0000ff]Private[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] Button1_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] Button1.Click
[/size][size=2][color=#0000ff]Call[/color][/size][size=2] TestFields()
[/size][size=2][color=#0000ff]If[/color][/size][size=2] [/size][size=2][color=#0000ff]Not[/color][/size][size=2] TResult [/size][size=2][color=#0000ff]Then
[/color][/size][size=2]MsgBox("There is an Invalid selection. PLease check your entries, and try again", MsgBoxStyle.OKOnly, "Missing Data")
[/size][size=2][color=#0000ff]Exit[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub
[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If
[/color][/size][size=2][/size][size=2][color=#0000ff]Call[/color][/size][size=2] DoUpdate()
[/size][size=2][color=#0000ff]Call[/color][/size][size=2] ResetForm()
[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub
[/color][/size][size=2][/size][size=2][color=#0000ff]Public[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] ResetForm()
LabelLuid.Text = LabelNuid.Text
LabelNuid.Text = [/size][size=2][color=#0000ff]CStr[/color][/size][size=2]([/size][size=2][color=#0000ff]CInt[/color][/size][size=2](LabelLuid.Text) + 1)
TextBoxMnum.Text = [/size][size=2][color=#0000ff]Nothing
[/color][/size][size=2]TextBoxMname.Text = "Enter Movie Title Here"
CatCBox.Text = "Select Category"
RateCBox.Text = "Select Rating"
GenreCBox.Text = "SelectGenre"
FormatCBox.Text = "SelectFormat"
TextBoxMcost.Text = [/size][size=2][color=#0000ff]Nothing
[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub
[/color][/size][size=2][/size][size=2][color=#0000ff]Public[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] DoUpdate()
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] UpdString [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] MName [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] Mnum [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Integer
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] Rate [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Object
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] Cate [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] Genre [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Object
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] FType [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Object
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DAdded [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Date
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DRelea [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Date
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] UID [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Double
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] RTime [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Single
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] MCost [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Double
[/color][/size][size=2][/size][size=2][color=#008000]' Rental Price is set to Zero
[/color][/size][size=2][/size][size=2][color=#008000]' Status is set to Active'
[/color][/size][size=2]MName = TextBoxMname.Text
Mnum = TextBoxMnum.Text
UID = [/size][size=2][color=#0000ff]CDbl[/color][/size][size=2](LabelNuid.Text)
Genre = [/size][size=2][color=#0000ff]CStr[/color][/size][size=2](GenreCBox.SelectedItem)
DAdded = [/size][size=2][color=#0000ff]CDate[/color][/size][size=2](DateTime.Now)
DRelea = [/size][size=2][color=#0000ff]CDate[/color][/size][size=2](DateTime.Now)
MCost = 0
[/size][size=2][color=#008000]'Retreive Rental Time
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] MCat [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Object
[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] MCTime [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Single
[/color][/size][size=2]MCat = [/size][size=2][color=#0000ff]CStr[/color][/size][size=2](CatCBox.SelectedItem)
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] CatQuery [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "SELECT Rental_time FROM rental_Categories WHERE Rental_Category = '" + MCat + "'"
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] RentConn [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection(ConnString)
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] RentCmd [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(CatQuery, RentConn)
RentConn.Open()
MCTime = [/size][size=2][color=#0000ff]CSng[/color][/size][size=2](RentCmd.ExecuteScalar)
RentConn.Close()
[/size][size=2][color=#008000]'End Retrieve Rental Time
[/color][/size][size=2]Rate = [/size][size=2][color=#0000ff]CStr[/color][/size][size=2](RateCBox.SelectedItem)
FType = [/size][size=2][color=#0000ff]CStr[/color][/size][size=2](FormatCBox.SelectedItem)
UpdString = [/size][size=2][color=#0000ff]String[/color][/size][size=2].Format("INSERT INTO videos (Ref, Title_ref, Title,Genre,Date_Added,Release_Date,Cost_Price,Rental_Category,Rental_Time,Rental_Price,Category,Spare1,Status) Values ('{0}',{1},'{2}','{3}',{4},{5},{6},'{7}',{8},0,'{9}','{10}','Active')", Mnum, UID, MName, Genre, DAdded, DRelea, MCost, MCat, MCTime, Rate, FType)
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] UpdateConn [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection(ConnString)
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] UpdateCmd [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(UpdString, UpdateConn)
UpdateConn.Open()
UpdateCmd.ExecuteNonQuery() ' <-----THIS IS WHERE IT ERRORS OUT
UpdateConn.Close()
MsgBox("Update Completed.", MsgBoxStyle.Information)
[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub
[/color][/size][size=2][/size][size=2][color=#0000ff]Private[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] Button2_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] Button2.Click
[/size][size=2][color=#0000ff]Call[/color][/size][size=2] TestFields()
[/size][size=2][color=#0000ff]If[/color][/size][size=2] [/size][size=2][color=#0000ff]Not[/color][/size][size=2] TResult [/size][size=2][color=#0000ff]Then
[/color][/size][size=2]MsgBox("There is an Invalid selection. PLease check your entries, and try again", MsgBoxStyle.OKOnly, )
[/size][size=2][color=#0000ff]Exit[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub
[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If
[/color][/size][size=2][/size][size=2][color=#0000ff]Call[/color][/size][size=2] DoUpdate()
LabelLuid.Text = LabelNuid.Text
LabelNuid.Text = [/size][size=2][color=#0000ff]CStr[/color][/size][size=2]([/size][size=2][color=#0000ff]CInt[/color][/size][size=2](LabelLuid.Text) + 1)
TextBoxMnum.Text = [/size][size=2][color=#0000ff]Nothing
[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size]
[size=2][color=#0000ff][/color][/size]
[size=2][color=#0000ff]