Adding Values for DB in VB code . . .

I need to append a db entry on the vb code side to add incremental numbers to the db entry. The field I'm adding to has a unique constraint.

My code looks like this:

VB Code:

txtCustomerNumber.Text = txtCustomerNumber.Text & " /S"

I need to append numbers, starting with 1, to the end of the /S so that the entries into the database are indeed unique.

I'm guessing I'll need to use a "For...Next" somewhere, but, believe it or not, I've never used "For...Next" (because it involves math :eek: ), so I'm not sure how to do it.

Now that I think about it, I'll need to know the value of the previous entry, huh.

What should look like the result ?
Is it supose to be like this

Code:

---

Id  Sequence
--  ---------
1  1/S
2  2/S
3  3/S
...

---

or like this

Code:

---

1-2-3-4-5/S

---

the For .. Next Work like this

VB Code:

Dim Counter As Integer
 
For Counter = 1 To 10
    ' Thing to do 10 time
Next

For more info on For...Next

The /S appendage is to represent a shipping address for the parent customer, so it's more like:

Code:

---

customername          customernumber
--------------          ----------------
Company 1              ABC-DEF-123
Company 1              ABC-DEF-123 /S1
Company 1              ABC-DEF-123 /S2
Company 1              ABC-DEF-123 /S3

---

...etc, with an unlimited end point.

So I'm realizing that I'll need to know the previous /S* value in order to assign a new one. These are addresses that are added manually, so each time a new address is entered for that customer, an new /S* value is appended.

Had something written here, but for some reason it didn't post.

You are going to have a hard time with getting the highest possible number. You would need to get the MAX() of the substring beginning after the S. Offhand, I don't know if there is a SQL function for getting a substring.

Alternatively, you could put the whole thing in an UGLY loop. The loop would pick a number, try it, and if an exception is raised, try the next number, etc., continuing until a number worked. I can hardly think of a worse solution than this, but it could be made manageable if there are any patterns to the data. If you can find it once, then retain that value such that you won't fail very often, that would be almost ok.

I would suggest that a MUCH better solution would be to split the /S stuff out into a separate field in the DB. If you put that number in it's own field, and concatenate it back into the order number on views, that would allow for far more efficient searching/sorting/updating/inserting.

Why don't you keep your last entered sequence value in a Parameter table ?

(PseudoCode)

VB Code:

NextIdvalue = GetNextSequenceValue()
 
INSERT INTO MyTable (Id, ClientName, CustomerNumber) VALUES (txtNextId.Text, TxtCustomerName.Text,[B] CustomerNumber & " /S" & NextIdvalue [/B] ) 
 
Function GetNextSequenceValue() As String
    ' Look at param table ans return the new Sequence value + 1;
End Function

Is this suggestion is helping ??

Quote:

---

Originally Posted by Shaggy Hiker

Had something written here, but for some reason it didn't post.

You are going to have a hard time with getting the highest possible number. You would need to get the MAX() of the substring beginning after the S. Offhand, I don't know if there is a SQL function for getting a substring.

Alternatively, you could put the whole thing in an UGLY loop. The loop would pick a number, try it, and if an exception is raised, try the next number, etc., continuing until a number worked. I can hardly think of a worse solution than this, but it could be made manageable if there are any patterns to the data. If you can find it once, then retain that value such that you won't fail very often, that would be almost ok.

I would suggest that a MUCH better solution would be to split the /S stuff out into a separate field in the DB. If you put that number in it's own field, and concatenate it back into the order number on views, that would allow for far more efficient searching/sorting/updating/inserting.

---

This is also a really good solution ... ;)

Could you not create a separate table for shipping address, you can then add as many as you want.
Table: ShipAddress

CustomerNumber | ShippingAddress | AddressOrder (place a priority on shipping addresses)
1234 | Address1 | 1
1234 | Address2 | 2
1234 | Address3 | 3
3455 | Address1 | 1

All you then need to do is select which shipping address or default to the address with the highest priority.
Hope this is of some help.

I originally planned on doing that, even had a 'shipto' db created, but I need all customers (including shipping addresses) to return into one dataset when I do a customer search...I'm not too good at normalizing databases (I'm green at all of this, if you can't tell), so it's already getting really complicated. Any direct help would be much appreciated.

The code for linking your tables would be:

SELECT Id, mt.ClientName, mt.CustomerNumber, st.ShippingAddress
FROM Mytable mt, ShipTo st
WHERE st.CustomerNumber = mt.ShippingAddress

this would be the simplest method but would return lots of rows, it would return a row for each Shipping Address stored in the ShipTo folder for each customer returned from MyTable. You would probably have a datagrid on your customer form that shows all shipping addresses for the current customer by adding this to the previous code:

(previous code)
AND CustomerNumber = '" & txtCustomerNumber.Text & "'

Hope this helps

Might a Key Table help?

Quote:

---

Originally Posted by Shaggy Hiker

I would suggest that a MUCH better solution would be to split the /S stuff out into a separate field in the DB. If you put that number in it's own field, and concatenate it back into the order number on views, that would allow for far more efficient searching/sorting/updating/inserting.

---

Ok, I've been racking my brain all day trying to figure this out, but now my brain is full (dang-it, not again). I have done as above and created a new field for 'shiptonumber', so that I can append the 'customernumber' as I see fit, eliminated the primary key 'customername', but now, I can add duplicate customers, of course, which is the whole point of all of this.

Now, if I make the customernumber the pkey, unique, or otherwise, and use the shiptonumber instead for shipto addresses, then the customernumber can no longer be unique, and it can't be null. Nor can it be used more than once as a unique key, even if it's set to Not Null = No. So what now.