Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 991
  • Last Modified:

Need help inserting a record into MSSQL Table using ASP VBscript

I inadvertently deleted a record from my database and now I need to recreate it. It has to be exact wiuth the original ID number. Here are 2 .asp pages I wrote to accomplish this as well as the error message I get when I try to use them. The first page is a form that gives the second page it's info.

ASP PAGE 1:

 <!--#include virtual="/functions.asp"-->
<!--#include virtual="/admin/header.asp"-->


<%
'********************************************
'* Incoming variables
'********************************************


'********************************************
'* Database setup
'********************************************
set conn=server.createobject("adodb.connection")
conn.open application("dsn_string")
set rs=server.createobject("adodb.recordset")
rs.cursortype=adForwardOnly
rs.cursorlocation=adUseClient
rs.activeConnection=conn
%>

<form action="DivAddwID2.asp">
Division Name: <input type=text name="descr" value=""><br>
Division ID: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <input type=text name="division_id" value=""><br>
Series ID:&nbsp; &nbsp;&nbsp;&nbsp;<input type=text name="season_id" value=""><br>
Prize Division:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <input type=text name="isPrizeDivision" value=""><br>
<input type=submit value="Save New Division">
</form>


<!--#include virtual="/admin/footer.asp"-->


ASP PAGE 2:

<!--#include virtual="/functions.asp"-->
<!--#include virtual="/admin/header.asp"-->

<%
'********************************************
'* Incoming variables
'********************************************
descr=request("descr")
division_id=request("division_id")
season_id=request("season_id")
isPrizeDivision=request("isPrizeDivision")

'********************************************
'* Database setup
'********************************************
set conn=server.createobject("adodb.connection")
conn.open application("DSN_String")
set rs=server.createobject("adodb.recordset")
rs.cursortype=adForwardOnly
rs.cursorlocation=adUseClient
rs.activeConnection=conn
sqlString="insert into divisions (descr, division_id, isPrizeDivision, season_id) values (" & descr & ", " & division_id & ", " & isPrizeDivision & ", " & season_id & ")"
rs.open sqlString

response.redirect("DivAddwID.asp")
%>


AND LASTLY, HERE IS THE ERROR:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'Divisions' when IDENTITY_INSERT is set to OFF.
/admin/DivAddwID2.asp, line 23


Also, if I include any text in the first area of the form (descr), I get a different error like this:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]The name 'Film' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
/admin/DivAddwID2.asp, line 23
0
bishopandsix
Asked:
bishopandsix
  • 4
  • 3
1 Solution
 
chapmandewCommented:
It looks like your table has an IDENTITY field (probably the primary key).  You can either omit that value in your insert statement, or run this before the insert

SET IDENTITY_INSERT tablename ON

--do your insert

SET IDENTITY_INSERT tablename OFF

the first step (omit the field) is probably the easiest.
0
 
bishopandsixAuthor Commented:
exactly where would I put those lines? Like this:

SET IDENTITY_INSERT tablename ON

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]The name 'Film' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
/admin/DivAddwID2.asp, line 23

rs.open sqlString

SET IDENTITY_INSERT tablename OFF


like that?

omiting the field is not an option as I assume the filed that is locked is the one I specifically need to assign the ID of.
0
 
chapmandewCommented:
you could try this:  

sqlString="set identity_insert divisions ON;insert into divisions (descr, division_id, isPrizeDivision, season_id) values (" & descr & ", " & division_id & ", " & isPrizeDivision & ", " & season_id & ") ;set identity_insert divisions OFF"
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
bishopandsixAuthor Commented:
Ok, that worked. However, now I get this error when I try to modify another field in this table with an ASP page I've been using forever:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/admin/divisionmod2.asp, line 29

What do I need to do to put it back how it was?
0
 
chapmandewCommented:
Not sure...I would say that your recordset gets closed somewhere, but I can't say for sure.  You're better off posting a new question here (as this is really a different question).  You'll probably get some good VB guys to help you out.
0
 
bishopandsixAuthor Commented:
Actually it was my fault. I had commented out a single line of code in this page while I was trying to reverse engineer a way to insert the record I originally posted about. Just a matter of putting it back the way it was. Thanks for the help!

Steve
0
 
bishopandsixAuthor Commented:
AWESOME!!! Thanks a ton.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now