How is everyone 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 coded 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)

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

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


        End Try

        Dim txtStr = txtName.Text
        Dim strSQL As String = _
        "Insert INTO db2.Names( First) values (txtStr)"
        Dim objCommand As New OleDbCommand(strSQL, objConnection)

'* From here Im stuck and I dont know what else to do *'

As you can see by the code its very simple to follow, but I m having such a hard time just to insert 1 simple input...then again what is simple to some is hard for others LOL

Well if anyone can lead the correct way please let me know.

Thank you in advance.

From : theNewVB Noob :)

**Update :) **

I just wanted to say i have seen many examples of datagrids , I just wanted to say that I don't need to see the data for now at least, I just need a simple way of inserting data to that simple form for a very simple Access data base.

**Update :) **

Also I just wanted to know the steps I took to get where I got stuck on is it the same for Updating and Searching within the Access Database.

Thanks in advance and again any advice I can get I would appreciate it as well as perhaps other noobs reading this.

hi there.. objcommand.executescalar will do the insert..

HTH

Gonzalo

Do you mean to use it with my SQL string.
Example objcommand.executescalar (strSQL, objConnection)
??

I'm sorry but I 'm a big noob when it comes to DB stuff.

hi there..

just look in the help objcommand.executescalar...

it dont need any parameter since it already have all the parameters it need.. it will return the numbers of affected rows...

HTH

Gonzalo

I'm sorry to bother again ,but I tried to use the method you told me within the try block and I still don't see anything happening.

Here I will post the new code block

///////////


 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.Messa ge)


        End Try

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

I double and triple check to see if the the database file and the column name are correct and yet im not able to insert data to the database.

I'm sure there has to be something im missing or a piece of code that I'm not aware of .

As you can see again my code is very simple and its not too hard to follow given the fact that its a small form adding just 1 value given by the user in the form text box.

If anyone could help me out I would definetly appreciate it, also thankyou for the executescalar method gbianchi .

HI THERE...

let's see..

"Insert INTO Names(FName) values (txtStr)"

your string is not rigth...

"INSERT INTO Names(FName) VALUES ('" & txtstr.text & "')"

since i presume txtstr is a textbox this string will do the insert.. but i dont understand why you are not receving an error... put one more catch that get generic exception to see if you have any one missing...

HTH

Gonzalo

Hey I was able to fix the first problem with the insertion, but now im having another small problem
Im trying to retrive data and then put it in a datagrid...but eventually the connection is going haywire and I also think my SQL code may also have something to do with it.

Tell me what you think of 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.Messa ge)



        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.

hi there...

also shouldn't be the parameter inside the SQl with @???

another question i have.. why use a command object if you are only filling a grid?? you should use only the dataset and dataadapter (disconected)... use the dataadapter to get to the db and the dataset to fill the grid..


HTH

Gonzalo