Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-09-24
3
Medium Priority
?
597 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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

972 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