Hynek Bernard Hynek Bernard - 4 months ago 34
SQL Question

Pass getdate() in dynamic query as parameter

Good morning everyone! I am building a dynamical query with parameters as seen in code below.

c.Open();
string columns = "(";
string values = "(";
foreach (DataGridViewColumn column in rootDataView.Columns)
{
if (virtualColumns.Contains(column.Name))
{
}
else
{
columns += column.Name + ",";
values += "@" + column.Name + " ,";
}
}
columns = columns.Substring(0, columns.Length - 1) + ")";
values = values.Substring(0, values.Length - 1) + ")";
SqlCommand command = new SqlCommand("insert into " + tableName + " " + columns + " Values " + values, c);
foreach (DataGridViewColumn column in rootDataView.Columns)
{
if (virtualColumns.Contains(column.Name))
{
}
else
{
if (column.Name != "timeFrom")
{
command.Parameters.AddWithValue("@" + column.Name, editedRow.Cells[column.Name].Value);
}
else
{
command.Parameters.AddWithValue("@" + column.Name, System.DateTime.Now);
}
}
}
command.ExecuteNonQuery();


But I have encountered a problem on this line

command.Parameters.AddWithValue("@" + column.Name, System.DateTime.Now);


I need to synchronize this time with SQL server, so I need to somehow pass
GETDATE()
as parameter, but when I enter it like this

command.Parameters.AddWithValue("@" + column.Name, "GetDate()");


it is being sent as string/varchar so it does not call function.

I would like to know if there is a way to pass function as parameter, or if I need to replace the
@timeFrom
part of query string.


EDIT:Please note the bold part, that is the question - I am asking for Yes/No and explanation why

Answer

You will have to try something like this:

            c.Open();
            string columns = "(";
            string values = "(";
            foreach (DataGridViewColumn column in rootDataView.Columns)
            {
                if (virtualColumns.Contains(column.Name))
                {
                }
                else
                {
                    columns += column.Name + ",";
                    if (column.Name != "timeFrom")
                    {
                        values += "@" + column.Name + " ,";
                    } else {
                        //change in string
                        values += "GETDATE() ,";
                    }
                }
            }
            columns = columns.Substring(0, columns.Length - 1) + ")";
            values = values.Substring(0, values.Length - 1) + ")";
            SqlCommand command = new SqlCommand("insert into " + tableName + " " + columns + " Values " + values, c);
            foreach (DataGridViewColumn column in rootDataView.Columns)
            {
                if (virtualColumns.Contains(column.Name))
                {
                }
                else
                {
                    if (column.Name != "timeFrom")
                    {
                        command.Parameters.AddWithValue("@" + column.Name, editedRow.Cells[column.Name].Value);
                    }
                }
            }
            command.ExecuteNonQuery();

Basically, you do not treat timeFrom as parameter but you use GETDATE() to set it's value in the INSERT.

SQL Server and the client library that you are using, do not support passing parameters containing SQL. That is the reason why this construct will not work:

command.Parameters.AddWithValue("@" + column.Name, "GetDate()");

The only thing that is allowed to pass in AddWithValue is something that will translate to a literal SQL value.

Comments