CODEWITHSUNDEEP
sqlvb.netnewline
angel
angel

Reputation: 4642

How to send a newline from vb.net to sql?

on c# you could to have your variable

string sql =@"create table #order (oldcolumn int)  IF OBJECT_ID('tempdb..#order') IS NOT NULL DROP TABLE #order
go
create table #order (newcolumn int)
select newcolum from #order

"

and when this is execute this work

now on vb.net I do

dim sql as string

sql=" create table #order (oldcolumn int) IF OBJECT_ID('tempdb..#order') IS NOT NULL DROP TABLE #order " & _
    " go " & _
    " create table #order (newcolumn int) " & _
    " select newcolum from #order "

but it doesnt work because go must to have on a new line and the before code return all the script on a large line..

I have try witn vbCrLf, Environment.NewLine,"\r\n" but this continue failing.

the real query is similar to before example.. this fail because the column "newcolumn" not exist (yet) on table and I am doing the select to this table.. if you execute the drop table first it does work

Upvotes: 0

Views: 3342

Answers (2)

miroxlav
miroxlav

Reputation: 12204

Is it possible for you to upgrade to Visual Studio 2015?
Visual Basic 14 has the same multi-line literals as C# @"strings":

Dim sql As String = "
    SELECT t1.Name, 
           t1.Description, 
           t1.Address, 
           t1.PhoneNumber, 
           t2.RegistrationID, 
           t2.Date, 
           t2.Description, 
           t2.RegistrationStatus 
    FROM   Users t1 
           JOIN Registrations t2 ON t1.UserID = t2.UserID 
    WHERE   t2.RegistrationID = @RegistrationID
"

The only thing you need to escape inside the string is " (replace it with "").

And great new string interpolation feature helps you make your strings readable like never before:

Dim tableName As String = "Registrations"
Dim currentOrderByColumn As String = "t2.Date"

Dim sql = $"SELECT t1.Name, t1.Description FROM {tableName} ORDER BY {currentOrderByColumn}"

Dim sql2 = $"
    SELECT t1.Name, t1.Description
    FROM {tableName}
    ORDER BY {currentOrderByColumn}
"

Expressions inside interpolated strings also fully support variable renaming, so renaming tableName to mainTableName will also perform renaming inside the string.

More information: 1, 2

If you cannot upgrade, then use

Dim sql = <sql>SELECT t1.Name, 
           t1.Description, 
           t1.Address, 
           t1.PhoneNumber, 
           t2.RegistrationID, 
           t2.Date, 
           t2.Description, 
           t2.RegistrationStatus 
    FROM   Users t1 
           JOIN Registrations t2 ON t1.UserID = t2.UserID 
    WHERE   t2.RegistrationID = @RegistrationID</sql>.Value

but I do not recommend this (I recommend upgrading the VS) because this workaround has now became obsolete and has its caveats. (Think about escaping <, & and others. If you forget...)

Another workaround is:

Dim sql = String.Join(vbCrLf,
                      "SELECT ...",
                      "FROM ...",
                      "WHERE ...")

Upvotes: 0

S. Adam Nissley
S. Adam Nissley

Reputation: 776

The white space is optional in SQL. There is no difference at all between: 

SELECT * FROM Table1
GO
SELECT * FROM Table2

and

SELECT * FROM Table1 GO SELECT * FROM Table2

I would suggest looking into the following items in your SQL:

  1. The spelling of newcolum and newcolumn do not match in your CREATE and SELECT statements.
  2. The very first thing you are trying to do is CREATE TABLE #order, but it may already exist in the database left from the last time you created it in your CREATE TABLE #order (newcolumn int) statement.

Upvotes: 1

Related Questions