VB and adding data to a database [RESOLVED]
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.
Re: VB and adding data to a database
Sure. Download the samples in my signature (for your VS Version) and try them ALL out!
Re: VB and adding data to a database
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".
Code:
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.
Re: VB and adding data to a database
Code:
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
Re: VB and adding data to a database
That fixed that problem, thanks!. Although my next error is stating "No value given for one or more required parameters."
Is that referring to
Code:
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!!
Re: VB and adding data to a database
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:
Code:
VALUES (' " & trim(txtName.text) &" ',' " & ....
I used a space above to make it readable but the space should not be used in actual code.
Re: VB and adding data to a database
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.
Re: VB and adding data to a database
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:
Code:
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:
Code:
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:
Code:
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:
Code:
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.
Re: VB and adding data to a database
I also just noticed that your connection string has two data sources:
Code:
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.
Re: VB and adding data to a database
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!
1 Attachment(s)
Re: VB and adding data to a database
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.
Re: VB and adding data to a database
Thanks all for the input, it was very helpful and I learned a great deal. I appreciate all the help.
Re: VB and adding data to a database
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.
Re: VB and adding data to a database
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.
