none
Error in dynamic sql query RRS feed

  • Question

  • Hi All,

    I want to use a dynamic query in one of my win service. Here is the way,

            public DataSet GetValidServerGroups(DateTime time)
            {
                string ss = "SELECT Id, Ip FROM dbo.tblStart " +
                                    "WHERE (dbo.tblStart.Status = 1 OR dbo.tblStart.Status = 0) AND " +
                                    "(dbo.tblStart.ProcessOn BETWEEN \'" + time + "\' AND \'" + time.AddSeconds(35) + "\')";
    
                try
                {
                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.CommandText = ss;
    
                    sqlAdapter.SelectCommand = sqlCommand;
                    DataSet ds = new DataSet();
                    sqlAdapter.Fill(ds);
    
                    return ds;
                }
                catch(Exception ex)
                {
                    EventLog.WriteEntry("RFAutomateWinService", "GetValidServerGroups() " + ex.Message + " " + ss);
                }
                return null;
            }
    When I run the above code, i got the following error.

    GetValidServerGroups() The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. SELECT GroupId, InternalIp FROM dbo.tblServersToStart WHERE (dbo.tblServersToStart.Status = 1 OR dbo.tblServersToStart.Status = 0) AND (dbo.tblServersToStart.ProcessOn BETWEEN '6/29/2009 9:17:44 AM' AND '6/29/2009 9:17:49 AM')

    But when I run the same query on the sql query window it works fine.

    Can someone help me to solve this problem.

    Thanks a lot
    Monday, June 29, 2009 8:31 AM

All replies

  • It is, actually, a good idea not to use dynamic SQL at all. It is a straight way to the SQL injection attack and those conversion issues. Best way is to use parameterized query and provider will handle formatting for all the types automatically. In this case you do not need to convert anything and just assign value to the parameter as is. Here is the link with some samples

    http://support.microsoft.com/kb/310070
    Val Mazur (MVP) http://www.xporttools.net
    Monday, June 29, 2009 10:18 AM
    Moderator