[AlexG788]

I have been programming in VB6 for a while now and am switching over to .net. There are a few basic functions which I just can't get for some reason... I am forced at the moment to use a DAO/ADO connection, and I've been primarily using DAO in vb6, so I thought I'd carry this over to .net... atleast for the time being.

I have a basic snippet of code:

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

		Dim db As dao.Database
		Dim rs As dao.Recordset
		Dim pathdb As String
		Dim txt As dao.Field
		Dim dbe As dao.DBEngine



		pathdb = "C:\Users\SashaG\Documents\db1.mdb"

		dbe = New dao.DBEngine()
		db = dbe.OpenDatabase(pathdb)

		rs = db.OpenRecordset("SELECT * FROM [Table1] WHERE [Key] = 3")
		TextBox1.Text = rs("Txn ID")



	End Sub 

What I want is for that rs("Txn ID") record to show up in the textbox (no problems in vb6) but I'm getting an error which says:
"value of type 'dao.field' cannot be converted to string". Any idea what I'm doing wrong?

Thanks in advance for your help!
Alex

[ZRonZ]

I'd suggest that you back up the bus a little bit. Don't get all involved in ADO quite yet (based on personal experience). You don't need to because VB.Net will do almost all of the work for you. Using Visual Studio (2005 or 2008) create your database in SQL Express or add an existing database to your project. Then create your dataset. Once you have the dataset ready, to bind a control to a database field is as easy drag and drop the field on the control. You'll have the connection string, the code and, you automatically get the navigator bar as a bonus. So take a deep breath and go back to the basics of VB.Net.

[AlexG788]

Ok, I just tried this out.. the concept is not bad, the execution I'm still having some trouble with. I made a simple query:

open the database, (there are 4 records and 3 fields.. "Key", "Txn ID", "Project Name")
I want the database to be updated with the new value i provide for the "Project Name" via Textbox1

I run the below code, and get an error saying "Syntax Error in Update clause". I understand why, and i get the same error if i try to delete/add.... but I can't possibly have to change my sql statement every time i need to update/change a record! I can't find an easy way to do a simple update/add/delete to a record from an access database! in vb6 it was a 3 line job.. what am i doing wrong?

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

		Dim sql As String
		Dim con As New OleDb.OleDbConnection()
		Dim ConString As String

		ConString = "PROVIDER = Microsoft.jet.OLEDB.4.0;Data Source = C:\Users\SashaG\Documents\db1.mdb"
		con.ConnectionString = ConString

		sql = "SELECT * FROM [Table1]"

		Dim da As New OleDb.OleDbDataAdapter(sql, con)
		Dim cb As New OleDb.OleDbCommandBuilder(da)
		Dim ds As New DataSet("Test")

		con.Open()

		da.MissingSchemaAction = MissingSchemaAction.AddWithKey
		da.Fill(ds, "Test")

		ds.Tables("Test").Rows(1).Item(3) = TextBox1.Text

		Try
			da.Update(ds, "Test")
		Catch myexceptions As System.Exception
			MsgBox(myexceptions.Message)
		End Try

		con.Close()

	End Sub

is what you are talking about more along the lines of actually importing the table into the ide? I tried this before but I'm having some trouble creating the recordsets based on that data (databinding). Do you know of any good tutorials on this? I'm scavenging through all of my books, and I keep coming up with the same errors.

Thanks again!
Alex

[sharpy]

Hi

If you did what ZRonZ said and you ended up with a navigation bar there should have been an add delete and save button atomatically added to the navigation bar. If you want to edit something just change it and press save. The others speak for themselves you should not have to code it.

PS before you drag you table onto your form from the datset highlight the table in the dataset and from the dropdown menu select details this will give you textboxes instead of a datagridview.

This post has been edited by sharpy: 29 January 2009 - 09:21 AM

[ZRonZ]

I think you’re working way too hard. I had some of the same problems migrating from Visual FoxPro to VB.NET so I feel your pain. After fighting with the code you’re probably ready for the easy way. Here’s what I just did:


VB101:

I created a new Access database called “Table1” in My Documents
Key as integer, index key
TxnID as integer
ProjectName as Text

I added data:

Key TxnID ProjectName
1 111 Project 1
2 112 Project 2
3 113 Project 3
4 114 Project 4
5 115 Project 5

I then created a new project in VB.Net 2008: C:\VBTest\TestProject
Once opened, Form1 will be showing
In Server Explorer (Ctrl+Alt+S if not showing)
Right Click on “Data Connections” and Left Click on “Add Connections…”
The Add Connections.. Dialogue box opens
Make sure “Data Source:” text box says
“Microsoft Access Database File (OLE DB)
If not, change it so it does
Click the Browse button
Navigate to My Documents; select Table1 and click “Open”
You’ll be taken back to the Add Connection Dialogue
Click “Test Connection” if it fails, figure out what went wrong above
Once it succeeds, click “OK”
You should now be back to Server Explorer and Table1 should be showing as a data connection
Make sure Form1 is active/selected

Open “Data Sources” (Shift+Alt+D) - click the pushpin to keep it open and not auto-hide

Datasources should be empty; click on “Add New Data Source…”
The “Data Source Configuration Wizard” opens
Highlight “Database” and click “Next”
Table1.mdb will be one of the selections, select it and click “Next”
In the dialog box, click “Yes” to copy the database to the project

You now have a Connection String. Check “Yes, save the connection as:”
(leave the default name for this exercise)
Click “Next”

“Choose Your Database Objects” window will appear showing tables and views from Table1 that can be selected.
Check “Tables” and click “Finish”

Make sure Form1 is shown next to “Data Sources”

Click “+” to expand Table1 definition
You’ll see a drop box for Table1, click the down arrow and select “Details”
Highlight the Key field, click down arrow and select “TextBox”
Highlight the TxnID field, click down arrow and select “TextBox”
Highlight the ProjectName field, click down arrow and select “TextBox”

Now you have a Data Connection to the Access Database, a dataset with a structure identical to the database structure and an empty form. Now for the good stuff:

Click the icon to the left of Table1 in DataSources and drag onto the form. A little CPU crunching and you now have Form1 with a navigation bar with 3 text boxes (complete with labels) that are bound to the data source. Push F5 and you can navigate through your data! Try adding data, deleting data.

Now that you’re all excited about it working, count the keystrokes used for creating the code to do this. Zero, Zip, Nada.

One last word: Beginners think running the app in debug mode saves data to the actual database. (I did) It doesn’t. Don’t be surprised. You’re working in test mode and usually that means you don’t want to change the database. It will all work once you publish it and install it.

[sharpy]

try this tut aswell


http://www.dreaminco...wtopic74114.htm

[AlexG788]

sharpy, on 29 Jan, 2009 - 08:43 AM, said:

try this tut aswell


http://www.dreaminco...wtopic74114.htm

Thanks! This worked perfectly!!

[sharpy]

No Problem

Its always nice to help