linebreaks not working in multiline string (1 Viewer)

Glimmerman

New member
Local time
Today, 19:08
Joined
Oct 9, 2012
Messages
9
Hi im trying to make pass through query that triggers a database restore on a SQL server based on a few parameters entered in a form.
As soon as i run the command button the output just put all lines as one line without line breaks, can anyone help me on whats going wrong here?

Here is my code:

Code:
Private Sub cmdRestore_Click()
On Error GoTo ErrHandler

Dim sSQL As String
Dim sSQL_Local As String
Dim rs As ADODB.Recordset
Dim rs_Local As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sCriteria As String

If chkDIFF.Value = False Then GoTo Full
If chkDIFF.Value = True Then GoTo FullDiff

Full:
sSQL = "RESTORE DATABASE [" & txtEngID.Value & "]" & _
"FROM DISK='H:\" & txtBAK.Value & ".bak'" & _
"With RECOVERY" & _
"GO"
txtSQLStatement.Value = sSQL
GoTo Restore

FullDiff:
sSQL = "RESTORE DATABASE [" & txtEngID.Value & "]" & _
"FROM DISK='H:\" & txtBAK.Value & ".bak'" & _
"With Move 'Audit_log' to 'F:\MSSQL10.INSTANCENAME\MSSQL\DATA\" & txtBAK.Value & "_log.ldf'," & _
"NORECOVERY" & _
"GO" & _
"" & _
"RESTORE DATABASE [" & txtEngID.Value & "]" & _
"FROM DISK='H:\" & txtDIFF.Value & ".diff'" & _
"With RECOVERY" & _
"GO"
txtSQLStatement.Value = sSQL
GoTo Restore

Restore:
Set cn = New ADODB.Connection
cn.Open "ODBC;Description=Local SQL Server;DRIVER=SQL Server;SERVER=" & cmdServername.Value & "\INSTANCENAME;Trusted_Connection=Yes;"
 
Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
sSQL_Local = txtSQLStatement.Value
cn.Close

MsgBox "The database has been restored succesfully"

Exit_ErrHandler:
    Exit Sub

ErrHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_ErrHandler
End Sub
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 18:08
Joined
Feb 19, 2013
Messages
16,553
looks like you are missing spaces for example

"FROM DISK='H:\" & txtBAK.Value & ".bak'" & _
"With Move....

I would think should be
"FROM DISK='H:\" & txtBAK.Value & ".bak'" & _
" With Move...
 

nanscombe

Registered User.
Local time
Today, 18:08
Joined
Nov 12, 2011
Messages
1,082
To use multiple SQL commands on one line you need to terminate each command, except for the last one, with a semicolon.

Code:
SQL command 1; SQL command 2; SQL command 3

Batches of SQL Statements
Explicit Batches An explicit batch is two or more SQL statements separated by semicolons (;). For example, the following batch of SQL statements opens a new sales order. This requires inserting rows into both the Orders and Lines tables. Note that there is no semicolon after the last statement.

To take one of your options as an example:

Code:
sSQL = "RESTORE DATABASE [" & txtEngID.Value & "]" & _
"FROM DISK='H:\" & txtBAK.Value & ".bak'" & _
"With RECOVERY" & _
"GO"

Would produce the output

Code:
RESTORE DATABASE [txtEngID] FROM DISK='H:\txtBAK.Value.bak'With RECOVERYGO

What you probably want to do is add a semicolon after the first command, and appropriate spaces, so it becomes ...

Code:
sSQL = "RESTORE DATABASE [" & txtEngID.Value & "]" & _
"FROM DISK='H:\" & txtBAK.Value & ".bak'" & _
" With RECOVERY[COLOR="Red"];[/COLOR]" & _
" GO"

Which will output the following

Code:
RESTORE DATABASE [txtEngID] FROM DISK='H:\txtBAK.Value.bak' With RECOVERY[COLOR="red"];[/COLOR] GO

The semicolon will terminate the SQL command.
 
Last edited:

nanscombe

Registered User.
Local time
Today, 18:08
Joined
Nov 12, 2011
Messages
1,082
As an aside the "&_" construct is used to continuation of a single command line over several lines, it does not insert line breaks.
 

Users who are viewing this thread

Top Bottom