I am trying to create a new employee application that gets input from a user in text boxes and then take that information and populate a database in Access that will later be pulled to the Track IT! database on SQLserver2005. How do i do it
Thank you.
there are a few ways of doing this but the easiest way probably would be to use a datagridview and dataAdapters.
the datagridview is a control which allows you to databind values to it, and you can view/delete/edit/add records to the control (similar type of layout as when you view a Table in MS Access)
using a DataAdapter allows you to easily "fill" a dataset with the query given and being able to allow you to also update the datasource (database). There are many topics about this on these forums, if you do a forum search you will find some hits and a good few examples :-)
Typical example:
we have an MS Access database/file. We want to get all the records from a specific table and show it to the user.
import the System.Data.OleDb namespace at the top of your class file:
imports System.Data.OleDb
drag and drop a datagridview on the form in designer view. Change the properties of it if you like to whatever you need it to be changed to.
next up, say if we have a button which goes and "fetches" the records, we need to code on how to do this. So, there are a couple of things that need to be done.
Create an OleDbCommand and OleDbDataAdapter as well as the dataset (which contains our records) globally so you can access these from any where within the current class:
Dim theOleDbCommand as new OleDbCommand()
Dim theOleDbDataAdapter as new OleDbDataAdapter(theOleDbCommand)
Dim theDataSet as new DataSet()
Now, double click on the button to create a button click event so on this we would code on retrieving the records. The code would look something like this:
Me.theOleDbCommand = new OleDbCommand("SELECT * FROM table", new OleDbConnection(ConnectionString)) Me.theOleDbDataAdapter = new OleDbDataAdapter(Me.theOleDbCommand) Me.theDataSet = new DataSet() Me.theOleDbCommand.Connection.Open() Me.theOleDbDataAdapter.Fill(Me.theDataSet) Me.theOleDbCommand.Connection.Close() Me.theDataGridView.DataSource = Me.theDataSet.Tables(0).DefaultView |
the connection string looks like this, but you need to modify it appropriately (Changing the path to the file for example)
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\somepath\mydb.mdb;User Id=;Password=;"
So whats happening above is, we create a query (SELECT * FROM table) to be executed, we then create a dataAdapter (which does all the hard work for us) and "fill" the dataset with records returned from that query.
Next we then "bind" the dataset (which has the records) to the datagridview control so the users can see the results.
That's all there is to it for this part!
The user can update the records if they like from the datagridview, the changes then are committed to the dataset so when we do an Update() on the data adapter, the changes are then committed to the database itself. The code would go something like this, but not guarenteed to work as I am writing from mind.
Place an "Update" button (drag and drop button on form and double click it) and then do this:
Me.theOleDbCommand.Connection.Open()
Me.theOleDbDataAdapter.Update(Me.theDataSet)
Me.theOleDbCommand.Connection.Close()
this should then update the database. Of course be sure to catch errors etc... but for now, lets just think there are no errors just to make it simple.
As for inserting the data from the textboxes to the database, the above solution would be best simply because you don't need to worry about what fields exist in the database so you can generate the INSERT command (as you need to know the fields to insert the data to)
However if you need to then the above still applies but with a couple of changes. No need for the data adapter or the dataset.
'Insert record Me.theOleDbCommand = new OleDbCommand("INSERT INTO [tableName] (field1, field2) VALUES ( , )") Me.theOleDbCommand.Parameters.Add(new OleDbParameter("@p1", OleDbType.fieldType).Value = Me.theTextBox1.Text)) Me.theOleDbCommand.Parameters.Add(new OleDbParameter("@p2", OleDbType.fieldType).Value = Me.theTextBox2.Text)) Me.theOleDbCommand.Connection.Open() Me.theOleDbCommand.ExecuteNonQuery() Me.theOleDbCommand.Connection.Close() |
Now, we create an insert command to insert the data/record. We need to know the field names and then supply it the "parameters" or values to insert into those fields.
We then create a parameter and add it into the OleDbCommand object, so when it executes the command it can take those parameters and insert them correctly.
You also generally need to know about the data field type of that fields (if its an Integer, NvarChar, Binary etc...) as otherwise the insertion may fail.
The values are then given for that parameter with the values entered from the textbox. The code above is generally how it works but not guarenteed to work 100%
does this help/shed some light
This helps... but i still dont fully understand it :(... I like the datagridview way better... but seeing as how many users will be using it i dont think that my boss will like it so much... unfortunately... I am going to do it both ways and see which he likes better... however, what i dont understand is: (i am only going to be asking questions from the insert into the database from textbox b/c i am pretty sure thats how he wants it)
A) how does the OLEdbCommand work... (sorry i am just a student trying to do a proffessional's job :( )
B) all the code is done on the submit button right we dont need to establish the connection on the form load
C) what is an nvarchar :(
I think i am going to leave it at that for now... and there is a 99% chance i will be asking more questions from your reply and i will come up with more questions as i am trying to code this.
a) the OleDbCommand is a command object in order for you to do some things with the database, such as executing queries (insert/delete/select/delete). Here is some documentation about it:
http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbcommand.aspx
b) the code is up to you where you are wanting to place it - it depends what you want to do really and what the "code" does. In my first example I am selecting all records from a table in the database so it would be good to place it on a button. 99% of the time, its best not to instantly connect to the database to do your task - slow start ups, establishing connections to databases and doing database related stuff can be expensive so its best to do it "on demand" meaning, when the user likes to - generally this is the case.
inserting data would be the same thing also - on a button for example, or perhaps everytime the user enters details/modifies details in the datagridview - it depends on the code you have. When you modify data in the datagridview, it automatically updates the dataset its bound to then to finally commit changes to the datasource (database) then you call the Update(theDataSet) command of the dataAdapter (this is an example)
if you are inserting data say from textboxes into the database, then yes, it should be done on the click of a button (example in my second example). There are many ways of going about doing this, and depends on what is happening and what you want to achieve :-)
c) Nvarchar is a database data type - basically in a sense, a string. Read about data types:
http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbtype.aspx
I hope this helps you in some way or gives you some more/better understanding
Me.theOleDbCommand = new OleDbCommand("INSERT INTO [tableName] (field1, field2) VALUES ( , )")
Me.theOleDbCommand.Parameters.Add(new OleDbParameter("@p1", OleDbType.fieldType).Value = Me.theTextBox1.Text))
Me.theOleDbCommand.Parameters.Add(new OleDbParameter("@p2", OleDbType.fieldType).Value = Me.theTextBox2.Text))
a couple questions about this part of the code.
A) i realize the [tablename] is the name of the database... my question is with the fields and values... its like normal arguments do they line up
For example
Me.theoledbcommand = new oledbcommand("INSERT INTO [newemployee.db] (date, employee name) VALUES (9/24/06, IGiberson)
B) can you explain the lines under that to me or point me in the direction that i can read about what that is exactly and how it works :(
a) absolutely I believe
b) the lines for the code sure:
giving parameters is safer than say giving the values within the INSERT statement otherwise if you do, you may have some "injection" attacks - its a security thing but this is the way to go really. Let's not make it over complicated, hopefully! :-)
in order to give values to the command, you need to add parameters to the OleDbCommand, which is what this is doing, giving the parameters and its values so when executed, the database will take it in, take those parameters and command and execute it on itself and report back the results to the caller (your app)
does this help
correct. Basically if you have 5 fields in a table, then you need 5 parameters but it depends - if some of the fields are left to be nullable, in other words data in that field is not required, then you only need as many parameters as you like.
so if i had 5 fields and all of them were set to be do not allow nulls (or whatever the MS Access equivelent is of this) then I would have to put in 5 parameters with values
if I had 5 fields but only 2 of them were set to allow nulls, then I can put in either 5 parameters with values or 3 parameters with values and leave the other 2 as they are not required - depends on how you created the database (schema) :-)
IGiberson wrote:
Also, it is a 3 form application... with different information on all forms... would it be easier to populate the database each time they go through the form or should i just have it get all the info at the end and populate it then
it depends entirely on the database and how you want the application to run/how it should run. If the forms are for 3 different tables then I would do the operations on the table on each form, so if I had a form of "registration/customer details" and this was for a table called "Customers" then I would operate on the table in this form, same with the others with their own tables. :-)
i have to declare the oledbcommand as a variable dont i and would i do that just as a variable
pretty much yes - take a look at the link I supplied earlier about the OleDbCommand - there should be some examples but definately some explaination/documentation too! :-)