Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to make a simple input from VB.net to MS access DB

Posted on 2006-04-11
22
Medium Priority
?
199 Views
Last Modified: 2010-04-23
Hello how is every one doing today?.

Well I just recently jumped into VB and as well as dealing with VB inserting data to an MS Access database.

The problem I have is this....

First and foremost I'm making a simple form which has 1 text box
and 1 button. I just Need to know how to insert the data to the database.

So far Im trying to code it manually instead of using the wizard and I can tell you where I'm stuck at.

I will show the code and then you can tell me what am I doing wrong or what step am I missing.

////////////////////////////////////////////////////////////////////////////////////////
Dim strConnectionString As String = _
        "Provider = Microsoft.Jet.OLEDB.4.0" & _
        "Data Source = C:\db2.mdb;"

        Dim objConnection As New OleDbConnection(strConnectionString)

        'oledbCommand to push SQL statement against the database
        Dim txtStr = txtName.Text   'This is the textbox which will receive the input from user
        Dim strSQL As String = _
        "Insert INTO Names(FName) values (' " & txtStr & " ')"
        Dim objCommand As New OleDbCommand(strSQL, objConnection)


        'Now to open a connection


        Try
            objConnection.Open()
            Debug.WriteLine(objConnection.State.ToString)
            objCommand.ExecuteScalar()
        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)

           
        End Try

///////////////////////////////////////////////////////////////////////////////////////////

As you can see the code is fairly simple since I'm just trying to see how I can insert data to access, I know I am probably missing a whole
lot of things, or maybe not, but If there is something that I am missing please let me know because I have been stuck on this simple insertion process for
quiet sometime now!! (Sorry big time Noob lol )

Also I wanted to know if the same process I'm doing could also be used to Update, select, and perhaps even delete.


Thankyou for your time guys!!

P.S: Thanks for helping this noob and perhaps other noobs out there :)


0
Comment
Question by:masterat03
  • 11
  • 5
  • 4
  • +1
22 Comments
 
LVL 4

Expert Comment

by:pbocanegra
ID: 16426732
Use objCommand.ExecuteNonQuery()
instead
Use objCommand.ExecuteScalar()

0
 

Author Comment

by:masterat03
ID: 16427114
I tried objCommand.ExecuteNonQuery()
and nothing happen, even though I inserted information in the textbox the database does not add the info. :(

0
 
LVL 4

Expert Comment

by:pbocanegra
ID: 16427148
CHeck the textbox properties. Check to true the "AUTOPOSTBACK" Property.

Into the event
    Private Sub Textbox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles textbox1.TextChanged
        'Insert your code here
    End Sub
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Expert Comment

by:LordWabbit
ID: 16427153
ExceuteScalar will return one value whereas ExceuteNonQuery simply executes the statement (although you can get the number of records affected as a return value).

Just for your info if you put a ' (single quote) into that text box of yours your program will fail as it breaks the SQL statement.  You can either do a Replace("'", "''") which is replace one single quote with two so that it wont break the SQL statement, or you can do something like this

Dim strSQL As String = "Insert INTO Names(FName) values (@Name)"
Dim objCommand As New OleDbCommand(strSQL, objConnection)
objCommand.Parameters.Add("@Name", txtStr)
objCommand.ExecuteNonQuery

Which IMHO is probably the best way to go about it.
0
 
LVL 6

Expert Comment

by:cubixSoftware
ID: 16427260
Hi

The following code will add records to the Access NorthWind database....

===================================================================

    Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Northwind.mdb"
    Dim myConnection As OleDbConnection = New OleDbConnection
    Dim insCmd As New OleDbCommand

       myConnection.ConnectionString = connString
        myConnection.Open()

        ' create insert command
        With insCmd
            .Connection = myConnection
            .CommandText = "Insert Into Customers (CustomerID, CompanyName) " & _
                                      "VALUES (@CustomerID, @CompanyName) "
            .CommandType = CommandType.Text
            .Parameters.Add(New OleDbParameter("@CustomerID", OleDbType.Char, 5))
            .Parameters.Add(New OleDbParameter("@CompanyName", OleDbType.Char, 40))
            ' -- now assign values
            .Parameters("@CustomerID").Value = "AQWSQ"
            .Parameters("@CompanyName").Value = "TEST COMPANY"
        End With

          ' update the database
        insCmd.ExecuteNonQuery()

        If myConnection.State <> ConnectionState.Closed Then myConnection.Close()

=====================================================================

HTH :)


0
 

Author Comment

by:masterat03
ID: 16427484
I wanted to know if I'm doing this correct or not, but I think I may have done a mistake BIG time. The code you see is under the submit button which I assume once a user presses the submit button the code will run upon pressed.

I could be wrong, but I dont know since im such a noob at this DB connection.

Also pbocanegra I try to find the "AUTOPOSTBACK" Property and couldnt find it, please elaborate a bit.

and Lordwabbit thankyou for explaining the part of ExecuteScalar and ExecuteNonQuery :).

Well here is the new modify code according to what you guys thought.

//////////////



Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click


        Dim strConnectionString As String = _
        "Provider = Microsoft.Jet.OLEDB.4.0" & _
        "Data Source = C:\db2.mdb;"

        Dim objConnection As New OleDbConnection(strConnectionString)

        'oledbCommand to push SQL statement against the database
        Dim txtStr = txtName.Text   'This is the textbox which will receive the input from user
        Dim strSQL As String = _
                      "Insert INTO Names(FName) values (@txtStr )"
        Dim objCommand As New OleDbCommand(strSQL, objConnection)
        objCommand.Parameters.Add("@Name", txtStr)
       

        'Now to open a connection


        Try ' The reason we use try is to see if the database can open and what can happen
            objConnection.Open()
            Debug.WriteLine(objConnection.State.ToString)
            objCommand.ExecuteNonQuery()
        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)
       


        End Try


//////////////////////////////////////
0
 

Author Comment

by:masterat03
ID: 16429001
Hey CubixSoftware the NorthWind DB example you gave me it works, but
if I try to modify it a bit...let say take a user input from a text box then the program breaks. :(

/////////////////////////

/////ERROR MESSAGE ( An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll )

//////NEW CODE/////


Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        Dim Name = txtName.Text

        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =C:\db2.mdb;"
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim insCmd As New OleDbCommand  ' Stores my command for the DB


        myConnection.ConnectionString = connString
        myConnection.Open()

        'Create an insert command NOW!!! :)

        With insCmd
            .Connection = myConnection
            .CommandText = "Insert INTO Names(FName) " & _
                        "VALUES (@FName) "

            .CommandType = CommandType.Text
            .Parameters.Add(New OleDbParameter("@FName", OleDbType.Char, 8))

            '--now assign values

            .Parameters("@FName").Value = Name

        End With

        'update the database
        insCmd.ExecuteNonQuery()

        If myConnection.State <> ConnectionState.Closed Then myConnection.Close()

End Sub
0
 
LVL 11

Expert Comment

by:LordWabbit
ID: 16430232
Mmm, the error message is not very helpful, which line did it fail on?  If it was the ExecuteNonQuery is it possible you are trying to add a value which is longer than 8 chars, try trimming eg. txtName.Text.Trim to remove any spaces.  But more off the wall would be checking that the access database file is not set to readonly (which depending on the OS could happen if copied from a  CD/DVD).  Can you query the database?  If all else fails try installing the lastest version of the Microsoft Data Access Components (MDAC) I think the latest version is 2.8 (but don't quote me on that) you may just have a set of corrupted drivers.  Can you create a DSN to the database (and test it)?  The list of possible issues goes on, a good look at the exception objects inner exceptions may provide an insight.
0
 
LVL 11

Expert Comment

by:LordWabbit
ID: 16430290
Oh yeah I forgot, having the database open in Access can (and frequently does) lock the database, not sure if this is the case but be sure nothing else is using the database when you run the insert.
0
 

Author Comment

by:masterat03
ID: 16430512
Ok this is a bit funny LordWabbit When I modified the NorthWind DB example I added
my own custom Form with 2 txtboxes and 1 submit button

Now check this out .
///////////////////////////Semi-Modified NorthWind/////////2 TextBox included/////////////

  Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Nwind.mdb;"
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim insCmd As New OleDbCommand
        Dim Name = txtName.Text    ' User can input ID or name etc *****CUSTOM TEXT BOX
        Dim Last = txtLast.Text         ' User can input last name or company name etc...you get the point lol *****CUSTOM TEXT BOX


        myConnection.ConnectionString = connString
        myConnection.Open()

        ' create insert command
        With insCmd
            .Connection = myConnection
            .CommandText = "Insert Into Customers (CustomerID, CompanyName) " & _
                                      "VALUES (@CustomerID, @CompanyName) "
            .CommandType = CommandType.Text
            .Parameters.Add(New OleDbParameter("@CustomerID", OleDbType.Char, 8))
            .Parameters.Add(New OleDbParameter("@CompanyName", OleDbType.Char, 40))
            ' -- now assign values
            .Parameters("@CustomerID").Value = Name        *****VALUE FROM THE TEXT BOX
            .Parameters("@CompanyName").Value = Last      *****VALUE FROM THE TEXT BOX
        End With

        ' update the database
        insCmd.ExecuteNonQuery()

        If myConnection.State <> ConnectionState.Closed Then myConnection.Close()

End Sub
////////////////////////

Now in my own code I pretty copy almost everything down to the teeth except of course the
name of the Fields and the name of the Table

Also to answer the other question you said about inserting data out of the range I haven't what so ever
sometimes I only put one key example (A or D or etc) and I still get my error.

The other question you ask about the database I have full read/write permission I just right click on the properties to see the security tab.

***sorry for so much trouble lol *** :)


0
 

Author Comment

by:masterat03
ID: 16430610
as for the error message yes it does fall in the line of
insCmd.ExecuteNonQuery()
0
 
LVL 11

Expert Comment

by:LordWabbit
ID: 16431892
Well I have to admit I am a bit stumped, the only thing I can think of is that the target database missing the required tables and / or columns as specified in the code.  Otherwise your code looks fine and should be working, could you show me a schema layout of the access database?
0
 

Author Comment

by:masterat03
ID: 16433086


How can I show you the schema?. its a noob question I know, but I try :).
If you want I can email you the access db file its really small in size being the fact that it is only
1 field name.

0
 
LVL 6

Accepted Solution

by:
cubixSoftware earned 2000 total points
ID: 16434148
Hi masterat03

Well I ran your code and the error you get is due to Syntax error in the INSERT INTO command. It appears that the table "Names" is conflicting with some special reserved word. I changed the code so that it is as below and it now inserts records without a problem.

 .CommandText = "Insert INTO [Names] (FName) " & _
                            "VALUES (@FName) "


HTH :)
0
 

Author Comment

by:masterat03
ID: 16435852
WOW finally lol, lol sorry I can't stop laughing but you know when you finally get something solve that great feeling you get within you, kinda like jedi force powers or something.

I would like to know where can I get perhaps a list of reserved words like that I don't encounter a problem of that nature again, as you know this for me was a small practice on what I really need to do on my real database. I decided to use just 1 text box and 1 field name to see if I can learn how to do a simple insertion of data, I have read a few books , but most of them have datagrids, dataset, Visual studio wizards which makes my understanding a bit complicated because I just need to do 1 simple thing before I move on ahead with the other components of OLEDB.

I wanted to know also is it possible to use the Visual studio wizard in the future to update/delete/insert data using Access database file....I heard it is mostly done for SQL SERVER DB...then again I could be wrong.

Also I would like to know if it is possible to use the same kind of structure code for updating and deleting and perhaps even searching.
I'm assuming it may work for all as long as the SQL code does what it say it suppose to do correct?....

Thankyou guys sooooooo much for your help, I'm sure I will stick around when I get these headache lol

Thankyou  CubixSoftware,LordWabbit, you guys are truly on top of your game!!

0
 
LVL 6

Expert Comment

by:cubixSoftware
ID: 16436021
If I had to give you one bit of advice, learn how to use datasets. You may think this is overkill for your simple database but it will give you an architecture that allows you to easily do insert, update, deletes, searching and databinding.

Remember you will not be working on simple databases forever!

0
 

Author Comment

by:masterat03
ID: 16436129
Last question I have regarding all of this. I just need to know is there a way to confirm data was indeed enter without checking the Access file
itself...perhaps an IF statement.
0
 

Author Comment

by:masterat03
ID: 16436143
I know as I learn more I will work on more complex databases in the future...
I thought dataset was only to display current data in a table from your database to a GUI
0
 
LVL 6

Expert Comment

by:cubixSoftware
ID: 16436214
As regards confirming data then you could do the following...


dim recordsAfftected as integer

recordsAffected = insCmd.ExecuteNonQuery()

Messagebox.Show("I inserted " & recordsAffected & " records(s) in the database")

The ExecuteNonQuery returns how many records it affected - for inserts typically 1 but if you did UPDATE or DELETE then obviously the number of records could be any number.

-----

Datasets - they can handle everything and are very powerful.


0
 
LVL 6

Expert Comment

by:cubixSoftware
ID: 16443519
masterat03  -  don't forget to close this question if we have answered your issue.
0
 

Author Comment

by:masterat03
ID: 16481040
Sorry I haven't closed it.

I just recently took your advice on Datasets and well I tried something but unfortunatly I'm having errors again..(whats new lol)

Anyhow look at this code it looks very similar to the last one, but with a few differences.

This time I'm trying to have the user input something in the text box and then when they press the search button
I tried to apply the information to a DataGrid.

The error Im getting is this ///// * An unhandled exception of type 'System.NullReferenceException' occurred in AlphaDesign.exe

Additional information: Object reference not set to an instance of an object.*///////


The code is this :





        If txtUserID.Text = "" Then
            MessageBox.Show("User ID Text Box is Empty!!", "Error", MessageBoxButtons.OK _
           , MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
            lblUserIDError.Visible = True

        Else
            lblUserIDError.Visible = False

        End If


        '/////////////////////////////Here we will now try to FIND first the USer

        Dim connString As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = X:\members.mdb;"
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim insertCmd As OleDbCommand ' This stores my Command for the DB


        Dim UserID = txtUserID.Text.Trim

        'Opening the DB connection
        myConnection.ConnectionString = connString
        myConnection.Open()

        'NOw to create a Select Command to search the DB
        'Trying to put a search command with the user input from the text box

        With insertCmd
            .Connection = myConnection                   //////****This is where the error occurs/////
            .CommandText = "Select User_ID From UserTable" & _
                           "Where User_ID = UserID "
            .CommandType = CommandType.Text
           
            .Parameters("@User_ID").Value = UserID  ' This is the textBox.



        End With

        'Now to declare a OLEDBDataAdapter object
        Dim Adapter As New OleDbDataAdapter

        'Now to declare a dataset
        Dim DataSet As New DataSet

        'Now to apply my Command to the DataAdapter
        Adapter.SelectCommand = insertCmd




        Try ' The reason we use try is to see if the database can open and what can happen

            insertCmd.ExecuteNonQuery()
            Adapter.Fill(DataSet)

            'To bind the Dataset to the DataGrid :)
            grdResults.DataSource = DataSet

            'Tell the DataGrid which table in the Dataset to use
            grdResults.DataMember = DataSet.Tables(0).TableName


            'Setting the Alternating Colors property to the Grid
            grdResults.AlternatingBackColor = Color.WhiteSmoke

            'Set the GridLineStyle Property :)
            grdResults.GridLineStyle = DataGridLineStyle.None

            'Set the SelectionBackColor and the Selection ForeColor Properties
            grdResults.SelectionBackColor = Color.LightGray
            grdResults.SelectionForeColor = Color.Black

           
        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)



        End Try


        'Cleaning up the Mess
        insertCmd.Dispose()
        insertCmd = Nothing
        Adapter.Dispose()
        Adapter = Nothing
        DataSet.Dispose()
        DataSet = Nothing

        myConnection.Dispose()
        myConnection = Nothing
        myConnection.Close()

///////////////////////////////////////////////////////////


Also I just wanted to know if my SQL is good or is that also messed up  :(

Sorry if I didnt make a new topic for this situation, being that they are almost similar I don't want to take up too much space.

Thankyou in advance as always and please let me know what you guys think.


0
 

Author Comment

by:masterat03
ID: 16481425
I'm Sorry I will open up a new post I remember you guys need to earn points I will give you a good review CubixSoftware since you did help me alot.
Well if you can help me out in the new post when you get to see it .

Take care
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

581 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