Simple SP Delete fails in code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple SP Delete fails in code

Author

Topic

laurie
Starting Member

2 Posts

Posted - 2010-10-22 : 22:54:14

Hi

This SP fails with syntax error when I call it from the code below however it works when I run it inside SQLExpress2005 interface
Can anyone point me to the problem. Sorry about the long post

Any help would be greatly appreciated

Thanks
Laurie

SP Here
++++++++
CREATE PROCEDURE [dbo].[deleteRisk]
-- Add the parameters for the stored procedure here
@Id varChar(36)
AS
DECLARE @guidID uniqueidentifier

SELECT @guidID = CAST(@Id as uniqueidentifier)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Delete
FROM Risks
WHERE Id = @guidID
END

Code Here
++++++++++
public void DeleteChemical(String Id)
{
String ErrorTrap;

using (SqlConnection mySqlConnection = new SqlConnection(AppConfiguration.ConnectionString))
{
SqlCommand myCommand = new SqlCommand("deleteRisk", mySqlConnection);
myCommand.Parameters.AddWithValue("@Id", Id);

try
{
mySqlConnection.Open();
myCommand.ExecuteNonQuery();
}
catch (Exception EX)
{
ErrorTrap = EX.Message;
}
finally
{
mySqlConnection.Close();
}
}
}

GRIDView Here
+++++++++++++++
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"
DataSourceID="odsRisk"
OnRowDataBound="GridView1_RowDataBound"
OnRowDeleting="GridView1_RowDeleting">
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" visible="true"/>
<asp:BoundField DataField="OriginalName" HeaderText="OriginalName" SortExpression="OriginalName" />
<asp:BoundField DataField="ContentType" HeaderText="ContentType" SortExpression="ContentType" />
<asp:BoundField DataField="DateCreated" HeaderText="DateCreated" SortExpression="DateCreated" />
<asp:HyperLinkField DataNavigateUrlFields="Id" DataNavigateUrlFormatString="~/DownloadRisk.aspx?Id={0}" HeaderText="Download" Text="Download" />
<asp:TemplateField HeaderText="View">
<ItemTemplate>
<asp:HyperLink ID="lnkView" runat="server" NavigateUrl='<%# Eval("Id", "~/ViewRisk.aspx?Id={0}") %>' Text="View"></asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True" />
</Columns>
</asp:GridView>
<br />
<asp:ObjectDataSource
ID="odsRisk"
runat="server"
SelectMethod="GetList"
DeleteMethod="DeleteChemical"
TypeName="RiskInfo" >

</asp:ObjectDataSource>

Code Behind Here
+++++++++++++++
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{

int index = e.RowIndex;

String Id = GridView1.Rows[index].Cells[0].Text;

odsRisk.DeleteParameters.Add("Id", TypeCode.String, Id.ToString());
odsRisk.Delete();

}

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-23 : 01:39:43

how are you passing the GUID value? also whats the syntax error you got?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

laurie
Starting Member

2 Posts

Posted - 2010-10-23 : 02:18:55

Hi
Thanks everyome for taking the time to reply
I finally found some else in the world who had done the same dumb thing.
I forgot the line
myCommand.CommandType = CommandType.StoredProcedure;

The final code is as follows

public void DeleteChemical(String Id)
{
String ErrorTrap;

Guid myGidID = new Guid(Id);

using (SqlConnection mySqlConnection = new SqlConnection(AppConfiguration.ConnectionString))
{
SqlCommand myCommand = new SqlCommand("deleteRisk", mySqlConnection);
myCommand.CommandType = CommandType.StoredProcedure;

// Set up the ID parameter
SqlParameter prmId = new SqlParameter("@Id", SqlDbType.UniqueIdentifier);
prmId.Value = myGidID;
myCommand.Parameters.Add(prmId);

try
{
if (mySqlConnection.State == ConnectionState.Open)
{
//if the db connection is currently open it, close it.
mySqlConnection.Close();
}
mySqlConnection.Open();
myCommand.ExecuteNonQuery();

}
catch (Exception EX)
{
ErrorTrap = EX.Message;
}
finally
{
mySqlConnection.Close();
}
}
}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources