Incorrect Syntax near ',' Error in VB .net

Question

0.00/5 (No votes)

See more:

Dear All,
I have problem when am click the edit button:Incorrect syntax near ','.. please ASAP..

VB

Public Function GetStaffInfo(ByVal StaffSearch As StaffSearchData) As SqlDataReader
        Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString)
        Dim cmd As SqlCommand = New SqlCommand("Sp_GetStaffInfo_New", conn)
        cmd.CommandType = Data.CommandType.StoredProcedure
        conn.Open()

        cmd.Parameters.Add("@StaffId", Data.SqlDbType.NVarChar, 50)
        cmd.Parameters.Add("@LastName", Data.SqlDbType.NVarChar, 50)
        cmd.Parameters.Add("@FirstName", Data.SqlDbType.NVarChar, 50)
        cmd.Parameters.Add("@MiddleName", Data.SqlDbType.NVarChar, 50)
        cmd.Parameters.Add("@IsActive", Data.SqlDbType.NVarChar, 50)
        cmd.Parameters.Add("@IsDeleted", Data.SqlDbType.NVarChar, 50)
        cmd.Parameters.Add("@Practiceid", Data.SqlDbType.NVarChar, 50)
        cmd.Parameters.Add("@Orderby", Data.SqlDbType.NVarChar, 250)

        cmd.Parameters.Item("@StaffId").Value = StaffSearch.StaffId
        cmd.Parameters.Item("@LastName").Value = StaffSearch.LastName
        cmd.Parameters.Item("@FirstName").Value = StaffSearch.FirstName
        cmd.Parameters.Item("@MiddleName").Value = StaffSearch.MiddleName
        cmd.Parameters.Item("@IsActive").Value = StaffSearch.IsActive
        cmd.Parameters.Item("@IsDeleted").Value = StaffSearch.IsDeleted
        cmd.Parameters.Item("@Practiceid").Value = StaffSearch.Practiceid
        cmd.Parameters.Item("@Orderby").Value = StaffSearch.Orderby

        Dim Rec As SqlDataReader = cmd.ExecuteReader(Data.CommandBehavior.CloseConnection)

        Return Rec
    End Function

Posted 9-Aug-15 19:28pm

Vivek.anand34

Updated 9-Aug-15 19:44pm

Suvendu Shekhar Giri

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2015-08-09T19:40:00

Solution 1

1) Check your query for errors
2) Check there is no ' single quote in any of the data you pass to the database. If there are quotes. escape them - http://blog.sqlauthority.com/2008/02/17/sql-server-how-to-escape-single-quotes-fix-error-105-unclosed-quotation-mark-after-the-character-string/[^].

Posted 9-Aug-15 19:40pm

Abhinav S

Comments

Vivek.anand34 10-Aug-15 1:50am

USE [chakra]
GO
/****** Object: StoredProcedure [dbo].[SP_GetStaffInfo_New] Script Date: 08/10/2015 11:17:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_GetStaffInfo_New]
(
@Staffid nvarchar(50)='',
@LastName nvarchar(50)='',
@FirstName nvarchar(50)='',
@MiddleName nvarchar(50)='',
@IsActive nvarchar(50)='',
@IsDeleted nvarchar(50)='',
@Practiceid nvarchar(50)='',
@Orderby nvarchar(200)=''
)
AS
Declare @SQLQuery AS NVarchar(4000)
Set @SQLQuery = 'select Practice.[Name] as PracticeName ,Mas_Prefix.Title as Prefix,Staff.* from Staff
inner join Practice on Practice.Practiceid=Staff.Practiceid
inner join Mas_Prefix on Mas_Prefix.Prefixid=Staff.Prefixid where (1=1) '

If len(@Staffid) > 0 and isnumeric(@Staffid)=1
Set @SQLQuery = @SQLQuery + ' And (Staff.Staffid = '+ ltrim(str(@Staffid)) +')'

If len(@FirstName) > 0
Set @SQLQuery = @SQLQuery + ' And (Staff.FirstName like ''%'+ @FirstName +'%'')'

If len(@LastName) > 0
Set @SQLQuery = @SQLQuery + ' And (Staff.LastName like ''%'+ @LastName +'%'')'

If len(@MiddleName) > 0
Set @SQLQuery = @SQLQuery + ' And (Staff.MiddleName like ''%'+ @MiddleName +'%'')'

If len(@IsActive) > 0 and isnumeric(@IsActive)=1
Set @SQLQuery = @SQLQuery + 'And (Staff.IsActive = '+ltrim(str(@IsActive)) +') '

If len(@IsDeleted)> 0 and isnumeric(@IsDeleted)=1
Set @SQLQuery = @SQLQuery + 'And (Staff.IsDeleted = '+ltrim(str(@IsDeleted)) +') '

If len(@Practiceid) > 0 and isnumeric(@Practiceid)=1
Set @SQLQuery = @SQLQuery + ' And (Staff.Practiceid = '+ ltrim(str(@Practiceid)) +')'

If len(@Orderby) > 0
Set @SQLQuery = @SQLQuery + @Orderby

exec(@SQLQuery)

Vivek.anand34 10-Aug-15 1:51am

please tel me this is my sp.. any prob in this query.. incorrect syntax near ','

Maciej Los 10-Aug-15 3:08am

+5!

Vivek.anand34 10-Aug-15 4:43am

what?

Maciej Los 10-Aug-15 5:23am

+5 meant that i voted for that answer.

Abhinav S 10-Aug-15 5:41am

Thank you.

Vivek.anand34 10-Aug-15 6:23am

What is my problem.. i can't cleared that problem still now..

Vivek.anand34 10-Aug-15 6:26am

Dim rec As SqlDataReader = Staff.GetStaffInfo(StaffSearch) i put this line in another screen to get that value... then gets error..
I just hide this line its execute but value didn't get..

Vivek.anand34 10-Aug-15 6:30am

to get the value i add this code:

'Reading Staff Details
Dim rec As SqlDataReader = Staff.GetStaffInfo(StaffSearch)
If rec.Read Then
PageTitle = System.Configuration.ConfigurationManager.AppSettings("AdminPageTitle") & "- Edit Staff"
If Not IsDBNull(rec.Item("PracticeName")) Then
txtpractice.Text = rec.Item("PracticeName")
End If

txtadd1.Text = Trim(rec.Item("add1"))
txtadd2.Text = Trim(rec.Item("add2"))
txtcity.Text = Trim(rec.Item("city"))
'txtcountry.Text = Trim(rec.Item("country")) //def
txtfirstname.Text = Trim(rec.Item("firstname"))
txtlastname.Text = Trim(rec.Item("lastname"))

txtphone1.Text = Mid(IIf(IsDBNull(rec.Item("phone")), "", rec.Item("phone")), 1, 3)
txtphone2.Text = Mid(IIf(IsDBNull(rec.Item("phone")), "", rec.Item("phone")), 4, 3)
txtphone3.Text = Mid(IIf(IsDBNull(rec.Item("phone")), "", rec.Item("phone")), 7, 4)

txtzipcode.Text = Trim(rec.Item("zipcode"))
txtzipcode4.Text = Trim(rec.Item("zipcode4"))
ddlstate.SelectedValue = Trim(rec.Item("stateid"))
'txtdesignation.Text = Trim(rec.Item("designation")) //
txtdegree.Text = Trim(rec.Item("degree"))
If Not IsDBNull(rec.Item("dob")) Then
txtdob.Text = Trim(rec.Item("dob"))
End If

txtemail.Text = Trim(rec.Item("email"))
txtmiddlename.Text = Trim(rec.Item("middlename"))

txtssn1.Text = Mid(rec.Item("SSN"), 1, 3)
txtssn2.Text = Mid(rec.Item("SSN"), 4, 2)
txtssn3.Text = Mid(rec.Item("SSN"), 6, 4)

txtstafftitle.Text = Trim(rec.Item("title"))
txtusername.Text = Trim(rec.Item("username"))
txtpassword.Attributes.Add("value", rec.Item("password")) //def
txtPracticeId.Value = Trim(rec.Item("practiceid"))
'com.FillDropDown(ddllocation, "locationid", "locationname", "select locationid,locationname from location where practiceid=" & txtPracticeId.Value, 0) //
'ddllocation.SelectedValue = Trim(rec.Item("locationid")) //
ddlprefix.SelectedValue = Trim(rec.Item("prefixid"))
ddlrole.SelectedValue = Trim(rec.Item("roleid"))
ddlgender.SelectedValue = Trim(rec.Item("sex"))
Else
rec.Close()
Response.Redirect("default.aspx")
End If
rec.Close()