Equivalent in VB.net of VBA rs.Addnew, rs!Field1 = "Bla", rs!Field2 = "Blo", rs.Update ?

Hi

I'm converting an Access/VBA/SQLServer app to VB.Net/SQLServer, and in some cases, I have to add one row to existing tables.

In Access there is this very easy and elegant way to fill the fields and create the row by using (rs = recordset name):
rs.AddNew
   rs!Field1 = "Bl1"
   rs!Field2 = "Bl2"
   ....
   rs!Fieldn = "BLn"
rs.Update

which is highly readable and vastly superior to the messy:

"INSERT INTO myTable (field1, field2, ...., fieldn) VALUES ('Bla', 'Blo', ...., 'BLn')

especially when there are a lot of fields and the values are data in forms or from other tables, which leads to unwieldy, never ending concatenations of strings.

When searching for insert examples in .Net, I can only find the "ugly" INSERT INTO ... syntax.

Is there no equivalent to the Access syntax above ?

And I don't want to use a datatable when I have only one row to add, because it seems a huge waste to load the whole existing table in a datatable when only one row is added from time to time.

Thanks for help
Bernard
LVL 1
bthouinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ReneD100Commented:
Assuming you have a typed dataset you can use the structure to add a new row, without actually filling the dataset first, so
        Dim ta As New yourDS.myTableTableAdapter
        Dim dt As New yourDS.myTableDataTable
        Dim MyNewRow As yourDS.MyTableRow = dt.NewRow()
        MyNewRow.myfield = "myvalue"
        dt.AddMyTableRow(MyNewRow)
        ta.Update(dt)

Open in new window

As you see no need to have a huge waste to load the whole thing ;)
and you can use intellisense for the field names.
0
AndyAinscowFreelance programmer / ConsultantCommented:
>>.... and vastly superior to the messy:

Hmmm.  I hope you realise an INSERT INTO should use less resources (= faster) and if you use a stored procedure (for the INSERT INTO) you will still have the readability.  It can be argued that using a recordset.AddNew is inferior.
0
bthouinAuthor Commented:
@Rene
Sorry, don't get it, where do I set the physical table name ? I think you'll have to make a concrete example, I don't understand the first 2 Dim statements. The rest is clear.

@Andy
Point taken. But: a) I'm not depending on "faster", as I have very low volumes; b) Readability is for me more important than speed, because my software will be maintained by other people when I'm finished developing it; c) I have yet to learn about using stored procs, so for the moment I want to the work in the app to save development time. Surely, it's not optimal, but it's a question of priority of deliveries. SPs can be done in a second step.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

ReneD100Commented:
If you create a dataset in VB.net that links to your database, you can use server explorer to 'drag and drop' your tables in the dataset. That  creates the tableadapter and typed datasets automatically. Once you have those my dim statements make more sense.
You can also directly write the code, for example:
http://support.microsoft.com/kb/301216
but then you will not have the advantages of having a typed dataset.
0
bthouinAuthor Commented:
Hi Rene

Well, I didn't know about the drag and drop from the server explorer, so I actually loaded all my table with VB code in a dataset exactly like in the MS article you refer to. Still I don't really know what you meant with:

Dim ta As New yourDS.myTableTableAdapter

VB tells me that that Dim should be like this:

Dim ta As New SqlClient.SqlDataAdapter(cmd, connSQL)

This does not look like yours at all. The dataset is not used in there, and a command and the connection must be used.
connSQL is the name of the connection I have defined because of the load of my dataset. But cmd needs a string, do you mean if I set cmd = "MyTableName", then I can use your code to add a new row without loading the whole table in the dataset ?
0
ReneD100Commented:
Hmm nope. In VB.net go to add new item(shft-ctrl-a), select data and then dataset. You can then add items (tables, views, etc) from the database. Or right click and select add tableadapter.
Create a table adapter (you need to define your connection here).
Once you have done that you have a dataset and you can refer to datasetname.tableadaptername and the above code will work. I've also used Access before and it took me a while to get used to VB.net's way. But once you're used to it you'll find out it 's way more versatile.
0
bthouinAuthor Commented:
Nope, I don't succeed at all. I cannot define a datatable the way you mention it, neither do I see how to do a drag and drop from the SQL Server Explorer to a VB definition of a dataset. So I'm completely lost. Your syntax is nothing I can replicate.

Unless you can provide me with more details, I will have to bite the bullet and use the "ugly" INSERT INTO syntax.
0
bthouinAuthor Commented:
Ah, sorry, I did not see your post. OK, I'll try on Monday.
0
Jacques Bourgeois (James Burger)PresidentCommented:
First, forget about ReneD100 comments (sorry Rene), he did not understand you need. You are right in thinking that a DataTable is too much to insert only one record, and what he offers requires even more resources than a DataTable does (again, sorry Rene).

The way to do it when you work with only one record at a time is a simple Command object (SqlCommand if you are working with SQL Server) and... an "ugly" INSERT INTO. I concur with you, it is ugly. HTML and XML are also ugly. But we could not have the world we have now without them. And there are everywhere. There must be a reason why ugliness is everywhere, and I am not talking about what I see tonight in the street (Halloween today).

I spent many years with Access and its easy ways of generating tables and SQL commands. I still cannot understand why someone goes through the trouble of creating a SQL Server table through a CREATE TABLE command, when it is so more easy to do in the designer. But I came to understand the advantage of INSERT INTO, its siblings and stored procedures (give them a look, its worth the time).

One thing you have to be aware of. Microsoft Access is a closed system that works around and in itself.

.NET is built in a way that makes data operations similar between almost any database. The ugly INSERT is a standard. But knowing that if you had taken the habit of working with that standard in Access (you can, through the SQL view of the Query Designer), you code would port almost directly to SQL Server, or to Oracle, or to DB2, or to MySQL, or to YouNameIt, you would accept ugliness more easily.

I have a couple of databases that made it from VIP Database, to Alpha Four (raise your hands, those of you who know about these), to DBase, to Access and finally to SQL Server since the 1980's. Each of these, except for SQL Server, had wonderful ways to make you forget about "ugly". But each of these required ugly ways and a complete rewrite of the applications when moving from one database to the next. Hey, during that time, we went from vinyl records, to cassette tapes, to CDs, to DVDs, to Blu-Ray, with some .mp3 and .mp4 thrown in (they also were a pain to convert from one format to the other). I feel confident that my last ugly INSERT INTO will convert quite easily to UltimateDatabase if it ever become available.

I am ready to accept the ugliness of standards such as SQL so that future conversions won't be such a pain in the you know what as the previous ones have been.

A closed system is always easier to work with and update... as long as you can stick with it. And I do not know of anything in computing that you can stick with for more than a few years. So be prepared. INSERT yourself INTO the best way to work for the future.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ReneD100Commented:
Jacques, I agree with your ideas and view, and I would most likely also use the direct parameterized INSERT statement.
However, the original question was "Is there no equivalent to the Access syntax above" which is what I provided.
If I want to paint my house red and go to the store to ask which paint is the best to use, I can appreciate a salesperson telling me why I would be better of using a shade of white, but that still does not answer what red paint to use...
0
AndyAinscowFreelance programmer / ConsultantCommented:
>>I will have to bite the bullet and use the "ugly" INSERT INTO syntax.

There is nothing to stop you making a class for each recordset to hide the ugly details.
some air code:
class RS_Table1
{
  public RS_Table1(provide connection details here);
  public int ID = 42;  //default of 42
  public string string1;
  publc string string2 = "hello world";
  public bool Update()
    {  put the details into an INSERT INTO and run in this function }
}

and then
RS_table1 rs = new RS_Table1(connection);  //New record, not yet added to table
rs.string1 = "some value";
rs.string2 = "not the default value";
rs.Update();  //write to table
0
Jacques Bourgeois (James Burger)PresidentCommented:
I answered your question indirectly. Access is dedicated to Access. .NET is a generalist that can work with any database. Thus, it cannot have the equivalent of what you had in Access.

You can always explore the concept of data entities, which is a concept that lets you go around the SQL by hiding it from you, but it adds overhead, and this is what you wanted to do first noticing that a DataTable is too much for simple inserts.
0
bthouinAuthor Commented:
@Jacques
Thanks again a LOT for your clear explanations. OK, I will use the "ugly" INSERT (which I have done anyway because of not having a better answer), and yes, I surely will look into stored procs (which is most surely the best answer anyway). And yes, I realize all the advantages of a self-contained sytsem such as Access compared to generic stuff which has to work in many different languages. I just thought that there *might* be another syntax in this particular case. So many methods in .Net have so many different syntaxes (sometimes way too many it seems when you look at them for the 1st time) that I hoped that somebody had taken that particular problem to heart... without adding too much overhead :-). But, OK, never mind, now at least I know what I have to cope with.

@Andy
I have already located many of my insert statements in subroutines anyway, because that's also much easier to handle it that way in Access, so the "ugly" INSERTs will only be few and far between. I could also re-use a technique which I used loooong time ago when I was working with Gupta's SAL language (raise your hands,those of you who know about this one), where I had developed a generic routine which would take a command (INSERT, SELECT, UPDATE, DELETE), an optional list of fields, an optional list of values, an optional WHERE clause, and an optional ORDER BY clause, and it would build the SQL statement from that. That was quite handy. I was using SQLBase as a DB, and that did not support stored procs.

I'll be back soon anyway, I have more basic questions about .Net.
Bernard
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.