Checking if text already exisits into SQL Server using C#

Question

I'm building simple ERP system for the first time using C# and SQL Server. I'm trying to check if the value from textBox already exists in the database; when form is up and typing into fields, I get an exception

Here is the checking function :

    private void txtRef_Validated(object sender, EventArgs e)
    {          
        BL.CLS_PRODUCTS prd = new BL.CLS_PRODUCTS();
        DataTable Dt = new DataTable();
        Dt = prd.VarifyProductID(txtRef.Text);

        if (Dt.Rows.Count > 0)
        {
            MetroMessageBox.Show(this, "الصنف موجود مسبقاً", "تنبيه", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            txtRef.Focus();
            txtRef.SelectionStart = 0;
            txtRef.SelectionLength = txtRef.Text.Length;
        }
    }

Data table which stops at

da.Fill(dt);

public DataTable SelectData(string stored_procedure, SqlParameter[] param)
{
        SqlCommand sqlcmd = new SqlCommand();
        sqlcmd.CommandType = CommandType.StoredProcedure;
        sqlcmd.CommandText = stored_procedure;
        sqlcmd.Connection = sqlconnection;

        if (param != null)
        {
            for (int i = 0; i < param.Length; i++)
            {
                sqlcmd.Parameters.Add(param[i]);
            }
        }

        SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

and here is my verification check function :

public DataTable VerifyProductID(string ID)
{
    DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
    DataTable Dt = new DataTable();

    SqlParameter[] param = new SqlParameter[1];
    param[0] = new SqlParameter("@ID", SqlDbType.VarChar, 50);
    param[0].Value = ID;

    Dt = DAL.SelectData("VerifyProductID", null);
    DAL.Close();

    return Dt;
}

SQL Server stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[VerifyProductID]
    @ID VARCHAR(50)
AS
    SELECT * 
    FROM PRODUCTS 
    WHERE ID = @ID

The exception:

System.Data.SqlClient.SqlException was unhandled by user code
HResult=-2146232060
Message=Procedure or Function 'VerifyProductID' expects parameter '@ID', which was not supplied.
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=16
LineNumber=0
Number=201
Procedure=VerifyProductID
Server=.\SQLEXPRESS
State=4

StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at E_AND_M.DAL.DataAccessLayer.SelectData(String stored_procedure, SqlParameter[] param) in c:\Users\kh\Documents\Visual Studio 2012\Projects\E_AND_M\E_AND_M\DAL\DataAccessLayer.cs:line 58 at E_AND_M.BL.CLS_PRODUCTS.VarifyProductID(String ID) in c:\Users\kh\Documents\Visual Studio 2012\Projects\E_AND_M\E_AND_M\BL\CLS_PRODUCTS.cs:line 49 at E_AND_M.PL.FRM_ADD_PROUDECT.txtRef_Validated(Object sender, EventArgs e) in c:\Users\kh\Documents\Visual Studio 2012\Projects\E_AND_M\E_AND_M\PL\FRM_ADD_PROUDECT.cs:line 33 at System.Windows.Forms.Control.OnValidated(EventArgs e) at System.Windows.Forms.Control.PerformControlValidation(Boolean bulkValidation) at System.Windows.Forms.ContainerControl.ValidateThroughAncestor(Control ancestorControl, Boolean preventFocusChangeOnError) InnerException:

Dale K Dale K · Accepted Answer · 2018-11-20T00:38:39

Dt = DAL.SelectData("VarifyProductID", null);

should be

Dt = DAL.SelectData("VarifyProductID", param);

Checking if text already exisits into SQL Server using C#

1 Answers