[02/03] Problems with a DA Class

[ddmeightball]

Hello,

I am trying to write a data access class that will write a food order to a microsoft access database. I am having problems with writing to the database.

I am only able to write one piece of an order to the access database, even though there are multiple items in the arrraylist. When I choose to Place Order(write the items in the arraylist to the DA Class) I get the error

System.InvalidOperationException: ExecuteNonQuery: Connection property has not been initialized.

I understand that it says the connection has not been intialized, but I dont understand why I am getting this error. On line 57 of my Order class I call the OrderDA.Intialize method to initialize the connection. It seems like it can do it once, since it writes the first item of the arraylist. To me it seems like a problem with my connection but I cant see it.

Below is my code in a zip file, I have also included the access database that I am using, if that will help.

[ddmeightball]

I am wondering if my problem is with where I am initializing my connection. I initialize the connection in my ORder class, right before I send the orderNum, orderDate, and ArrayList. I was reading online that I need to initialize the connection each time that I write to the database? Is this true because if it is I need to initialize the connection in my for each loop in the order DA class.

What do you think? ANy other suggestions about the error?

[ddmeightball]

i set break points and when it tries to put the second item into the database it says it cant because of a duplicate key issue. Man I feel kinda dumb.

System.Data.OleDb.OleDbException: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

EDIT::

It also doesnt help when i dont clear the array list that had the previous order in it, therefore it would try to put those food items back into the database again.

[Crushinator]

Hey ddmeightball,

Don't get Discouraged.

In your Access Database in the table you are trying to insert to you have a Primary Key Defined. This Key Must be different, it's what sets that record apart from all the others.

In some fashion when you are attempting the Insert Statement you are trying to put a Value in the Primary Key area that is already there. Hence the Duplicate-Value-in-the-Key-Column Error.

When executing the class that does the Insert I noticed you had a loop. For each Product in Something type loop. I would setup breakpoints and add watches so you can see exactly what values you are trying to add. Seeing what values you are attempting to Insert may shed some light on your problem.

[Shaggy Hiker]

If the code isn't too big, it would be much better if you could post it (with the appropriate VBCode tags, of course). There are several folks here who are more than a little reluctant to download a file, but very willing to look through posted code.

[ddmeightball]

If the code isn't too big, it would be much better if you could post it (with the appropriate VBCode tags, of course). There are several folks here who are more than a little reluctant to download a file, but very willing to look through posted code.

The Da class is a little long but it is doable. Let me post what I am working on right now. I am trying to search the access database and get all the records with a certain orderID. The order class is just a class that holds an array list of product objects.

Here is the button click method in my form that sends a value to the Order class, which then sends the values to the DA class

Private Sub btnInvoiceDA_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInvoiceDA.Click
        Me.itemsOrdered = order1.OrderDAFind(CType(txtIndex.Text, Integer))
 
        rtbInvoice.Visible = True
        rtbInvoice.Clear()
 
        For i As Integer = 0 To Me.itemsOrdered.Count
            rtbInvoice.AppendText(CType(Me.itemsOrdered.Item(i), String))
        Next
    End Sub

Here is my Order class method that recieves the order number that the user entered and sends it to the da class.

Public Function OrderDAFind(ByVal index As Integer) As ArrayList
        OrderDA.Initialize()
        itemsOrderedDB = OrderDA.FindOrder(index)
        OrderDA.Terminate()
        Return itemsOrderedDB
    End Function

Here is my Order DA Find method. It isnt working right now. When I try to fill my dataadapter I am getting an error

Public Shared Function FindOrder(ByVal index As Integer) As ArrayList
        Dim sqlInsert As String = "SELECT * FROM order WHERE orderID=" & index
 
        Dim dsOrder As New DataSet
 
        Try
            Dim adpCustomer As New OleDbDataAdapter(sqlInsert, cnnOrder)
            adpCustomer.Fill(dsOrder, "foodorder")'error here
            If dsOrder.Tables("foodorder").Rows.Count > 0 Then
                Dim dsRow As DataRow
                ' Clear the array list
                orders.Clear()
                For Each dsRow In dsOrder.Tables("foodorder").Rows
                    Dim p As Product
                    p.ProductName = dsRow("ProductName")
                    p.ProductID = dsRow("ProductID")
                    p.Price = dsRow("ProductPrice")
                    p.Quantity = dsRow("Quantity")
                    p.mTotal = dsRow("Total")
                    orders.Add(New Order(dsRow("OrderID"), dsRow("OrderDate"), p))
                Next
            Else
                MessageBox.Show("No Records In The Database.")
            End If
            dsOrder = Nothing
        Catch e As Exception
            Console.WriteLine(e.ToString)
            MessageBox.Show(e.ToString)
        End Try
 
        Return orders
    End Function

Here is my error. Since there are two records in my database, the order num can be either 1 or 2

System.Data.OleDb.OleDbException: Syntax error in FROM clause.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at Program3.OrderDA.FindOrder(Int32 index) in C:\Documents and Settings\Matt\Desktop\Program3\Program3\OrderDA.vb:line 68
An unhandled exception of type 'System.ArgumentOutOfRangeException' occurred in mscorlib.dll

Additional information: Index was out of range. Must be non-negative and less than the size of the collection.

[ddmeightball]

Oh, btw here is an update of my code

[Shaggy Hiker]

Boy, I hate SQL errors, the information with them is generally not so valuable. Error in FROM clause? Two thoughts. I assume that order is a table name. Unfortunately, it is also a key word in SQL, so you should have some real trouble using it as a table name.

[TokersBall_CDXX]

try putting square brackets around the keyword
[order]

[ddmeightball]

try putting square brackets around the keyword
[order]

What do you mean? I dont understand where or why u want me to do this. Could you explain it a little. Thanks

[ddmeightball]

Oh, btw here is an update of my code. I was thinking that I might be missing some delimeter in my SQL code but I cant see any. Can anyone perhaps see it.

Im dont believe I am missing any. I cant see it if I am. I will post a new version of my code in this post for anyone interested.

Public Shared Function FindOrder(ByVal index As Date) As ArrayList
        Dim sqlInsert As String
        Dim dsRow As DataRow
        Dim dsOrder As New DataSet
        dsOrder.Clear()
 
        Try
            sqlInsert = "SELECT * FROM foodorder WHERE Date=" & "#" & index & "#"
            Dim adpCustomer As New OleDbDataAdapter(sqlInsert, cnnOrder)
            adpCustomer.Fill(dsOrder, "foodorder")
 
            If dsOrder.Tables("foodorder").Rows.Count > 0 Then
                'found an order with that date
                orders.Clear()
 
                For Each dsRow In dsOrder.Tables("foodorder").Rows
                    Dim p As New Product
                    p.ProductName = dsRow("ProductName")
                    p.ProductID = dsRow("ProductID")
                    p.Price = dsRow("ProductPrice")
                    p.Quantity = dsRow("Quantity")
                    p.mTotal = dsRow("Total")
                    orders.Add(New Order(dsRow("orderNum"), dsRow("Date"), p))
                Next
            Else
                MessageBox.Show("No Record Found In The Database. AE The Order With That Date Doesnt Exist.")
            End If
            dsOrder = Nothing
        Catch e As Exception
            Console.WriteLine(e.ToString)
            MessageBox.Show(e.ToString)
        End Try
 
        Return orders
    End Function

I dont know if I explained my self properly with what I am trying to do with this find method. I am trying to retrieve a dataset of orders from the database based on a specific date. For each order, I want to show the order number, the total cost of that order and the total for all the orders for that data. I was trying to use an integer earlier just to see if I could just get somethind from the the database.

Thanks for all the help btw

[Shaggy Hiker]

Ok, I see that you changed the name order to foodorder. That should solve the key word conflict from the earlier code. Frankly, everything you have posted here looks reasonable, though you could run into problems if one of the fields is NULL. Also, it is clear that you are not using Option Strict On. Setting that is a good idea, though I avoided it myself for a year or so. It makes you type more code, which can be a little tedious, and if you turn it on for an existing program, expect to spend an hour or two fixing the errors you get, but all that you are adding is beneficial. Without Option Strict On, you can do what you are doing with lines like this:

p.ProductName = dsRow("ProductName")


With Option Strict On, you have to specifically cast the ProductName into a string. Tedious, but it is happening already, and it is more efficient to explicitly cast it rather than let the compiler do it implicitly. This can also catch a few minor bugs.

However, that is unlikely to have anything to do with your question. So, what error are you getting now, and what line are you getting the error on?

[ddmeightball]

Well, how's it going?

Not too well unforunatly for me, I can actually get data back from the database, but not when I try to search for a specific date. WHen I try to search for a date in the database it always give me a data type mismatch. I switched it back to an integer value tha tI am searching the database for, just to make sure that I can actually get values from the database.

In my insert method, i put a vb date object into the database. When I went to look at the table in the database, it shows the date for one of the orders as Date: 12/30/1899 12:02:09 AM.

Its the same for every record in the database. Is there some way I should be putting a date in to the access database properly? And how do you recommend that I get a value from the user to best search a database for that date that they entered? Here is my code for the OrderDA find method.

    Public Shared Function FindOrder(ByVal index As Integer) As ArrayList
        Dim sqlInsert As String
        Dim dsRow As DataRow
        Dim dsOrder As New DataSet
        dsOrder.Clear()

        Try
            'sqlInsert = "SELECT * FROM foodorder WHERE Date=" & "#" & index & "#"
            'sqlInsert = "SELECT * FROM `foodorder` WHERE Date='" & index & "'"
            sqlInsert = "SELECT * FROM `foodorder` WHERE OrderNum=" & index

            Dim adpCustomer As New OleDbDataAdapter(sqlInsert, cnnOrder)
            adpCustomer.Fill(dsOrder, "foodorder")

            If dsOrder.Tables("foodorder").Rows.Count > 0 Then
                'found an order with that date
                orders.Clear()

                For Each dsRow In dsOrder.Tables("foodorder").Rows
                    Total = CType(dsRow("Total"), Double)
                    OrderNum = CType(dsRow("orderNum"), Integer)
                    OrderDate = CType(dsRow("Date"), Date)
                    orders.Add(New Order(OrderNum, Total, OrderDate))
                Next
            Else
                MessageBox.Show("No Record Found In The Database. AE The Order With That Date Doesnt Exist.")
            End If
            dsOrder = Nothing
        Catch e As Exception
            Console.WriteLine(e.ToString)
            MessageBox.Show(e.ToString)
        End Try
        Return orders
    End Function

I am wondering if I should just change the field in the databse from a datetime to a Char(10). I dont have Microsoft access so I will unfortunately have to write an SQL statement that will first drop the primary key(orderID, Date) drop the date column then add another column that is a Char(10). I think it will then be easier to search for a string "date" in the database than actually searching for an actual date.

Instead of putting a date object into the Microsoft Access database, should I just send it three values representing the day, month and year? Any suggestions? I will post the full version of my code in another zip file at the end of this message.

[VBDT]

I don't think you are making a search with this

If dsOrder.Tables("foodorder").Rows.Count > 0 Then
'found an order with that date
orders.Clear()

For Each dsRow In dsOrder.Tables("foodorder").Rows
Total = CType(dsRow("Total"), Double)
OrderNum = CType(dsRow("orderNum"), Integer)
OrderDate = CType(dsRow("Date"), Date)
orders.Add(New Order(OrderNum, Total, OrderDate))
Next
Else
MessageBox.Show("No Record Found In The Database. AE The Order With That Date Doesnt Exist.")
End If

and dateTime dataType of MS access is Ok to do search!

[Shaggy Hiker]

You should have no trouble searching for DATE variables. You seem to have that correct. However, it certainly sounds like what you are putting into the database is not a date at all, or is always the same date. Could you post the insert code? If all your dates are showing up as the same absurd value, then the only date you can search on that will return any records is a date that includes that absurd date.

Don't give up on the Date type, and don't switch to a string date. It isn't necessary, there is a way around this. Post the insert code, and maybe we can see why you are getting the wrong value saved to the database.

[VBDT]

You should have no trouble searching for DATE variables. You seem to have that correct

I am sorry, but where do you see search done in this code? It is just assigning the row column values to the local variables, over and over!

If dsOrder.Tables("foodorder").Rows.Count > 0 Then
'found an order with that date
orders.Clear()

For Each dsRow In dsOrder.Tables("foodorder").Rows
Total = CType(dsRow("Total"), Double)
OrderNum = CType(dsRow("orderNum"), Integer)
OrderDate = CType(dsRow("Date"), Date)
orders.Add(New Order(OrderNum, Total, OrderDate))
Next
Else
MessageBox.Show("No Record Found In The Database. AE The Order With That Date Doesnt Exist.")
End If

[ddmeightball]

I don't think you are making a search with this

and dateTime dataType of MS access is Ok to do search!

I know the datetime of Ms Acess is ok to do a search on, but I cant see to get it to work right. I dont think I will change it to a string, but I would like to get it working correctly.

And, yes, I am not searching with that piece of code that you posted. It doesnt do the search I am using the SQL statement that I wrote to do the search for me, instead of getting every record and searching for it.

Here is the full method where I try to find a record by date. Note the SQL statement. Two of them are commented out. First I send a value to search for, I then add that value to a SQL statement, I then fill my dataset with the results of that query. The code that you posted is just where I am getting the values for any record or records that I found that match the criteria that I sent it, and then add that object to an arraylist, which I then return.

Public Shared Function FindOrder(ByVal index As Integer) As ArrayList
        Dim sqlInsert As String
        Dim dsRow As DataRow
        Dim dsOrder As New DataSet
        dsOrder.Clear()
 
        Try
            'sqlInsert = "SELECT * FROM foodorder WHERE Date=" & "#" & index & "#"
            'sqlInsert = "SELECT * FROM `foodorder` WHERE Date='" & index & "'"
            [B]sqlInsert = "SELECT * FROM `foodorder` WHERE OrderNum=" & index[/B]
 
            Dim adpCustomer As New OleDbDataAdapter(sqlInsert, cnnOrder)
            [B]adpCustomer.Fill(dsOrder, "foodorder")[/B]
 
            If dsOrder.Tables("foodorder").Rows.Count > 0 Then
                'found an order with that date
                orders.Clear()
 
                For Each dsRow In dsOrder.Tables("foodorder").Rows
                    Total = CType(dsRow("Total"), Double)
                    OrderNum = CType(dsRow("orderNum"), Integer)
                    OrderDate = CType(dsRow("Date"), Date)
                    orders.Add(New Order(OrderNum, Total, OrderDate))
                Next
            Else
                MessageBox.Show("No Record Found In The Database. AE The Order With That Date Doesnt Exist.")
            End If
            dsOrder = Nothing
        Catch e As Exception
            Console.WriteLine(e.ToString)
            MessageBox.Show(e.ToString)
        End Try
        Return orders
    End Function

[ddmeightball]

You should have no trouble searching for DATE variables. You seem to have that correct. However, it certainly sounds like what you are putting into the database is not a date at all, or is always the same date. Could you post the insert code? If all your dates are showing up as the same absurd value, then the only date you can search on that will return any records is a date that includes that absurd date.

Don't give up on the Date type, and don't switch to a string date. It isn't necessary, there is a way around this. Post the insert code, and maybe we can see why you are getting the wrong value saved to the database.

I dont think I wil give up on the date searching, I would like to try to get it up and running because I think it would be beneficial to do it by date, rather that by a string.

It starts out in my Form, where I assign a value for a date object

Private Sub btnPlaceOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPlaceOrder.Click
        order1.OrderNumber = 1
        order1.OrderDate = order1.OrderDate.Today
        order1.OrderDAInsert()
    End Sub

I then call the OrderDAInsert method of my Order class.

Public Sub OrderDAInsert()
        OrderDA.Initialize()
        OrderDA.AddNewOrder(Me.OrderNumber, Me.OrderDate, Me.GetItemsOrdered)
        OrderDA.Terminate()
        Me.ClearItemsOrdered()
    End Sub

Here is my Insert code of my DA class.

    Public Shared Sub AddNewOrder(ByVal OrdNum As Integer, ByVal OrdDate As Date, ByVal aOrder As ArrayList)
        For Each MyFood As Product In aOrder
            OrderNum += OrdNum
            OrderDate = OrdDate
            ProductID = MyFood.ProductID
            ProductName = MyFood.ProductName
            ProductPrice = MyFood.Price
            Quantity = MyFood.Quantity
            Total = MyFood.CalculateTotal

            Dim sqlInsert As String = "INSERT INTO foodorder VALUES(" & OrderNum & "," & OrderDate & "," & "'" & ProductID & "'" & "," & "'" & ProductName & "'" & "," & ProductPrice & "," & Quantity & "," & Total & " ) "

            Try
                Dim adporder As New OleDbDataAdapter
                adporder.InsertCommand = New OleDbCommand(sqlInsert)
                adporder.InsertCommand.Connection = cnnOrder
                adporder.InsertCommand.ExecuteNonQuery()
            Catch sqle As OleDb.OleDbException
                Console.WriteLine(sqle.ToString)
                MessageBox.Show(sqle.ToString)
            End Try
        Next
    End Sub

[ddmeightball]

I found that if I use # symbols around my date variable in the Find Method, instead of giving me a datatype mismatch error, it just goes to my else method that prints a message saying there is no record that matched that date.

Here is the SQL code that I used

sqlInsert = "SELECT * FROM `foodorder` WHERE Date=#" & index & "#"

[ddmeightball]

Ok, I think I figured out why it isnt letting me get any record back from the database. The value of the Date field in the database for a record is 12/30/1899 12:02:09 AM. The date object that I using to search against the database has a value of 12/30/1899 12:00:00 AM. There are no records like that in the database.

I used Console.WriteLine() to print off my SQL statement to see what value I was seearching for the database and this is what it showed me. My date object has the day that I send it, 12/30/1899, but instead it uses the Hour minute second of that date object to search the database.

SELECT * FROM `foodorder` WHERE Date=#12:00:00 AM#

[VBDT]

I found that if I use # symbols around my date variable in the Find Method, instead of giving me a datatype mismatch error, it just goes to my else method that prints a message saying there is no record that matched that date.

Here is the SQL code that I used

sqlInsert = "SELECT * FROM `foodorder` WHERE Date=#" & index & "#"

I think that shoud work, but isn't "index" an integer?

[VBDT]

Also I don't understand why you are looping through the table rows

For Each dsRow In dsOrder.Tables("foodorder").Rows
Total = CType(dsRow("Total"), Double)
OrderNum = CType(dsRow("orderNum"), Integer)
OrderDate = CType(dsRow("Date"), Date)
orders.Add(New Order(OrderNum, Total, OrderDate))
Next

I think the order should be only one row!

[ddmeightball]

I think that shoud work, but isn't "index" an integer?

Index was an integer in an earlier version of my code, just to make sure I could return a value. I am now using a date so I need # symbols around it.

[ddmeightball]

Also I don't understand why you are looping through the table rows

I think the order should be only one row!

This is an Pizza Business program and I am searching for records that are on a certain date. Lets say the boss wants to see all the orders for 06/03/2006, it will get every order that has that date, then put its order number, the total price for that order, and eventually I want to be able to add up each total price for each individual order to get a sum total ofthem all. I think I wil do that in the SQL code by usingthe SUM() aggregate function.

If, at a later time, I could easily switch this to allow someone to enter an order number and return just that one record

[Ggalla1779]

try searching using Date serial.... I seem to recall this worked in Access. unsure if that command is available in .Net but if passint SQL to Access it would work

[ddmeightball]

try searching using Date serial.... I seem to recall this worked in Access. unsure if that command is available in .Net but if passint SQL to Access it would work

I havent heard of Date serial before? What is it?

[Shaggy Hiker]

I always use a parameterized INSERT statement, so I'm not sure whether what you have is correct or not, but I would expect that you might need to add the # around the date variable to get it to insert correctly.

The time is annoying on a date time. You would have to match it perfectly, which seems improbably. Therefore, I generally use a Date>= x AND Date< x + 1 day, or something like that. Seems safer.

[ddmeightball]

Ok, here is a new copy of my program. I have the Insert record method in my DA class working correctly now. I changed my database to have 2 tables instead of 1. Now it seems that when I insert a record into the database, instead of showing the date like it did earlier, as 12/30/1899 12:02:09 AM, it now shows the value that I send it, so now it shows just todays date of 6/6/2006.

Now I am still having problems getting values out of the database in my find method. When I print out my SQL statement, instead of sending the time to search for like it was doing earlier, it is now sending just the date that you send it so in this case 6/6/2006. I am having a problem though with my FROM clause in my SQL statement in this method. I get the error:

System.Data.OleDb.OleDbException: Syntax error in FROM clause.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at Program3.OrderDA.FindOrder(DateTime index) in C:\Documents and Settings\Matt\Desktop\Adv VB.net\Program3\Program3\OrderDA.vb:line 89

Line 89 is where I try to fill the dataset with information. I am wondering if I am doing my JOIN in the FROM clause correctly.

            sqlInsert = "SELECT * FROM ordertbl o JOIN producttbl p ON(o.OrderID=p.OrderID) WHERE o.OrderDate=" & index

Here is the full OrderDA.vb class if anyone doesny wish to open the zip file in this message.

'Matthew Davis
'Adv VB Project 3
'6/6/06
Option Strict On

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class OrderDA
    Shared cnnOrder As New _
        OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=order.mdb")

    Shared orders As New ArrayList
    Shared aFoodOrder As Order
    Shared ProductName, ProductID As String
    Shared ProductPrice, Total As Double
    Shared Quantity, OrderNum As Integer
    Shared OrderDate As Date

    Public Shared Sub Initialize()
        Try
            cnnOrder.Open()
        Catch e As Exception
            Console.WriteLine(e.ToString)
        End Try
    End Sub
    Public Shared Sub Terminate()
        Try
            cnnOrder.Close()
            cnnOrder = Nothing
        Catch e As Exception
            Console.WriteLine(e.Message.ToString)
        End Try
    End Sub

    Public Shared Sub AddNewOrder(ByVal OrdNum As Integer, ByVal OrdDate As Date, ByVal aOrder As ArrayList)
        Dim sqlInsert As String
        Dim adporder As New OleDbDataAdapter
        For Each MyFood As Product In aOrder
            OrderNum += OrdNum
            OrderDate = OrdDate
            ProductID = MyFood.ProductID
            ProductName = MyFood.ProductName
            ProductPrice = MyFood.Price
            Quantity = MyFood.Quantity
            Total = MyFood.CalculateTotal

            Try
                sqlInsert = "INSERT INTO producttbl VALUES(" & OrderNum & "," & "'" & ProductID & "'" & "," & "'" & ProductName & "'" & "," & ProductPrice & "," & Quantity & "," & Total & " ) "

                adporder.InsertCommand = New OleDbCommand(sqlInsert)
                adporder.InsertCommand.Connection = cnnOrder
                adporder.InsertCommand.ExecuteNonQuery()
            Catch sqle As OleDb.OleDbException
                Console.WriteLine(sqle.ToString)
                MessageBox.Show(sqle.ToString)
            End Try

            Try
                sqlInsert = "INSERT INTO ordertbl VALUES(" & OrderNum & ", #" & OrderDate & "# ) "
                adporder.InsertCommand = New OleDbCommand(sqlInsert)
                adporder.InsertCommand.Connection = cnnOrder
                adporder.InsertCommand.ExecuteNonQuery()
            Catch sqle As OleDb.OleDbException
                Console.WriteLine(sqle.ToString)
                MessageBox.Show(sqle.ToString)
            End Try
        Next
    End Sub

    Public Shared Function FindOrder(ByVal index As Date) As ArrayList
        Dim sqlInsert As String
        Dim dsRow As DataRow
        Dim dsOrder As New DataSet
        dsOrder.Clear()

        Try
            'sqlInsert = "SELECT * FROM foodorder WHERE Date=" & "#" & index & "#"
            'sqlInsert = "SELECT * FROM foodorder WHERE Date=#" & index & "#"
            'sqlInsert = "SELECT * FROM `foodorder` WHERE OrderNum=" & index
            'sqlInsert = "SELECT * FROM foodorder WHERE OrderDate="
            sqlInsert = "SELECT * FROM ordertbl o JOIN producttbl p ON(o.OrderID=p.OrderID) WHERE o.OrderDate=" & index
            MessageBox.Show(sqlInsert)
            Console.WriteLine(sqlInsert)
            Console.WriteLine(index)
            Dim adpCustomer As New OleDbDataAdapter(sqlInsert, cnnOrder)
            adpCustomer.Fill(dsOrder, "foodorder")

            If dsOrder.Tables("foodorder").Rows.Count > 0 Then
                'found an order with that date
                orders.Clear()

                For Each dsRow In dsOrder.Tables("foodorder").Rows
                    Total = CType(dsRow("Total"), Double)
                    OrderNum = CType(dsRow("orderNum"), Integer)
                    OrderDate = CType(dsRow("Date"), Date)
                    orders.Add(New Order(OrderNum, Total, OrderDate))
                Next
            Else
                MessageBox.Show("No Record Found In The Database. AE The Order With That Date Doesnt Exist.")
                Console.WriteLine("No Record Found In The Database. AE The Order With That Date Doesnt Exist.")
            End If
            dsOrder = Nothing
        Catch e As Exception
            Console.WriteLine(e.ToString)
            MessageBox.Show(e.ToString)
        End Try
        Return orders
    End Function
End Class

Any ideas on the FROM clause error? THanks for all the help btw.

[Shaggy Hiker]

I expect the problem is that ordertable o. What's that 'o' doing there? What's it mean?

[ddmeightball]

I expect the problem is that ordertable o. What's that 'o' doing there? What's it mean?

The o is a table alias since ordertbl and producttbl have two variable, OrderID, which have the same name. I am thinking i am not doing my JOIN statement right.

[ddmeightball]

Ok, I am having a problem with a nother method that I am writing. I am trying to get the max number in the OrderID field, so I just increment it to add another order. I am getting the error message

System.ArgumentException: Column 'OrderID' does not belong to table producttbl.
at System.Data.DataRow.get_Item(String columnName)
at Program3.OrderDA.GetOrderNumber() in C:\Documents and Settings\Matt\Desktop\Adv VB.net\Program3\Program3\OrderDA.vb:line 125

Line 125 is:

maxNum = CType(dsRow("OrderID"), Integer)

Here is my method, below it is the full OrderDa.vb class.

    Public Shared Function GetOrderNumber() As Integer
        Dim sqlInsert As String
        Dim maxNum As Integer

        Dim dsRow As DataRow
        Dim dsOrder As New DataSet
        dsOrder.Clear()

        Try
            sqlInsert = "SELECT MAX(OrderID) FROM ordertbl"
            Dim adpCustomer As New OleDbDataAdapter(sqlInsert, cnnOrder)
            adpCustomer.Fill(dsOrder, "ordertbl")

            If dsOrder.Tables("ordertbl").Rows.Count > 0 Then
                For Each dsRow In dsOrder.Tables("ordertbl").Rows
                    maxNum = CType(dsRow("OrderID"), Integer)
                    MessageBox.Show("maxNum in GetOrderNumber in OrderDA=" & maxNum)
                Next
            Else
                maxNum = 1
                MessageBox.Show("No Record Found In The Database. AE The Max(OrderID) Doesnt Exist because there are not records.")
            End If
            dsOrder = Nothing
        Catch e As Exception
            Console.WriteLine(e.ToString)
            MessageBox.Show(e.ToString)
        End Try
        Return maxNum
    End Function

Here is the full DA class so you know how I am calling the GetOrderNumber method

'Matthew Davis
'Adv VB Project 3
'6/6/06
Option Strict On

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class OrderDA
    Shared cnnOrder As New _
        OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=order.mdb")

    Shared orders As New ArrayList
    Shared aFoodOrder As Order
    Shared ProductName, ProductID As String
    Shared ProductPrice, Total As Double
    Shared Quantity, OrderNum As Integer
    Shared OrderDate As Date

    Public Shared Sub Initialize()
        Try
            cnnOrder.Open()
        Catch e As Exception
            Console.WriteLine(e.ToString)
        End Try
    End Sub
    Public Shared Sub Terminate()
        Try
            cnnOrder.Close()
            cnnOrder = Nothing
        Catch e As Exception
            Console.WriteLine(e.Message.ToString)
        End Try
    End Sub

    Public Shared Sub AddNewOrder(ByVal OrdNum As Integer, ByVal OrdDate As Date, ByVal aOrder As ArrayList)
        Dim sqlInsert As String
        Dim adporder As New OleDbDataAdapter

        OrderNum = OrderDA.GetOrderNumber + 1
        OrderDate = OrdDate

        Try
            sqlInsert = "INSERT INTO ordertbl VALUES(" & OrderNum & ", #" & OrderDate & "# ) "
            adporder.InsertCommand = New OleDbCommand(sqlInsert)
            adporder.InsertCommand.Connection = cnnOrder
            adporder.InsertCommand.ExecuteNonQuery()
        Catch sqle As OleDb.OleDbException
            Console.WriteLine(sqle.ToString)
            MessageBox.Show(sqle.ToString)
        End Try

        For Each MyFood As Product In aOrder
            ProductID = MyFood.ProductID
            ProductName = MyFood.ProductName
            ProductPrice = MyFood.Price
            Quantity = MyFood.Quantity
            Total = MyFood.CalculateTotal

            Try
                sqlInsert = "INSERT INTO producttbl VALUES(" & OrderNum & "," & "'" & ProductID & "'" & "," & "'" & ProductName & "'" & "," & ProductPrice & "," & Quantity & "," & Total & " ) "
                adporder.InsertCommand = New OleDbCommand(sqlInsert)
                adporder.InsertCommand.Connection = cnnOrder
                adporder.InsertCommand.ExecuteNonQuery()
            Catch sqle As OleDb.OleDbException
                Console.WriteLine(sqle.ToString)
                MessageBox.Show(sqle.ToString)
            End Try
        Next
    End Sub

    Public Shared Function FindOrder(ByVal index As Date) As ArrayList
        Dim sqlInsert As String
        Dim dsRow As DataRow
        Dim dsOrder As New DataSet
        dsOrder.Clear()

        Try
            sqlInsert = "SELECT * FROM ordertbl JOIN producttbl ON ordertbl.OrderID=producttbl.OrderID) WHERE producttbl.OrderDate=" & index
            MessageBox.Show(sqlInsert)
            Console.WriteLine(sqlInsert)
            Console.WriteLine(index)
            Dim adpCustomer As New OleDbDataAdapter(sqlInsert, cnnOrder)
            adpCustomer.Fill(dsOrder, "foodorder")

            If dsOrder.Tables("foodorder").Rows.Count > 0 Then
                'found an order with that date
                orders.Clear()

                For Each dsRow In dsOrder.Tables("foodorder").Rows
                    Total = CType(dsRow("Total"), Double)
                    OrderNum = CType(dsRow("orderID"), Integer)
                    OrderDate = CType(dsRow("Date"), Date)
                    orders.Add(New Order(OrderNum, Total, OrderDate))
                Next
            Else
                MessageBox.Show("No Record Found In The Database. AE The Order With That Date Doesnt Exist.")
                Console.WriteLine("No Record Found In The Database. AE The Order With That Date Doesnt Exist.")
            End If
            dsOrder = Nothing
        Catch e As Exception
            Console.WriteLine(e.ToString)
            MessageBox.Show(e.ToString)
        End Try
        Return orders
    End Function

    Public Shared Function GetOrderNumber() As Integer
        Dim sqlInsert As String
        Dim maxNum As Integer

        Dim dsRow As DataRow
        Dim dsOrder As New DataSet
        dsOrder.Clear()

        Try
            sqlInsert = "SELECT MAX(OrderID) FROM ordertbl"
            Dim adpCustomer As New OleDbDataAdapter(sqlInsert, cnnOrder)
            adpCustomer.Fill(dsOrder, "ordertbl")

            If dsOrder.Tables("ordertbl").Rows.Count > 0 Then
                For Each dsRow In dsOrder.Tables("ordertbl").Rows
                    maxNum = CType(dsRow("OrderID"), Integer)
                    MessageBox.Show("maxNum in GetOrderNumber in OrderDA=" & maxNum)
                Next
            Else
                maxNum = 1
                MessageBox.Show("No Record Found In The Database. AE The Max(OrderID) Doesnt Exist because there are not records.")
            End If
            dsOrder = Nothing
        Catch e As Exception
            Console.WriteLine(e.ToString)
            MessageBox.Show(e.ToString)
        End Try
        Return maxNum
    End Function
End Class

What do you think I am doing wrong? I am looking at the table in my database and it shows that the column name is OrderID so I think I am missing something, but just cant see it.

[Ggalla1779]

you dont need this in "Imports System.Data.SqlClient" your using oledb for this.

I normally would have 3 tables; Product, Customer, OrderDetails. This way you can move away from the date search and goto CustomerName search.

On your interface as you built up the order I would display it below on a listbox, adding to order as u go. And clearing the top 2 boxes as you go.

Generally with loading Combos; Salad, Pizza, Lunchbox should all be in the database and loaded into the combos. Having data loading from Code isnt the way I would go.

On the SQL SELECT * FROM ordertbl JOIN producttbl ON ordertbl.OrderID=producttbl.OrderID)

You seem to have a close brackets but no open.

Also you should not use *, as it can bring back to much data..... also it will bring back OrderID twice from both tables

cheers George

[Ggalla1779]

SELECT OrderID, ProductID, ProductName, ProductPrice, ProductQuantity, ProductTotal, OrderDate
FROM ordertbl INNER JOIN producttbl ON ordertbl.OrderID = producttbl.OrderID;

SQL I would use you would add where

[Ggalla1779]

Ok the problem with the Max No was that if you dont give the field a name access will allocate 1 (ie expr1000)

If you change select to this
"SELECT Max([OrderID]) AS myMax FROM ordertbl"

and code to this
maxNum = CType(dsRow("myMax"), Integer)

It should now work

[ddmeightball]

you dont need this in "Imports System.Data.SqlClient" your using oledb for this.

I normally would have 3 tables; Product, Customer, OrderDetails. This way you can move away from the date search and goto CustomerName search.

On your interface as you built up the order I would display it below on a listbox, adding to order as u go. And clearing the top 2 boxes as you go.

Generally with loading Combos; Salad, Pizza, Lunchbox should all be in the database and loaded into the combos. Having data loading from Code isnt the way I would go.

On the SQL SELECT * FROM ordertbl JOIN producttbl ON ordertbl.OrderID=producttbl.OrderID)

You seem to have a close brackets but no open.

Also you should not use *, as it can bring back to much data..... also it will bring back OrderID twice from both tables

cheers George

SELECT OrderID, ProductID, ProductName, ProductPrice, ProductQuantity, ProductTotal, OrderDate
FROM ordertbl INNER JOIN producttbl ON ordertbl.OrderID = producttbl.OrderID;

SQL I would use you would add where

Ok the problem with the Max No was that if you dont give the field a name access will allocate 1 (ie expr1000)

If you change select to this
"SELECT Max([OrderID]) AS myMax FROM ordertbl"

and code to this
maxNum = CType(dsRow("myMax"), Integer)

It should now work

Yeah, thank you for the help. I think it was a combonation of the fact that I needed a table alias and that I wasnt getting just the information that I needed from the database, which as I found out from doing some Console.Writeline in a select places, did include 2 OrderID fields. It is now getting the max number, now making it easier to keep from inserting duplicate orderIDs into the ordertbl table.

[Ggalla1779]

I always test the SQL out in Access queries. And when doing Dynamic SQl always output it to console or a textbox so I can cut and paste and test it.......

[ddmeightball]

I am having a problem with an error. It is saying that one of the fields in the SELECT statement doesnt have a value.

System.Data.OleDb.OleDbException: No value given for one or more required parameters.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at Program3.OrderDA.FindOrder(DateTime index) in C:\Documents and Settings\Matt\Desktop\Adv VB.net\Program3\Program3\OrderDA.vb:line 87

I printed off my SQL statement to see what it what showing and it showed this:

SELECT ordertbl.OrderID, ProductID, ProductName, ProductPrice, ProductQuantity, ProductTotal, OrderDate FROM ordertbl INNER JOIN producttbl ON ordertbl.OrderID = producttbl.OrderID WHERE producttbl.OrderDate=6/7/2006

I dont believe the error means any of the fields in the table because they all have a value.

Here is my find method

    Public Shared Function FindOrder(ByVal index As Date) As ArrayList
        Dim sqlInsert As String
        Dim dsRow As DataRow
        Dim dsOrder As New DataSet
        dsOrder.Clear()

        Try
            sqlInsert = "SELECT ordertbl.OrderID, ProductID, ProductName, ProductPrice, ProductQuantity, ProductTotal, OrderDate FROM ordertbl INNER JOIN producttbl ON ordertbl.OrderID = producttbl.OrderID WHERE producttbl.OrderDate=" & index
            Dim adpCustomer As New OleDbDataAdapter(sqlInsert, cnnOrder)
            adpCustomer.Fill(dsOrder, "foodorder")

            If dsOrder.Tables("foodorder").Rows.Count > 0 Then
                'found an order with that date
                orders.Clear()

                For Each dsRow In dsOrder.Tables("foodorder").Rows
                    Total = CType(dsRow("Total"), Double)
                    OrderNum = CType(dsRow("orderID"), Integer)
                    OrderDate = CType(dsRow("Date"), Date)
                    orders.Add(New Order(OrderNum, Total, OrderDate))
                Next
            Else
                MessageBox.Show("No Record Found In The Database. AE The Order With That Date Doesnt Exist.")
                Console.WriteLine("No Record Found In The Database. AE The Order With That Date Doesnt Exist.")
            End If
            dsOrder = Nothing
        Catch e As Exception
            Console.WriteLine(e.ToString)
            MessageBox.Show(e.ToString)
        End Try
        Return orders
    End Function

[Ggalla1779]

you have to paste SQL into Access Queries and run them...it will speed up your development time.

When I pasted the SQL above into Access it put it to [producttbl].[OrderDate] instead of against the orderstbl.....also dates require ## round them

[ddmeightball]

you have to paste SQL into Access Queries and run them...it will speed up your development time.

When I pasted the SQL above into Access it put it to [producttbl].[OrderDate] instead of against the orderstbl.....also dates require ## round them

I wish I could paste SQL into access. I dont have microsoft access here at home so I cant get at the database even if I need to. I have a program that will allow me to see the data in the database but nothing else. Could you show me what you mean in code?

[Ggalla1779]

ok do search on the web for Access runtime.....MS allowed u to create and modify queries on the runtime version of Access. Just open up database hit F11 and you should then see your tables/ queries etc

sqlInsert = "SELECT ordertbl.OrderID, ProductID, ProductName, ProductPrice, ProductQuantity, ProductTotal, OrderDate FROM ordertbl INNER JOIN producttbl ON ordertbl.OrderID = producttbl.OrderID WHERE producttbl.OrderDate=" & index

to

sqlInsert = "SELECT ordertbl.OrderID, ProductID, ProductName, ProductPrice, ProductQuantity, ProductTotal, ordertbl.OrderDate FROM ordertbl INNER JOIN producttbl ON ordertbl.OrderID = producttbl.OrderID WHERE producttbl.OrderDate=#" & index & "#"