Click here to Skip to main content
15,891,431 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have a form which will backup my db when the backup button is clicked. the backup
location should be taking from txtbackuppath. the name of the backup should be
SMS + the current date. i tried some this but i get the error Incorrect syntax near '.'.
pls help me out

C#
private void btnBrowes_Click(object sender, EventArgs e)
        {
            // open folder dialog
            FolderBrowserDialog fbd = new FolderBrowserDialog();
            fbd.ShowDialog();

            this.txtBackuppath.Text = fbd.SelectedPath.ToString();
        }

        private void btnBackup_Click(object sender, EventArgs e)
        {
           //Connect to DB 
            SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            conn.ConnectionString = "Data Source=MICKY-PC;Initial Catalog=SMS;User ID=sa;Password=mike";
                                     
            string backuppath;
            backuppath = this.txtBackuppath.ToString();

            SqlCommand cmd = new SqlCommand();
            string sqlQuery = null;
            sqlQuery = "backup database SMS to disk = ''" + backuppath + "' + 'SMS' + DateTime.Now.ToShortDateString()'";

            cmd.Connection = conn;
            cmd.CommandText = sqlQuery;
            cmd.CommandType = System.Data.CommandType.Text;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
                MessageBox.Show("The backup was successfully performed");
        }
Posted
Updated 20-Jul-12 8:42am
v2
Comments
[no name] 20-Jul-12 14:42pm    
You have too many single quote characters in your string.
OriginalGriff 20-Jul-12 14:54pm    
...and a few to few double quotes! :laugh:
StianSandberg 20-Jul-12 15:53pm    
A little tip.Use the debugger and check the value of your sqlQuery variable. You will see what is wrong..
lewax00 20-Jul-12 16:12pm    
Except he's getting a syntax error, so he could never compile it to debug it... :P
StianSandberg 20-Jul-12 16:14pm    
ah.. :p

1 solution

Probably, it is your short date string - it contains "/" characters.
Instead of
C#
DateTime.Now.ToShortDateString();
Try
C#
DateTime.Now.ToString("yyyy-MM-dd");
Which will put it in ISO format.
You also want to remove some of the single quotes from your sql statement...
string sqlQuery = "backup database SMS to disk = ''" + backuppath + "' + 'SMS' + DateTime.Now.ToShortDateString()'";

generates:
backup database SMS to disk = ''D:\temp\' + 'SMS' + DateTime.Now.ToShortDateString()'

so you probably want:
string sqlQuery = "backup database SMS to disk = '" + backuppath + "SMS" + DateTime.Now.ToString("yyyy-MM-dd")+ "'";

which generates:
backup database SMS to disk = 'D:\temp\SMS2012-07-20'
 
Share this answer
 
Comments
mikeoabban 20-Jul-12 16:00pm    
thanks for your. but it still give the error message Incorrect syntax near 'emp'.
for sqlQuery = "backup database SMS to disk = ''D:\temp\' + 'SMS' + DateTime.Now.ToShortDateString()'";

and error Cannot open backup device 'C:\Users\MICKY\Desktop\sms\SMS7/20/2012'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.

for sqlQuery = "backup database SMS to disk = '" + backuppath + "\\SMS" + DateTime.Now.ToShortDateString()+ "'";

please am confused wat can i do
lewax00 20-Jul-12 16:14pm    
Try replacing backuppath with backuppath.Replace("\", "\\")
OriginalGriff 20-Jul-12 16:18pm    
Try replacing ToShortDateString as I suggested...
lewax00 20-Jul-12 16:48pm    
Oh wait, never mind, I see what you did, "D:\temp\" should be "D:\\temp\\", you need to escape the \ character. And what Griff said (the / character is not valid in Windows file names, so you really need to replace that with what he suggested).
mikeoabban 20-Jul-12 18:52pm    
thanks you all for your support it is working and this is it
sqlQuery = "backup database SMS to disk = '" + backuppath + "\\SMS" + DateTime.Now.ToString("yyyy-MM-dd") + ".bak" + "'";

am now working on the restore form

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900