Insert using stored procedure in mulitple table in C#

Question

ALTER PROCEDURE [dbo].[sp_CreateProcess]
   @type varchar(50),--0
   @HospitalID varchar(50),--1
   @PatientID varchar(50) ,--2
   @PreAuthorization varchar(50) ,--3
   @Status varchar(50) ,--4
   @Amount varchar(50) ,--5
   @AmtWords varchar(500) ,--6
   @AppTrt varchar(50) ,--7
   @TypeTrt varchar(50) ,--8
   @AilName varchar(100) ,--9
   @AilCode varchar(50) ,--10
   @RoomType varchar(50) ,--11
   @RoomRent varchar(50) ,--12
   @Signature varchar(50) ,--13
   @ProcessBy varchar(50) ,--14
   @CorpScheme varchar(50) ,--15
   @InsuranceDiv varchar(50) ,--16
   @DrRmk varchar(50) ,--17
   @Created_Date datetime ,--18
   @Modified_Date datetime ,--19
   @Created_By varchar(50) ,--20
   @Modified_By varchar(50) ,--21
   @CoRmk varchar(100) ,--22
   @Remark varchar(1500) ,--23
   @CreatedBy varchar(50) ,--24
   @ModifiedBy varchar(50) ,--25
   @CreatedDate datetime ,--26
   @ModifiedDate datetime, --27
   @UserRole int --28
AS
BEGIN
    if @type = 'insertProcess'
    begin
        insert into Patient_Process_Status values
        (
            @HospitalID,
            @PatientID,
            @PreAuthorization,
            @Status,
            @AilName,
            @AilCode,
            @RoomType,
            @RoomRent,
            @Signature,
            @ProcessBy,
            @CorpScheme,
            @InsuranceDiv,
            @DrRmk,
            CURRENT_TIMESTAMP,
            CURRENT_TIMESTAMP,
            @Created_By,
            @Modified_By
        )

        update dbo.UserCommunication set RoleID=@UserRole
        where PatientID=@PatientID  and  HospitalID=@HospitalID

        update PD_Patients set LastUpdatedRole=@UserRole
        where PatientID=@PatientID and  HospitalID=@HospitalID

        insert into PostEnhancetbl values
        (
            @PatientID,
            @HospitalID,
            @Amount,
            @AmtWords,
            @AppTrt,
            @TypeTrt ,
            @CoRmk,
            @Remark,
            @CreatedBy,
            @ModifiedBy,
            CURRENT_TIMESTAMP,
            CURRENT_TIMESTAMP
        )

    end

    if @type='getTPADetails'
    begin
        select PS.*,LastUpdatedRole from Patient_Process_Status PS
        left join PD_Patients PD on PS.PatientID=PD.PatientID  and  PS.HospitalID=PD.HospitalID
        where PS.PatientID=@PatientID  and  PS.HospitalID=@HospitalID
    end
END

This is the error I get:

This is the Error facing in C# code **

The above code is my SQL Server 2014 stored procedure where I want to join two tables and insert my form value in 2 tables.

Please help me to resolve the issue and let me know why I am facing error of string to datetime conversion

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, May 11 '15 at 11:34
Use parameters with types rather than just values and convert `DateTime`s in your code and dont' send them as whatever format strings to SQL Server — Sami Kuhmonen, May 14 '15 at 09:26

score 0 · Answer 1 · answered May 11 '15 at 11:29

0

The problem here is that you are passing a string type into a datetime parameter and it is failing to convert that string into datetime.

Check your parameters and make sure you pass a datetime format parameter value or you can use CONVERT in SQL Server side to convert the parameter into a proper and valid datetime format.

You could try using DateTime variableName instead of string variableName.

Hope this helps.

answered May 11 '15 at 11:29

Dhrumil

3,221
6
21
34

i am passing datetime through sql server i am confused why the error is occured. – Sanjay Gangurde May 11 '15 at 11:31
Show the list of all parameters. That will help get more idea about the types. – Dhrumil May 11 '15 at 11:36
Can you show the full line of the query parameters in your C# code ? – Dhrumil May 11 '15 at 11:39
public int insertProcess(string str, string HospitalID, string PatientID, string PreAuthorization, string Status, string Amount, string AmtWords, string AppTrt, string TypeTrt, string AilName, string AilCode, string RoomType, string RoomRent, string Signature, string ProcessBy, string CorpScheme, string InsuranceDiv, string DrRmk, string CoRmk, string Remark, string CreatedBy, string ModifyBy, string Created_By, string Modify_By, string UserRole) – Sanjay Gangurde May 11 '15 at 11:44
Please check that the order in which you are passing the parameters in your SQL insert query matches the order of the columns of the table exactly with the same datatypes. – Dhrumil May 11 '15 at 11:47

Insert using stored procedure in mulitple table in C#

1 Answers1