?
Solved

How to get cell value from Access DB

Posted on 2009-04-17
13
Medium Priority
?
371 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:Benvor
  • 8
  • 5
13 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 24165796
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
 

Author Comment

by:Benvor
ID: 24165825
All i would like to do is get the SessionID("{1FEE852B-E9D0-4AD0-BECB-F139CD8440F2}") and save it as a String Variable("WriteToSession")
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24165873
But you only have on value on the db ? How do you know what record to read ?
0
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.

 
LVL 48

Expert Comment

by:jpaulino
ID: 24165887
I think I got it! You insert and then you want to know the SessionID for the inserted record, right ?
 
0
 

Author Comment

by:Benvor
ID: 24165927
Yes thats it, Sorry for not mentioning it earlier
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24166072
Do you have any autonumber field ?
0
 

Author Comment

by:Benvor
ID: 24166126
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 24166132
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
 
LVL 48

Accepted Solution

by:
jpaulino earned 2000 total points
ID: 24166155
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
 

Author Comment

by:Benvor
ID: 24166202
It works fine, but I get the value as "0"
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24166290
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
 

Author Closing Comment

by:Benvor
ID: 31571352
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 24166637
Glad I could help!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

807 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