Recognize SQL Queries from TextBox and Execute Separately

xpert_ali
xpert_ali used Ask the Experts™
on
Hi

I am doing a project where user enters SQL Queries like in SQL Management Editor . Many Individual queries ..

Here even i am doing the same . If user writes 2 or more queries  in a text box they all needs to be recognized separately Like individual queries and needs be executed separately.

Regards
Avin
Query.bmp
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HI, try this on the button event:

Dim conn As SqlConnection
        Dim cmd As New SqlCommand
        Dim dr As SqlDataReader
        Dim i, j As Integer
        Dim qry(10) As String ' Say max 10 queries
        Dim str1 As String
        str1 = Me.TextBox3.Text
        i=0
        While Len(str1) > 5
            qry(i) = Mid$(UCase(str1), 1, InStr(str1, Chr(13)))
            str1 = Mid$(str1, InStr(str1, Chr(13)) + 1, Len(str1))
            i = i + 1
        End While
        conn = New SqlConnection(ConfigurationManager.ConnectionStrings("YourConnectionString").ConnectionString)
        For j = 0 To 9
            cmd.CommandText = qry(j)
            dr = cmd.ExecuteReader
            While dr.Read
                Response.Write("Executing query")
            End While
            dr.close

        Next
        cmd.close
        conn.Close()

Havent tested it, but guess it should work properly.

Author

Commented:
Hi

See the text box might contain 1st query which might have length of 15 lines and 2nd with 2 lines and 3rd with 1 line .

But while executing currently i am using directly textbox, that is wrong . I want to know how can i detect 3 queries written and differentiate them . Executing part already i have done with that.

Now only i need to find how many individual queries are there for which the output needs to be shown separately .

Regards
Avin
The solution which I have posted works if Enter is pressed after each query and doesnt matter if its single line or 15 lines.
Else you may ask the users to seperate the queries by semicolon and modify the above code accordingly.

Author

Commented:
Hi

Sorry Have Checked the code . It wont return any query in the array, Just it will be filled with space for every index and Even throws Array out of index exception after completing 9th while condition .

Regards
Avin
IT Engineer
Distinguished Expert 2017
Commented:
For me the best solution is to determine a statement terminator.
Like use semicolon (;) in the end of each query or even the keyword GO as terminator. Without these kind of rules it will be very hard to do what you want.

Good luck

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial