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

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 :)


masterat03Asked:
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.

pbocanegraCommented:
Use objCommand.ExecuteNonQuery()
instead
Use objCommand.ExecuteScalar()

0
masterat03Author Commented:
I tried objCommand.ExecuteNonQuery()
and nothing happen, even though I inserted information in the textbox the database does not add the info. :(

0
pbocanegraCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

LordWabbitCommented:
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
cubixSoftwareCommented:
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
masterat03Author Commented:
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
masterat03Author Commented:
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
LordWabbitCommented:
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
LordWabbitCommented:
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
masterat03Author Commented:
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
masterat03Author Commented:
as for the error message yes it does fall in the line of
insCmd.ExecuteNonQuery()
0
LordWabbitCommented:
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
masterat03Author Commented:


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
cubixSoftwareCommented:
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

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
masterat03Author Commented:
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
cubixSoftwareCommented:
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
masterat03Author Commented:
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
masterat03Author Commented:
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
cubixSoftwareCommented:
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
cubixSoftwareCommented:
masterat03  -  don't forget to close this question if we have answered your issue.
0
masterat03Author Commented:
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
masterat03Author Commented:
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
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
Visual Basic.NET

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.