SQL Syntax Insert in two tables - Classic ASP

Assuming course Id is an identity field (which it should be, if not, I suggest making it one)

strSQL = "INSERT INTO Training_Courses (Active, CourseDate, CourseName, CourseDescription) VALUES ('"& strActive &"','"& strCourseDate &"','"& strCourseName &"','"& strCourseDescription &"'); Select @@Identity"



I'm a .Net developer so not sure how to read it correctly, but, what the @@identity does is it returns the number of the record that was just created.

In .Net it would be:

int CourseId = cmd.ExecuteScalar(strSql);

you can then create a second sql script and fire that off to do your insert.

the final script should look something like:

insert into Training_Completion (emp_id, Completed, course_id)
   "select emp_id, completed, " + courseId.Tostring() + " from employee_info "  ' where isActive = 1 if you're keeping track of actives

You should be able to create another query on the table you just inserted.  Which db are you using?  

If you can do this via a stored procedure it would be best.  Otherwise, you can use select @@identity.

I am using SQL Server database. Creating a stored procedure would be the best, I just don't have experience with it. I'm reading through the tutorial, but I'm not sure how to call a stored procedure from my classic ASP page, when I insert data.

I made CourseID and identity field.
I wonder if the ID of the last submission in the Training_Coursed table will be captured if I add the new INSERT statement after the line Conn.Execute(strSql)

if you want to go the stored procedure route, try this as your stored procedure, and tweak it as needed:

create procedure [dbo].[sp_saveCourseInfo]
	@active varchar( 10 ), 
        @courseDate datetime,
        @courseName varchar( 50 ),
        @courseDescription varchar( 1000 )
AS
BEGIN
   SET NOCOUNT ON;
declare @courseID int;

INSERT INTO Training_Courses (Active, CourseDate, CourseName, CourseDescription) VALUES ( active , @courseDate, @courseName, @courseDescription;

select @courseID = @@identity;

-- now we have the courseID for this record, so insert into the Training_Completion table. you may want to pass the employeeID along as a parameter to the stored procedure

insert into Training_Completion value......    -- I assume you can finish that statement

end

Select allOpen in new window

and to call the stored procedure, i recommend using parameterized queries. you'll need to download adovbs.inc for a list of constants definitions. save it to your site, for example, in your includes folder, then your asp code would look like:

<!--#include virtual = "/includes/adovbs.asp" -->
<%
'declare variables
Dim Conn, strSQL, rs, cmd, strCourseDescription, strCourseName, CourseDate

'Set values into variables
strActive = request.Form("Active")
strCourseDate = request.Form("CourseDate")
strCourseName = request.Form("CourseName")
strCourseDescription = request.Form("CourseDescription")

set rs = Server.CreateObject("ADODB.RecordSet")
set cmd = Server.CreateObject("ADODB.Command")

'create SQL string for inserting data into database
strSQL = "exexc sp_saveCourseInfo ?, ?, ?, ?"

with cmd
    .ActiveConnection = conn
    .CommandText = strSQL 
    .Parameters.Append  .CreateParameter( "active", adVarChar, adParamInput, 10, strActive )
    .Parameters.Append  .CreateParameter( "courseDate", adDate, adParamInput, , strCourseDate )
    .Parameters.Append  .CreateParameter( "courseName", adVarChar, adParamInput, 50, strCourseName )
    .Parameters.Append  .CreateParameter( "courseDescription", adInteger, adParamInput, 1000, strCourseDescription )
end with
rs.Open cmd

rs.close
set rs = nothing
'close connection object
Conn.Close
Set Conn = Nothing
%>

Select allOpen in new window

here's more info on parameterized queries, essentially they prevent sql injection attacks by using parameters in a sql query. have a look and let me know if you have any questions

It is really easy compared to what you read.  It took me a bit to figure this out but now I just throw this into a function and include it in my asp file.  

My function is taking the form input and sending it to my function that is calling the SP. The function is grabbing the identity just created and returning to my variable NewContactID.

Let me know if this makes sense.

<%
' ** cleanHTML is another function not shown here to make sure I have good data  **
prefix = cleanHTML(request.form("prefix"))
First = cleanHTML(request.form("First"))
middle = cleanHTML(request.form("middle"))
Last = cleanHTML(request.form("last"))
Suffix = cleanHTML(request.form("suffix"))
Address = cleanHTML(request.form("address"))
City = cleanHTML(request.form("city"))
St = cleanHTML(request.form("st"))
Zip = cleanHTML(request.form("zip"))

' ** CALL AddNewContact FUNCTION **
NewContactID =AddNewContact(Prefix,First,Middle,Last,Suffix,Address,City,St,Zip)


Function AddNewContact(Prefix,First,Middle,Last,Suffix,Address,City,St,Zip)
	
set cmd = Server.CreateObject("ADODB.Command")
With cmd
   ' Set up DB connection to use, set the type of SQL command
   	.ActiveConnection = my_connection_string
   	.CommandType = adCmdStoredProc
   	.CommandText = "sp_add_new_contact" ' This is the  name of the stored proc
   
   	.Parameters.Append .CreateParameter("@1prefix",adVarWChar, adParamInput, 30)
   	.Parameters("@1prefix") = Prefix
   
   	.Parameters.Append .CreateParameter("@2firstname",adVarWChar, adParamInput, 30)
   	.Parameters("@2firstname") = First

  	.Parameters.Append .CreateParameter("@3middle",adVarWChar, adParamInput, 30)
   	.Parameters("@3middle") = Middle

   	.Parameters.Append .CreateParameter("@4lastname",adVarWChar, adParamInput, 30)
   	.Parameters("@4lastname") = Last

   	.Parameters.Append .CreateParameter("@5address",adVarWChar, adParamInput, 30)
   	.Parameters("@5address") = Address

	.Parameters.Append .CreateParameter("@6city",adVarWChar, adParamInput, 30)
   	.Parameters("@6city") = City
   
   	.Parameters.Append .CreateParameter("@7st",adVarWChar, adParamInput, 2)
   	.Parameters("@7st") = St
   
   	.Parameters.Append .CreateParameter("@8zip",adVarWChar, adParamInput, 20)
   	.Parameters("@8zip") = Zip
	
   
   
   ' Execute the command, assigning the returned recordset to rs
   set rsNewContact = .Execute
End With

  AddNewContact= rsNewContact(0)
 
set cmd = nothing
set rs = nothing

end Function
%>

Select allOpen in new window

Thank you guys; I think I'm getting closer. I'm going to customize your script Padas, but first I'll have to make this work.
Big Monty, I've downloaded adovbs.asp and saved it in my includes folder. I've saved the file that follows too, as AddCourseSend.asp
I'm still struggling with the stored procedure. I'm afraid I can't finish the statement. The error message I get is the following:
Msg 102, Level 15, State 1, Procedure sp_saveCourseInfo, Line 13
Incorrect syntax near ';'.
This is the code of the SP I'm trying to create:

CREATE PROCEDURE [dbo].[sp_saveCourseInfo]
	@active varchar( 10 ), 
        @courseDate datetime,
        @courseName varchar( 80 ),
        @courseDescription varchar( 1000 )
AS
BEGIN
   SET NOCOUNT ON;
declare @courseID int;

INSERT INTO Training_Courses (Active, CourseDate, CourseName, CourseDescription) VALUES ( @active , @courseDate, @courseName, @courseDescription )

select @courseID = @@identity;

Select allOpen in new window

Take out the semicolon.

I end mine with,

SELECT NEWID = SCOPE_IDENTITY()

Actually, it is safer to use scope_identity.    @@identity could give you something you didn't expect.

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

try changing

select @courseID = @@identity;

to

set @courseID = @@identity

keep in mind this statement will only work if you have a column identified as an identity in your table. also, you need a final end at the bottom of the stored procedure, s it should look like this now:

CREATE PROCEDURE [dbo].[sp_saveCourseInfo]
	@active varchar( 10 ), 
        @courseDate datetime,
        @courseName varchar( 80 ),
        @courseDescription varchar( 1000 )
AS
BEGIN
   SET NOCOUNT ON;
declare @courseID int;

INSERT INTO Training_Courses (Active, CourseDate, CourseName, CourseDescription) VALUES ( @active , @courseDate, @courseName, @courseDescription )

select @courseID = @@identity;
end

Select allOpen in new window

where is the employeeID coming from that you need to insert into Training_Completion? will you have that value before you execute the stored procedure? if so, add it as parameter as type int, then you can add the following sql statement to the end of your stored procedure:

insert into Training_Completion (empl_ID, courseID ) values( @employeeID, @courseID )

It worked:

Command(s) completed successfully.

Now I will have to submit the form to my newly created AddCourseSend.asp or there are other steps I need to follow?

submit the page to the asp code i added in my first response. if you need help adding the employee ID let me know

I'm afraid I will need your help adding the Emp_Id
I'm sorry for being such a dummy; once it is working I can look into its details and understand the process.

I've submitted the form but I get an error message:

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/EWR/Training/AddCourse_send1.asp, line 19

This is the complete code:

<!--#include file = "includes/adovbs.asp" -->
<%
'declare variables
Dim Conn, strSQL, rs, cmd, strCourseDescription, strCourseName, CourseDate

'Set values into variables
strActive = request.Form("Active")
strCourseDate = request.Form("CourseDate")
strCourseName = request.Form("CourseName")
strCourseDescription = request.Form("CourseDescription")

set rs = Server.CreateObject("ADODB.RecordSet")
set cmd = Server.CreateObject("ADODB.Command")

'create SQL string for inserting data into database
strSQL = "exec sp_saveCourseInfo ?, ?, ?, ?"

with cmd
    .ActiveConnection = conn
    .CommandText = strSQL
    .Parameters.Append  .CreateParameter( "active", adVarChar, adParamInput, 10, strActive )
    .Parameters.Append  .CreateParameter( "courseDate", adDate, adParamInput, , strCourseDate )
    .Parameters.Append  .CreateParameter( "courseName", adVarChar, adParamInput, 50, strCourseName )
    .Parameters.Append  .CreateParameter( "courseDescription", adInteger, adParamInput, 1000, strCourseDescription )
end with
rs.Open cmd

rs.close
set rs = nothing
'close connection object
Conn.Close
Set Conn = Nothing
%>

Select allOpen in new window

And this is the code of the SP:

CREATE PROCEDURE [dbo].[sp_saveCourseInfo]
	@active varchar( 10 ), 
        @courseDate datetime,
        @courseName varchar( 80 ),
        @courseDescription varchar( 1000 )
AS
BEGIN
   SET NOCOUNT ON;
declare @courseID int;

INSERT INTO Training_Courses (Active, CourseDate, CourseName, CourseDescription) VALUES ( @active , @courseDate, @courseName, @courseDescription )

select @courseID = @@identity;
end

Select allOpen in new window

Scott,

Take out the semicolon.
The problem was not the semicolon, that is perfectly legal and eventually will be required.  The problem is that you are missing an END statement or you can lose the BEGIN.

Big Monty,

I suspect you will find that your Stored Procedure does not return a result set (all you have done is assign the value of the IDENTITY column to a local variable), so either @courseid has to be an OUTPUT parameter or lose the @ sign.

And yes always, always use SCOPE_IDENTITY() instead of @@IDENTITY.  There is one exception to that rule: You are still using SQL Server 7 (SCOPE_IDENTITY() was introduced with SQL Server 2000)

You need to modify your code as follows:
.Parameters.Append  .CreateParameter( "active", adVarChar, adParamInput, 10, strActive )
.Parameters.Append  .CreateParameter( "courseDate", adDBTimeStamp, adParamInput, , strCourseDate )
.Parameters.Append  .CreateParameter( "courseName", adVarChar, adParamInput, 80, strCourseName )
.Parameters.Append  .CreateParameter( "courseDescription", adVarChar, adParamInput, 1000, strCourseDescription )

You also need to modify your Stored Procedure code as it does not return a record set.  Change this line:
select @courseID = @@identity;

To:
SELECT  courseID = SCOPE_IDENTITY()

Anthony, I've modified the code but I still get the same error message

I also changed the code in the stored procedure, but the error is still the same after I submit

Finally, as it stands in your ASP code you are not retrieving the IDENTITY value:  All you are doing is:
rs.Open cmd

rs.close

if you want to do something useful with the IDENTITY value, then at least first determine if you can read it as in:
rs.Open cmd
Response.Write "My IDENTITY value is:" & rs.Fields("courseID ").Value
rs.close

Anthony, I've modified the code but I still get the same error message
Post the code as it stands now.

Actually, I see a lot of problems with that code, I am not sure where you got it from, I thought you were using Scott's approach.

Let's take it one step at a time.  First let's make sure the Stored Procedure actually returns data.

See if you can execute that Stored Procedure using SQL Server Management Studio (SSMS).  

Let me know if you need help with that.

When you are done post the parameters you used, the output if it was successful and any error message otherwise.

Anthony,
I'm using SQL Server Management Studio. How do I check if the Stored Procedure returns data.
Right now it looks like this:

USE [EWR]
GO

/****** Object:  StoredProcedure [dbo].[sp_saveCourseInfo]    Script Date: 06/02/2014 21:37:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_saveCourseInfo]
	@active varchar( 10 ), 
        @courseDate datetime,
        @courseName varchar( 80 ),
        @courseDescription varchar( 1000 )
AS
BEGIN
   SET NOCOUNT ON;
declare @courseID int;

INSERT INTO Training_Courses (Active, CourseDate, CourseName, CourseDescription) VALUES ( @active , @courseDate, @courseName, @courseDescription )

SELECT  courseID = SCOPE_IDENTITY()
end
GO

Select allOpen in new window

In the Training_Courses table I have 5 columns:
ID = int
CourseName = varchar(100)
CourseDescription = varchar(1000)
CourseDate = smalldatetime
Active = varchar(1)

Is is possible that the data types are causing the problem?

From SSMS:
USE YourdatabaseName -- To make sure you are in the right database.

exec sp_saveCourseInfo 'ActiveValueGoesHere', '<courseDateValueFormattedYYYYMMDD>GoesHere', 'SourcenameValueGoesHere', "courseDescriptionValueGoesHere'

Press F5

I got this error:

Msg 8114, Level 16, State 5, Procedure sp_saveCourseInfo, Line 0
Error converting data type varchar to datetime.

I would also make the following changes to your Stored Procedure in order to sync up with those data types:

ALTER PROCEDURE [dbo].[sp_saveCourseInfo]
    @active varchar(1),
    @courseDate smalldatetime,
    @courseName varchar(100),
    @courseDescription varchar(1000)
AS
    BEGIN

        SET NOCOUNT ON;
        DECLARE @courseID int;

        INSERT  INTO Training_Courses
                (Active,
                 CourseDate,
                 CourseName,
                 CourseDescription
                )
        VALUES  (@active,
                 @courseDate,
                 @courseName,
                 @courseDescription
                )

        SELECT  courseID = SCOPE_IDENTITY()
    END
GO

Select allOpen in new window

Msg 8114, Level 16, State 5, Procedure sp_saveCourseInfo, Line 0
Error converting data type varchar to datetime.
Unfortunately, I am not in front of your computer, so you are going to have to tell me what you typed and executed.

Yes, I made the correction to the date and now it returned the value: 21

But I will go out on a limb and guess that the courseDateValueFormattedYYYYMMDD is not a valid date. :)

Then this ASP code should (I have not actively coded in ASP in at least 10 years, so there may be errors) produce the next value (22 ?):

<!--#include file = "includes/adovbs.asp" -->
<%
'declare variables
Dim Conn, rs, cmd, strActive, strCourseDate, strCourseName, strCourseDescription

'Set values into variables
strActive = request.Form("Active")
strCourseDate = request.Form("CourseDate")
strCourseName = request.Form("CourseName")
strCourseDescription = request.Form("CourseDescription")

Conn = " Your connection string goes here"

Set cmd = Server.CreateObject("ADODB.Command")

With cmd
    .ActiveConnection = conn
    .CommandType = adCmdStoredProc
    .CommandText = "sp_saveCourseInfo"
    .Parameters.Append .CreateParameter( "@active", adVarChar, adParamInput, 1, strActive )
    .Parameters.Append .CreateParameter( "@courseDate", adDate, adParamInput, , strCourseDate )
    .Parameters.Append .CreateParameter( "@courseName", adVarChar, adParamInput, 50, strCourseName )
    .Parameters.Append .CreateParameter( "@courseDescription", adInteger, adParamInput, 1000, strCourseDescription )
    Set rs = .Execute
End With

rs.Open cmd

Response.Write "My IDENTITY value is:" & rs.Fields("courseID ").Value

rs.close
Set rs = nothing

'close connection object
Conn.Close
Set Conn = Nothing
%>

Select allOpen in new window

This is what I did:
exec sp_saveCourseInfo 'A', '6/30/2014', 'My Course Name', 'My Description'

It wrote the actual values into my table and gave me the ID number

Notice how you need to fill in the Connection string.  Also, make sure all the values are true before you execute the Stored Procedure  as in:

Response.Write "strActive :" & strActive & "<br>"
Response.Write "strCourseDate :" & strCourseDate & "<br>"
Response.Write "strCourseName :" & strCourseName & "<br>"
Response.Write "strCourseDescription :" & strCourseDescription & "<br>"

You would be surprised at how often a simple thing like that trips people up.

It wrote the actual values into my table and gave me the ID number
I don't like the way you formatted the date, in SQL Server you should get in the habit of using an unambiguous date format such as YYYYMMDD, however for the most part that will "work".

Now this is my ASP code and I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name too long

/

<!-- #include file="includes/SQLConn.asp" -->
<!--#include file = "includes/adovbs.asp" -->
<%
'declare variables
Dim Conn, rs, cmd, strActive, strCourseDate, strCourseName, strCourseDescription

'Set values into variables
strActive = request.Form("Active")
strCourseDate = request.Form("CourseDate")
strCourseName = request.Form("CourseName")
strCourseDescription = request.Form("CourseDescription")

Conn = " Your connection string goes here"

Set cmd = Server.CreateObject("ADODB.Command")

With cmd
    .ActiveConnection = conn
    .CommandType = adCmdStoredProc
    .CommandText = "sp_saveCourseInfo"
    .Parameters.Append .CreateParameter( "@active", adVarChar, adParamInput, 1, strActive )
    .Parameters.Append .CreateParameter( "@courseDate", adDate, adParamInput, , strCourseDate )
    .Parameters.Append .CreateParameter( "@courseName", adVarChar, adParamInput, 50, strCourseName )
    .Parameters.Append .CreateParameter( "@courseDescription", adInteger, adParamInput, 1000, strCourseDescription )
    Set rs = .Execute
End With

rs.Open cmd

Response.Write "My IDENTITY value is:" & rs.Fields("courseID ").Value

rs.close
Set rs = nothing

'close connection object
Conn.Close
Set Conn = Nothing
%>

Select allOpen in new window

Training/AddCourse_send1.asp, line 18

I've noticed the connection string problem and fixed it. Now I get an error on this line:

.Parameters.Append .CreateParameter( "@courseDescription", adInteger, adParamInput, 1000, strCourseDescription )

The error is the following:
ADODB.Command error '800a0d5d'

Application uses a value of the wrong type for the current operation.

/Training/AddCourse_send1.asp, line 22

I've changed the code to the following and now it's working:

<!-- #include file="includes/SQLConn.asp" -->
<!--#include file = "includes/adovbs.asp" -->
<%
'declare variables
Dim Conn, rs, cmd, strActive, strCourseDate, strCourseName, strCourseDescription

'Set values into variables
strActive = request.Form("Active")
strCourseDate = request.Form("CourseDate")
strCourseName = request.Form("CourseName")
strCourseDescription = request.Form("CourseDescription")

Set cmd = Server.CreateObject("ADODB.Command")

With cmd
    .ActiveConnection = conn
    .CommandType = adCmdStoredProc
    .CommandText = "sp_saveCourseInfo"
    .Parameters.Append .CreateParameter( "@active", adVarChar, adParamInput, 1, strActive )
    .Parameters.Append .CreateParameter( "@courseDate", adDate, adParamInput, , strCourseDate )
    .Parameters.Append .CreateParameter( "@courseName", adVarChar, adParamInput, 50, strCourseName )
    .Parameters.Append .CreateParameter( "@courseDescription", adVarChar, adParamInput, 1000, strCourseDescription )
    Set rs = .Execute
End With

rs.close
Set rs = nothing

'close connection object
Conn.Close
Set Conn = Nothing
%>

Select allOpen in new window

Thank you Anthony, now I'm just missing the final step. How do I send the Emp_Id from my EmpInfo table into the Training_Completion table?

<!-- #include file="includes/SQLConn.asp" -->
<!--#include file = "includes/adovbs.asp" -->
<%
'declare variables
Dim Conn, rs, cmd, strActive, strCourseDate, strCourseName, strCourseDescription

'Set values into variables
strActive = request.Form("Active")
strCourseDate = request.Form("CourseDate")
strCourseName = request.Form("CourseName")
strCourseDescription = request.Form("CourseDescription")

Set cmd = Server.CreateObject("ADODB.Command")

With cmd
    .ActiveConnection = conn
    .CommandType = adCmdStoredProc
    .CommandText = "sp_saveCourseInfo"
    .Parameters.Append .CreateParameter( "@active", adVarChar, adParamInput, 1, strActive )
    .Parameters.Append .CreateParameter( "@courseDate", adDate, adParamInput, , strCourseDate )
    .Parameters.Append .CreateParameter( "@courseName", adVarChar, adParamInput, 50, strCourseName )
    .Parameters.Append .CreateParameter( "@courseDescription", adVarChar, adParamInput, 1000, strCourseDescription )
    Set rs = .Execute
End With

rs.close
Set rs = nothing

'close connection object
Conn.Close
Set Conn = Nothing
%>

Select allOpen in new window

With this type of error, it is a good idea to check your input.  Get this working first.  (The error is you are setting your description as adInteger (Integer) and should probably be adVarChar.

' make sure Active is what you expect.  For illustration, let's say you expect I or A
strActive = "I"  ' default to I
if instr("A I", trim(request.Form("Active")))>0 then
    strActive=request.Form("Active")
end if
strCourseDate = "NULL"  ' default to null or some date
if isdate(request.Form("CourseDate")) then
    strCourseDate = request.Form("CourseDate")
end if
strCourseName = request.Form("CourseName") ' Are you escaping html or quotes?
strCourseDescription = left(request.Form("CourseDescription"),1000) ' only take the first 1000 chars. Are you escaping html or quotes?

Select allOpen in new window

You can't trust the input form and this will prevent an error when somebody figures out how to post bad data.   You can also test for a null date or bad date value as example, and if that is the case, skip the insert and throw a friendly warning/modal/alert

I've made the corrections and now I can submit data into the Training_Courses table. My final goal is to insert the last CourseID into the Training_Completion table, together with all Emp_Id from the EmpInfo table.

Well now that you have returned the data to ASP you may be able to skip that entirely as it may no longer be needed, just change your Stored Procedure to add the additional INSERT into the Training_Completion table.

Would this be correct?

insert into Training_Completion(
[CourseId]
      ,[Emp_Id]
)
(select [Emp_Id]
 from EmpInfo)

I'm not sure where to place the code. Would this be correct?

USE [EWR]
GO
/****** Object:  StoredProcedure [dbo].[sp_saveCourseInfo]    Script Date: 06/02/2014 23:49:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_saveCourseInfo]
    @active varchar(1),
    @courseDate smalldatetime,
    @courseName varchar(100),
    @courseDescription varchar(1000)
AS
    BEGIN

        SET NOCOUNT ON;
        DECLARE @courseID int;

        INSERT  INTO Training_Courses
                (Active,
                 CourseDate,
                 CourseName,
                 CourseDescription
                )
        VALUES  (@active,
                 @courseDate,
                 @courseName,
                 @courseDescription
                )

        SELECT  courseID = SCOPE_IDENTITY()
        
        insert into Training_Completion(
[CourseId]
      ,[Emp_Id]
)
(select [Emp_Id]
 from EmpInfo)
    END

Select allOpen in new window

I've tried to alter the procedure but I get this error message:
Msg 120, Level 15, State 1, Procedure sp_saveCourseInfo, Line 26
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

USE [EWR]
GO
/****** Object:  StoredProcedure [dbo].[sp_saveCourseInfo]    Script Date: 06/02/2014 23:49:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_saveCourseInfo]
    @active varchar(1),
    @courseDate smalldatetime,
    @courseName varchar(100),
    @courseDescription varchar(1000)
AS
    BEGIN

        SET NOCOUNT ON;
        DECLARE @courseID int;

        INSERT  INTO Training_Courses
                (Active,
                 CourseDate,
                 CourseName,
                 CourseDescription
                )
        VALUES  (@active,
                 @courseDate,
                 @courseName,
                 @courseDescription
                )

        SELECT  courseID = SCOPE_IDENTITY()
        
        insert into Training_Completion(
[CourseId]
      ,[Emp_Id]
)
(select [Emp_Id]
 from EmpInfo)
    END

Select allOpen in new window

I also tried the following, but it gave me this error:
Msg 137, Level 15, State 2, Procedure sp_saveCourseInfo, Line 33
Must declare the scalar variable "@Emp_Id".

USE [EWR]
GO
/****** Object:  StoredProcedure [dbo].[sp_saveCourseInfo]    Script Date: 06/03/2014 00:03:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_saveCourseInfo]
    @active varchar(1),
    @courseDate smalldatetime,
    @courseName varchar(100),
    @courseDescription varchar(1000)
AS
    BEGIN

        SET NOCOUNT ON;
        DECLARE @courseID int;

        INSERT  INTO Training_Courses
                (Active,
                 CourseDate,
                 CourseName,
                 CourseDescription
                )
        VALUES  (@active,
                 @courseDate,
                 @courseName,
                 @courseDescription
                )

        SELECT  courseID = SCOPE_IDENTITY()
        
                INSERT  INTO Training_Completion(
CourseId
      ,Emp_Id
)
VALUES
(
@courseID,
@Emp_Id
)
(select [Emp_Id]
 from EmpInfo)
    END

Select allOpen in new window

USE [EWR]
GO
/****** Object:  StoredProcedure [dbo].[sp_saveCourseInfo]    Script Date: 06/02/2014 23:49:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_saveCourseInfo]
    @active varchar(1),
    @courseDate smalldatetime,
    @courseName varchar(100),
    @courseDescription varchar(1000)
AS
    BEGIN

        SET NOCOUNT ON;
        DECLARE @courseID int;

        INSERT  INTO Training_Courses
                (Active,
                 CourseDate,
                 CourseName,
                 CourseDescription
                )
        VALUES  (@active,
                 @courseDate,
                 @courseName,
                 @courseDescription
                )

        SELECT  courseID = SCOPE_IDENTITY()
        
        insert into Training_Completion(
[CourseId]
      ,[Emp_Id]
)
(select [Emp_Id]
 from EmpInfo)
    END

Select allOpen in new window

I think you are missing values.

 SELECT  courseID = SCOPE_IDENTITY()
        
insert into Training_Completion
([CourseId] ,[Emp_Id])
VALUES
(@courseID, @Emp_Id)

Select allOpen in new window

I give you my (ASP) code:

                  set comm = server.createobject("ADODB.Command")
                  comm.activeconnection = conn
                  comm.commandtext = "create_mailing"
                  comm.commandtype = 4
                  comm.commandtimeout = 0
                  comm.prepared = true
                  comm.parameters.append comm.createparameter("@mUserID", adVarchar, adParamInput, len(tmp_userid), tmp_userid)
                  comm.parameters.append comm.createparameter("@mDateSent", adVarchar, adParamInput, len(vTodayTime), vTodayTime)
                  comm.parameters.append comm.createparameter("@mGUID", adGUID, adParamOutput, 35)
                  comm.execute
                  tmp_gID = comm.parameters("@mGUID")
                  set comm = nothing

I hope it helps.

Only Emp_Id comes from the EmpInfo table.
courseID is the last ID from the Training_Courses table.
CourseID and Emp_Id have to be inserted in the Training_Completion table.
Each Emp_Id from the EmpInfo table has to be inserted, and has to match the last CourseID from Training_Courses.
For example if the CourseID is 27, the number 27 has to go in the CourseID column as many times as many employee numbers are in the EmpInfo table.

Your Stored Procedure code should look like this:

ALTER PROCEDURE [dbo].[sp_saveCourseInfo]
    @active varchar(1),
    @courseDate smalldatetime,
    @courseName varchar(100),
    @courseDescription varchar(1000),
    @Emp_Id integer	    -- Change data type appropriately
    
AS
    BEGIN

        SET NOCOUNT ON;
        DECLARE @courseID int;

        INSERT  INTO Training_Courses
                (Active,
                 CourseDate,
                 CourseName,
                 CourseDescription
                )
        VALUES  (@active,
                 @courseDate,
                 @courseName,
                 @courseDescription
                )

        SELECT  @courseID = SCOPE_IDENTITY()
        
        INSERT  INTO Training_Completion
                (CourseId, Emp_Id)
        VALUES  (@courseID, @Emp_Id)
    END

Select allOpen in new window

Notice how:
1.  We are not assigning the SCOPE_IDENTITY() to a local variable so this Stored Procedure now does not return a result set.  
2. We have added an additional parameter @Em_Id

I need you to test this from SSMS first and when you confirm is functions as required we can show you the ASP code.

Scratch that.  I see you we have to make some changes to the code.

Try it this way:

ALTER PROCEDURE [dbo].[sp_saveCourseInfo]
    @active varchar(1),
    @courseDate smalldatetime,
    @courseName varchar(100),
    @courseDescription varchar(1000)

    
AS
    BEGIN

        SET NOCOUNT ON;
        DECLARE @courseID int;

        INSERT  INTO Training_Courses
                (Active,
                 CourseDate,
                 CourseName,
                 CourseDescription
                )
        VALUES  (@active,
                 @courseDate,
                 @courseName,
                 @courseDescription
                )

        SELECT  @courseID = SCOPE_IDENTITY()
        
        INSERT  INTO Training_Completion
                (CourseId, Emp_Id)
        SELECT	@courseID, Emp_Id
        FROM	EmpInfo
    END

Select allOpen in new window

Again, test it first with SSMS.

I've altered the SP and now I get this error:
Cannot insert explicit value for identity column in table 'Training_Completion' when IDENTITY_INSERT is set to OFF.

wow I take a break from EE for 12 hours and everything blows up here :)

I see AP is on the path that I had originally wanted to go down, insert the data into the Training_Courses table and then get the ID and put it in the Training_Completion table, all in the stored procedure. I'm still not understanding where the empl_ID is coming from, albeit I haven't read everything yet).

regarding the last error you're getting, you have IDENTITY turned on for one of the fields in Training-Completion, find out which column and turn it off (on both columns if applicable). Select the table, right mouse click on it, then select each field and look in the details view to see if the identity is on.

When I test it in SSMS the error is the same:
Cannot insert explicit value for identity column in table 'Training_Completion' when IDENTITY_INSERT is set to OFF.

CourseID is identity, shall I make Emp_Id identity too?

you do not want identity for either one in the Training_Completion table

I turned the identity off and the error is "Cannot insert explicit value for identity column in table 'Training_Completion' when IDENTITY_INSERT is set to OFF."

what other columns are in that table?

In the Training_Courses table I have 5 columns:
ID = int
CourseName = varchar(100)
CourseDescription = varchar(1000)
CourseDate = smalldatetime
Active = varchar(1)

IN the Training_Completion I have
ID = int
Course_Id = int
Emp_Id = Varchar(10)
CompletionDate = smalldatetime
Status = Varchar(5)

ok the error is happening on Training_Completion, double check the error message. what columns are in that table?

the only column in Training_Completion that should have identity is the ID field

I've made the change, only ID has identity now.
The error is the same

please post the error message again so we can verify its happening on Training_Completion

In the SP I have the following:
        INSERT  INTO Training_Completion
                (CourseId, Emp_Id)
        SELECT      @courseID, Emp_Id
        FROM      EmpInfo

It't trying to select courseID from the EmpInfo table.
There is no courseID column in the EmpInfo table

This is the stored procedure

ALTER PROCEDURE [dbo].[sp_saveCourseInfo]
    @active varchar(1),
    @courseDate smalldatetime,
    @courseName varchar(100),
    @courseDescription varchar(1000)

    
AS
    BEGIN

        SET NOCOUNT ON;
        DECLARE @courseID int;

        INSERT  INTO Training_Courses
                (Active,
                 CourseDate,
                 CourseName,
                 CourseDescription
                )
        VALUES  (@active,
                 @courseDate,
                 @courseName,
                 @courseDescription
                )

        SELECT  @courseID = SCOPE_IDENTITY()
        
        INSERT  INTO Training_Completion
                (CourseId, Emp_Id)
        SELECT	@courseID, Emp_Id
        FROM	EmpInfo
    END

Select allOpen in new window

And the error message is this:
Cannot insert explicit value for identity column in table 'Training_Completion' when IDENTITY_INSERT is set to OFF.

ALTER PROCEDURE [dbo].[sp_saveCourseInfo]
    @active varchar(1),
    @courseDate smalldatetime,
    @courseName varchar(100),
    @courseDescription varchar(1000)

    
AS
    BEGIN

        SET NOCOUNT ON;
        DECLARE @courseID int;

        INSERT  INTO Training_Courses
                (Active,
                 CourseDate,
                 CourseName,
                 CourseDescription
                )
        VALUES  (@active,
                 @courseDate,
                 @courseName,
                 @courseDescription
                )

        SELECT  @courseID = SCOPE_IDENTITY()
        
        INSERT  INTO Training_Completion
                (CourseId, Emp_Id)
        SELECT	@courseID, Emp_Id
        FROM	EmpInfo
    END

Select allOpen in new window

in this case, it's selecting the variable name @courseID, not a field name, as determined by the @ symbol, so that sql is correct.

it's similiar to running this perfectly valid sql statement:

select 'hi', 'bye'

the error message is saying that the table Training_Completion still has an identity field other than ID set,

My mistake, I opened the wrong table. I'm so sorry. Making the corrections now

no problem, it's how we all learn :)

Now the error is this:
Cannot insert the value NULL into column 'ID', table 'EWR.dbo.Training_Completion'; column does not allow nulls. INSERT fails.

you want to keep identity on for the ID field, off for the rest of them in Training_Completion

Now the error is this, after I changed ID to make it identity

Operation is not allowed when the object is closed.

/EWR/Training/AddCourse_send1.asp, line 26

ok we're making progress!

post the asp code please

also, as a test, run the stored procedure in the SSMS and make sure you're getting the results you expect

<!-- #include file="includes/SQLConn.asp" -->
<!--#include file = "includes/adovbs.asp" -->
<%
'declare variables
Dim Conn, rs, cmd, strActive, strCourseDate, strCourseName, strCourseDescription

'Set values into variables
strActive = request.Form("Active")
strCourseDate = request.Form("CourseDate")
strCourseName = request.Form("CourseName")
strCourseDescription = request.Form("CourseDescription")

Set cmd = Server.CreateObject("ADODB.Command")

With cmd
    .ActiveConnection = conn
    .CommandType = adCmdStoredProc
    .CommandText = "sp_saveCourseInfo"
    .Parameters.Append .CreateParameter( "@active", adVarChar, adParamInput, 1, strActive )
    .Parameters.Append .CreateParameter( "@courseDate", adDate, adParamInput, , strCourseDate )
    .Parameters.Append .CreateParameter( "@courseName", adVarChar, adParamInput, 100, strCourseName )
    .Parameters.Append .CreateParameter( "@courseDescription", adVarChar, adParamInput, 1000, strCourseDescription )
    Set rs = .Execute
End With

rs.close
Set rs = nothing

'close connection object
Conn.Close
Set Conn = Nothing

Select allOpen in new window

I've tested the stored procedure in SSMS and it's working fine

exec sp_saveCourseInfo 'A', '6/30/2014', 'My Course Name', 'My Description'

did that work by commenting out that line then?

.ActiveConnection = conn

I'm not seeing where you initialize the connection, but again, don't know that much about classic asp.  Do you have it defined somewhere else?

Thank you very much Everyone! It is finally working.
I learnt a lot from all of you. Padas, your code is very useful, I'm going to study it and I might have to post a question.
Have a great day all of you!

The main difference is I placed my ASP code that calls the SP inside of a function.   A long thread but good learning experience from good experts.  Glad you have this part down.

function addCourse(strActive,strCourseDate,strCourseName,strCourseDescription
Set cmd = Server.CreateObject("ADODB.Command")

With cmd
    .ActiveConnection = conn
    .CommandType = adCmdStoredProc
    .CommandText = "sp_saveCourseInfo"
    .Parameters.Append .CreateParameter( "@active", adVarChar, adParamInput, 1, strActive )
    .Parameters.Append .CreateParameter( "@courseDate", adDate, adParamInput, , strCourseDate )
    .Parameters.Append .CreateParameter( "@courseName", adVarChar, adParamInput, 100, strCourseName )
    .Parameters.Append .CreateParameter( "@courseDescription", adVarChar, adParamInput, 1000, strCourseDescription )
    .Execute
End With

'close connection object
Conn.Close
Set Conn = Nothing

end Function

Select allOpen in new window