I created on Employee master form. The details which i enter in this form is saving to sql table. But if i use Wild characters example in Name field if i enter "Varghese D'Costa", then the following error comes : Line 1: Incorrect syntax near 'Costa' Unclosed quotation mark before the character string ''.

To avoid this error how can i write codes.

In VB.Net variable declared as string, in Stored Procedure and Table i declared as varchar

The following are my VB.NET codes...


[ Sub MoveData()
EmpCode = Trim(txtEmpcode.Text)
Desig = Trim(txtDesig.Text)
EmpName = Convert.ToString(Trim(txtEmpName.Text))
PPNo = Trim(txtPPNo.Text)
PPExp = Format(CDate(dtPPexp.Text), "MM/dd/yyyy")
VisaExp = Format(CDate(dtVisaExp.Text), "MM/dd/yyyy")
LabExp = Format(CDate(dtLabExp.Text), "MM/dd/yyyy")
Password = Trim(txtPassword.Text)
Basic = Trim(txtBasic.Text)
HRA = Trim(txtHRA.Text)
TA = Trim(txtTA.Text)
Birth = Format(CDate(dtBirth.Text), "MM/dd/yyyy")
join = Format(CDate(dtJoin.Text), "MM/dd/yyyy")
Addr1 = Trim(txtAddr1.Text)
Addr2 = Trim(txtAddr2.Text)
Addr3 = Trim(txtAddr3.Text)
Addr4 = Trim(txtAddr4.Text)

End Sub ]

[ Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
If btnAdd.Text = "&Add" Then
btnAdd.Text = "&Save"
ElseIf btnAdd.Text = "&Save" Then
ChkNum = 1
textmsg = "Do you want to save your record...?"
EmptyChk()
If ChkNum = 0 Then
btnAdd.Text = "&Add"
End If
End If
btnCancel.PerformClick()
End Sub ]

[ Sub SaveData()
SaveGetcode()
MoveData()
objclsStud.SaveEmpData(ChkNum, EmpCode, EmpName, Desig, DptId, EmpGrpId, PPNo, PPExp, VisaExp, LabExp, Password, Basic, HRA, TA, Birth, join, Addr1, Addr2, Addr3, Addr4)
btnCancel.PerformClick()
End Sub ]


[ Public Function SaveEmpData(ByVal WorkID As Integer, ByVal EmpCode As String, ByVal EmpName As String, ByVal desig As String, ByVal dptId As Integer, ByVal EmpGrpId As Integer, ByVal PPNo As String, ByVal PPExp As String, ByVal VisaExp As String, ByVal LabExp As String, ByVal Password As String, ByVal Basic As Integer, ByVal HRA As Integer, ByVal TA As Integer, ByVal Birth As String, ByVal Join As String, ByVal Addr1 As String, ByVal Addr2 As String, ByVal Addr3 As String, ByVal Addr4 As String)
db.Execute("sp_Empsave " & WorkID & ",'" & EmpCode & "','" & EmpName & "','" & desig & "'," & dptId & "," & EmpGrpId & ",'" & PPNo & "','" & PPExp & "','" & VisaExp & "','" & LabExp & "','" & Password & "'," & Basic & "," & HRA & "," & TA & ",'" & Birth & "','" & Join & "','" & Addr1 & "','" & Addr2 & "','" & Addr3 & "','" & Addr4 & "'")
End Function ]


Stored Procedure.....

[ CREATE PROCEDURE sp_EmpSave
(
@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 ]


SQL Table

[ CREATE TABLE [dbo].[employee] (
[empid] [smallint] IDENTITY (1, 1) NOT NULL ,
[Empcode] [varchar] (50) NOT NULL ,
[EmpName] [varchar] (50) NOT NULL ,
[PPNo] [char] (12) NOT NULL ,
[PPexp] [datetime] NOT NULL ,
[Visaexp] [datetime] NOT NULL ,
[Labexp] [datetime] NOT NULL ,
[Designation] [varchar] (50) NOT NULL ,
[DeptId] [smallint] NOT NULL ,
[EmpGrpId] [smallint] NOT NULL ,
[Password] [ntext] NULL ,
[Basic] [int] NULL ,
[HRA] [int] NULL ,
[TA] [int] NULL ,
[Bdate] [datetime] NULL ,
[Jdate] [datetime] NULL ,
[Addr1] [varchar] (50) NULL ,
[Addr2] [varchar] (50) NULL ,
[Addr3] [varchar] (50) NULL ,
[Addr4] [varchar] (50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] ]

You can replace a single quote with two single quotes before you add the string to the query. For example

insert into table1 (name) values('D''Costa')

If you need to search for fields that contain characters like '%' and '_' you can use the ESCAPE keyword as follows:

select * from table1 where name like '%\_%' ESCAPE '\'

This will return all names containing an underscore. We have defined the backslash as a special character to prevent the underscore in the LIKE clause from being used as a wildcard.

These strings are passing through variables from textbox from vb.net query to sql. How can i insert single cords infront of '.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.