[vargheserapheal]

I am face an error in vb.net and sql server while inserting string value "Name D'Souza" into SQL. The error is "Line 1: Incorrect syntax near Souza. Unclosed quotation mark before the character string ' "

In VB.net Variable is declare as string, in Stored Procedure and in SQL Table variables are declared as Varchar



Please help me....

[nK0de]

its nothing to do with the data types. The problem is with the query caused by the quotation mark. Please post the full SQL query you've written.

try with an extra quotation mark. D''Souza

This post has been edited by nK0de: 05 February 2012 - 02:37 AM

[Ionut]

You have to escape all quotation marks inside a string

INSERT INTO Table1(VarcharColumn) VALUES ('Name D''Souza')
--OR for MSSQL
INSERT INTO Table1(VarcharColumn) VALUES (N'Name D'Souza')

[vargheserapheal]

nK0de, on 05 February 2012 - 02:17 AM, said:

its nothing to do with the data types. The problem is with the query caused by the quotation mark. Please post the full SQL query you've written.

try with an extra quotation mark. D''Souza

This is my query....

@mode		int=0,
@EmpCode 	varchar(10)='',
@EmpName	varchar(50)='',
@Desig  	varchar(50)='',
@DptId		smallint=0,
@EmpGrpId	smallint=0,
@PPNo		varchar(12)='',
@PPExp	datetime='',
@VisaExp	datetime='',
@LabExp	datetime='',
@Password	varchar(10)='',
@Basic  	int=0,
@HRA 		int=0,
@TA  		int=0,
@Birth 		smalldatetime='',
@Join		smalldatetime='',
@Addr1		varchar(50)='',
@Addr2		varchar(50)='',
@Addr3		varchar(50)='',
@Addr4		varchar(50)=''
)
 AS

if @mode=1
begin
set dateformat dmy
insert into employee (EmpCode,EmpName,Designation,DeptId,EmpGrpId,PPNo,PPExp,VisaExp,LabExp,Password,Basic,HRA,TA,Bdate,Jdate,Addr1,Addr2,Addr3,Addr4) values(@EmpCode,@EmpName,@Desig,@DptId,@EmpGrpId,@PPNo,convert(datetime,convert(varchar,@PPExp),103),convert(datetime,@VisaExp),convert(datetime,@LabExp),@Password,@Basic,@HRA,@TA,convert(datetime,@birth),convert(datetime,@join),@addr1,@addr2,@addr3,@addr4)
end

if @mode=2
begin
delete employee where empcode=@empcode
end
if @mode=3
begin

select employee.Empcode,employee.EmpName,employee.PPNo,employee.PPExp,employee.VisaExp,employee.LabExp,employee.Designation,department.DeptName,EmpGroup.EmpGrpName,employee.password,employee.basic,employee.hra,employee.ta,employee.bdate,employee.jdate,employee.addr1,employee.addr2,employee.addr3,employee.addr4 from employee,Department,EmpGroup where Employee.deptId=department.deptId and employee.empgrpid=empgroup.empgrpid order by employee.EmpName
end

if @mode=4
begin
set dateformat dmy
update employee set EmpName=@EmpName,PPNo=@PPNo,PPExp=convert(datetime,convert(varchar(10),@PPExp,103)),VisaExp=convert(datetime,@VisaExp,103),LabExp=convert(datetime,@LabExp,103),Designation=@Desig,DeptId=@DptId,EmpGrpId=@EmpGrpId,Password=@Password,Basic=@Basic,HRA=@HRA,TA=@TA,Bdate=convert(datetime,@birth,103),Jdate=convert(datetime,@join,103),Addr1=@addr1,Addr2=@addr2,Addr3=@addr3,Addr4=@addr4  where empcode=@empcode
end

Please go through it help me .... thanks

This post has been edited by modi123_1: 05 February 2012 - 11:31 AM
Reason for edit:: please use code tags!