Question

VS 2005. Windows Forms. What is the VB syntax for executing a Dataset query?

Asked by: FJRMill

Hi Experts.

New to Visual Studio & Windows Forms ..  I hope that this is a really simple question ..

I have a form.  On the form I have a dataset linked to tables in an Access database.
To the dataset, I have added an insert query.

In the code behind the form, please could you describe the syntax I will need to use to populate the variables of the dataset.insert query and add a new record to my database table?

Many thanks
Mill

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2008-01-14 at 14:18:48ID23082359
Tags

windows

,

syntax

,

vb

Topics

Visual Studio

,

Microsoft Visual Basic.Net

Participating Experts
1
Points
500
Comments
9

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Populating a dataset with a stored procedure
    I'm pretty of new to ado.net and easily add tables to a dataset and modify the sql behind them. However, I'd like to populate a dataset (or datareader? recommendations?) for a visual studio .net web app that I just made. I plan to use the dataset to populate a crystal repo...
  2. To dataset or not to dataset
    Platform: Win2K/XP/2K3, VB.Net 2003 w/Visual Studio I have been tasked with writing a parsing engine for a pipe-delimited text file. The data in this file must be extracted and then converted to an XML format. My first idea is to get the data out of the text file, create a...
  3. Bind a dataset to a datagridview
    We recently purchased and started using Visual Studio 2005. We are trying to create an application in Visual Basic that has a datagrid which displays data from a SQL select statement (dataset). In Visual Studio 2003 the code below works fine, with a datagrid (in 2003 it's c...
  4. custom dataset for CrystalReport in Visual Studio 2005
    Greeting, I used Visual Studio 2005 to create a web application. I want to use my own dataset instead of those provided by CrystalReport. But the report is still using the auto-bind data from the report. It doesn't take my dataset(ds). PLease see my code below and let me kno...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: SanclerPosted on 2008-01-14 at 15:21:44ID: 20658202

The "default" way of passing data between an Access database and a VB.NET application is with an OleDbDataAdapter.  If you used the wizards to set up your application Dataset with reference to the Access database, those dataadapter/s may be "wrapped" in TableAdapter/s.

Both dataadapters and tableadapters have .Update methods.  If those adapters have ben properly set up, any new record that you insert into your application will be passed back as an Insert to the database by executing that .Update method.  Here's a link to a thread that says - post near the end - #17871205 - a bit more about that, although with specific reference to a DataAdapter.

    http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_22048420.html

There are alternative methods.  For instance, you can self-code an OleDbCommand and call .ExecuteNonQuery on that.  And, within that, you can either code the whole thing as one statement yourself or (usually preferable if you are going down that route) code it with parameters and put the values that you want to be inserted into those parameters.

I appreciate that none of the above answers your specific question.  But it might explain why I am not sure that there is a single answer to what "syntax [you] will need to use".  It really depends on details of your set up that your description, so far, does not make clear.

If what's said about is not enough to get you up and running, can you please come back and say, in a bit more detail, what that set-up is?  How did you set up the "dataset linked to tables in an Access database"?  How do you fill that dataset?  If you are using a self-coded Command to do the insert, what is its commandtext and does it have parameters?  Things like that.

Roger

 

by: FJRMillPosted on 2008-01-14 at 16:07:27ID: 20658729

Thanks Roger, I appreciate you taking the time to answer so fully.  

It's bedtime now where I am .. :)  so I'm going to have to sign off.  It may be 24 hours now before I'm able to properly review your advice and come back.  

Mill

 

by: FJRMillPosted on 2008-01-15 at 12:17:26ID: 20666065

Hi Roger,

I have had a good look throught the thread you linked for me and this was a helpful start. Thanks.

I think if I give you more info about what I am trying to achieve, there’s more chance of a good solution for me :)


I’m building a small app to:

Read in some .txt log files routinely output from another system;
Sort the row contents into fields;
Write to appropriate tables in my database;
accept user criteria settings and build SQL for display and reporting.


I thought of using a SQL Express database but after investing in William Vaughn’s Hitchhiker’s Guide to Visual Studio and SQL Server (7th), whilst I am sure that this book is very good, I have now decided that my app is a tad too simple to warrant all of the reading, + security & setup work that this would entail. (when it’s ready, want to make my app available for use by a wide range of users ). So I have now decided to run with an Access datastore, making use of OleDB.  I appreciate that Access has some significant limitations and is frowned upon by many professionals – but it has aways served my needs.


My code can already:
1. locate the text files to be imported;

2. Read in the data from the files
(using my.computer.FileSystem.ReadAllText() ..
and fileContents.Split(vbCrLf)  )

3. Break each line of data into a collection of variables (I’ve tested this by writing the variables into a DataTable and using this to populate and display a datagrid.)


I am stumbling over the syntax to get this data into my database table..


I’d be really grateful for a steer on this ..

Many thanks

Mill

 

by: SanclerPosted on 2008-01-15 at 12:55:33ID: 20666449

I'm tied up this evening.  I'll respond tomorrow.

Roger

 

by: FJRMillPosted on 2008-01-15 at 13:07:22ID: 20666603

Many thanks Roger ..  I'll check in again tomorrow

Mill

 

by: SanclerPosted on 2008-01-16 at 07:36:11ID: 20672720

It's still not clear to me which way to steer you.  Taking your last post's description on its own - that you are just looking for "syntax" to put your new records in the database - it might seem that the creation of a OleDbCommand to be used with .ExecuteNonQuery is the way to go.  I anticipate, however, that you will also - at some point in or connected with this app - be wanting to read the data from the database into it.  That suggests a dataadapter and, given that a dataadapter brings with it its own method of inserting new data into the database, perhaps would make use of that, even for the specific purpose you describe, the most sensible course.

To cut throught the Gordian Knot, can I suggest the following approach.  In an Access database create a table called "TestTable" and put in it a few fields making sure that one of them is nominated as a PrimaryKey: I usually use an AutoNumber field for that, even if I do not need it for any other purpose.

Now Open a VB.NET project and on the default form put two buttons and this code.

Imports System.Data.OleDb

Public Class Form1
    Private con As OleDbConnection
    Private da As OleDbDataAdapter
    Private dt As New DataTable
    Private cb As OleDbCommandBuilder

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\test.mdb" 'substitute your own database path for C:\Test\test.mdb
        con = New OleDbConnection(conString)
        Dim sql As String = "SELECT * FROM TestTable"
        da = New OleDbDataAdapter(sql, con)
        cb = New OleDbCommandBuilder(da)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'some code to create new records and put them in the datatable - dt - equivalent to step 3 in your post ...
        '... DO NOT include any value for the Primary Key field, Access will be filling that with AutoIncrement.
        '... then ...
        da.Update(dt)
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim cmd As OleDbCommand = cb.GetInsertCommand
        Debug.WriteLine(cmd.CommandText)
        For Each p As OleDbParameter In cmd.Parameters
            Debug.WriteLine(p.ParameterName)
            'and such other properties as you are interested in - e.g. SourceColumn, DataType, whatever - the list will appear in intellisense as you type the code)
        Next
    End Sub
End Class

Now, if you run that project (amplifying/modifying the code as necessary in the light of the comments it contains) and click on button1 you will find that the records you have created in the app get inserted into the database table.  And this, provided the datatable has already been created in Access and has a Primary Key, will happen even if, before you run the app, there were NO records in the database table at all.

If you want to see the "syntax" for an insert command, click button2 and it will show the one that the dataadapter's .Update method executes when a record needs to be inserted.  What the dataadapter does is get the values to give to each parameter from each new row in the app's datatable and itself, in effect, calls .ExecuteNonQuery on that command for each row.  If you want to do it yourself, rather than using a dataadapter with a commandbuilder, you will need to write a similar command text and parameter collection and then have code to cycle through the rows collecting the values from the relevant fields and calling .ExecuteNonQuery on the command for each row.  Like

    For Each dr As DataRow In myTable.Rows
        myCommand.Parameters("@1").Value = dr("ThisColumn")
        myCommand.Parameters("@2").Value = dr("ThisColumn")
        myCommand.ExecuteNonQuery
    Next

What I'm suggesting is a learning process, rather than actually an answer, but I think it might help us on the way to getting you going.  If you like what you see, you will need to adapt it to your specific needs.  But at least it might give a clearer idea of what's available and help you to decide what route you want to go down.

If, when you've tried that, you have specific questions/problems, I'll be happy to try and help with them.

Roger

 

by: FJRMillPosted on 2008-01-16 at 13:12:14ID: 20676191

Roger - you're a star!

As you quite righly suggested, this is a learning process...   working .. I may be a little while!  :)

Mill

 

by: FJRMillPosted on 2008-01-17 at 12:15:25ID: 20684891

Thank you so much Roger .. exactly what I needed - and some!  

I have achieved my aim for the app to populate a table in Access.  In the end, I used your final option (myCommand.Parameters) and I'm now able to move on towards the next problem ..(yet to manifest itself:).

When running my code, I found that it only worked without errors when I started with:   MyCommand.Parameters (0).Value = MyValue

I learned a good lesson here, I wish I could pass over more than 500 points (+ some beers!)


As an aside .. How was my decision to run with Access instead of SQL Server Express?    If I had persevered, might I have been able to roll out a small app to use SQL Server - but without users having to configure SQL Server on their machines?  Just interested ..


Bye for now

Mill



 

by: SanclerPosted on 2008-01-17 at 15:41:07ID: 20686728

Sorry, I can't answer the supplementary.  If you look at my profile you'll see I'm a hobbyist programmer.  Although I've got various flavours of SQL databases installed on my own set up, I've never actually rolled out an app based on one.  So I know no more about what would need configuring on an installation than you do.

Thanks for the points and the kind comments.

Roger

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...