Pass Textbox Text to Stored Procedure in C#

See more:

C#

ASP.NET

Hello, all!

I'm putting together a point-of-sale system that will work in conjunction with an already working eCommerce website we have here. There is a SQL Server table (Product) that contains records of items that we sell. One of the columns in the table is SKU which contains the our unique product number.

When someone enters a SKU number into a textbox on the sale screen I want to execute a stored procedure to query the Product table and tell me whether the SKU exists or not. If it does it will be added to the customer's receipt; if it isn't it will kick back an error telling the person to try entering the SKU again.

If I execute the stored procedure within SQL it works fine. It returns a value of 10 if the SKU exists and 30 if it doesn't. It does just what it's supposed to do. If I execute the stored procedure from my script using the SKU the user enters into the textbox it always returns 30 (SKU doesn't exist). If I hard-code a good SKU into my script it returns 10 and works fine. It doesn't seem to be taking the text entered into my textbox.

Thanks so much for any help!

My stored procedure:

USE [SchoolStoreWeb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [dbo].[ochs_Validate_SKU]
	@EnteredSKU NVARCHAR(50)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SKU NVARCHAR(50)

	SELECT @SKU = SKU FROM PRODUCT WHERE SKU = @EnteredSKU

	IF @SKU IS NOT NULL
		BEGIN
		INSERT INTO ochs_InvoiceDetails (SKU) VALUES (@SKU)
		SELECT 10 --SKU Does Exist in main product table
		END
	ELSE
		BEGIN
		SELECT 30 --SKU Does NOT Exist in any table
		END
END	
GO

My Code:

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="SaleScreen.aspx.cs" Inherits="SaleScreen" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <h2>Sale</h2>
    <p style="text-align: left">
        <asp:Label ID="lblInvoiceNumber" runat="server" Text=""></asp:Label>
        <asp:HiddenField ID="hfInvoiceNumber" runat="server" Value="" />
    </p>
    <table class="sellscreen-main-table">
        <tr>
            <td style="width:25px"> </td>
            <td style="width:75px">SKU</td>
            <td style="width:425px">Description</td>
            <td style="width:30px">Qty</td>
            <td style="width:50px">Price</td>
            <td style="width:50px">Total</td>
            <td style="width:25px"> </td>
        </tr>

        <asp:Repeater ID="ItemDetailRepeater" runat="server">
            <ItemTemplate>

            </ItemTemplate>
        </asp:Repeater>
        <tr>
            <td></td>
            <td><asp:TextBox ID="txtSKU" runat="server" Text="" CssClass="sku-textbox"></asp:TextBox><asp:Button ID="btnSKUsubmit" runat="server" Text="" OnClick="btnSKUsubmit_Click" /></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
        </tr>
    </table>

    <asp:Label ID="lblErrorMessage" runat="server" Text="" CssClass="ErrorMessage"></asp:Label>

</asp:Content>

Code Behind:

protected void btnSKUsubmit_Click(object sender, EventArgs e)
    {
        //Check to see if SKU exists. If it does add the item to Invoice Details.
        int SKUExist = 0;
        string constr = ConfigurationManager.ConnectionStrings["csPOS"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("ochs_Validate_SKU"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@EnteredSKU", txtSKU.Text.Trim());
                cmd.Connection = con;
                con.Open();
                SKUExist = (int)cmd.ExecuteScalar();
                con.Close();
                lblErrorMessage.Text = SKUExist.ToString();
            }
        }
    }