Validation on database entry 2

[hamish75]

I have a dropdown menu where I select a product, then I type two-character code(s) into an input text box I then press submit.

I use a javascript function so when the user types a two-character code the string is separated by a space:

what.value=what.value.replace(/ /g,'').replace(/(..)/g,'$1 ')

The following code splits the input string of codes if more than one is entered and then inserts each one into a new record into the database.

<%
Dim response_to_split, split_array
Dim one, two

response_to_split = Request.Form("Size")
split_array = Split(response_to_split, " ")
one = split_array(0)
two = split_array(1)

value = rtrim(Request.Form("ProductCode"))

val = split(value," ")

For i = 0 to Ubound(val)
	if len(val(i))=2 then
	
	sql = "INSERT INTO Product ([BusinessID], [Product Code], [Product Type] )" 
	sql = sql & " VALUES "
	sql = sql & "('" & one & "', '" & val(i) & "', '"  & "Product Code " & val(i) & " for company " & two  & "')"
	end if
	
	Set rs = obj_CN.Execute(sql, adBoolean)
Next

%>

When I type "A" product code into the input box it will not allow entry so my only problems are: if I type "AA B" it adds two records for AA, AA?

How do I make this so it only adds one AA?

Also, If the product code is already in the database table, how do I stop adding a duplicate?

Thanks.


Ah dinnae ken...

 

[foxbox]

Many ways to solve this. One rude way is this:
1. Define a unique index on [Product]
2. Put an ON ERROR RESUME NEXT before the obj_CN.Execute line

The database now will not accept duplicates. Normally your script would present this as an error, but the ON ERROR RESUME NEXT will skip that.


In some situations (maybe here) you are sure there are no records in the database yet, so when you do unique INSERTS you are fine. Then you're problem is:how to get unique INSERTS.
One way to solve this is to store the INSERTed codes in a variable. eg like this: (pseudocode)

dim cInserted
cInserted = ""

For i = 0 to Ubound( myArray )
    
    cValue = myArray(i)
    if instr( cInserted, cValue) = 0 then
      ' The code does not exist in cInserted
      cSQL = "INSERT INTO "   etc
      obj_CN.Execute(cSQL)
      cInserted = cInserted & code(i)
    end if
    
Next

 

[hamish75]

Thanks man. I'm struggling to implement this into the code I already have but thanks for guidance!

Ah dinnae ken...

 

[gmmastros]

If you don't want duplicates in a table, then you should define a unique index for it. This isn't rude, it's good programming practice.

What type of database is being used here?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hamish75]

Okay thanks george. I'll try to sort that.

I'm using sql server 2008.



Ah dinnae ken...

 

[gmmastros]

In my opinion, your data is your most valuable asset. You need to protect it with every bit of technology you can find.

But... when building a front end app, it's best to validate your data as much as possible before going to the database. Sometimes this means double checking your data (in the front end and also in the database). This is NOT bad, and it really doesn't lead to that much extra code either.

Now... if you don't want duplicates in your database, then you should FORCE the data to be unique. In SQL Server, this is usually implemented with a UNIQUE INDEX on your data.

create table NoDuplicates(Col1 Int, Col2 VarChar(20))

Create Unique Index idx_NoDuplicates_Col1_Col2 On NoDuplicates(Col1, Col2)

Insert Into NoDuplicates(Col1, Col2) Values(1, 'Apple')
Insert Into NoDuplicates(Col1, Col2) Values(1, 'Banana')
Insert Into NoDuplicates(Col1, Col2) Values(2, 'Apple')
Insert Into NoDuplicates(Col1, Col2) Values(1, 'Apple')

Copy/paste the code above in to a query window. Highlight each line separately and run it. You'll see that each line executes just fine until you get to the last line. But go ahead and run it so you can see what the error message looks like.

To remove this table when you're done...

Drop Table NoDuplicates

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hamish75]

Thanks guys. I know have this working after creating the unique index.

Thanks again.

Ah dinnae ken...