• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • Last Modified:

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

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
Murray Brown
Asked:
Murray Brown
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
ArkCommented:
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
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now