sql query result from executenonquery to text file vb.net

Question

Please see the following Code:

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO


Public Class frm_main

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim sqlFile As String = "ExecOnServer.sql"
        Dim sqlText = File.ReadAllText(sqlFile)
        Dim connStr = "Server=MyServer; Database=MyServer; Integrated Security=SSPI"
        Dim conn = New SqlConnection(connStr)
        Dim str_result_int As Integer
        Dim str_result As String
        Dim sqlCmd = New SqlCommand(sqlText, conn)

        Try
            conn.Open()
            File.AppendAllText("ExecOnServer.txt", vbCrLf & DateTime.Now.ToString("HH:mm dddd, dd MMMM yyyy") & "...Opening Connection..." & vbCrLf)
            str_result_int = sqlCmd.ExecuteNonQuery()
            If str_result_int >= 1 Then
                str_result = "Executed Successfully!!"
            Else
                str_result = "Executed But No Rows were Affected!!"
            End If
            File.AppendAllText("ExecOnServer.txt", DateTime.Now.ToString("HH:mm dddd, dd MMMM yyyy") & "...Executing Query..." & vbCrLf)
            File.AppendAllText("ExecOnServer.txt", DateTime.Now.ToString("HH:mm dddd, dd MMMM yyyy") & " " & str_result & vbCrLf)
        Catch ex As Exception
            'MsgBox("Something went wrong: " & ex.Message)
            File.AppendAllText("ExecOnServer.txt", DateTime.Now.ToString("HH:mm dddd, dd MMMM yyyy") & "...Something went wrong: " & ex.Message & vbCrLf)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
                File.AppendAllText("ExecOnServer.txt", DateTime.Now.ToString("HH:mm dddd, dd MMMM yyyy") & "...Done!" & vbCrLf)
                'MsgBox("Done")
            End If
            Me.Close()
        End Try
    End Sub
End Class

This is working perfectly fine!! The issue is that the output file (ExecOnServer.txt) that is being appended needs to include the Messages of the query executed in ExecOnServer.sql. By Messages, I mean "(1 row(s) affected)" or "Msg 208, Level 16, State 1, Line 3 Invalid object name 'User_Tax_Rates'.". The variable "str_result" can be used or a new variable can be created.

Please help.

score 0 · Accepted Answer · answered Jan 19 '15 at 11:06

you have the numbers of affected rows

        If str_result_int >= 1 Then
            str_result = "Executed Successfully!!"
            MessageBox(str_result_int & " row(s) affected")
        Else

and the error message you can cahtch from ex.Message .. or put a brekpoint and see what property has that message in ex.

sql query result from executenonquery to text file vb.net

1 Answers1