C# select SQL query with parameter-CodePudding

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?

CodePudding user response：

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?