Database tutorial for vb.net ??

[loiter]

Hello,

 

I have a class where the prof is spoon feeding us vb.net database access. I am requesting a site that has a good tutorial on database access - specifically adding, deleting and updating records from a form (text boxes) in VB.net (to an access 2000 db).

 

The stupid book we have in class is all about opening and updating entire datatables and not specific fields in a text box.

 

Thank you in advance

 

J

[a_jam_sandwich]

Here is a tutorial ive just written. Was supposed to be short a few lines well, never mind...

 

Right what you need to do is first create a connection to the database, because you using Access you will use the OLEdb namespace.

 

Your fistline(s) should be your imports so type

 

Imports System.Data.OleDb

 

This will enable us to access all the functions for the OLEdb namespace in shorthand.

 

To access the Access database you will first need to create a connection to it via the OLEdb provider this can be done with the following

 

code which creates a connection based of you connection string.

 

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MYDB.mdb")
MyConnection.Open

 

Of course your Connection string will differ depending on your Database location, UID and password the above example only specifys the

 

database location via the 'Data Source=' section. The second line opens the connection to the database so we can insert, update, delete and

 

read from the database.

 

Now depending on what you want to do with the database depends on the next set of commands.

 

To read from the database you have 2 options the Datareader and the Dataadapter. Im not going into the Dataadapter here as for simple

 

database applications you probley will not need it.

 

The Dataadapter is an Forward only reader for returned database results.

 

Anytime you wish to read, insert, update or delete information from the database you will need to ask the database before anything happens,

 

this is an command. Command are just standard SQL (Standard Query Language) which enables you to do all the operations with the database as

 

described above and guess what... Im not going into any detail about SQL either.

 

the first example will be to get all the data from an Table called 'MyTable' in an database called 'MYDB.mdb' as shown above in the

 

connection example.

 

start of with the connection to the database as above

 

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MYDB.mdb")
MyConnection.Open

 

Now create the command to get the Data from MyTable

 

Dim MyCommand as New OleDbCommand("SELECT * FROM MyTable WHERE ID = 1", MyConnection)

 

The above line does the following... Create a new command, The bit in the indices is the important stuff for fistoff Select all fields from

 

the Table MyTable where the where we find a record in the table where its ID field is equal to 1. Now for the second bit the bit after the

 

comma specifies to use the Connection 'MyConnection' as created in the previous lines.

 

Right this is the Next line is needed so when we ask the database for Data (SQL) we can display through VB.NET

 

Dim MyReader As OleDbDataReader = MyCOmmand.ExecuteReader()

 

This is a very very important line as we are tell the command ('MyCOmmand') to execute and then put the returned data into the Reader

 

'MyReader'. This is line that in a second we will change so we can update, insert and delete instead of reading from the database the result

 

will be only one record providing the database found the record from MyTable where ID = 1, in my case because i've put the record into

 

database previously it exists and therefore returned.

 

Now for the section we can see, displaying the returned result. All im going to show you is displaying the data in 3 Textboxes Lastname,

 

Firstname and Address (Fields I have in mydatabase), this is done by the following code.

 

While MyReader.Read
Lastname.Text = MyReader("Lastname")
Firstname.Text = MyReader("Firstname")
Address.Text = MyReader("Address")
End While

 

All the above code does is first create an while loop and set the condition MyReader.Read all this means is loop though the reader until

 

where at the end of the returned results. This happens when .Read = False. Each time we go though the loop through MyReader where are just

 

advancing through the recordset, since we have only one Data result returned this will only go through the loop once.

 

Notice MyReader("") this is the bit where we get the Field from the Reader and place it into the textbox the bit inbetween "" is the

 

Fieldname.

 

The last thing needed to do is to dispose \ Close all the objects as used above this is done by the following code.

 

MyConnection.Close
MyReader.Close
MyCommand.Dispose

 

 

The full code is below for displaying...

 

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MYDB.mdb")
MyConnection.Open
Dim MyCommand as New OleDbCommand("SELECT * FROM MyTable WHERE ID = 1", MyConnection)
Dim MyReader As OleDbDataReader = MyCOmmand.ExecuteReader()
While MyReader.Read
Lastname.Text = MyReader("Lastname")
Firstname.Text = MyReader("Firstname")
Address.Text = MyReader("Address")
End While
MyConnection.Close
MyReader.Close
MyCommand.Dispose

 

 

Now for inserting.

 

All the code is the same up until we create MyCommand. The Command will now hold the SQL for inserting a record. As above im just using

 

'Firstname', 'Lastname' and 'Address' I shold also point out that 'ID' as used in getting a record is a autonumber field in the database. Now

 

for the SQL.

 

INSERT INTO MyTABLE (firstname, Lastname, Address) VALUES ('Andrew', 'Duff', 'Not Telling')

 

Thats it for the SQL, the first secion in the indices are the Fields I want to insert into and as in the SQL the second secion in indices are

 

the values in inserting. Note... '' in the value list indicate that the values are Text if the values are numerical then '' would apply.

 

right now we have the SQL the code looks like the follow to the point as above

 

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MYDB.mdb")
MyConnection.Open
Dim MyCommand As New OleDbCommand("INSERT INTO MyTABLE (firstname, Lastname, Address) VALUES ('Andrew', 'Duff', 'Not Telling')", MyConnection)

 

As when doing the Reader example we need to Run the command. To do this we only need one line of code which is.

 

MyCommand.ExecuteNonQuery()

 

This will now send the command to the Database to insert a record. now as above we need to dispose all the Objects

 

MyConnection.Close
MyReader.Close
MyCommand.Dispose

 

 

The code in Full for Inserting

 

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MYDB.mdb")
MyConnection.Open
Dim MyCommand As New OleDbCommand("INSERT INTO MyTABLE (firstname, Lastname, Address) VALUES ('Andrew', 'Duff', 'Not Telling')", MyConnection)
MyCommand.ExecuteNonQuery()
MyConnection.Close
MyReader.Close
MyCommand.Dispose

 

When Updating or Inserting only thing now needing to be changed is the SQL the syntax is the same as inserting

 

Example SQL of Updating.

 

UPDATE MyTable SET Firstname = 'Bobby' WHERE ID = 1

 

The above SQL will find the record where ID = 1 the same as in the Reading and change the Firstname from whatever it is to Bobby

 

Full code as below.

 

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MYDB.mdb")
MyConnection.Open
Dim MyCommand As New OleDbCommand("UPDATE MyTable SET Firstname = 'Bobby' WHERE ID = 1", MyConnection)
MyCommand.ExecuteNonQuery()
MyConnection.Close
MyReader.Close
MyCommand.Dispose

 

Last one now fingers hurting

 

Example SQL of Delete.

 

DELETE FROM MyTable WHERE ID = 1

 

You've guessed remove the record where the ID = 1 from the Database.

 

Full code as below.

 

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MYDB.mdb")
MyConnection.Open
Dim MyCommand As New OleDbCommand("DELETE FROM MyTable WHERE ID = 1", MyConnection)
MyCommand.ExecuteNonQuery()
MyConnection.Close
MyReader.Close
MyCommand.Dispose

 

 

I hope this helps if you need a example i'll knock one up

 

Andy.

Edited February 19, 2003 by a_jam_sandwich

[loiter]

PerFect!

 

Thanks. This is better than expected !!!

 

Jeff

[jjjamie]

I have just read this very helpful post and I have a quick question - how would you go about using a Data Source, rather than a connection string to connect to the database?

[a_jam_sandwich]

I Don't belive the OLEdb provider supports ODBC connections to use the ODBC use the ODBCdb namespace which you will have to download additionally. see

 

http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/668/msdncompositedoc.xml

 

Andy

[stustarz]

Fantastic tutorial - should be added to the Tutors Corner!

[stustarz]

A couple of questions about the above if you don't mind:

 

From what I have read it seems that you are using the DataReader to also perform Insert, Updates and Deletes on the tables within the database - am i right? I was always under the impression that a DataSet should be used for these functions.

 

Also, is the above code the fastest way to do database manipulation?

 

I ask purely on the basis that I use datasets to perform update, insert e.t.c. and datareaders if i simply want to show one record, but I am still not happy with the speed of my app. I amended all my database code to the above and it does work much quicker! There is just a lot more manual code writing than before. (saying that I wrote most of it into a 4 functions and now just call the specific function and attach the variables as required).

 

Thanks

[a_jam_sandwich]

No the datareader doesn't perform any inserting, updating or deleting it is just a data reader.

 

The way the inserting, updating and deleting works is that that you send a command to the database (SQL), and the database perfoms this command.

 

Dim MyCommand As New OleDbCommand("DELETE FROM MyTable WHERE ID = 1", MyConnection)
MyCommand.ExecuteNonQuery()

 

If you look at the above example the command is created with the SQL (Command) within the first part of the indices.Because we are using .ExecuteNonQuery with the command 2nd line down the only thing returned from this function is the count of rows affected by the SQL command.

 

And yes this is a quick method for speed, the datasets are greate for holding information without being connected to database but slow when it comes to updating.As with everything else that means more code.

 

Andy

Edited February 26, 2003 by a_jam_sandwich

[stustarz]

Thanks! Thats cleared everything for me now!

[turrican]

Wow that was a great thread. Good questions too! I saved the whole thing. thanks for taking the time a_jam.

[desmondtan]

TYou can downlaod 101 code sample form Microssoft web site .

There is a data entry sample using Northwind .

http://msdn.microsoft.com/vbasic/downloads/samples/default.asp

[scorpion555]

great tutorial, but I still have a question, will this work with an access db or sql only db?? how can you tell or how do you set what kind of database you want?

 

Also, what other kinds of DB can you do?? I heard of a text only db or something like that. I would like to use mysql, how do I declare that is what I want to use?

 

Thanks in advance, thanks for your time!!

[a_jam_sandwich]

http://www.mysql.com/articles/dotnet/index.html this should give you some insight on how to use mySQL with vb.net

 

Andy

[turrican]

I have a question. How could I use the text that the user is entering into the text boxes to insert into the database. In other words I want to replace the values "andrew" , "duff" with textbox1.text , textbox2.text , etc. Can I do it like this? Please help a newbie.

[a_jam_sandwich]

When you build you SQL insert string instead of somthing like

 

SQL = "INSERT INTO MyTable (Lastname, firstname) VALUES ('Andrew', 'Duff')"

 

You replace the values with the following based on 2 textboxs

 

SQL = "INSERT INTO MyTable (Lastname, firstname) VALUES ('" & Textbox1.Text & "', '" & Textbox2.Text & "')"

 

 

Hope this helps

 

Andy

[turrican]

thanks for the reply.This is my first time not using the dataset so could you please tell me where I am going wrong with my connection string? when I run it , it stops at the myConnection.Open line. when I put the cursor over it a message pops up sying that It is trying to call a function. Thats what I want isnt it? That is a minor thing though, my real problem is with the connection string I think since I keep getting the message saying Additional information: ExecuteNonQuery: Connection property has not been initialized.....Please take a look at the following code.

 

 

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

 

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= d:\DvdList.mdb;User Id=admin;")

 

MyConnection.Open()

 

 

Dim myCommand As New OleDbCommand("INSERT INTO movies (title, rating, category) VALUES ('" & TextBox1.Text & "', '" & TextBox2.Text & "', '" & TextBox3.Text & "' ")

 

myCommand.ExecuteNonQuery()

 

MyConnection.Close()

 

MyCommand.Dispose()

 

 

sorry to tke up so much space, thanks for your help!

[ashrobo]

  turrican said:

Dim myCommand As New OleDbCommand("INSERT INTO movies (title, rating, category) VALUES ('" & TextBox1.Text & "', '" & TextBox2.Text & "', '" & TextBox3.Text & "' ")

 

myCommand.ExecuteNonQuery()

 

 

Not too sure if it's still needed, turrican.

       Dim myCommand As New OleDbCommand("INSERT INTO movies (title, rating, category) VALUES ('" & TextBox1.Text & "', '" & TextBox2.Text & "', '" & TextBox3.Text & "' ", MyConnection)

add "MyConnection" to myCommand.

 

cheers!

[Boomstar]

Hey, thanks for the nice tutorial, it helped me with the inserting but I'm having a problem with deleting records.

I try to use this code with a button

 

 

Private Sub cmdClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClear.Click

Dim MyDel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\reports.mdb")

MyDel.Open()

Dim MyCommand As New OleDbCommand("DELETE FROM Records WHERE f14 = 1", MyDel)

 

Try

MyCommand.ExecuteNonQuery()

MsgBox("Works", MsgBoxStyle.Information, "Works")

Catch ex As Exception

MsgBox("Error in Clearing Memory", MsgBoxStyle.Information, "Error")

End Try

 

MyDel.Close()

MyCommand.Dispose()

End Sub

 

Whenever I execute this code, I just get the error message, am I forgetting to do somtething here, any help would be greatly appriciated, thanks.

[dEEM]

i would say this is a very good tutorial :)

ups...

it certainly help a newbie like me...

[helpme]

i'm trying to get my delete function to work... here's what I have

 

  Quote

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

 

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Us\My Documents\Visual Studio Projects\WindowsApplication1\bin\Dictionary.mdb")

MyConnection.Open()

Dim MyCommand As New OleDbCommand("DELETE FROM dictionary WHERE Words = '" & TextBox1.Text & "'", MyConnection)

Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader()

Try

MyCommand.ExecuteNonQuery()

MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Successfully Deleted")

Catch ex As Exception

MsgBox("Cannot Be Deleted", MsgBoxStyle.Information, "Error")

Finally

End Try

 

MyReader.Close()

MyConnection.Close()

MyCommand.Dispose()

TextBox1.Text = ""

End Sub

 

No matter what I type in the textbox, the msg box will always say "Cannot Be Deleted". The code partially works. For the words I've typed in the textbox which are in the database gets deleted but the msg box pops up "Cannot Be Deleted" instead of "Successfully Deleted" you know what I mean? So is there anyway I can fix it?

 

Edit: If I delete the Dim MyReader line of code, it's the other way around (Only the "Successfully Deleted" message box pops up no matter what you type in the textbox.)

Edited April 4, 2004 by helpme

[bman9111]

please help

 

I am new to vb.net and I want to create and application that connects to an access database. I have seen many examples via internet however I want to make sure I use the best possible way.

 

I notice that many of the examples do not use the data toolbox options most seem to be hard coded and not related to the data components. Is this good or bad. I don't even see them using the server feature either that allows you to make a connection right to the database and have access to it from vb design side. Now I know in vb 6 that data environment was horrible and really draged the system down. So I use to hard code using adodb. Example

dim rs as new adodb. recordset

dim db as new adodb.connection

I never used the components just what I listed above and to like to a text box instead of using the properties of the textbox, datafield, datasource. I would just hard code it. Seemed to be the best way. Anyways please some give me a step by step outline of how to create a program using access the best optimal way.

 

My friend an I have been listing this on several forums and really haven't had a solid answer yet. I ran into this and it really helped me with the database, but I would still love to have this question answer. My friend has pretty much given up hope.

 

 

any help??????