VB and adding data to a database

[Xxsomethingxx]

Hello all,

I have a project I created for my VB class where you enter what type of ticket(combobox), how many tickets(textbox), what type of tickets(listbox) and will give you the total amount.

I want to do a little more to the program and was wondering about adding the person's name, address, phone, city, state, zip and then having all the information put into a database. I wanted to also take the information entered from the program and use the printPreview control to view a report from Access(possible?).

I've only worked with viewing database information on the forms in VB but I've never entered data into the database. Is this possible? Can I have the information entered on the form be entered into a database that keeps track of all tickets purchased and customer information?

I can see this working in my head but don't know if it's logistically possible.

[dglienna]

Sure. Download the samples in my signature (for your VS Version) and try them ALL out!

[Xxsomethingxx]

Hello all,

I've been busy trying to get this implemented in my program but am having a problem trying to get the data added to the database.

The program will run without problems but as soon as I call the button click event procedure I get an error "Command text was not set for the command object".

Module ModDB

    Public Function test() As OleDb.OleDbConnection
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String

        dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\database1.accdb;Persist Security Info=False;"
        dbSource = "Data Source = C:/customerInfo1.accdb"
        con.ConnectionString = dbProvider & dbSource
        'con = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\customerInfo1.accdb;Persist Security Info=False;" & Application.StartupPath.ToString & "customerInfo1.accdb")
        Return con
        con = Nothing
    End Function

    Public bool As Boolean
    Public da As OleDb.OleDbDataAdapter
    Public dt As New DataTable
    Public i As Int16
    Public cmd As OleDb.OleDbCommand
    Public da1 As OleDb.OleDbDataAdapter
    Public strSQL As String
    Public CB As OleDb.OleDbCommandBuilder
    Public con = ModDB.test

End Module

'This is in the button click method
Try

            Dim connString As String
            Dim cmd As OleDb.OleDbCommand
            Dim testDB = ModDB.test

            connString = "INSERT INTO customerINfo (FullName, Address1, PhoneNumber)VALUES (trim(txtName.text), trim(txtAddress.text),trim(txtPHone.text))"

            cmd = New OleDb.OleDbCommand(strSQL)
            cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()


        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

I'm still very new to this so if I'm going at this the completely wrong way please point me in the right direction. Is this error telling me to use an SQL command? I only want to enter data into the database and then later have a report that has all the users that bought tickets show up in a print preview.

Thank you in advance.

[DataMiser]

connString = "INSERT INTO customerINfo (FullName, Address1, PhoneNumber)VALUES (trim(txtName.text), trim(txtAddress.text),trim(txtPHone.text))"

            cmd = New OleDb.OleDbCommand(strSQL)

look at the highlighted variable names above

[Xxsomethingxx]

That fixed that problem, thanks!. Although my next error is stating "No value given for one or more required parameters."

Is that referring to

connString = "INSERT INTO customerINfo (FullName, Address1, PhoneNumber)VALUES (trim(txtName.text), trim(txtAddress.text),trim(txtPHone.text))"

As far as I know that's the way to insert data into the table. What other methods are there? I'm not expecting an answer just a hint on where to go or what to look for a possible solution. Thanks again!!

[DataMiser]

you need to use single quotes around your text strings. You also need to break apart that string and use proper concantionation to get your varibles to work as you would expect.

Example:

VALUES (' " & trim(txtName.text) &" ',' " & ....

I used a space above to make it readable but the space should not be used in actual code.

[JohnnyD]

As a side-note you might want to use a StringBuilder to build your SQL statements. It can make things a lot easier to read once your SQL statements get really long. It can also help performance slightly.

Another thing worth noting - I would steer away from using module-level functions and try to encapsulate them into classes. VB.NET is fully object-oriented so take advantage If you're ever going to hand this code over to someone else, they're going to have a hell of a time trying to figure out what's going on.

If you're new to VB.NET, trying to get into the mindset that 'everything is an object' can be very difficult to start with, especially if you've come from a VB6 background.

[JohnnyD]

I hope you don't mind, but I have had a go at re-writing your code in a way which I think should help you understand what I meant in my previous post (about encapsulating things into classes).

It is by no means a complete example and I have left out a lot of code that I would normally include in a real-world application to make it easier to follow.

I would split your project into the following files:

Errors.vb:

Option Strict On
Option Explicit On 

Public Class MaximumLengthException
    Inherits System.ApplicationException

    Public Sub New(ByVal maxLength As Integer, ByVal parameterName As String)
        MyBase.New("The maximum length for " & parameterName & " is " & _
                   maxLength.ToString & " characters.")
    End Sub
End Class

Public Class MinimumLengthException
    Inherits System.ApplicationException

    Public Sub New(ByVal minimumLength As Integer, ByVal parameterName As String)
        MyBase.New("The minimum length for " & parameterName & " is " & _
                    minimumLength.ToString & " characters.")
    End Sub
End Class

Shared.vb:

Option Strict On
Option Explicit On 

Public Class AppConstants
    Public Shared DBConnString As String = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\database1.accdb;" & _
        "Persist Security Info=False;" & _
        "Data Source = C:/customerInfo1.accdb"

End Class

Customer.vb:

Option Strict On
Option Explicit On 

Imports System.Data.OleDb

Public Class Customer

    Private _fullName As String
    Private _addressLine1 As String
    Private _phoneNumber As String

    Private _isDirty As Boolean = False

    Public ReadOnly Property IsDirty() As Boolean
        Get
            Return _isDirty
        End Get
    End Property

    Public Property FullName() As String
        Get
            Return _fullName
        End Get
        Set(ByVal Value As String)

            Dim ParamName As String = "Customer Name"

            ' you can add whatever validation you like here.
            ' eg - I have said that this field can't be Null
            ' or and empty string and has to be between
            ' 3 and 25 characters long.
            If Value = Nothing Then Throw New ArgumentNullException(ParamName)

            If Value.Length < 3 Then Throw New MinimumLengthException(3, ParamName) Else 
            If Value.Length > 25 Then Throw New MaximumLengthException(25, ParamName)

            ' Here, I am saying "if the new value is different from the existing value then
            ' update the curent value and mark the object as dirty"
            If _fullName <> Value Then
                _fullName = Value
                _isDirty = True
            End If

        End Set
    End Property

    Public Property PhoneNumber() As String
        Get
            Return _phoneNumber
        End Get
        Set(ByVal Value As String)

            ' You can go mad here and add all kinds of nifty validation using regular
            ' expressions, etc...
        End Set
    End Property

    Public Property AddressLine1() As String
        Get
            Return _addressLine1
        End Get
        Set(ByVal Value As String)

            ' You can also do a lot of cool validation with addresses
        End Set
    End Property

    Public Sub Save()
        Dim connectionString As String = AppConstants.DBConnString
        Dim cn As New OleDbConnection(connectionString)
        Dim cmd As New OleDbCommand

        ' Only save the data if it has changed.
        If _isDirty Then
            Try
                cn.Open()

                With cmd
                    .Connection = cn
                    .CommandText = "INSERT INTO customerINfo " & _
                                   "   ( " & _
                                   "    FullName " & _
                                   "   ,Address1 " & _
                                   "   ,PhoneNumber " & _
                                   "   ) " & _
                                   "VALUES " & _
                                   "   (?, ?, ?) "

                    With .Parameters
                        .Add("full_name", _fullName.Trim)
                        .Add("address_line1", _addressLine1.Trim)
                        .Add("phone_number", _phoneNumber.Trim)
                    End With

                    .ExecuteNonQuery()

                End With

                cmd = Nothing
                cn.Close()

                _isDirty = False

            Catch ex As Exception

                cn.Close()
                Throw ex

            End Try

        End If
    End Sub
End Class

Form1.vb:

Option Strict On
Option Explicit On 

Public Class Form1
    Inherits System.Windows.Forms.Form

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cust As New Customer

        Try

            cust.FullName = txtName.Text
            cust.AddressLine1 = txtAddress.Text
            cust.PhoneNumber = txtPhone.Text

            cust.Save()

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Sub
End Class

I would be much happier as a developer if I had to inherit a project like this.

Let me know if there are any bits you don't understand and I'll try and explain as best I can.

Regards,

John.

[JohnnyD]

I also just noticed that your connection string has two data sources:

        dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\database1.accdb;Persist Security Info=False;"
        dbSource = "Data Source = C:/customerInfo1.accdb"

You probably want to get rid of that reference to database1.accdb in the dbProvider string.

[Xxsomethingxx]

JohnnyD,

Thank you for taking your time and showing me possibilities on how to do this in a much cleaner manner.

All those 4 snippets of code, are you creating separate classes for those or are they all on the same form? My current code looks like a jumbled mess trying to get all of that in there so if I could use seperate forms to do it would be fantastic!

I've only been working with VB for about a year so anytime I can learn something that would help me out in the 'real world' is a plus! Everything else seems to make sense. I will try to incorporate my own version of this code later today while I'm at home and see where it takes me.

Thank you for your input!

[JohnnyD]

That was weird.. i just walked in front of my pc and this notification just popped up in front of me. Seeing as I'm here and still in programming mode I'll try and help you out right now.

I think you might be confusing files with classes. I think visual studio is to blame for this. When you right-click your project, you get the 'add module' and 'add class' menu items. All they do is add a new file to your project with a tiny bit of pre-defined code in. If you create one of each, you'll see that they both have a .vb file extension. You could highlight everything in one of those files, cut it and then paste it at the end of the other file and you'd have no problems at all. What I'm trying to get at is it really doesn't matter where you put your code as long as it makes it straightforward to understand. The actual file name has absolutely no relation to any of the code inside it.

Since using VB.NET I've never had any cause to use a module. I think this is a left-over from VB6/VBA days and you should probably avoid modules at all costs and stick to using classes.

It's worth noting that Forms are slightly different because they have a visual / designer (where you drag and drop the buttons) element to them, so you should always have one .vb file per form.

If you have complicated classes, it's good to separate them into their own files but you don't have to. So, if I wanted to, I could create one file in my project and call it 'allmycode.vb' and put all of the code in the first 3 snippets (excluding the code for the form) of my previous post into it and that would work fine, but it would make it hard to find what I was looking for quickly. Or you could create a separate file for every single class in your project, but sometimes it's good to group classes which are closely related and keep them together in one file.

So, keeping my previous post as an example, if you look carefully above each one I've suggested a file name which just helps me identify what is in that file. You'll also notice that the Customer.vb file only has one class in it, whereas the Errors.vb has 2 (and would definitely have a lot more in a real-world app).

Sorry, this is turning into quite a long, potentially confusing post. I'll try attaching a zip file so you can have a look for yourself what I mean.

[Xxsomethingxx]

Thanks all for the input, it was very helpful and I learned a great deal. I appreciate all the help.

[viperbyte]

That listing of JohnnyD is tutorial level stuff. Should be submitted as an article with a little polishing. Simple enough to not be overwhelming but more useful than than oop Mammals level analogy stuff. I like it! Thanks for sharing.

[JohnnyD]

Glad to help

I can't take full credit for the code, it is based on code I learnt from a really great book - "Building Client/Server applications with VB.NET: An example driven approach" by Jeff Levinson, although greatly simplified for the purposes of this forum. It's very old now (2003) and based on .NET 1.1 so I'm not sure how much use it would be to someone learning vb2008/2010 but I learnt a great deal about the general concepts of writing n-tier apps from it.

I did hear straight from the horse's mouth that Jeff is currently re-writing it for the most recent version of the .NET framework so I'd be very interested in having a look at that when it comes out.

I would also recomment another book for beginners - "Introduction to Visual Basic using .NET" by Dana L. Wyatt and Robert J. Oberg which teaches all the basics of object-oriented programming using VB.NET. I found it really useful during the transition from VB6 to VB.NET as it has little notes throughout the book pointing out the differences between the two.