Conversion failed when converting the varchar value '..' to data type int

Question

The problem is in variable hour when I try use in the CmdString is throwing an exception. I don't understand why is throwing the exception when variable hour is string. How can I solve this problem. What I'm missing here.

string ConString = ConfigurationManager.ConnectionStrings["DbSystem"].ConnectionString;
string CmdString = string.Empty;

DateTime time = DateTime.Now;
string hour = time.Hour.ToString();

using (SqlConnection con = new SqlConnection(ConString))
{
    string folderName = DateTime.Now.Day.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Year.ToString();
    string folder = Path.Combine(FOLDER_FOR_BACKUP_FILES);
    Directory.CreateDirectory(FOLDER_FOR_BACKUP_FILES);

    CmdString = @"declare @fileName varchar(100); declare @dbName varchar(100); declare @fileDate varchar(20); set @fileName = '" + folder + @"\'; set @dbName = 'DbSystem'; set @fileDate = convert(varchar(20), getdate(), 105); set @fileName = @fileName + @dbName + ' ' + @fileDate + '-' + " + hour + @" + '.bak'; backup database @dbName to disk = @fileName;";

    SqlCommand cmd = new SqlCommand(CmdString, con);

    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable("*");
    sda.Fill(dt);
}

you should use a parameterized query instead of building your command string like this due to SQL Injection! look at the answers here : http://stackoverflow.com/questions/7505808/using-parameters-in-sql-statements — CeOnSql, Oct 16 '15 at 06:27

score 2 · Accepted Answer · answered Oct 16 '15 at 06:28

You should CONVERT your hour to VARCHAR:

@fileDate + '-' + CONVERT(VARCHAR(20), " + hour + @") + '.bak';

Though hour is a string in the application side, when it's executed by the SQL Server, it's an INT. So you need to implicitly include a CONVERT function.

score 1 · Answer 2 · answered Oct 16 '15 at 06:38

Because hour is string now. You can change CmdString to

CmdString = @"declare @fileName varchar(100); declare @dbName varchar(100); declare @fileDate varchar(20); set @fileName = '" + folder + @"\'; set @dbName = 'DbSystem'; set @fileDate = convert(varchar(20), getdate(), 105); set @fileName = @fileName + @dbName + ' ' + @fileDate + '-' + '" + hour + @"' + '.bak'; backup database @dbName to disk = @fileName;";

Conversion failed when converting the varchar value '..' to data type int

2 Answers2