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

Here i have to use Procedure that contain different type of variable have different datatype and i have to specify insert command in single quotes(as a string) and execute insert command using this

 EXEC  sp_executesql @SQLString command inside sp_GpsdataInsert procedure .

Now i have to execute this procedure then i have to get this error

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

Incorrect Procedure:

create proc sp_GpsdataInsert

@dataindication tinyint,

@vichelId varchar(15),

@Datatime datetime,

@lat varchar(15),

@lang varchar(15),

@speed decimal(5,2),

@direction decimal(6,3),

@odometer decimal(9,2),

@Ignition bit,

@digitalInputOne bit,

@digitalInputTwo bit,

@Immobilizer bit,

@GPSValidityStatus char(1)

as begin

DECLARE @SQLString NVARCHAR(MAX) 

SET @SQLString = 'insert into Tab_'+convert(varchar,getdate(),112) +'([date],dataindication,vichelId,Datatime,lat,lang,speed

,direction,odometer,Ignition,digitalInputOne,digitalInputTwo,Immobilizer,GPSValidityStatus) values('''+GETDATE()+''','+@dataindication+','+

@vichelId+','+convert(varchar, @Datatime, 109)+','+@lat+','+@lang+','+@speed+','+@direction+','+@odometer+','+@Ignition+','+@digitalInputOne+','+@digitalInputTwo+','+@Immobilizer+','+

@GPSValidityStatus

+')'

EXEC sp_executesql @SQLString

 end

Correct Procedure:

Cast each parameter of procedure as varchar because sp_executesql  command operate only string data.

SET @SQLString = 'insert into Tab_'+convert(varchar,getdate(),112) +'([date],dataindication,vichelId,Datatime,lat,lang,speed

,direction,odometer,Ignition,digitalInputOne,digitalInputTwo,Immobilizer,GPSValidityStatus) values

('''+cast(GETDATE() as varchar)+''','+cast(@dataindication as varchar)+','+

@vichelId+','''+cast(@Datatime as varchar)

+''','+@lat+','+@lang+','+cast(@speed as varchar)+','+cast(@direction as varchar)+','

+cast(@odometer as varchar)+','+cast(@Ignition as varchar)+','+cast(@digitalInputOne as varchar)

+','+cast(@digitalInputTwo as varchar)+','+cast(@Immobilizer as varchar)+','+

cast(@GPSValidityStatus as varchar)

+')'

Insert Bulk row at a time in sql table from C#

(1) Initially create Type in sql server...its interesting.

Detail about Type in sql Follow this link:
http://technet.microsoft.com/en-us/library/ms175007.aspx
http://technet.microsoft.com/en-us/library/bb510489.aspx#Restrictions

----Create type-----
create type _table as table
(
E_Name varchar(50),
Salary varchar(200)
)

(2)After that create Procedure in sql:=>here i have to use _table type as table-valued parameter in Procedure.

The table-valued parameter "@tablevariable" must be declared with the READONLY option.

------Create procedure with table-valued parameter---

create proc uspInsertBulkRow

@tablevariable _table readonly
as
begin
insert into mytable(Name,salary) select E_Name,Salary from @tablevariable
end

(3)After that i have to pass datatable as parameter value in procedure and This procedure insert multiple row in sql table.
private DataTable _datatableData(){
               DataTable _dt = new DataTable();
            _dt.Columns.Add("id");
            _dt.Columns.Add("Name");
            _dt.Columns.Add("Salary");
            _dt.Rows.Add("1","Anu", "12000");
            _dt.Rows.Add("3","Anamika", "15000");
            _dt.Rows.Add("4", "Ram", "17000");
            _dt.Rows.Add("5","Aman", "4000");
            _dt.Rows.Add("6","Tarun", "18000");
            _dt.Rows.Add("7","Akash", "5000");
            return _dt;}
    
 using (SqlCommand cmd = new SqlCommand("uspInsertBulkRow", con))
            {
         // ------_datatableData() Is a  method and its return datatable-----------
                cmd.Parameters.AddWithValue("@tablevariable", _datatableData());
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
Output : It's a simple example.you can use this concept as per requirement

How to delete duplicate row from database table in Sql server

(1) select * from student
 duplicate row in table

 

(2)select ROW_NUMBER() over(partition by name order by name) R
,name,dob from student
R is Row number

(3)delete q from(select ROW_NUMBER() over(partition by name order by name) R
,name,dob from student)q where R>1
(4)After delete command table look like.

Refrence Link

http://www.sqlservercentral.com/articles/duplicate+data/102383/