UPDATE, SELECT, INSERT

[takwirira]

Hie guys im new to VB.NET (ADODB.Net), i used to program in VB6 and things have changed with Adapters and Readers etc. I need help with updating, searching and inserting records in an access database. i.e. from the connection string to closing the connection.

Situation; 2 labels on a form 3 textboxes and 3 buttons and off course db1.mdb. db1 has 1 table and 3 fields i.e. id, name and surname

The sample code should do the following

button 1
insert contents of 3 variables into the database e.g. id, name and surname
these variables get their values from textbox 1,2 and 3 e.g. txtID, txtName and txtSurname

button 2
update the name and surname where id = id variable contents. id variable will be taken from the Textbox

button 3
search and populate label 1 and 2 i.e. lblName and lblSurname using the id variable. id variable will be taken from txtID

THE FOLLOWING ARE EXAMPLES OF HOW I DID IT IN VB6

dim name as string
dim surname as string
dim id as string

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\details.mdb"

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim sConnString As String

'open the connection
conn.Open sConnString
Set cmd.ActiveConnection = conn

cmd.CommandText = "INSERT INTO Personal (ID,Name,Surname)" & _
"VALUES " & _
"('" & txtID.Text & "'," & _
"'" & txtName.Text & "'," & _
"'" & txtSurname.Text & "')"



cmd.Execute

Set cmd = Nothing
conn.Close
Set conn = Nothing


SEARCH EXAMPLE

conn.Open sConnString
Set cmd.ActiveConnection = conn

cmd.CommandText = "SELECT * FROM Details Where ID ='" & id & "'"

Set rs = cmd.Execute

If Not rs.EOF Then

lblName.caption = rs.Fields(1).Value

lblSurname.caption = rs.Fields(2).Value

Else
MsgBox "Sorry person does not exist"
End If

Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing

UPDATE EXAMPLE

conn.Open sConnString
Set cmd.ActiveConnection = conn

cmd.CommandText = "UPDATE Details SET Name = '" & name & "' Where ID ='" & id & "'"

OR for multiple updates


cmd.CommandText = "UPDATE Details SET Name = '" & name & "' , Surname = '" & surname & "' WHERE ID ='" & ID & "'"


cmd.Execute

Set cmd = Nothing
conn.Close
Set conn = Nothing

i had tried to use this code in VB2005 but that didnt work and the Upgrade VB6 code under Tools didnt work either.

please help

 

[toon10]

The following code should work for connecting to Access.

dim name as string
dim surname as string
dim id as string
Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\details.mdb")
        AccessConn.Open()

        Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO Personal (ID,Name,Surname)" & _
       "VALUES " & _
        "('" & txtID.Text & "'," & _
        "'" & txtName.Text & "'," & _
        "'" & txtSurname.Text & "')

        Try
            With AccessCommand
                .Connection = AccessConn
                .CommandType = CommandType.Text
                .ExecuteNonQuery()
            End With
            AccessConn.Close()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "error")
        End Try