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] ]
