Database access
Database access
Database access
Database access
Database access
Database access Database access Database access Database access Database access Database access Database access Database access
Database access Database access
Database access
Go Back  Xtreme Visual Basic Talk > > > Database access


Reply
 
Thread Tools Display Modes
  #1  
Old 05-14-2004, 08:25 AM
pintopt pintopt is offline
Newcomer
 
Join Date: May 2004
Location: Porto, Portugal
Posts: 10
Default Database access


Hello,

I'm starting with VB .NET,

I want to connect my VB project to a Microsoft Access Database,
open a table, and work with rows how I did when I work in VB 6...

If someone can help me I'll be great...
Reply With Quote
  #2  
Old 05-14-2004, 09:08 AM
MKoslof's Avatar
MKoslofDatabase access MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Use native .Net methods, you want to import the proper System data class (OLEDB, ODBC) and use a standard connection string. Then use the command and adapter objects to fill a dataset, populate a DataGrid, etc. See below, a brief example:

Code:
Imports System.Data.OleDb Module ConnectDB Public conn_String As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb" Public Sub ConnectToDB(ByRef f As Form1, ByVal sSelect As String) Dim olapConnection = New OleDbConnection(conn_String) Dim olapAdaptor = New OleDbDataAdapter Dim olapCommand = New OleDbCommand Dim ds As New DataSet olapConnection.open() olapCommand.Connection = olapConnection olapCommand.CommandType = CommandType.Text olapCommand.CommandText = sSelect olapAdaptor.SelectCommand = olapCommand 'fill a dataset to be used for whatever purpose olapAdaptor.Fill(ds) 'if populating a dataGrid located on form1, etc f.DataGrid1.DataSource = ds.Tables(0) olapConnection.close() olapAdaptor = Nothing olapCommand = Nothing End Sub End Module

The caller method could be:

Code:
Private Sub cmdConnect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdConnect.Click Dim sSQL As String sSQL = "SELECT * FROM myTable WHERE field1 = 'mkoslof'" Call ConnectToDB(Me, sSQL) End Sub
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 05-17-2004, 05:18 AM
pintopt pintopt is offline
Newcomer
 
Join Date: May 2004
Location: Porto, Portugal
Posts: 10
Default new ask

hello,

now I have a connection to database in order...

now I add new rows..
but the changes aren't saved and when I stop and run again the program the changes had been lost...
Reply With Quote
  #4  
Old 05-17-2004, 06:26 AM
MKoslof's Avatar
MKoslofDatabase access MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

In .Net everything is command based. Basically, with your Ole Data Adapter, you can now send updates or insert into statements to the defined Data Set. So basically, you can update the Data Set at the .Net layer, and save your changes to the underlying database table. So do you want to save changes at the .Net level, or just send an INSERT INTO statement back to the database? If you don't have a Data Set to maintain, you could do the following:

Code:
Dim myconnection As New OleDbConnection(conn_String) Dim strCom as string Try myconnection.Open() strCom = "INSERT INTO Table1 (Field1) VALUES('" & Me.Text1.Text & "')" Dim mycommand As New OleDbCommand(strCom, myconnection) mycommand.ExecuteNonQuery() Catch ex As Exception MsgBox("The following error has occured: " & ex.Message) End Try myconnection.Close
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #5  
Old 05-17-2004, 07:02 AM
pintopt pintopt is offline
Newcomer
 
Join Date: May 2004
Location: Porto, Portugal
Posts: 10
Default

I have connected the access database to a dataset..

now I want to read and save the changes of this dataset..

I read other threads but I don't understand what are to do..
Reply With Quote
  #6  
Old 05-17-2004, 09:57 AM
MKoslof's Avatar
MKoslofDatabase access MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

What piece of the current code that I posted don't you understand? What is your current code for inserting the new records? Can you post it.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #7  
Old 05-18-2004, 02:32 AM
pintopt pintopt is offline
Newcomer
 
Join Date: May 2004
Location: Porto, Portugal
Posts: 10
Default

I still am to test my knowledge to see if I obtain to program in .NET,

the code that I have to insert new registers is:

Dim rs As DataRow
rs = Dataset11.clmails.NewRow
rs("cdcl") = "12"
rs("mail") = "ol"
rs("cdmail") = "13"
Dataset11.clmails.Rows.Add(rs)

Dataset11.WriteXml(New IO.FileStream("C:\Visual Studio Projects\WindowsApplication1\bd.xml", IO.FileMode.Create))

it is finishes line of codigo that it does not work

Last edited by pintopt; 05-18-2004 at 03:06 AM.
Reply With Quote
  #8  
Old 05-18-2004, 06:59 AM
MKoslof's Avatar
MKoslofDatabase access MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

How is this DataTable created? Do the rows already exist? (as in have the column names been created)? How do you populate this data set initially, if you are building this DataTable via code, see below:

Code:
Dim dt As DataTable = New DataTable("MKoslof") dt.Columns.Add(New DataColumn("Order")) dt.Columns.Add(New DataColumn("Number")) dt.Columns.Add(New DataColumn("Name")) Dim workRow As DataRow workRow = dt.NewRow() workRow(0) = Me.Text1.Text workRow(1) = Me.Text2.Text workRow(2) = Me.Text3.Text dt.Rows.Add(workRow)
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #9  
Old 05-18-2004, 07:29 AM
pintopt pintopt is offline
Newcomer
 
Join Date: May 2004
Location: Porto, Portugal
Posts: 10
Default

Thanks,
I've tried the code that you give to me and I thinks it's working...

now how I can list the records that I save???
Reply With Quote
  #10  
Old 05-19-2004, 09:40 AM
MKoslof's Avatar
MKoslofDatabase access MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

You can loop each row in your dataTable. And in turn, you can loop each DataTable in your DataSet. This would provide you all the values that are currently there.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #11  
Old 05-19-2004, 09:47 AM
pintopt pintopt is offline
Newcomer
 
Join Date: May 2004
Location: Porto, Portugal
Posts: 10
Default

I'm sorry but I don't understand what you wanna say...
because I don't speak and understand english very well.

If you can give to me some code lines, I think that are the best way to me...
Reply With Quote
  #12  
Old 05-19-2004, 09:55 AM
MKoslof's Avatar
MKoslofDatabase access MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

fine, something like this:

Code:
Dim Dt as DataRow 'where DS is your defined data set For each DT in DS.Tables(0).rows msgbox dt(0) msgbox dt(1) Next

So, for each DataRow in the first table within the DataSet I display a message box containing the first two fields. You can tweak this as needed
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #13  
Old 05-20-2004, 03:54 AM
pintopt pintopt is offline
Newcomer
 
Join Date: May 2004
Location: Porto, Portugal
Posts: 10
Default

Hi
I don't understand why nothing work...

I ask you if you can send me a little program, with a Microsoft Access database connection, only a little program that have the code to add, edit and delete records...

If you can help me I will be been thankful
Reply With Quote
  #14  
Old 05-21-2004, 06:14 AM
MKoslof's Avatar
MKoslofDatabase access MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

What is your error? What is the current code you are using? We are not here to write a program for you. If you are experiencing an error, post your current code and tell us what happens when you run it, we will help you fix it.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #15  
Old 09-11-2004, 01:44 AM
Davearia's Avatar
Davearia Davearia is offline
Junior Contributor
 
Join Date: Jul 2004
Location: In A Perfect Cadence
Posts: 358
Default I Tried It Too

Hi,

Thought I'd give it a go as I too seem to struggle to get database examples to work on my machine. I use FoxPro at work which is far more straight forward at creating and using databases. Please don't get me wrong, I would prefer to use V.B.NET along with SQL Server it's just a struggle to start off with.

Anyway I created a new windows project and created a new module Module1. I put a datagrid and button on the form of my project and put the click code in the button.

I created the C:\db1.mdb database with a table myTable with field1 called mkoslof with a few records in.

I then attempted to run it, it goes as far as you clicking the button and then I got an error message on the olapAdaptor.Fill(ds) of Module1.

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in microsoft.visualbasic.dll . See attachment for more detail.

As I said earlier I admit that I am new to databasing in V.B.NET and I find it hard to understand the error messages that I get when trying to run code. I am sure that I will get the hang of it but examples such as this are helpful.

If you can help me with this error please do?

Thanks, Dave.
Attached Images
File Type: jpg Fault.JPG (140.9 KB, 16 views)
Reply With Quote
  #16  
Old 09-11-2004, 08:47 AM
MKoslof's Avatar
MKoslofDatabase access MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Again, check your table, check your query, check your references. The code looks fine, if you used that particular example.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #17  
Old 10-05-2004, 09:25 PM
wowzers wowzers is offline
Newcomer
 
Join Date: Oct 2004
Location: Hobart
Posts: 11
Default

Hi guys,

Thanks very much for the database connection code in this thread. I am new to .NET, migrating from ASP. I have been attempting to use this code to store entries in a MS Access database into variables in my VB.NET app. This code seems to be what I need I just haven't been successful in getting it to work. I have attached an error message I receive when I try to built the app. Does anyone know what I might be doing wrong?

Some information about the app:

WebMon is the Form.

I'm using the following code to call the Subroutine:

Code:
        Dim sSQL As String
        sSQL = "SELECT * FROM Links"
        Call ConnectToDB(Me, sSQL)
Thats about all I can think of at the moment, if anyone could help that would be much appreciated, thank you.

Regards.
Attached Images
File Type: jpg database.jpg (92.2 KB, 9 views)
Reply With Quote
  #18  
Old 10-05-2004, 09:39 PM
wowzers wowzers is offline
Newcomer
 
Join Date: Oct 2004
Location: Hobart
Posts: 11
Default

Hello,

I managed to find the error in my code, the path to the mdb file was incorrect but I have fixed this. The DataGrid is created now and the program launches and grabs the data as I wanted, shown in the image below. How do I now abstract cells from the DataGrid and place them into variables? I want to manipulate the table data elsewhere in the program but I haven't figured out how to abstract it yet, thank you.

EDIT:
I re-read the ConnectToDB Subroutine and noticed the line:

Dim ds As New DataSet

I'm not sure if ds is a global DataSet because it isn't being recognised with the following code:

Code:
Dim Dt As DataRow
        'where DS is your defined data set
        For Each Dt In ds.Tables(0).rows
            MsgBox(Dt(0))
            MsgBox(Dt(1))
        Next
Feel free to point out where I'm going wrong , thank you.

Regards.
Attached Images
File Type: jpg database.jpg (22.3 KB, 6 views)

Last edited by wowzers; 10-05-2004 at 10:05 PM.
Reply With Quote
  #19  
Old 10-06-2004, 06:40 AM
MKoslof's Avatar
MKoslofDatabase access MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

In your code, where do you define DS? If it is private in scope, it won't be available to all other caller methods. Where is your Dim statement and new instance declared?
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #20  
Old 10-06-2004, 07:23 AM
wowzers wowzers is offline
Newcomer
 
Join Date: Oct 2004
Location: Hobart
Posts: 11
Default

Well the DS is defined in a public sub in the module, like you said to do in the code at the top of the thread. The DataGrid is on the form, not sure whether this causes a problem. Even said. There is data now in the datatgrid, now do I grab that data and put into a variable?

EDIT:
The attachment in my first post shows where the DS is declared, its the form that the datagrid is used, thanks.

Regards.

Last edited by wowzers; 10-06-2004 at 07:31 AM.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
User Accounts with an Access Database Sazlo Database and Reporting 1 11-13-2003 05:31 PM
Creating a database application with VB v. Doing it all in Access Dwayne 76 Database and Reporting 1 11-13-2003 09:50 AM
Populate ComboBox from Access Database PianoMan Database and Reporting 3 11-06-2003 07:13 AM
HELP WITH ACCESS & VB 5 XVB Database and Reporting 2 10-27-2003 09:28 PM
Database Help! dimples Database and Reporting 8 06-25-2001 12:18 AM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Database access
Database access
Database access Database access
Database access
Database access
Database access Database access Database access Database access Database access Database access Database access
Database access
Database access
 
Database access
Database access
 
-->