Sales Order

[Roey]

I am trying to create a Sales Order entry screen, in an 'n' tier environment.

 

For new Sales Orders I retrieve the schema of two tables tblSalesOrder, and tblSalesOrderDetails from my database. I then load those schemas into a DataSet called SalesOrders with a relationship between the two tables, agianst OrderID.

 

Clicking the New button on my Windows form adds a new record to the SalesOrder table and dragging an Item from the Products DataGrid to the SalesOrderDetails Datagrid adds a new record to the SalesOrderDetails table.

 

The problem I am having is that I need to somehow place the new sales orders OrderID against each individual SalesOrderDetail line item. How do I go about this. I would love to simply use

 

temprow = Me.dsSalesOrder.Tables("SalesOrderDetails").NewRow()

 

temprow.Item("OrderID") = Me.dsSalesOrder.Tables("SalesOrder), "OrderID")

 

 

But realise that this won't work.....

[Heiko]

How volatile is your DB schema?

You could try to use strongly typed datasets.

That would ease up things a bit.

 

Also, I don't understand why your idea wouldn't work.

(Apart from the typo, that is).

[Roey]

Thanks for the reply.

 

My DB schema isn't volitile, and the only reason that I haven't used strongly typed datasets is a lack of knowledge to be honest. I am about 80% through the development of a software package and was planning to look at strongly typed datasets in the next release, but for simplicty and support reasons have stuck with what I know works.

 

When you say that you don't understand why my idea doesn't work, I am assuming that you mean the line

 

temprow.Item("OrderID") = Me.dsSalesOrder.Tables("SalesOrder", "OrderID")

 

I get an error saying expression does not produce a value, if you know what I am doing wrong, that would REALLY help.

[Heiko]

Well, this is REALLY a shot out of the chest (or whatever that german idiom will translate to in Canada).

 

I'd try

Private mCurrentOrderID as string

Private Sub btnNew_Click (...) handles ....

mCurrentOrderID = whatever
'... more stuff ...

End Sub

Private Sub DetailGrid_DragDrop (...) handles ...

Dim tempRow as datarow

temprow = Me.dsSalesOrder.Tables("SalesOrderDetails").NewRow()

temprow.Item("OrderID") = mCurrentOrderID
' ... fill the other values

 

If for some reasons you can't use a member variable, then perhaps ...

temprow = Me.dsSalesOrder.Tables("SalesOrderDetails").NewRow()

try
'This only works when there's definitely just 
'one new Order in the SalesOrder Table. 
' In other cases you are lost, because it's not
' computable which OrderID has to be chosen

temprow.Item("OrderID") = dsSalesOrder.Tables("SalesOrder").rows(0).Item("OrderID")
Catch ex as exception
' no new order so far. (probably. Or a Type Convesion error)
end try

[Roey]

Thanks again for the reply. I think I've been on this one too long as I don't seem to be thinking too clearly at the minute......

 

The problem is that I don't know where to get the OrderID from. The btnNew Adds a new record to the Datatable SalesOrder, but how do I reference that Order number.

[Heiko]

Your SalesOrder table probably has a field in it that is the OrderID ?

 

Or do you in fact need a means to create a new OrderID ?

 

Or is this an autonumber field in the database ?

(In this case I have no good solution in mind right now.)

[Roey]

The autonumber field of the database

[Heiko]

autonumbers are bad. :-)

 

No, in this case there is no way that I know of.

You may have to store the new order as soon as it is created, the retrieve the id and the store the details in a second transaction.

[pendragon]

At the moment I am still using ADO but have been looking into changing to ADO.NET later, I have a book that I am going through and it has a small section on doing what you want.

 

It says on the OrderID DataColumn set the AutoIncrementSpeed and AutoIncrementStep to -1, when new orders are added they get order numbers -1, -2 etc., When you add your order lines the OrderID on the Line needs to be set to this number.

 

As long as you have a DataRelation setup, then by default, when you save the sales order header the DataRelation will cascade the changes through your DataSet and give you the right OrderID for your header and Lines, then when that is done you should be able to save your order line details.

 

As I say, I have not tried this yet but everything I have tried so far from this book has worked.