Getting Data from a list box or text box

[mojoT]

I'm working on a windows application with a SQL Server backend that deals with stock control of books. A book has the option of being serial numbered, and the first time that book is recieved, the user has the option of manually entering the serial numbers they want.

This is where I get stuck. I'm not quite sure how to let them do that, and so far I've thought of using a list box that they can enter the serial numbers into, or a multi-line text box. Is this firstly possible, and also practical?

If it is, how do I go about retrieving what they've entered, and separating each serial number so I can create a record of each serial number in the database?

I hope this isn't too confusing. Am so looking forward to any ideas and help.

 

[TheBitDoctor]

mojoT:

Allowing a user to add records to a database is always dangerous. Preventing typos and duplicate data is primary. Therefore, I would stay away from multiline text boxes. The best way to add a record to a database is to present the user with a form that has all of the fields that are in that record. Ideally the fields should be predefined lists that the user can select an item from. For example, your serial number issue. Is this serial number assigned by your application? Is it sequential? Can you search your database and create the next sequential serial number then pre-populate an ‘Add-Record’ form with this data, then allow the user to fill in the rest of the new book information?

In any event, whenever you allow the user to type something in manually then you need to perform rigorous validation checks to ensure that you don’t enter duplicate or erroneous data into your database.

Good Luck,
Ron

 

[chiph]

Adding to TheBitDocter's post:
What are the consequences of a duplicate serial number being entered? Is this value used as a key to the table, or maybe corresponds to a number printed on a paper form, or is it just a value used to identify a record with no outside meaning?

If the value is intended to be the primary key to the table, then you need to change the table structure a bit -- you should add an identity column to use as a primary key (never use user-data as keys!), and then add an alternate-key for that column. This will allow your SQL relationships to continue to work when someone has to correct a mis-entered value.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[mojoT]

Thanks so far for the response. In answer to BitDoctor's questions, sometimes the application will be able to generate the serial numbers, sometimes it won't. I'm asking the question for the times the serial numbers aren't generated.

I apologise, I should have explained my problem a bit better

In my database, I have a table that holds a book's details. I could have many copies of one book, and these could be serial numbered, for example, I have 5 copies of the book VB.Net in Easy Steps, and these are serial numbered VB1, VB2, VB3, VB4, VB5. (The serials are stored in a Serials table, with a 1:M relationship with the Book table) In this case, I can easily get the application to generate the serial numbers when I first receive them in.

Another example would be if the book Crystal Reports Explained also has 5 copies, but I want to manually enter the following serial numbers: CR1, CR4, CR9, CR23, CR45. It's this scenario that I'm trying to code. It might seem a bit weird not adding serial numbers sequentially, but it's a requirement the user needs. I hope I've made sense.

As for ChipH's questions, the serial numbers are not the primary key, and I have identity's separate to what the user enters. It is possible that every book I have on my database has 5 copies, and are all serial numbered 1-5. Therefore in the Serial Table I might have:

SerialID SerialNumber
1 1
2 2
3 3
4 4
5 5
6 1
7 2
8 3
9 4
10 5

Is that what you mean by duplicates? If it is, then I don't think that's a problem because of the unique SerialID for each record.

Thanks again for your help so far. I managed to use a multiline text box to enter the serial numbers, and use the mid function to separate each serial number (it looks for the newline ascii character) although it's not quite right. Any better ideas on how to do it, or tips on the way I'm working with it at the moment would be greatly appreciated. :-D