Insert in to Access DB Troubles

  • Thread starter Thread starter Justin Emlay
  • Start date Start date
J

Justin Emlay

Just getting into ado.net and already hit a snag. No problems when using a
datgrid however now that I've moved on to textboxes I cant insert.

My form has 5 textboxes, an adaptor/connection/dataset. I simply want to
fill these out and create a new record from them. I've simplified my code
(hardcoded) to at least test the insert statement:

OleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO
Employee([Number]) VALUES (55555)"
OleDbDataAdapter1.Update(DsEmployee1)

I set the Insert Command and I Update. Seems like I'm missing something?
This doesn't even look right. So then I tried:

OleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO
Employee([Number]) VALUES (55555)"
OleDbDataAdapter1.InsertCommand.UpdatedRowSource = UpdateRowSource.Both
OleDbDataAdapter1.Update(DsEmployee1)
DsEmployee1.AcceptChanges()

Nada. Help!

Thanks,
Justin
 
....And where are you modifying the DataSet? The update method takes the
data from your DataSet and updates it to the Database.
 
Modify or create? I dont have anything to modify I want to add a new record
to the DB. Do I need to insert my new record into the DS then update the DB
with the DS?

If so then how do I add information in to the DS? Say from 5 textboxes?

Thanks for your time Scott.



Scott M. said:
...And where are you modifying the DataSet? The update method takes the
data from your DataSet and updates it to the Database.


Justin Emlay said:
Just getting into ado.net and already hit a snag. No problems when
Click to expand...
using
a
datgrid however now that I've moved on to textboxes I cant insert.

My form has 5 textboxes, an adaptor/connection/dataset. I simply want to
fill these out and create a new record from them. I've simplified my code
(hardcoded) to at least test the insert statement:

OleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO
Employee([Number]) VALUES (55555)"
OleDbDataAdapter1.Update(DsEmployee1)

I set the Insert Command and I Update. Seems like I'm missing something?
This doesn't even look right. So then I tried:

OleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO
Employee([Number]) VALUES (55555)"
OleDbDataAdapter1.InsertCommand.UpdatedRowSource = UpdateRowSource.Both
OleDbDataAdapter1.Update(DsEmployee1)
DsEmployee1.AcceptChanges()

Nada. Help!

Thanks,
Justin
Click to expand...
Click to expand...
 
Hi Justin,

If you need to execute just a simple INSERT statement, then you need to use
OledbCommand or SqlCommand and call ExecuteNonQuery method to do this.
DataAdapter will transfer changes from the DataSet into the database and is
not designed to execute SQL statements separately. Another way is to add new
record to the DataSet and then call DataAdapter. Check next KB articles with
the examples for both ways

http://support.microsoft.com/default.aspx?scid=kb;en-us;301075&Product=adonet

http://support.microsoft.com/default.aspx?scid=kb;EN-US;301216
 
So I take it I have to push my 5 textboxes into the dataset where I then
write the dataset to the DB.

How do I dump textboxes into a dataset?

Thanks, Val.


Val Mazur said:
Hi Justin,

If you need to execute just a simple INSERT statement, then you need to use
OledbCommand or SqlCommand and call ExecuteNonQuery method to do this.
DataAdapter will transfer changes from the DataSet into the database and is
not designed to execute SQL statements separately. Another way is to add new
record to the DataSet and then call DataAdapter. Check next KB articles with
the examples for both ways

http://support.microsoft.com/default.aspx?scid=kb;en-us;301075&Product=adonet

http://support.microsoft.com/default.aspx?scid=kb;EN-US;301216

--
Val Mazur
Microsoft MVP


Justin Emlay said:
Just getting into ado.net and already hit a snag. No problems when using
a
datgrid however now that I've moved on to textboxes I cant insert.

My form has 5 textboxes, an adaptor/connection/dataset. I simply want to
fill these out and create a new record from them. I've simplified my code
(hardcoded) to at least test the insert statement:

OleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO
Employee([Number]) VALUES (55555)"
OleDbDataAdapter1.Update(DsEmployee1)

I set the Insert Command and I Update. Seems like I'm missing something?
This doesn't even look right. So then I tried:

OleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO
Employee([Number]) VALUES (55555)"
OleDbDataAdapter1.InsertCommand.UpdatedRowSource = UpdateRowSource.Both
OleDbDataAdapter1.Update(DsEmployee1)
DsEmployee1.AcceptChanges()

Nada. Help!

Thanks,
Justin
Click to expand...
Click to expand...
 
A dataset is made up of dataTables. You need to add the data from your
texboxes to a dataTable that resides in a DataSet. You can then use the
DataAdapter's update method to transfer the data to the database.

Look at the links provided in the previous post on how datasets work.


Justin Emlay said:
So I take it I have to push my 5 textboxes into the dataset where I then
write the dataset to the DB.

How do I dump textboxes into a dataset?

Thanks, Val.


Val Mazur said:
Hi Justin,

If you need to execute just a simple INSERT statement, then you need to use
OledbCommand or SqlCommand and call ExecuteNonQuery method to do this.
DataAdapter will transfer changes from the DataSet into the database and is
not designed to execute SQL statements separately. Another way is to add new
record to the DataSet and then call DataAdapter. Check next KB articles with
the examples for both ways
Click to expand...
http://support.microsoft.com/default.aspx?scid=kb;en-us;301075&Product=adonet
http://support.microsoft.com/default.aspx?scid=kb;EN-US;301216

--
Val Mazur
Microsoft MVP


Justin Emlay said:
Just getting into ado.net and already hit a snag. No problems when using
a
datgrid however now that I've moved on to textboxes I cant insert.

My form has 5 textboxes, an adaptor/connection/dataset. I simply want to
fill these out and create a new record from them. I've simplified my code
(hardcoded) to at least test the insert statement:

OleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO
Employee([Number]) VALUES (55555)"
OleDbDataAdapter1.Update(DsEmployee1)

I set the Insert Command and I Update. Seems like I'm missing something?
This doesn't even look right. So then I tried:

OleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO
Employee([Number]) VALUES (55555)"
OleDbDataAdapter1.InsertCommand.UpdatedRowSource = UpdateRowSource.Both
OleDbDataAdapter1.Update(DsEmployee1)
DsEmployee1.AcceptChanges()

Nada. Help!

Thanks,
Justin
Click to expand...
Click to expand...
Click to expand...
 
I did and it only tells me how to fill a dataset directly from another
table.

So I googled "textbox dataset" and found the following:

Dim myrow As DataRow
myrow = DsEmployee1.Tables("Employee").NewRow
'One per field
myrow("Number") = "55555"
DsEmployee1.Tables("Employee").Rows.Add(myrow)
OleDbDataAdapter1.Update(DsEmployee1, "Employee")

Now this works. Is this the best method? I should "DsEmployee1.Clear()"
each I use this method right?

Thanks for all the help guys!
 
The are many variations on how to do this but this is essentially how it is
done. You won't need to clear the dataset because after the page finishes
processing, all objects are destroyed. The next time the page is called,
the objects will be created again from scratch.
 
Back
Top