Click here to Skip to main content
15,886,518 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Sir,
I am getting this error while taking backup of sql database in below code that
Incorrect syntax error near 'D:' Unclosed quotation mark after the character string "

Code:-
VB
Sub blank()
        Dim cmd As System.Data.SqlClient.SqlCommand
        Dim dr As System.Data.SqlClient.SqlDataReader
        Dim cn As System.Data.SqlClient.SqlConnection
        Dim bkp_path As String
        Try
            cn = New System.Data.SqlClient.SqlConnection(DBset())
            cn.Open()
            cmd = New System.Data.SqlClient.SqlCommand("Select [BackupPath] from Owner", cn)
            dr = cmd.ExecuteReader
            While dr.Read
                bkp_path = dr(0)
            End While
        Catch ex As Exception
        End Try
        dr.Close()
        cn.Close()
        Try
            Dim t As String
            Dim d As String
            Dim m As String
            Dim y As String
            t = Date.Now.Minute.ToString + Date.Now.Hour.ToString
            d = Date.Now.Day.ToString
            m = Date.Now.Month.ToString
            y = Date.Now.Year.ToString
            Dim uniqueid As String
            uniqueid = t & d & m & y
            If cmbserver.Text = "" Or cmbdatabase.Text = "" Then
                MsgBox("Server & Database Name Should Not Blank")
                Exit Sub
            Else
                Timer1.Enabled = True
                ProgressBar1.Visible = True
                cmd = New SqlCommand("backup database " & cmbdatabase.Text.Trim & " to disk=" & bkp_path & "" & cmbdatabase.Text.Trim & "_" & uniqueid & ".bak'", con)
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
End Sub


Please help me how to solve this.
Posted
Updated 21-Aug-15 18:26pm
v2

Google for "Sql Injection Attack" to find out why building a SQL statement using string concatenation is so bad.

Next, Google for "vb.net Sql parameterized queries" to find out how to mitigate the SQL Injection Attack problem AND fix the problem you're having in your code.
 
Share this answer
 
Forget unclosed quotation mark, you will easily fix this problem if you re-write your code in accurate way.

Your approach is wrong from the very beginning. The query composed by concatenation with strings taken from UI. Not only repeated string concatenation is inefficient (because strings are immutable; do I have to explain why it makes repeated concatenation bad?), but there is way more important issue: it opens the doors to a well-known exploit called SQL injection.

This is how it works: http://xkcd.com/327.

Are you getting the idea? The string taken from a control can be anything, including… a fragment of SQL code.

What to do? Just read about this problem and the main remedy: parametrized statements: http://en.wikipedia.org/wiki/SQL_injection.

With ADO.NET, use this: http://msdn.microsoft.com/en-us/library/ff648339.aspx.

Please see my past answers for some more detail:
EROR IN UPATE in com.ExecuteNonQuery();,
hi name is not displaying in name?.

—SA
 
Share this answer
 

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