none
Need Help with SQL Connection (VB.net)

    Question

  • Hi All

    Hoping someone can help me, hopefully an easy fix!

    I'm very new to VB/Visual Studio, I'm basically teaching myself how - I've got a Windows Form I have built, very simple, just a First Name and Last Name field and I'm trying to work out how to get it to wrtie whatever value is entered into those two fields, into my SQL Database - where i have a table called "Users". I have imported the SQL database into the project so i'm sure thats all working ok - but i just cant seem to get it to execute! i keep getting the below error.

    I've sat and read/watched hours of youtube videos and guides on how to get this to work, but i just cant get past this. Please could someone provide some help? My thanks in advance!

    System.InvalidOperationException: 'ExecuteNonQuery: Connection property has not been initialized.'

    This happens on the line:

    command.ExecuteNonQuery()

    Here is the full code:

    Imports System.Data
    Imports System.Data.SqlClient
    
    Public Class SQTest
    
        Dim cn As New SqlConnection("Data Source=DESKTOP-94NTTM7\SQLEXPRESS01;Initial Catalog=HATS;Integrated Security=True")
    
    
        Private Sub SQTest_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
        End Sub
    
    
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim command As New SqlCommand("insert into Users(First Name,Last Name) value('" & FName.Text & "', '" & Lname.Text & "')")
    
            cn.Open()
    
            command.ExecuteNonQuery()
    
            cn.Close()
    
    
    
    
        End Sub
    
    
    End Class


    Saturday, January 12, 2019 2:47 PM

All replies

  • Hello,

    The following separates out creating the connection string which couples with a base exception class. I assume you have a primary auto-incrementing primary key (if you don't you really need to add one).

    Full source is downloadable here.

    Form code to run the insert operations, I used hard coded values but you can have them come from say two TextBox controls.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New DataOperations
            Dim newIdentifier = ops.InsertHat("Karen", "Payne")
            If ops.IsSuccessFul Then
                MessageBox.Show($"New record identifier is {newIdentifier}")
            Else
                MessageBox.Show($"Failed to insert record{Environment.NewLine}{ops.LastExceptionMessage}")
            End If
        End Sub
    End Class

    Add the following classes to your project.

    Imports System.Data.SqlClient
    Public Class BaseExceptionsHandler
        Protected mHasException As Boolean
        Public ReadOnly Property HasException() As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Protected mLastException As Exception
        Protected ReadOnly Property LastException() As Exception
            Get
                Return mLastException
            End Get
        End Property
        Public ReadOnly Property HasSqlException() As Boolean
            Get
                If LastException IsNot Nothing Then
                    Return TypeOf LastException Is SqlException
                Else
                    Return False
                End If
            End Get
        End Property
        Public ReadOnly Property LastExceptionMessage() As String
            Get
                Return LastException.Message
            End Get
        End Property
        Public ReadOnly Property IsSuccessFul() As Boolean
            Get
                Return Not HasException
            End Get
        End Property
    End Class

    .

    	Imports ExceptionsLibrary
    
    Public MustInherit Class BaseSqlServerConnection
    	    Inherits BaseExceptionsHandler
        ''' <summary>
        ''' This points to your database server
        ''' </summary>
        Protected DatabaseServer As String = "DESKTOP-94NTTM7\SQLEXPRESS01"
        ''' <summary>
        ''' Name of database containing required tables
        ''' </summary>
        Protected DefaultCatalog As String = "HATS"
        Public ReadOnly Property ConnectionString() As String
            Get
                Return $"Data Source={DatabaseServer};Initial Catalog={DefaultCatalog};Integrated Security=True"
            End Get
        End Property
    End Class

    Add this class which does the insert into your table. The first method is called in the form code, the second method is an example of inserting a new record w/o a primary key which is easy enough to use too.

    Imports System.Data.SqlClient
    Public Class DataOperations
        Inherits BaseSqlServerConnection
        ''' <summary>
        ''' Inserts new record, assumes there is a primary auto incrementing key where the following part gets the new id.
        ''' If you don't have a auto-incrementing primary key you need one so add it.
        ''' 
        ''' SELECT CAST(scope_identity() AS int);
        ''' 
        ''' </summary>
        ''' <param name="firstName"></param>
        ''' <param name="lastName"></param>
        ''' <returns></returns>
        Public Function InsertHat(firstName As String, lastName As String) As Integer
            mHasException = False
            Dim insertStatement As String =
            <SQL>
                    INSERT INTO Users 
                    (FirstName, LastName) 
                    VALUES (@FirstName,@LastName); 
                    SELECT CAST(scope_identity() AS int);
             </SQL>.Value
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = insertStatement
    
                    cmd.Parameters.AddWithValue("@FirstName", firstName)
                    cmd.Parameters.AddWithValue("@LastName", lastName)
    
                    cn.Open()
    
                    Try
    
                        Return CInt(cmd.ExecuteScalar)
    
                    Catch ex As Exception
                        mHasException = True
                        mLastException = ex
                        Return 0
                    End Try
                End Using
            End Using
        End Function
        Public Function InsertHatNoPrimaryKey(firstName As String, lastName As String) As Boolean
            mHasException = False
            Dim insertStatement As String =
                    <SQL>
                    INSERT INTO Users 
                    (FirstName, LastName) 
                    VALUES (@FirstName,@LastName); 
             </SQL>.Value
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = insertStatement
    
                    cmd.Parameters.AddWithValue("@FirstName", firstName)
                    cmd.Parameters.AddWithValue("@LastName", lastName)
    
                    cn.Open()
    
                    Try
    
                        cmd.ExecuteNonQuery()
    
                        Return True
    
                    Catch ex As Exception
                        mHasException = True
                        mLastException = ex
    
                        Return False
    
                    End Try
                End Using
            End Using
        End Function
    
    End Class

    Important notes

    Never use string concatenation in a query statement, use the following is one idea which I've been using for over a decade. It's known as xml literals where .Value turns the statement into a string.

    Dim insertStatement As String =
    <SQL>
            INSERT INTO Users 
            (FirstName, LastName) 
            VALUES (@FirstName,@LastName); 
            SELECT CAST(scope_identity() AS int);
     </SQL>.Value
    Always uses parameters e.g.
    cmd.Parameters.AddWithValue("@FirstName", firstName)
    cmd.Parameters.AddWithValue("@LastName", lastName)
    Not using parameters will bite you today, tomorrow or later so just use parameters.

    For a less complex code sample, here is one on MSDN I did several years ago.


    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



    Saturday, January 12, 2019 3:29 PM
    Moderator
  • [...]

    
            Dim command As New SqlCommand("insert into Users(First Name,Last Name) value('" & FName.Text & "', '" & Lname.Text & "')")
    



    Try specifying the connection:

       Dim command As New SqlCommand("insert into . . .", cn)

    Saturday, January 12, 2019 6:33 PM
  • You can also use the ADO.NET Entity Framework too with MS SQL Server to do CRUD (Create, Read, Update and Delete).

    https://www.tutorialspoint.com/entity_framework/entity_framework_overview.htm

    https://www.cmarix.com/benefits-of-entity-framework/

    https://code.msdn.microsoft.com/windowsapps/Entity-Framework-6-for-06edd4a8

    Example....

    Imports System.Data.Entity
    Imports Entities
    
    Public Class DaoProject
        Implements IDaoProject
    
        Private ReadOnly context As ProjectManagementEntities
    
        public sub New (dbcontext As ProjectManagementEntities)
            context = dbcontext
        End sub
    
        Public Function GetProjectById(ByVal id As Int32) As DtoProject Implements IDaoProject.GetProjectById
    
            Dim dto = New DtoProject()
       
            Dim project = (context.Projects.Where(Function(a) a.ProjectId = id)).SingleOrDefault()
    
            If IsNothing(project) Then
                Return dto
            End If
    
            dto.ProjectId = project.ProjectId
            dto.ClientName = project.ClientName
            dto.ProjectName = project.ProjectName
            dto.Technology = project.Technology
            dto.ProjectType = project.ProjectType
            dto.UserId = project.UserId
            dto.StartDate = project.StartDate
            dto.EndDate = project.EndDate
            dto.Cost = project.Cost
    
            Return dto
    
        End Function
    
        Public Function GetProjectsByUserId(ByVal userid As String) As List(Of DtoProject) Implements IDaoProject.GetProjectsByUserId
    
            Dim dtos = New List(Of DtoProject)
    
            dtos = (From a In context.Projects.Where(Function(a) a.UserId.Contains(userid))
                    Select New DtoProject With {.ProjectId = a.ProjectId,
                                                .ClientName = a.ClientName,
                                                .ProjectName = a.ProjectName,
                                                .Technology = a.Technology,
                                                .ProjectType = a.ProjectType,
                                                .UserId = a.UserId,
                                                .StartDate = a.StartDate,
                                                .EndDate = a.EndDate,
                                                .Cost = a.Cost}).ToList()
           
    
            Return dtos
    
        End Function
    
        Public Sub CreateProject(ByVal dto As DtoProject) Implements IDaoProject.CreateProject
    
            Dim project = New Project() With {.ClientName = dto.ClientName,
                                                .ProjectName = dto.ProjectName,
                                                .Technology = dto.Technology,
                                                .ProjectType = dto.ProjectType,
                                                .UserId = dto.UserId,
                                                .StartDate = dto.StartDate,
                                                .EndDate = dto.EndDate,
                                                .Cost = dto.Cost}
            context.Projects.Add(project)
            context.SaveChanges()
    
           
        End Sub
    
        Public Sub UpdateProject(ByVal dto As DtoProject) Implements IDaoProject.UpdateProject
    
            Dim project = New Project()
           
            project = (context.Projects.Where(Function(a) a.ProjectId = dto.ProjectId)).SingleOrDefault()
           
            If Not IsNothing(project) Then
                project.ClientName = dto.ClientName
                project.ProjectName = dto.ProjectName
                project.Technology = dto.Technology
                project.ProjectType = dto.ProjectType
                project.UserId = dto.UserId
                project.StartDate = dto.StartDate
                project.EndDate = dto.EndDate
                project.Cost = dto.Cost
            End If
    
          
            If IsNothing(project) Then
                Exit Sub
            End If
    
            context.Entry(project).State = EntityState.Modified
            context.SaveChanges()
           
        End Sub
    
        Public Sub DeleteProject(ByVal id As Int32) Implements IDaoProject.DeleteProject
    
            Dim project As Project
            
            project = (context.Projects.Where(Function(a) a.ProjectId = id)).Include("Tasks").SingleOrDefault()
           
            If IsNothing(project) Then
                Exit Sub
            End If
    
            For i As Integer = 0 To  project.Tasks.Count - 1
                Dim task = project.Tasks(i)
                context.Entry(task).State = EntityState.Deleted
            Next
            
            context.Entry(project).State = EntityState.Deleted
            context.SaveChanges()
           
        End Sub
    
        End Class
    

    Sunday, January 13, 2019 12:45 AM
  • Hi,

    I agree with the reply of Viorel_,I think it can solve your problem.If your issue is solved, please Mark as answer.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 14, 2019 8:53 AM
    Moderator