How to get cell value from Access DB

Hi Experts

I have a Access database and in my program I can read the data and write to the database. But now I would just like to get the value of a specific cell in a selected row. Can anyone please help me with this?

I would like to get the "SessionID" and save it in a VAR = WriteToSession

Thanx in advance
Function WriteToSession(ByVal un As String) As String
 
        Dim con As New OleDb.OleDbConnection
        Dim ds As New System.Data.DataSet
        Dim da As New OleDb.OleDbDataAdapter
        Dim sql As String
        Dim Dte As String = Date.Now.ToString()
        Dim ip As String = GetIP()
        Try
 
 
            dbpath = GetDBPath()
            con.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & dbpath
 
            con.Open()
 
            sql = "SELECT * from Sessions"
 
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "Sessions")
 
            con.Close()
 
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dt As DataTable = ds.Tables("Sessions")
 
 
 
 
            con.Open()
 
            sql = "INSERT INTO Sessions (UserName, SessionDate, Workstation, IPAddr) VALUES ('" & un & "', '" & Dte & "', '" & un & "', '" & ip & "')"
 
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "Sessions")
 
            con.Close()
 
 
            WriteToSession = ds.Tables("Sessions").Rows("SessionID").ToString()
 
        Catch ex As Exception
            MessageBox.Show("Error trying to add Session Info:" & vbCrLf & vbLf & ex.Message.ToString())
            WriteToSession = ""
        End Try
 
 
    End Function
 
 
Example of Record
 
SessionID	UserName	SessionDate	SessionTime	Workstation	IPAddr	Action
 
{1FEE852B-E9D0-4AD0-BECB-F139CD8440F2}	My Name	2009/04/17 08:48:32 nm		My Name	0.0.0.0	Open

Open in new window

BenvorAsked:
Who is Participating?
 
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
Try this way then (you may need to change somethings)
    Function WriteToSession(ByVal un As String) As String
        Dim Dte As String = Date.Now.ToString()
        Dim ip As String = GetIP()
        Try
 
            Dim connString As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & GetDBPath()
            Using connection As New OleDbConnection(connString)
                connection.Open()
 
                Dim SQL As String = "INSERT INTO Sessions (UserName, SessionDate, Workstation, IPAddr) VALUES ('" & un & "', '" & Dte & "', '" & un & "', '" & ip & "')"
                Using command As New OleDbCommand(SQL, connection)
 
                    command.ExecuteNonQuery()
 
                    command.CommandText = "Select @@IDENTITY"
                    WriteToSession = command.ExecuteScalar().ToString()
 
                End Using
            End Using
 
 
        Catch ex As Exception
            MessageBox.Show("Error trying to add Session Info:" & vbCrLf & vbLf & ex.Message.ToString())
            Return String.Empty
        End Try
 
 
    End Function

Open in new window

0
 
Jorge PaulinoIT Pro/DeveloperCommented:
I don't understand your code!
If you want to read data from the database you should use and OleDbCommand.ExecuteReader Method and for update information, using a Transact SQL command like you have that INSERT, you should use and OleDbCommand.ExecuteNonQuery Method.
You also have OleDbCommand.ExecuteScalar to read single values
Can you try to explain more what you need ?
0
 
BenvorAuthor Commented:
All i would like to do is get the SessionID("{1FEE852B-E9D0-4AD0-BECB-F139CD8440F2}") and save it as a String Variable("WriteToSession")
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Jorge PaulinoIT Pro/DeveloperCommented:
But you only have on value on the db ? How do you know what record to read ?
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
I think I got it! You insert and then you want to know the SessionID for the inserted record, right ?
 
0
 
BenvorAuthor Commented:
Yes thats it, Sorry for not mentioning it earlier
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Do you have any autonumber field ?
0
 
BenvorAuthor Commented:
The SessiodID is the autonumber field, and thats why I dont know what the value is. Because I would like to use that value in another SLQ WHERE statement
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
If you have an autonumber field you could do something like
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb;"
        Using connection As New OleDbConnection(connString)
            connection.Open()
 
            Dim SQL As String = "INSERT INTO myTable([fiedl1],[field2]) VALUES ('some value','some value');"
            Using command As New OleDbCommand(SQL, connection)
 
                command.ExecuteNonQuery
          
                command.CommandText = "Select @@IDENTITY"
                Debug.WriteLine(command.ExecuteScalar().ToString)
 
            End Using
        End Using

Open in new window

0
 
BenvorAuthor Commented:
It works fine, but I get the value as "0"
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
That works fine if you have an autonumber field but not and Replication ID field. If you can change to Long Integer type you will see that.
If you still need an GUI field you can create a new one and insert it in the INSERT transact-sql command using

Dim strGuid As String = System.Guid.NewGuid.ToString()
0
 
BenvorAuthor Commented:
Thanx allot for youre patience and help, I just changed the field to Long Integer and will keep it like that way.
Thanx again
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Glad I could help!
0
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.

All Courses

From novice to tech pro — start learning today.