Sql-server – Inserting ID and Date to locate data for C#/VBA application

sql serverstored-proceduresvba

I am trying to allow users to upload an excel sheet to insert data into the sql database. When they upload data I want a datagrid view to show the data they just uploaded in the database and to be able to see that in retrospect as well based on the date.

I created a table like this: 

**dbo.DB_TRACK**  
V_ID Identity  
TBL_N varchar(20)  
ID  int  
ENTR_DT date

I wrote Stored Procedure for one of the database tables like this: 

Create Procedure dbo.Insert_Students  
(  
SName varchar(10),  
SAge varchar(10),  
SAdmit_Dt varchar(10)  
)  
    Declare @id table(id int),  
     @pk int,  
     @iSAge int,  
     @iSAdmit_Dt date  

     Set @iSAge=@SAge  
     Set @iSAdmit_Dt=Convert(Date,@SAdmit_Dt)

    BEGIN TRY  

      BEGIN TRANSACTION  

      Insert into dbo.Student(Name,Age,AdmissionDt)  
      OUTPUT INSERTED.S_ID into @id    
      Values  
      (@SName,@iSAge,@iSAdmit_Dt)  

      COMMIT TRANSACTION  

      BEGIN TRANSACTION  

      Insert into dbo.DB_TRACK (TBL_N, ID, ENTR_DT ) 
      VALUES ('dbo.Student', (Select @pk=id from @id),GETDATE)

      COMMIT TRANSACTION  

    END TRY  
    BEGIN CATCH

      IF @@TRANCOUNT > 0  
          ROLLBACK TRANSACTION  

    END CATCH

I am getting the error:

Incorrect syntax near '='.

UPDATE:
Not getting this error anymore (Thanks to David Browne) and was able to save the SP but when executing the SP, I am getting this error:

Conversion failed when converting date and/or time from character
string.

I tried Convert(date,GetDate()) but it did not work

The code that calls this stored procedure is in VBA like this: 

Dim SName, SAge,SAdmit_DT as String  
Dim oSName, oSAge,oSAdmit_DT as ADODB.Parameter  

SName= ws.Cells(1, 11)  
SAge = ws.Cells(1, 12)  
SAdmit_DT = ws.Cells(1, 13)  

Set oSName = cmd.CreateParameter("@SName", adVarChar, adParamInput, 10, SName)  
cmd.Parameters.Append oSName  
Set oSAge = cmd.CreateParameter("@SAge", adVarChar, adParamInput, 10, SAge)  
cmd.Parameters.Append oSAge  
Set oSAdmit_DT = cmd.CreateParameter("@SAdmit_DT", adVarChar, adParamInput, 10, SAdmit_DT)  
cmd.Parameters.Append oSAdmit_DT 

cmd.Execute' This is where it errors out

I tried removing SAdmit_Dt but still got the error
I tried passing DATE() through excel but got an Arithmatic overflow error
Any idea how to fix this? Also is there a better way to do what I am trying to do?

Best Answer

-Please don't use short, cryptic column names.
-There's no reason to wrap individual statements in explicit transactions.

You'll want to extract the key value from the table variable in a separate statement. Something like:

Create Procedure dbo.Insert_Student  
(  
SName varchar(10),  
SAge int,  
SAdmit_Dt date  
)  
BEGIN
    Declare @ids table(id int)
    Declare @pk int 

    BEGIN TRY  

      BEGIN TRANSACTION  

      Insert into dbo.Student(Name,Age,AdmissionDt)  
      OUTPUT INSERTED.S_ID into @id    
      Values  (@SName,@SAge,@SAdmit_Dt);

      set @pk = (Select id from @id);

      Insert into dbo.DB_TRACK (TBL_N, ID, ENTR_DT ) 
      VALUES ('dbo.Student', @pk,GETDATE());

      COMMIT TRANSACTION  

    END TRY  
    BEGIN CATCH

      IF @@TRANCOUNT > 0  
          ROLLBACK TRANSACTION  

      THROW;

    END CATCH
END
Related Question