C# select SQL query with parameter

Question

I would like to migrate database from MS Access to SQL Server and I'm trying to modify my SQL queries. In MS Access they worked.

I have this C# code:

private DataSet GetIncident_ByIncident(string inc_num)
{
    MainIncident mi = new MainIncident();
    mi.incident_full_number = inc_num;

    string query = @"SELECT MainIncidentTable.Incident_Full_Num ,
            MainIncidentTable.Customer_Name ,
            MainIncidentTable.Service_Representative ,
            MainIncidentTable.Incident_Date ,
            MainIncidentTable.Average_Invoice ,
            MainIncidentTable.Street ,
            MainIncidentTable.City ,
            MainIncidentTable.Contact ,
            IncidentStatuses.Incident_Status_Name,
            Stations.Station_Name 

            FROM(MainIncidentTable INNER JOIN
                 Stations ON MainIncidentTable.Current_Station_ID = Stations.Station_ID)
                 INNER JOIN IncidentStatuses ON MainIncidentTable.Current_Incident_Status_Id = IncidentStatuses.Incident_Status_Id
            WHERE Incident_Full_Num = '@Incident_Full_Num'";

    List<OleDbParameter> l = new List<OleDbParameter>();

    l.Add(new OleDbParameter("@Incident_Full_Num", mi.incident_full_number));
    l.Add(new OleDbParameter("@Customer_Name", mi.customer_name));
    l.Add(new OleDbParameter("@Service_Representative", mi.service_representative));
    l.Add(new OleDbParameter("@Incident_Date", mi.incident_date));
    l.Add(new OleDbParameter("@Average_Invoice", mi.average_invoice));
    l.Add(new OleDbParameter("@Street", mi.street));
    l.Add(new OleDbParameter("@City", mi.city));
    l.Add(new OleDbParameter("@Contact", mi.contact));
    l.Add(new OleDbParameter("@Current_Station_ID", mi.current_station_id));
    l.Add(new OleDbParameter("@Current_Incident_Status_Id", mi.current_incident_status_id));

    var con = GetDataSetWithParameters(query, l);

    return con;
}

Connection method :

public static DataSet GetDataSetWithParameters(string query, List<OleDbParameter> parameters)
{
    DataSet ds = new DataSet();
       
    OleDbConnection Con = new OleDbConnection(sqlConnectionString);
    Con.Open();

    try
    {
        using (OleDbCommand cmd = new OleDbCommand(query, Con))
        {
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters.ToArray());
            }

            using (OleDbDataAdapter Adapter = new OleDbDataAdapter(cmd))
            {
                Adapter.Fill(ds);
            }

            return ds;
        }
    }
    catch
    {
        throw;
    }
    finally
    {
        CloseConnection(ref Con);
    }
}

SQL query is working but not returning rows. I guess that problem is a syntax of parameter in query.

Can someone direct me?

Strange that you have so many unused parameters. You should use SqlConnection and SqlCommand rather than Ole... And use using blocks rather than try\finally — Charlieface
– Charlieface, Commented Oct 4, 2021 at 9:57

derpirscher · Accepted Answer · 2021-10-04 12:28:50Z

4

You must not enclose your parameters in quotes. The library will take care of that. Ie use

WHERE Incident_Full_Num = @Incident_Full_Num

instead of

WHERE Incident_Full_Num = '@Incident_Full_Num'

EDIT

Oh sorry, forgot, that OleDb doesn't use named parameters but only positional parameters. See docs

So either update your query to

WHERE Incident_Full_Num = ?

or you use SqlCommand and SqlParameter instead of OleDbCommand and OleDbParameter.

Be aware, that if you use OleDbCommand the order in which you pass the parameters is important. Ie, the first ? will be bound to the first parameter, the second ? to the second parameter and so on. If you need the same parameter in your query twice, you would also need to pass it twice.

Furthermore it seems, only the first parameter is used. Why are you passing numerous unused parameters to your command?

edited Oct 4, 2021 at 12:28

answered Oct 4, 2021 at 9:29

derpirscher

17.7k3 gold badges25 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yan Over a year ago

In this manner I get exception : Must declare the scalar variable "@Incident_Full_Num"

Collectives™ on Stack Overflow

C# select SQL query with parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related