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
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.