Insert in to Access DB Troubles

[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

 

[Scott M.]

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

 

[Justin Emlay]

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.

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

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

 

[Val Mazur]

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

 

[Justin Emlay]

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.

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

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

 

[Scott M.]

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.

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.

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

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

 

[Justin Emlay]

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!

 

[Scott M.]

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.