Error Passing parameters to Data Access Layer

Question

I am passing a string to a DAL which is the Select Query with to parameters, first name and last name. The answer I keep getting is System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@FirstName".' The request is for the 'DAL to return a Dataset.

I have a similar function that updates one record with the same parameters which works well

private void button1_Click(object sender, EventArgs e)
{
    {
        strCmd = "SELECT * FROM Person.Person WHERE FirstName=@FirstName AND LastName=@LastName";

        // Add the parameters.
        data.aCommand.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 50)).Value = "Morgan";
        data.aCommand.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 50)).Value = "Zoe";

        ds = data.ReturnDataSet(strCmd);
        dataGridView1.DataSource = ds.Tables[0];
        dataGridView1.AutoGenerateColumns = true;
        BindingSource bSource = new BindingSource();
        bSource.DataSource = ds.Tables[0];
        //set the DataGridView DataSource
        dataGridView1.DataSource = bSource;
    }
}

DAL

public DataSet ReturnDataSet(string SQLSelectCommand)
{
    SqlDataAdapter daGeneric = new SqlDataAdapter(SQLSelectCommand, this.sQLConnection);
    DataSet ds = new DataSet();
    daGeneric.Fill(ds);
    return ds;
}

public bool UpdateData(string OdbcCommand)
{
    bool ret = false;
    try
    {
        aCommand.CommandText = OdbcCommand;
        int i = aCommand.ExecuteNonQuery();
        if (i > 0)
            ret = true;
    }
    catch (SqlException ex)
    {
        MessageBox.Show(ex.Message);
        ret = false;
    }
    finally
    {
        //conn.Close();
    }
    return ret;
}

I am still having trouble as I am unable to create the stored procedure.

Here is the full DAL code:

using System.Data.Sql;
using System.Data;
using System.Configuration;
using System.Windows;
using System.Data.SqlClient;
using System;
using System.Windows.Forms;


namespace OJT.DAL
{
    public class OJT_Data
    {
        public SqlConnection sQLConnection = new SqlConnection();
        public SqlCommand aCommand = new SqlCommand();
        private string DatabaseName = "";
        public string strConnStr;
        private string strDatabaseName = "";

        public void openDB()
        {
            sQLConnection.ConnectionString = strConnStr;
            sQLConnection.Close();
            sQLConnection.Open();
            aCommand = sQLConnection.CreateCommand();
        }
        public SqlDataReader ReturnData(string SQLDBCommand)
        {
            SqlDataReader r = null;
            try
            {
                aCommand.CommandText = SQLDBCommand;
                r =   aCommand.ExecuteReader(System.Data.CommandBehavior.Default);
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return r;
        }
        public bool CheckTableExists(string strTableName)
        {
            bool tableExists = false;
            DataTable dt = sQLConnection.GetSchema("tables");
            foreach (DataRow row in dt.Rows)
            {
                string strTbleName = row["TABLE_NAME"].ToString();
                if (row["TABLE_NAME"].ToString() == strTableName)
                {
                    tableExists = true;
                    break;
                }
            }
            return tableExists;
        }
        public DataSet ReturnDataSet(string SQLSelectCommand)
        {
            SqlDataAdapter daGeneric = new SqlDataAdapter(SQLSelectCommand, this.sQLConnection);
            DataSet ds = new DataSet();
            daGeneric.Fill(ds);
            return ds;
        }


        public bool UpdateData(string OdbcCommand)
        {
            bool ret = false;
            try
            {
                aCommand.CommandType = CommandType.Text;
                aCommand.CommandText = OdbcCommand;
                int i = aCommand.ExecuteNonQuery();
                if (i > 0)
                    ret = true;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
                ret = false;
            }
            finally
            {
                //               conn.Close();
            }
            return ret;
        }
    }
}

score -1 · Answer 1 · edited Aug 02 '19 at 07:20

-1

First, command parameters are used when you want to use a stored procedure, for example, not for queries.

If you want to use procedures, you must create them like this:

CREATE PROCEDURE procedureName
-- Add the parameters for the stored procedure here
 @FirstName varchar(100),
 @LastName varchar(100)
AS
BEGIN
 SELECT * FROM Person.Person WHERE FirstName=@FirstName AND LastName=@LastName;
END

And then you can send the values:

SqlCommand command = new SqlCommand("procedureName", connection);
command.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 50)).Value = "Morgan";
command.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 50)).Value = "Zoe";

It'd very good if you could show us the full code where you are trying to execute the query.

edited Aug 02 '19 at 07:20

madreflection

4,744
3
19
29

answered Aug 01 '19 at 23:02

Lvcios

49
1
6

Patently FALSE. You can *and should* use parameters for *all* queries, stored procedures and text, unless you want to be vulnerable to SQL injection. – madreflection Aug 01 '19 at 23:09
Why would i use a stored procedure rather than a parameterized query? When running an update query that is parameterized it works great, when i run the above on trying to fill a dataset it does not – Bill Aug 02 '19 at 20:32
Sorry, It wasn't my intention suggest to use a stored procedure. – Lvcios Aug 02 '19 at 20:53

Error Passing parameters to Data Access Layer

1 Answers1