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 '='.
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?
Student2with the AdmissionDT defined as varchar(10). Then change your stored procedure to have SAdmit_Dt also defined as varchar(10) and insert into
Student2. Does the test work? If so, what values are in the SAdmit_Dt column in the test table?
Set @iSAdmit_Dt=Convert(Date,@SAdmit_Dt)? To be a good test, you need to change everywhere there is a Date to be varchar to see if that is where the problem is. I honestly don't think the
GETDATEstuff is the problem.