Distinct in SQL Query

Status
Not open for further replies.

Bahatmut

Active member
Joined
Jun 14, 2005
Messages
29
Location
NY
Programming Experience
Beginner
Distinct in SQL Query-Problem Resolved

I am writing a more ecomplex program than my previous one, one requiring parsing out dinstinct entries from a table (there are about 5 distinct values in on column across 36000 records). Here is my question.

I am using the OleDbDataReader to retrieve the data, my query is

SELECT DISTINCT Category FROM Videos

However, every time I try and execute the reader, the debugger fails. It says that all needed components (Query string, Connection String, and Command) are correct, but it still fails.


And PLEASE don't rip into me for failure to use that POS the MS calls the DataAdapter/DataSet, it's like trying to read cyrillic languages crosseyed and expecting people to get it. (No offense to anyone who native can read cyrillic). Besides, I have NO NEED for a data grid, I am merely trying to populate 4 combo boxes from the database.
 
Last edited:
Fails as in gives the error


An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll


at 'myreader=DBcommand.ExecuteReader()' line

terminating all operations. And yes, all varibles have been checked, spelling has been checked, syntax is all in comformance with that specified by the help files. In the debugger it even properly gives the varibles when asked at the breakign point.

Actuall code:

VB.NET:
[size=2]ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\video\videostore.mdb;ID=;Password=;"

[/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()

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] myReader1 [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataReader

myReader1 = DBCmd1.ExecuteReader()

ConnString is a public string varible, I have also tried removing the ID and password from teh conn string, as the database is NOT encrypted or password protected, I also tried using just 'Admin' for it's access id.

[/size]
 
Last edited:
First there's nothing wrong with using the DataReader, I use it too. I also use the DA and the DS/DT, but only when it makes sense.

Aaaanyways... It might, just might, be helpful to see more of the code. How you create your connectionstring, open the connection, build the command object, etc.

Otherwise it's like trying to get a diagnosis from your dr over the phone. "See doc, it hurts when I do this." "Do what?" "This!"....

Tg
 
PHP:
Dim myConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\video\videostore.mdb; Persist Security Info=False;"
 
Dim oledbcon As New OleDbConnection(myConn)
 
 
 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
Try
 
If oledbcon.State = ConnectionState.Closed Then
 
oledbcon.Open()
 
End If
 
Dim strSQL As String = "SELECT MAX(Title_Ref ) FROM Videos"
 
Dim cmd As OleDbCommand = New OleDbCommand(strSQL, oledbcon)
 
cmbNN.Items.Add(cmd.ExecuteScalar()) 'i've used comboBox here but you can pass value to any other control
 
 
Catch ex As Exception
 
MessageBox.Show(ex.Message)
 
Finally
 
oledbcon.Close()
 
End Try
 
End Sub

Cheers ;)
 
Ok, here is the complete code for what I am doing thus far. The ConnString is a Public declared varible further up the form.


VB.NET:
[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()

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] myReader1 [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataReader

myReader1 = DBCmd1.ExecuteReader()

[/size][size=2][color=#0000ff]While[/color][/size][size=2] myReader1.Read()

LabelLuid.Text = [/size][size=2][color=#0000ff]CStr[/color][/size][size=2](myReader1.GetValue(0))

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]While

[/color][/size][size=2]myReader1.Close()

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.GetString(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()

[/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()

[/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 Genres 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.GetString(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()

[/size][size=2][color=#008000]'End step 4

[/color][/size]

I added teh Security Persist and it still gives the same error. The goal here si to pull the last (max) UID out, and pull out the distinct Ratings and categories and genres for the video records.
 
As Jim told you you are not using MAX function correctly.
The MAX function returns the highest value in a column (NOTE that NULL values are not included in the calculation) so, you should make some changes in your code.


for instance:

PHP:
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\video\videostore.mdb; Persist Security Info=False"
 
Dim Query1 AsString = "Select MAX(Title_Ref) from Videos"
 
Dim DBConn1 As New OleDbConnection(ConnString)
 
Dim DBCmd1 As New OleDbCommand(Query1, DBConn1)
 
DBConn1.Open()
 
LabelLuid.Text = Ctype(DBCmd1.ExecuteScalar(), String) 'note that you don't need to perform conversion
 
 
{...}


Cheers ;)

P.S. Also the MAX function can also be used on text columns, to find the highest or lowest value in alphabetical order :)


________________________________________________________________
Using the DISTINCT keyword looks fine to me even i don't think so you have designed very well your DB (there must be separated table for almost everything/ genres, actors, titles etc. but however i think it hasn't throwed that exception.
Also, i noted that you are opening and slosing a connection each time you want to fetch a data. It's wrong!!! Just simply open connection retrieve all needed data and finally close it.

i.e.
VB.NET:
Try
 
conn.Open
'step 1 / get some data
'step 2 / get some data
'step 3 /get some data
Catch ex as exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
End try
 
Last edited:
As Kulrom says, don't create 4 different connections with the same connection string and open each seperately. Create 1 connection, open it, execute all the queries and finally close the connection. One last thing: when you start using it correctly, the MAX function will return a single value so you should call ExecuteScalar on the command rather than ExecuteReader. Like your multiple connections, it will work the other way but it is less efficient.
 
Ok, the whole ExecuteScalar thing is royally throwing me for a loop.....I have no clue what it is, so no offense, I am going to use DataReader. And I cannot open multiple datareaders at once, hence the open, read data, close repeat process approach. I am a self taught coder..I am NOT college educated or professionally trained. PLEASE bear that in mind when you try and give solutions to the problem. If I cannot understand what the code is doing, I will be even more hard pressed to debug it in the future. I will try some of the suggestions, and hopefully be able to get past this.

As for efficiency, that is already a non-issue,a as the DB being read has no indexing or keys, ao all performance increases from use of a true relational DB are lost from teh get go.
 
If you're self-taught then I would have thought that consulting the help documentation wouldn't be a problem. I am university-trained but all that means is that I understand the principles of OOP going in. I taught myself VB.NET, C# and the whole .NET platform purely by doing while self-employed and working from home, so I had to learn about all these classes and methods for myself as well by reading and experimenting.

ExecuteScalar is a method of the OleDbCommand. You call it on commands that contain a query that will return a single value and it returns that value as an Object, which you then cast as the correct type using CInt, CStr or whatever is appropriate.

As for the multiple connections, you can only have 1 OleDbDataReader open on a single connection at a time. All that meas is that you when using a single connection you need to Close each reader before you execute the next. You're doing that already so using a single connection won't be a problem.

Just because one aspect of your application is inefficient by necessity doesn't mean you should accept inefficiency elsewhere if you don't have to. Learn the proper way to do things now so that when you get onto a project where it really does matter you will already be prepared.

Be assured that I am not having a go at you. This is all intended to be constructive criticism. No-one expects that any of us know everything, particularly as we're starting out, but it's important to learn the "proper" way to do things whenever the opportunity arises. You and your customers will be the ones to benefit.
 
Very good points Jim!
palec.gif
 
Last Question.

Alright...now desipte the several migraines, and now total lack of understanding on what this si doing by implementing code you suggested that I do no comprehend, I am gettign one final error, and Yes, I tired th help files, no information there, I tried the MSDN, no help there. Everything I can determine tells me it is reight, but I am getting a -- An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll-- error when I actually try and insert the data back into the DB as a new record. Below is the complete code, sorry for length. I have double and triple checked to amke sure all the right number and formats of data are being passed into the INSERT string as well. All other routines appear to work perfectly when the main actual update routine was just a msgbox I put there so I could test the other parts of the program.

VB.NET:
[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]
[/color][/size]
 
actually the error is occuring in the line UpdString = String.Format( .....

Moreover it should look like:

UpdString = "UPDATE videos Set Ref = Mnum, Title = UID etc....
{..}
now you can call UpdateCmd.ExecuteNonQuery()

Btw,
i missed string formating purposely cuz i think in yoru case (you can find more about this at the end of this post) it should be replaced with StringBuilder class instead.

i.e.

Dim sb As New StringBuilder()
sb.AppendFormat("UPDATE videos SET ......

namely, the string formatting mechanism provided by the .NET Framework is extremely powerful and i found it as very fine control over how strings are built and how different types and values are represented in strings. This mechanism is extremely flexible and enables you to generate sophisticated customized string output very easily and i really love it.
However, for small concatenation operations, using the String.Format method is probably more efficient than using the StringBuilder.AppendFormat method. When you start increasing the number of concatenation operations, however, StringBuilder becomes the clear winner.

Cheers ;)


 
Status
Not open for further replies.
Back
Top