Need to move data from GUI to Access Database.

[mechwarrior3]

For some reason, I feel like I have not found anything about how to do this in .Net. I initially wanted to import a CSV text file into Access using oleDb stuff. However, I am struggling with this and a co-worker suggested I try to simply take all of my variable values and directly turn them over to Access.

Okay, so my problem is this: I have a GUI that stores quite a lot of information (about 70 items). Okay, 70 items is a lot to me. In any case, I need this information placed in an Access database. This database already exists. How do I get this information (which is all stored as different variables of different types) into this Access database? Like I said, I tried to simply import a text file containing all of the information, but I can not seem to figure out how to do that either. Please help. If this question has been asked before and I did not look well enough to find it, I am very sorry. I do not wish to bog the forums down in redundancy. Any advice or suggestions would be greatly appreciated. Thank you, everyone.

 

[jmcilhinney]

You need to do some reading on ADO.NET, which is the standard data access technology in .NET apps. My signature has some tutorial links that include ADO.NET information. Do a memebr search for TechGnome too, as his signature has a couple of ADO.NET specific tutorial links.

 

[TechGnome]

Supplying a shortcut so you don't have to look them up... they are in my sig below.

Something to note: the turoials were done against SQL Server. Which means they use the SQLClient namespace. However, the process is generic enough that changing the SQL items to OLEDB should be a no brainer.


-tg

 

[mechwarrior3]

Alright. I figured that I had not seen enough yet. Thank you for the links. I'll be reading up on those and if I have anymore questions, I'll look to there first, then here after. Thanks again, guys.

 

[mechwarrior3]

Something Broke Again...

Hello everyone. I am back and with pretty much the same problem I've been having since the very beginning. I am trying to use ADO.NET to transfer this data, but it just won't work. I've read several ADO.NET tutorials (a couple provided by TechGnome, thank you for those. They have been very helpful but still my problem persists. )

So, what is going on. Well, I'm trying to update information in an Access database. It's not working. I keep getting an unhandled exception at the .Update line. Here is a shortened version of the code I'm using. All that is excluded are lots of DataRow column value assignments.

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbConnection(MYACCESSCONN)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand(strCommand)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessDataSet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.DataSet
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessAdapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter
[/SIZE]
[SIZE=2]AccessAdapter.SelectCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand("SELECT * FROM tbl_HCI_Lot_Inspection_Data", AccessConn)
AccessAdapter.InsertCommand = AccessCommand
AccessAdapter.InsertCommand.Connection = AccessConn
AccessAdapter.InsertCommand.Parameters.Add("@MfgNum", OleDb.OleDbType.LongVarChar, 11, "strLotNumber")
[/SIZE]...
[SIZE=2]AccessAdapter.Fill(AccessDataSet, "tbl_HCI_Lot_Inspection_Data")
AccessDataSet.Tables("tbl_HCI_Lot_Inspection_Data").PrimaryKey = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataColumn() _[/SIZE]
[SIZE=2]{AccessDataSet.Tables("tbl_HCI_Lot_Inspection_Data").Columns("strLotNumber"), _[/SIZE]
[SIZE=2]AccessDataSet.Tables("tbl_HCI_Lot_Inspection_Data").Columns("strMixID")}
[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessDataRow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow = AccessDataSet.Tables("tbl_HCI_Lot_Inspection_Data").NewRow()
AccessDataRow("strMixID") = txtMixLotNum.Text
AccessDataRow("strLotNumber") = txtMfgLotNum.Text
AccessDataRow("strPartNum") = txtPartNum.Text
[/SIZE]...
[SIZE=2]AccessDataSet.Tables("tbl_HCI_Lot_Inspection_Data").Rows.Add(AccessDataRow)
[/SIZE][SIZE=2]AccessAdapter.Update(AccessDataSet, "tbl_HCI_Lot_Inspection_Data")
[/SIZE][SIZE=2]AccessConn = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE]

MYACCESSCONN is the correct connection string for an Access database. Also, strcommand is a standard INSERT INTO SQL command of the form INSERT INTO <table> (<columns>) VALUES (<parameters>).
My code always generates an error at AccessAdapter.Update. I checked the table and saw that one column, which is an autonumber column, was considered a NULL value by .NET and so I deleted that column. Did not fix the problem. I tried sticking a command builder line right above the .Update line. That did not work.

Why is my code not working? Please help. Any assistance is greatly appreciated.

 

[TechGnome]

try this:

Try
    AccessAdapter.Update(AccessDataSet, "tbl_HCI_Lot_Inspection_Data")
Catch sqlEx As OLEDBException
  MessageBox.Show sqlEx.ToString
End Try

That will give you more specifics on what maybe going wrong. It'll make it easier to diagnose the true problem.

-tg

 

[mechwarrior3]

Wow. Thank you TG. That little piece of code has really helped figure out the problem. Well, one of them. Now, with this new piece of code, I'm finding out that there are several layers of problems here. Thank you for your help, TG.

I have encountered a new problem. I fixed one. I had mislabeled a couple column names. In any case, my new problem is that I continue to now get an error that reads, "Data type mismatch in criteria expression."

What is the criteria expression?

Also, I have one column in my table that is an Autonumber column. I do not explicitly set that value or manipulate that value in my code. Could this be the cause for my type mismatch?

 

[TechGnome]

Typicaly that means you've got something that is a string that is trying to act as a number or vice versa.... or a date that is being compared to a number or something like that .... on what line of code does this happen on?

as for the autonumber, no, leave it alone, the database will fill that in for you.

-tg

 

[mechwarrior3]

Okay.

Well, I do have one field that is a "yes/no" field in Access. How do I represent that in ADO.NET? I'm using Oledb.OledbType. Is it Boolean? Binary? Integer? Everything else I'm using are "Text" and "Number" fields in the database. I have those represented by "VarChar" and "Integer" in ADO.NET, respectively. Would those be the right match ups? Also, must the size in ADO.NET equal the size of the field in the database?

 

[TechGnome]

The yes/no would be a boolean.... the rest of your datatypes are correct.

-tg

 

[mechwarrior3]

Resolved

It all works! Thanks everyone for all of your help with this one.