Pass a parameter query to a DAL and get a dataset returned?

Question

0.00/5 (No votes)

See more:

the following is the code behind the aspx form where i have been trying to use a DAL to get a dataset back.

C#

protected void Page_Load(object sender, EventArgs e)
        {
            data.strConnStr = strConnStr;
            data.openDB();

            strCmd = "SELECT * FROM tblMembers WHERE memNo = @memNo";
            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlCommand cmd = new SqlCommand(strCmd);


            SqlParameter param1 = new SqlParameter();
            param1.ParameterName = "@memNo";
            param1.Value = 63;

            cmd.Parameters.Add(param1);
        
            ds = data.ReturnDataSet(strCmd);
            BindingSource bSource = new BindingSource();
            bSource.DataSource = ds.Tables[0];

The DAL code is as follows

C#

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;
        }
        
    }
}

What I have tried:

I have tried for about a week to get his to work but continually get

System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@memNo".'

at the

public DataSet ReturnDataSet(string SQLSelectCommand)

at

daGeneric.Fill(ds);

Some help with this for a fairly newbie at this would really help

Posted 16-Aug-19 13:21pm

Member 14560926

Updated 16-Aug-19 18:46pm

Patrice T

v3

Add a Solution

Comments

Richard Deeming 22-Aug-19 10:37am

NB: You've said this is an ASPX form, which means it's ASP.NET, but you've used MessageBox.Show in your exception handlers.

Your code is running on the server. At best, it will crash with an exception telling you that you can't display a message box from non-interactive code. At worst, it will display the message box on the server, where nobody will ever see it, and your code will then hang waiting for an administrator to log in to the server and acknowledge thousands of message boxes.

It might appear to work when you debug your code in Visual Studio. But that's only because, in that specific situation, the server and the client are the same computer. Once you deploy it to a real server, horrible things will happen.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2019-08-16T14:55:00

Solution 1

The problem you have looks to be that while you are building the full SQL Command, you are only passing the command text to the DAL, not the actual command object

Normally what I do is to keep the DAL a little more separated from it's users, and pass in the query, parameter name, and value. If there are multiple values I will pass a list of key-value pairs (string, object).

C#

public DataSet ReturnDataSet(string SQLSelectCommand, string ParamName="", object ParamValue)
{
	SqlCommand cmd = new SqlCommand(SQLSelectCommand, this.sQLConnection);
	if (ParamName != "") 
	{
		cmd.Parameters.AddWithValue(ParamName, ParamValue);
	}	

	SqlDataAdapter daGeneric = new SqlDataAdapter(cmd);
	
	// continue on with your code
}

Posted 16-Aug-19 14:55pm

MadMyche

Comments

Member 14560926 16-Aug-19 22:54pm

Thanks for that but I am still getting an error, "optional parameter must appear after all required parameters" with the bracket att he end of the first line..."public DataSet ReturnDataSet(string SQLSelectCommand, string ParamName="", object ParamValue)" There is only that one parameter.

MadMyche 16-Aug-19 23:11pm

Oops... that should be object ParamValue = null

Member 14560926 17-Aug-19 0:20am

BINGO, THANKS VERY MUCH Allgood with one parameter but I suspect I can submit a list one way or another, I had to cALL
"ds = data.ReturnDataSet(strCmd,param1.ParameterName,param1.Value); " to get it working, not to sure on that but now I have this working, Great....

MadMyche 17-Aug-19 7:22am

Actually you could have passed the name/value directly in- you don't need most of the overhead in the caller

protected void Page_Load(object sender, EventArgs e)
{
	data.strConnStr = strConnStr;
	data.openDB();

	strCmd = "SELECT * FROM tblMembers WHERE memNo = @memNo";
	ds = data.ReturnDataSet(strCmd, "@memNo", 63);
	BindingSource bSource = new BindingSource();
	bSource.DataSource = ds.Tables[0];
}

Salman622 · Accepted Answer · 2019-08-16T18:46:00

DO as per below suggesstion:

At these below line you are passing a string variable where @memno is not define

ds = data.ReturnDataSet(strCmd);

change the above line to:

ds = data.ReturnDataSet(cmd);

and then change parameter type for

ReturnDataSet(string SQLSelectCommand)

as specified below

public DataSet ReturnDataSet(SqlCommand SQLSelectCommand)