Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB.net SQL Store SQL statement in nvarchar(max) field

Posted on 2014-09-24
3
Medium Priority
?
582 Views
Last Modified: 2014-09-25
Hi

I have a VB.net app that stores SQL statements in a SQL database nvarchar(MAX) field
How do I convert the enterire SQL statement so that it can be stored in my SQL database via
the  insert statement shown below

        Dim cn As New OleDbConnection(cs)

        Dim oReportName As String = Me.txtReportName.Text
        Dim oSQL As String = Me.lblALL_SQL.Text
        Dim oUserName As String = ""
        Dim oPassword As String = ""
        Dim oConnectionString As String = ""

        Dim sSQL As String
        sSQL = "INSERT INTO Users ( [Report_Name], [SQL], [UserName], [Password], [Connection_String] )"
        sSQL = sSQL & " SELECT "
        sSQL = sSQL & "'" & oReportName & "' AS Expr1,"
        sSQL = sSQL & "'" & SQLConvert(oSQL) & "' AS Expr2"
        sSQL = sSQL & "'" & oUserName & "' AS Expr3"
        sSQL = sSQL & "'" & oPassword & "' AS Expr4"
        sSQL = sSQL & "'" & oConnectionString & "' AS Expr5"


        '// define the sql statement to execute
        Dim cmd As New OleDbCommand(sSQL, cn)

        Try

            cn.Open()
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            Me.lblError2.Text = ex.Message
        Finally
            If cn.State <> ConnectionState.Closed Then
                cn.Close()
            End If
        End Try
0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 40341312
what error do you get?
anyhow, you should not use such dynamic sql, but is OleDbParameters to avoid using SQLConvert (or forgetting to do so)
for example, see here:http://msdn.microsoft.com/en-us/library/50xtbfet%28v=vs.110%29.aspx
code will be much shorter, easier to read and no hassle with special characters for your SQL
0
 
LVL 28

Expert Comment

by:Ark
ID: 40345344
Since all your values are strings:
Dim sSQL As String
sSQL = "INSERT INTO Users ( [Report_Name], [SQL], [UserName], [Password], [Connection_String] )"
sSQL += " VALUES ('"
sSQL += oReportName & "','"
sSQL += oSQL & "','"
sSQL += oUserName & "','"
sSQL += oPassword & "','"
sSQL += oConnectionString & "')"

Open in new window

Values are in brackets, separated with commas and each value is inside single quotes (telling SQL that they are strings)
0
 

Author Closing Comment

by:Murray Brown
ID: 40345387
Thanks
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question