Post Revised: A Desperate Plea for Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone give me a thorough explanation on how to use my data adapters to
insert new records into a SQL database that I have connected to my app. I am
very new to SQL and a little familiar with VB. The purpose of the app is to
be a front-end to populate fields in a SQL table with information obtained
from a wedge scanner.

I have the the data objects created already in the form designer...here are
the objects as follows:

Adapters:
db_Adapter1 'for the header table, tbl_HDR
db_Adapter2 'for the detail table, tbl_DET

SQL Connector:
db_Conn

Dataset:
db_RS

Now, the adapters, I have noticed, contain command text for INSERT, UPDATE,
DELETE...problem is, I don't know the syntax for using those commands or how
to declare them in code to use them. I see repeated use for them
everywhere...EX: INSERT INTO mytable(mycolumn) VALUES(@myvalue)...how the
heck do you get to this point?. Does anyone know what I speak of? Utter
frustration here!
 
bill,

The data adapter's Update method handles all the Insert, Update and Delete
commands.

Just call the data adapter's Update method to save the data table's changes
to the database.

Kerry Moorman
 
Well, not quite. This will only work if you have configured your
DataAdapter using the DataAdapter Wizard and, even then, you may not get the
update you want.

What you need to do is write the correct UPDATE, DELETE and INSERT logic for
your DataAdapter's built-in UpdateCommand, DeleteCommand and InsterCommand
objects. You can set these statements as the proper statements to use via
the individual command's CommandText property.

But the bottom line here is that you need to create the SQL Statements for
these command yourself (in many cases) and so you need to know SQL.
 
Scott,

The original poster already stated that he had Inser, Update and Delete SQL:

"Now, the adapters, I have noticed, contain command text for INSERT, UPDATE,
DELETE."

His problem is that he does not seem to realize that calling the adapter's
Update method will in turn call these sql statements:

"problem is, I don't know the syntax for using those commands or how
to declare them in code to use them."

That's why my advice was to call the data adapter's Update method.

Kerry Moorman
 
If you need to control the order of the executions ( for instance delete
first, then insert, then update), you must use call the update three
times as follows (C# code):


dsUpdate = db_RS.GetChanges(DataRowState.Deleted);
db_Adapter1.Update(); // using DeleteCommand

dsUpdate = db_RS.GetChanges(DataRowState.Added);
db_Adapter1.Update(); // using InsertCommand

dsUpdate = db_RS.GetChanges(DataRowState.Modified);
db_Adapter1.Update(); // using UpdateCommand


Charles Zhang
SpeedyDB Technologies (SpeedyDB ADO.NET Provider)
http://www.speedydb.com
 
But, you miss my point...The generated SQL commands are often not
appropriate for the developer's actual needs. Since you like quotes, look
at what I wrote in my message:

"and, even then, you may not get the update you want."

In my experience, only the simplest of INSERT, UPDATE and DELETE's can be
accomplished with the generated code. In most cases the generated code does
not prevent concurrency issues.
 
Scott,

I got your point that generated sql is often inadequate. Certaninly that
generated by MS tools such as the commandbuilder.

But you miss my point. If someone has already got generated sql but doesn't
know how to get the data adapter to call it then the answer to "How do I call
the dataadapter's Insert, Update and Delete commands?" is not "Those
generated commands may be inadequate" but "Use the dataadapter's Update
method".

But who knows? Perhaps I have completely misinterpreted the original post.
That's been known to happen.

Kerry Moorman
 
My reply was to you and regarding your post, not the OP. I am simply adding
more information to compliment what you stated. I'm not saying your info.
wasn't correct, just that once the OP uses your info., there is a good
chance that the generated code won't be sufficient.

You've never added more info. to a post than what was originally asked or
commented on someone else's reply to a post?
 
Back
Top