Solved

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

Posted on 2014-10-31
13
260 Views
Last Modified: 2014-11-03
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
0
Comment
Question by:bthouin
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 5

Expert Comment

by:ReneD100
ID: 40415612
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40415635
>>.... 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
 
LVL 1

Author Comment

by:bthouin
ID: 40415692
@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
 
LVL 5

Expert Comment

by:ReneD100
ID: 40415717
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
 
LVL 1

Author Comment

by:bthouin
ID: 40415857
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
 
LVL 5

Expert Comment

by:ReneD100
ID: 40415903
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:bthouin
ID: 40416113
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
 
LVL 1

Author Comment

by:bthouin
ID: 40416120
Ah, sorry, I did not see your post. OK, I'll try on Monday.
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 40416866
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
 
LVL 5

Expert Comment

by:ReneD100
ID: 40416904
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40416958
>>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
 
LVL 40
ID: 40417185
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
 
LVL 1

Author Comment

by:bthouin
ID: 40419156
@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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now