VB 2008 w/Access 07: How do I connect, select, insert & update? 2

[Christineeve]

Hi,
My experience is with VBA and MS Access.

I'm new to using Table Adapters in VB to consume data in Access. I'm trying to put all this new information in my head so I can move forward to the new technology. I cannot seem get data back from my Select Statement, Insert a row, or update a row I've hard coded.

I think if I could see how it would work in my own code, I could figure out how it works in examples and move forward.

Your help is very much appreciated.

The select statement passes False instead of passing the keyed variable "Christine.Lee".

I have no idea how to do an Insert with this new information.

The Update statement doesn't change keyed variable "Christine.Lee" to the name of the PC from the code.

So, nothing works.

Here's my code:

Private Sub SelectedMachineName()
'This is a test procedure to understand how to take a variable from our system and manipulate it.

'This procedure extracts the name of the client workstation and updates the table in our Access Database.

        Dim CurMachineName

        Dim DT As New DataTable
        Dim Con As New OleDb.OleDbConnection
        Dim cmd As New OleDb.OleDbCommand
        Dim DA As New OleDb.OleDbDataAdapter

        DA.SelectCommand = Cmd
        Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Christine Lee\BlahBlah Industries\Project Documents\Database\ClientData.accdb;Persist Security Info=False"

        Cmd.Connection = Con
        Cmd.CommandType = CommandType.Text

        'Get the Machine Name from the selected client.
        CurMachineName = System.Environment.MachineName

        'Write to the Console for debug--remove.
        Console.WriteLine("Machine Name: {0} ", CurMachineName)

        'select what is in the database now
        Dim strPreview As String = cmd.CommandText = "Select * from tblClient_Machine;"
        MsgBox("The value is:  " & strPreview)
        'Dim strPreview As String

        'strPreview just returnes "False" Not the data.
        Me.lblPreview.Text = strPreview
        
        'Add INSERT statement here. How do I Insert?

        'Update the hardcoded name.  
        
        'The Update doesn't take place here either.
        cmd.CommandText = "UPDATE tblClient_Machine " & _
                                                " SET pk_ClientWSName= '" & CurMachineName & " ;"

 

[Christineeve]

Is anyone able to help with the first part of my question?

 

[PhysicsGuy]

'The form for the sample project below has:
'a DataGridView
'a button named ExecuteADO
'a button named CloseForm
'You should be able to adapt the rest to your own use

Imports System.Data.OleDb

Public Class ADOExample
Dim MyDocs As String, DataSource As String

Private Sub ADOExample_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
MyDocs = My.Computer.FileSystem.SpecialDirectories.MyDocuments
DataSource = "Students.accdb"
End Sub

Private Sub ExecuteADO_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExecuteADO.Click
Dim oConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DataSource & ";User ID=Admin;Password=;")
Dim dr As OleDbDataReader, msg As String

oConn.Open()

ActionQuery("DROP TABLE [Students]", oConn)
ActionQuery("CREATE TABLE [Students] ([Surname] TEXT(50), Forenames TEXT(50), [Score] INT)", oConn)
ActionQuery("INSERT INTO [Students] VALUES ('Bloggs', 'Fred', 78)", oConn)
ActionQuery("INSERT INTO [Students] VALUES ('Snodgrass', 'Sam', 59)", oConn)
ActionQuery("UPDATE [Students] SET [Score] = 87 WHERE [Surname]='Bloggs'", oConn)
ActionQuery("DELETE * FROM [Students] WHERE [Surname]='Snodgrass'", oConn)
ActionQuery("INSERT INTO [Students] VALUES ('Turner', 'John', 97)", oConn)

'Read records by row
dr = ReaderQuery("SELECT [Surname], [Forenames], [Score] FROM [Students]", oConn)
msg = "Data reader:" & vbNewLine
Do While dr.Read
msg += dr("Forenames") & " " & dr("Surname") & ": " & dr("Score") & vbNewLine
Loop
MessageBox.Show(msg)

msg = "The maximum score is " & ScalarQuery("SELECT MAX([Score]) FROM [Students]", oConn)
MessageBox.Show(msg)

'No editing facilities provided here
DataGridView1.DataSource = GetDataTable("SELECT * FROM [Students]", oConn)

'If necessary, a datatable can be converted into a reader
Dim dtr As DataTableReader
dtr = GetDataTable("SELECT * FROM [Students]", oConn).CreateDataReader
msg = "DataTable converted to DataTableReader:" & vbNewLine
Do While dtr.Read
msg += dtr("Forenames") & " " & dtr("Surname") & ": " & dtr("Score") & vbNewLine
Loop
MessageBox.Show(msg)

dr.Close()
oConn.Close()
End Sub

Private Sub ActionQuery(ByVal sql As String, ByVal oConn As OleDbConnection)
Dim oCmd As OleDbCommand
oCmd = New OleDbCommand(sql, oConn)
oCmd.ExecuteNonQuery()
End Sub

Private Function ReaderQuery(ByVal sql As String, ByVal oConn As OleDbConnection) As OleDbDataReader
Dim oCmd As New OleDbCommand(sql, oConn)
ReaderQuery = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
End Function

Private Function ScalarQuery(ByVal sql As String, ByVal oConn As OleDbConnection) As String
Dim oCmd As OleDbCommand
oCmd = New OleDbCommand(sql, oConn)
ScalarQuery = oCmd.ExecuteScalar().ToString()
End Function

Private Function GetDataTable(ByVal sql As String, ByVal oConn As OleDbConnection) As DataTable
Dim oCmd As New OleDbCommand, da As New OleDbDataAdapter, dt As New DataTable
oCmd.Connection = oConn
oCmd.CommandText = sql
da.SelectCommand = oCmd
da.Fill(dt)
GetDatatable = dt
End Function

Private Sub CloseForm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CloseForm.Click
Me.Close()
End Sub
End Class

 

[Christineeve]

Thank you very much for responding. I will take alook at this in the morning. Again, your time and help is appreciated.

 

[Christineeve]

Hi,
I was able to get my GUI working well. I am trying to add records now to my MS Access database.

I'm having trouble with this information, I can't seem to get anything like "ActionQuery" to work in my visual basic code. I'm wondering if I'm missing a reference.

   ActionQuery("DROP TABLE [Students]", oConn)
   ActionQuery("CREATE TABLE [Students] ([Surname] TEXT(50), Forenames TEXT(50), [Score] INT)", oConn)
   ActionQuery("INSERT INTO [Students] VALUES ('Bloggs', 'Fred', 78)", oConn)
   ActionQuery("INSERT INTO [Students] VALUES ('Snodgrass', 'Sam', 59)", oConn)
   ActionQuery("UPDATE [Students] SET [Score] = 87 WHERE [Surname]='Bloggs'", oConn)
   ActionQuery("DELETE * FROM [Students] WHERE [Surname]='Snodgrass'", oConn)
    ActionQuery("INSERT INTO [Students] VALUES ('Turner', 'John', 97)", oConn)

I appreciate any help. Thank you for the help you've given up until now. Much Appreciated.
Christine

 

[PhysicsGuy]

Does the code build successfully?
What happens when you run the code?
Are you receiving an error message? If so, what?

 

[Christineeve]

Dear PhysicsGuy,
I apologize, I know better than to forget to provide that information. No excuses.

But, it says that ActionQuery is not defined. It doesn't compile, and it throws the same error, i.e., "is not defined."

Thanks, for your help.

 

[PRPhx]

try doing a google search. There are many examples out there that show how to set up your connection, query/insert/update/delete data from a number of different databases, including MS Access.

 

[Christineeve]

PRPhx,

One of the reasons I post here asking for help is that in some way, my questions might help other people to find their answers too.

Remember the old saying, there's no dumb question?

This forum would be pretty dull and not very useful if every single response was, "go look it up yourself."

I did research this issue. If you looked at my original post, I posted my ineffective code and asked for help.

I did not post here asking someone to do my project for me.

I'm grateful that PhysicsGuy did post very helpful information. However, I see I'm very much out of my league here and will have to do more research.

I have no doubt that his post will be meaningful to someone who knows what she's doing. It's a great example.

That's ok. All is not lost, because someone else will come my way and maybe benefit from the information.

Thanks for your suggestion anyhow.
Christine

 

[PRPhx]

Christineeve,
And please don't take this wrong. There are thousands of complete examples out there on the web. Doing a little bit of research would have helped you tremendously, Do we really need another how do I select/insert/delete/update data question when it's been answered many times? My answer is no, not really.

Also, being able to use (insert you favorite search engine here) is a very important asset. Web pages that answer one question often have clues to other abswers. That doesn't mean tha this forum is dull. Repitition is dull.

Research your problem. Try a solution, then if you can't figure out what's happening ask. Also, have you stepped thru your code line by line to see what's happening. Learn to debug.

Using MS Access SQL or MS SQL Server SQL isn't all that different, esp. if it's standard SQL.

What you really need to do is:
1) Establish a connection to your database
2) Open that connection
3) retrieve the data you need
4) close the connection
5) Display the data in some meaningful way.

And sorry PhysicsGuy, but that is not a great example. What or where does ActionQuery come from???? A very good example would be one that compiles and executes. Christineeve, you showed that it does not execute, due to ActionQuery being undefined. The code is close however.

As far as figuring out the correct syntax, sometimes it's easy if you view the sql generated by Access. And if it's pretty standard sql, you can copy and paste the code into your project. THEN if things aren't working, ask for help.

My point is, by doing your own research first, you'll learn how to code your solution the next time you need something similiar. Better yewt, as you become more comfortable with object oriented programming, you can create classes that will need little modification in the future, saving you time and effort.

 

[Christineeve]

Problem Solved. I failed to add the data reader part. I also walked away from the confusing data adapter and did old school stuff that I know how to do.

I'm posting my example for those who can use a real example. Examples help a great deal over writing long-winded statements telling one to look it up herself.

I have another connection class that passes the connection to this class. That's why the open connection/close connection are not in this snippet. Yes, this works just great.

Stars for your help, it's appreciated.

  Public Shared Sub ExecuteNonQueryInsert(ByVal oconn As IDbConnection)
        'This procedure executes both select and insert statement.

        Dim check As Integer
        Dim cmdAddEmp As New OleDbCommand
        Dim cmdAddEmp1 As New OleDbCommand
        Dim daAddEmp As New OleDbDataAdapter
        Dim dsAddEmp As New DataSet
        Dim dtAddEmp As New DataTable

 

        Try
            cmdAddEmp = oconn.CreateCommand
            cmdAddEmp1 = oconn.CreateCommand
            cmdAddEmp.CommandText = "SELECT * FROM tblEmployees WHERE LastName='" & Trim(frmRibbonCtl.txtLastName.Text) & " ' "

            daAddEmp.SelectCommand = cmdAddEmp
            'daAddEmp.CommandText = cmdAddEmp.
            daAddEmp.Fill(dsAddEmp, "AddEmps")
            dtAddEmp = dsAddEmp.Tables("AddEmps")

            If (dtAddEmp.Rows.Count > 0) Then
                MsgBox("Employee " & Trim(frmRibbonCtl.txtLastName.Text) & " has been added in data base", MsgBoxStyle.OkOnly, "Message :")
            Else
                cmdAddEmp = oconn.CreateCommand
                cmdAddEmp1.CommandText = "INSERT INTO tblEmployees(EmployeeID, LastName, FirstName, MiddleInitial, Company, Department, Position) VALUES('" & _
                Trim(frmRibbonCtl.txtEmpID.Text) & _
                "','" & Trim(frmRibbonCtl.txtLastName.Text) & _
                "','" & Trim(frmRibbonCtl.txtFirstName.Text) & _
                "','" & Trim(frmRibbonCtl.txtMiddleInit.Text) & _
                 "','" & Trim(frmRibbonCtl.txtCompany.Text) & _
                 "','" & Trim(frmRibbonCtl.txtDepartment.Text) & "')"

                check = cmdAddEmp1.ExecuteReader.RecordsAffected
                If check > 0 Then
                    MsgBox("Employee  " & Trim(frmRibbonCtl.txtLastName.Text) & " Succesfully Added", MsgBoxStyle.OkOnly, "Message :")
                Else
                    MsgBox("Employee " & Trim(frmRibbonCtl.txtLastName.Text) & " failed to added", MsgBoxStyle.OkOnly, "Message :")
                End If
            End If
            oconn.Close()
        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, " Error Connection!!")
        End Try
    End Sub

 

[Christineeve]

By the way, I'm new to VB 2008. I'm not new to programming.

 

[PhysicsGuy]

If you look in the code I posted, ActionQuery is defined as a sub:

I repeat it here:

  Private Sub ActionQuery(ByVal sql As String, ByVal oConn As OleDbConnection)
    Dim oCmd As OleDbCommand
    oCmd = New OleDbCommand(sql, oConn)
    oCmd.ExecuteNonQuery()
  End Sub