Stored Procedure Problem

brettdavis4 · November 3rd, 2003, 02:30 AM

Hi,

I am between a beginner / intermediate developer with C# and ASP.net. I am trying to do a site in asp.net and sql server and using stored procedures. I have figured out how to delete and edit, but i can't figure out how to add. Could you please look at my code and tell me what is wrong. I'm probably doing something wrong, but I can't tell what it is.

thanks,

brett

the store procedure
------------------------------------
CREATE PROCEDURE sp_CodesLinksAdd
@Type Int,
@Link Varchar(255),
@LinkName Varchar(255)
as
Insert Into CodeLinks(CodeLinkName, CodeLink, CodeID)
Values(@LinkName, @Link, @Type)
return
GO

the codebehind page
------------------------------------
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace somesite.admin
{
    /// <summary>
    /// Summary description for resource_links_add.
    /// </summary>
    public class resource_links_add : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.TextBox txtLinkname;
        protected System.Web.UI.WebControls.Button btnSubmit;
        protected System.Web.UI.WebControls.Label lblMessage;
        protected System.Web.UI.WebControls.TextBox txtLink;
        protected System.Web.UI.WebControls.DropDownList ddl_codes;

        SqlConnection myConnection = new SqlConnection(Global.CONNSTRING);

        private void Page_Load(object sender, System.EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                SqlDataAdapter myCommand = new SqlDataAdapter("sp_Codes", myConnection);
                myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet ds = new DataSet();
                myCommand.Fill(ds, "Codes");

                ddl_codes.DataSource = ds.Tables["Codes"].DefaultView;
                ddl_codes.DataBind();
            }
        }

        private void btnSubmit_Click(object sender, System.EventArgs e)
        {
            int CodeID = Convert.ToInt32(ddl_codes.SelectedItem.Value);
            string Link = Convert.ToString(txtLink.Text);
            string LinkName = Convert.ToString(txtLinkname.Text);

            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = new SqlConnection(Global.CONNSTRING);
            myCommand.Connection.Open();
            myCommand.CommandText = "sp_CodesLinksAdd" ;
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add(new SqlParameter("@LinkName", SqlDbType.VarChar, 255));
            myCommand.Parameters["@LinkName"].Value = LinkName;
            myCommand.Parameters.Add(new SqlParameter("@Link", SqlDbType.VarChar, 255));
            myCommand.Parameters["@Link"].Value = Link;
            myCommand.Parameters.Add(new SqlParameter("@Type", SqlDbType.Int, 4));
            myCommand.Parameters["@Type"].Value = CodeID;
            myCommand.ExecuteNonQuery();
        }

        #region Web Form Designer generated code
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: This call is required by the ASP.NET Web Form Designer.
            //
            InitializeComponent();
            base.OnInit(e);
        }

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion

    }
}

planoie · November 3rd, 2003, 11:08 AM

What is the error you are getting? Your procedure looks ok. Have you tried directly in SQL to confirm that the procedure does indeed work?

Peter

brettdavis4 · November 3rd, 2003, 11:11 AM

I'm not getting an error message. Everything looks like it is fine but the data doesn't get inserted. I double checked to see what my sql server permissions where on the table, and they are set to insert, delete, and update. So I'm not sure what i am doing wrong.

Brett

melvik · November 3rd, 2003, 11:21 AM

Code:

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<Script Runat="Server">
  void Page_Load( Object s, EventArgs e )
  {
    if ( !IsPostBack )
      BindData();
  }

  void BindData()
  {
    SqlConnection myConnection;
    SqlCommand myCommand;
    myConnection = new SqlConnection( "Server=Localhost;uid=sa;Database=Pubs" );
    myCommand = new SqlCommand( "Select au_id, au_lname, au_fname, phone from Authors order by au_lname", myConnection );
    myConnection.Open();
    myDataList.DataSource = myCommand.ExecuteReader();
    myDataList.DataBind();
    myConnection.Close();
  }

  void editAuthor( Object s, DataListCommandEventArgs e )
  {
    myDataList.EditItemIndex = e.Item.ItemIndex;
    BindData();
  }

  void cancelEdit( Object s, DataListCommandEventArgs e )
  {
    myDataList.EditItemIndex = -1;
    BindData();
  }

  void deleteAuthor( Object s, DataListCommandEventArgs e )
  {
    SqlConnection myConnection;
    SqlCommand myCommand;
    String sqlString;
    myConnection = new SqlConnection( "Server=Localhost;uid=sa;Database=Pubs" );
    sqlString = "Delete Authors Where au_id=@authorID";
    myCommand = new SqlCommand( sqlString, myConnection );
    myCommand.Parameters.Add( new SqlParameter( "@authorID", SqlDbType.VarChar, 11 ) );
    myCommand.Parameters[ "@authorID" ].Value = myDataList.DataKeys[ e.Item.ItemIndex ];
    myConnection.Open();
    myCommand.ExecuteNonQuery();
    myDataList.DataBind();
    myConnection.Close();
    myDataList.EditItemIndex = -1;
    BindData();
  }

  void updateAuthor( Object s, DataListCommandEventArgs e )
  {
    SqlConnection myConnection;
    SqlCommand myCommand;
    String sqlString;
    myConnection = new SqlConnection( "Server=Localhost;uid=sa;Database=Pubs" );
    sqlString = "Update Authors Set au_lname=@lastname, au_fname=@firstname, phone=@phone Where au_id=@authorID";
    myCommand = new SqlCommand( sqlString, myConnection );
    myCommand.Parameters.Add( new SqlParameter( "@lastname", SqlDbType.VarChar, 40 ) );
    myCommand.Parameters[ "@lastname" ].Value = ( (TextBox) e.Item.FindControl( "lastname" ) ).Text;
    myCommand.Parameters.Add( new SqlParameter( "@firstname", SqlDbType.VarChar, 20 ) );
    myCommand.Parameters[ "@firstname" ].Value = ( (TextBox) e.Item.FindControl( "firstname" ) ).Text;
    myCommand.Parameters.Add( new SqlParameter( "@phone", SqlDbType.Char, 12 ) );
    myCommand.Parameters[ "@phone" ].Value = ( (TextBox) e.Item.FindControl( "phone" ) ).Text;
    myCommand.Parameters.Add( new SqlParameter( "@authorID", SqlDbType.VarChar, 11 ) );
    myCommand.Parameters[ "@authorID" ].Value = myDataList.DataKeys[ e.Item.ItemIndex ];
    myConnection.Open();
    myCommand.ExecuteNonQuery();
    myDataList.DataBind();
    myConnection.Close();
    myDataList.EditItemIndex = -1;
    BindData();
  }
</Script>

<html>
<head><title>Edit Authors</title></head>
<body>

<form Runat="Server">

<asp:DataList id="myDataList" cellpadding=10 cellspacing=0 gridlines="both"
  RepeatColumns="3" RepeatDirection="Horizontal" DataKeyField="au_id"
  OnEditCommand="editAuthor" OnDeleteCommand="deleteAuthor"
  OnUpdateCommand="updateAuthor" OnCancelCommand="cancelEdit" Runat="Server">

  <ItemTemplate>
    <asp:LinkButton Text="Edit" CommandName="edit" Runat="Server"/>
    <%# DataBinder.Eval( Container.DataItem, "au_lname" )%>
  </ItemTemplate>

  <EditItemTemplate>
     <b>Last Name:</b>
     <br><asp:TextBox id="lastname"
       text='<%# DataBinder.Eval( Container.DataItem, "au_lname" ) %>'
       Runat="Server"/>
     <p>
     <b>First Name:</b>
     <br><asp:TextBox id="firstname"
       text='<%# DataBinder.Eval( Container.DataItem, "au_fname" ) %>'
       Runat="Server"/>
     <p>
     <b>Phone:</b>
     <br><asp:TextBox id="phone"
       text='<%# DataBinder.Eval( Container.DataItem, "phone" ) %>'
       Runat="Server"/>
     <p>
     <asp:Button Text="Update" CommandName="update" Runat="Server"/>
     <asp:Button Text="Delete" CommandName="delete" Runat="Server"/>
     <asp:Button Text="Cancel" CommandName="cancel" Runat="Server"/>
  </EditItemTemplate>

</asp:DataList>

</form>
</body>
</html>

HTH

Always:),
Hovik Melkomian.

planoie · November 3rd, 2003, 11:22 AM

Something I just noticed (I work in VB so I didn't catch this my first glimpse).
I think you are missing the handler wiring for your button.

Try this:

private void InitializeComponent()
    {
        this.Load += new System.EventHandler(this.Page_Load);
        btnSubmit.Click += new System.EventHandler(this.btnSubmit_Click);
    }

If this doesn't do it, try the procedure directly in SQL to confirm that at least the procedure is ok.

Peter
----------------------------------------
Work smarter, not harder.

pgtips · November 3rd, 2003, 11:25 AM

Maybe its because you add the parameters in a different order than they are specified in the stored proc. What I mean is that you should add the Type param first, then the Link param, then the LinkName.

Strange that you don't get any errors though. have you tried running a SQL Profiler trace while the code is being executed? I sometimes find it helpful to use Profiler to determine the exact command that is being sent to SQL Server, then running that exact same command in Query Analyser and seeing what happens.

hth
Phil

tonyellard · November 3rd, 2003, 03:41 PM

I agree with you pgtips - I've run into this before where I placed the parameters out of order and even though it doesn't give you an error on the ASP side, the Stored Proc. is dying because of it. Whenever you use a stored proc in ASP you need to make sure that the parameters are in the same order as they appear in the Procedure declaration.

brettdavis4 · November 3rd, 2003, 09:46 PM

Everyone Peter was right. Thanks a bunch!!

Brett