We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

cannot get my gridview to delete rows.

aneilg
aneilg asked
on
Medium Priority
276 Views
Last Modified: 2012-08-14
I am using the gridiews built in delete command, to delete a row in my gridview but I get the following error.
I am passing the results through a webservice that is called when the delete button is pressed.
But i get the following error.
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Incorrect syntax near ','.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at WAB_DataAccess.delaColleagueDetails(String strConn, Int32 intColleagueID, String strColleagueName, String strMobile, String strExtension)
at WAB_DataAccessWS.delColleaguedetails(String strConn, Int32 ColleagueID, String ColleagueName, String Mobile, String Extension)
--- End of inner exception stack trace ---


my webservice & class.
    'Delete method called from the webservice
    Public Shared Function delaColleagueDetails(ByVal strConn As String, ByVal intColleagueID As Integer, ByVal strColleagueName As String, ByVal strMobile As String, ByVal strExtension As String) As Integer
 
        Dim conn As SqlConnection = New SqlConnection(strConn)
        Dim cmd As SqlCommand = New SqlCommand("exec p_DeleteColleagueDetails '" & intColleagueID & "', '" & strColleagueName & "', " & strMobile & ", '" & strExtension & "'", conn)
 
        Dim result As Integer = 0
 
        conn.Open()
        result = cmd.ExecuteNonQuery()
 
        conn.Close()
 
        Return result
 
    End Function
 
    'Delete method
    <WebMethod()> _
    Public Function delColleaguedetails(ByVal strConn As String, ByVal ColleagueID As Integer, ByVal ColleagueName As String, ByVal Mobile As String, ByVal Extension As String) As Integer
 
        Return WAB_DataAccess.delaColleagueDetails(strConn, ColleagueID, ColleagueName, Mobile, Extension)
    End Function 
 
mygridview
<asp:objectdatasource id="objdsColleagueDetails" runat="server" Typename="WAB_DataAccessWS.WAB_DataAccessWS" SelectMethod="selColleagueDetails"
 
                                            UpdateMethod="updColleagueDetails" DeleteMethod="delColleagueDetails">
                    <selectparameters>
                        <asp:controlparameter ControlID="hdnMainConnStr" Name="strConn"  Propertyname="Value" Type="String" />
 
                        <asp:ControlParameter ControlID="ddlDepartment" Name="intDepartmentID" PropertyName="SelectedValue" Type="Int32" />
 
                    </selectparameters>
                    <UpdateParameters>
                        <asp:controlparameter ControlID="hdnMainConnStr" Name="strConn" Propertyname="Value" Type="String" />
 
                        <asp:Parameter Name="ColleagueID" Type="Int32" />
                        <asp:Parameter Name="ColleagueName" Type="String" />
                        <asp:Parameter Name="Mobile" Type="String" />
                        <asp:Parameter Name="Extension" Type="String" />
                    </UpdateParameters>
                    <DeleteParameters>
                        <asp:controlparameter ControlID="hdnMainConnStr" Name="strConn" Propertyname="Value" Type="String" />
 
                        <asp:Parameter Name="ColleagueID" Type="Int32" />
                        <asp:Parameter Name="ColleagueName" Type="String" />
                        <asp:Parameter Name="Mobile" Type="String" />
                        <asp:Parameter Name="Extension" Type="String" />
                    </DeleteParameters>
                </asp:objectdatasource>

Open in new window

Comment
Watch Question

Praveen VenuTechnical Project Manager

Commented:
can u please post the ptocedure
delColleagueDetails

Author

Commented:
thanks for your help.

t_ColleagueDetails
ColleagueID            int
ColleagueName      nvarchar(50)
Mobile                  nvarchar(50)
Extension            nvarchar(50)

t_DepartmentDetails
DepartmentID            int
DepartmentName      nvarchar(50)

t_ColleagueDepartment
CollDepID            int
ColleagueID            nvarchar(50)
DepartmentID            nvarchar(50)

FK_t_ColleagueDepartment_t_ColleagueDetails
FK_t_ColleagueDepartment_t_DepartmentDetails

i am displaying the information in a gridview as follows.

colleagueID  colleaguename  mobile  extension
1                   fred                   0112    0113
2                   mary                 0114     0222

CREATE PROCEDURE [dbo].[p_DeleteColleagueDetails]
@ColleagueID int,
@ColleagueName varchar(50),
@Mobile varchar(25),
@Extension varchar(25)
AS
DELETE FROM t_ColleagueDepartment
WHERE ColleagueID = @ColleagueID
DELETE FROM t_ColleagueDetails
WHERE ColleagueID = @ColleagueID
        AND ColleagueName = @ColleagueName
        AND Mobile = @Mobile
        AND Extension = @Extension

Praveen VenuTechnical Project Manager

Commented:
can u please add a watch to see what is the final sql string in the

WAB_DataAccess.delaColleagueDetails


Author

Commented:
thanks for that,
i've tried debugging it but i get WAB_DataAccess.delaColleagueDetails is not declared or the module containing it is not loaded in the debuggibg session.

Technical Project Manager
Commented:
Can you please tell me

Do you have any problem for deleting all the rows or is it for a particular  row.

If it is a particular row then if there any apostrophe in that row?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
good point,
i've tried it on all the rows but still get the error.

any more ideas.

i cannot see anything that stands out.

thanks.
Praveen VenuTechnical Project Manager

Commented:
is it solved?

Author

Commented:
Hi thanks for your help.

it's sort of solved.

I am using the built in edit, can, delete buttons.

When i click on edit button the gridview changes the columns to textboxes, so it enables my template fields.
I have an update button here, but when i changed it to delete it worked.

Currently when i add a delete button it puts it along side the edit button, so it was not binding to the gridview.

I just need to figure out how to add a delete button besides the update & cancel buton when i press the edit button.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.