Update database with a form on ASP page 1

[apex82]

I am currently displaying a list of ‘system codes’ which are grouped by a ‘unit code’ on an ASP webpage I want to be able to manually add a ‘system code’.

I have a form that contains two dropdowns (1) that contains unit codes (2) contains business codes - these are populated by the database. I also have a free-text input box, which I want to add a system code.

When I click submit on the form I want it to update my SQL database and webpage with the new ‘system code’.

Can anyone help or does anyone know any examples of how to do this?

My tables are as follows:

System

System ID - System code - System description - BusinessID - CoverID

1 - BG - LARGE - 1 - 4
2 - CH - SMALL - 2 - 4
3 - CO - LARGE - 1 - 4
4 - A0 - SMALL - 1 - 4
5 - BG - SMALL - 1 - 4
6 - DN - SMALL - 3 - 4
7 - EQ - SMALL - 3 - 4
8 - H8 - SMALL - 1 - 4
9 - AH - SMALL - 2 - 4
10 - AI - SMALL - 1 - 4


Unit

Unit ID - Unit code - Unit description

1 - GA - This is the Description
2 - GB - This is the Description
3 - GC - This is the Description
4 - GD - This is the Description


Business

Business ID - Business code - Business description - UnitID - Logo ID
1 - AA - Business AA - 1 - 1
2 - BB - Business BB - 2 - 2
3 - CC - Business CC - 3 - 3
4 - CC - Business CC - 4 - 4

 

[apex82]

I have managed to do this via this code:

 <%
str_SQL = "INSERT INTO System ([System_ID], [System Code])" 
str_SQL=str_SQL & " VALUES "
str_SQL =str_SQL & "('16', '" & Request.Form("SystemCode") & "')"

Set obj_RS1 = obj_CN.Execute(str_sql, adBoolean)

%>

The only problem with this is I want the System_ID to be System_ID+1 rather than the set value of 16.

Does anyone know how I can get this to take the last value from the database table and add 1?

Thanks.

 

[markros]

If you want your field to be auto-increment in SQL Server, set this field to be identity and then it would be incremented automatically (you would not need to list this field in your insert statement).

 

[apex82]

Thank you for your reply Markros.

I've taken a look at this link:

http://www.w3schools.com/Sql/sql_autoincrement.asp

From this I think I would need to add the code the "CREATE TABLE" bit. Is this correct or can something be added to the "INSERT INTO" section of code I posted previously?

I'm just not sure how I would code this into my page.

Can anyone help?

Thanks.

 

[markros]

Do you have an access to SSMS? Can you change the existing field System_ID from int to int identity (1,1)?

You would need to run the ALTER TABLE command to change this field in code. Try looking for the exact details of this command in BOL.

 

[apex82]

Thanks for your response.

Do you have an access to SSMS? Can you change the existing field System_ID from int to int identity (1,1)?

Yes I have access to SSMS.

If you mean changing the Data Type in the Table design from int to int identity (1,1)?

When I try this it just resets to int.

Thanks.

 

[markros]

I think in your case you need to create a new table with the same structure and identity column for SystemID

SET IDENTITY_INSERT OFF -- (check BOL if it should be ON or OFF - I always confuse)

insert from myOriginalTable

drop table myOriginalTable

and then renamed new table to original

 

[apex82]

Thanks for your response.

Basically all I want to do is add a new record to my table that automatically increments the ID.

I can't seem to get this to work!

I've also tried not specifying any field for the ID it gives the following error:

Cannot insert the value NULL into column 'System_ID', table 'Test.dbo.System'; column does not allow nulls. INSERT fails.

Do you know of any examples to help me on this creating a new table with the same structure and identity column for SystemID?

 

[apex82]

Looking at this again I've now managed to do this via the SSMS:

http://www.w3schools.com/Sql/sql_autoincrement.asp

Thanks for your help Markros.