none
Execute CREATE TABLE from SQL script File in VB.net RRS feed

  • Question

  • Dear Experts,

    I'm learning to improvise my coding knowledge in VB.NET and SQL Server. 

    I have written code to execute SQL queries from file. The sql script is tested and also the code for reading the file is working and also it goes to sub Runscript function but the table does not get created on DBCmd.ExecuteNonQuery().

    Private Sub btnOK_Click(sender As Object, e As EventArgs) Handles btnOK.Click
    
                    Dim dbCreateScriptPath As String = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "DBCreate.sql")
                    MessageBox.Show("Trying to access DML script at: {dbCreateScriptPath}.")
    
                    If Not File.Exists(dbCreateScriptPath) Then
                        MessageBox.Show("The DML script {dbCreateScriptPath} does not exist.")
                        Return
                    End If
    
                    Dim script As String = File.ReadAllText(dbCreateScriptPath)
                    MessageBox.Show(script)
                    If String.IsNullOrEmpty(script) Then
                        MessageBox.Show("Failed to load contents of the DML script:" & script)
                        Return
                    End If
                    MessageBox.Show("Successfully loaded contents of the DML script:" & script)
    
                    SQL.RunScript(ConnectionString, script)
                    MessageBox.Show("Successfully executed the DML script:" & script)
    
    End Sub


     The sql script is as below:

    Public Class SQLControl
        Private DBCon As New SqlConnection()
        Private DBCmd As SqlCommand
        'db data
        Public DBDA As SqlDataAdapter
        Public DBDT As DataTable
    
    Public Sub RunScript(connstr As String, script As String)
            Try
                MessageBox.Show("Attempting to run script on SQL Server.")
                MessageBox.Show(script)
                DBCon = New SqlConnection(connstr)
                DBCon.Open()
                DBCmd = New SqlCommand(script, DBCon)
                DBCmd.ExecuteNonQuery()
                MessageBox.Show("Successfully run script on SQL Server.")
            Catch ex As Exception
                Exception = "ExecQuery Error:" & vbNewLine & ex.Message
            Finally
                'Close connection
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
            End Try
        End Sub
    
    End Class

    The sql scrpt file is: 

    USE [ABCMain]
    GO
    
    CREATE TABLE Departments (
       Code INTEGER PRIMARY KEY NOT NULL,
       Name TEXT NOT NULL ,
       Budget REAL NOT NULL 
     );

    Please help me find what is the mistake I have done.

    Regards,
    NewBInVB


    NewBInVB



    Tuesday, September 5, 2017 5:27 PM

Answers

All replies

  • Hello NewBInVB

    command.ExecuteNonQuery() returns the affected rows when inserting , updating or deleting.

    You're doing a create statement it doens't need to return anything

    See the official documentation of ExecuteNonQuery()

    https://msdn.microsoft.com/en-us/library/system.data.idbcommand.executenonquery(v=vs.110).aspx

    Hope it helps you

    Friendly regards



    • Edited by laurens vdb Tuesday, September 5, 2017 5:42 PM
    Tuesday, September 5, 2017 5:41 PM
  • I understand that the ExecuteNonQuery is used only for update,insert and delete opeartion which returns the number of affected rows. 

    How can I execute for create statement using sql script? 

    NewBInVB

    Tuesday, September 5, 2017 5:54 PM
  • I have replaced DBCmd.ExecuteReader() and it works. 

    NewBInVB

    • Marked as answer by NewBInCoding Tuesday, September 5, 2017 6:19 PM
    Tuesday, September 5, 2017 6:19 PM
  • Hello,

    Good that it's working yet I'm puzzled that you failed with ExecuteNonQuery as that works for me e.g.

    Public Function CreateTable() As Boolean
        Dim fileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "create.txt")
        Try
            Using cn As New SqlConnection With {.ConnectionString = "Data Source=KARENS-PC;Integrated Security=True"}
                Using cmd As New SqlCommand With {.Connection = cn, .CommandText = IO.File.ReadAllText(fileName)}
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            End Using
        Catch ex As Exception
            Return False
        End Try
    End Function

    And note the changes for the create, no GO

    USE [ABCMain]
    
    CREATE TABLE Departments (
       Code INTEGER PRIMARY KEY NOT NULL,
       Name TEXT NOT NULL ,
       Budget REAL NOT NULL 
     );


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 5, 2017 11:13 PM
    Moderator