editing an sql row

I built a program that basically lets you navigate,edit,add,delete records in an access database. When i changed the connection over to an sql table "ish" hit the fan. the adding function and editing function turned into mush. i fixed the add function, but the edit is hairy. when i save it will keep the new value in the text box, but come to reopening the program the old value came back. i guess osme code is in order

VB Code:

'edit button 
Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        If btnAdd.Text = "&Cancel" Then
            LockText()
            btnEdit.Enabled = True
            btnDelete.Enabled = True
            btnAdd.Text = "&Add"
            btnSave.Enabled = False
            RejectChanges()
            blnEdit = False
        Else
            unlockText()
            NoNavigation()
            btnSave.Enabled = True
            btnAdd.Text = "&Cancel"
            btnEdit.Enabled = False
            btnDelete.Enabled = False
            blnEdit = True
            displayrecordposition()
        End If
end sub
'save button (don't mind what I have for edit already)
 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If blnAdd = True Then
            Try
                Dim newrow As DataRow = DsSQL1.Dem.NewRow
                newrow("first") = txtFirst.Text
                newrow("last") = txtLast.Text
                newrow("ssn") = txtSSN.Text
                newrow("dob") = txtBday.Text
                DsSQL1.Dem.Rows.Add(newrow)
            Catch exc As Exception
                MessageBox.Show("Unable to add record." & _
                ControlChars.NewLine & exc.Message, "dem")
            End Try
            blnAdd = False
            lblRecordNumber.Text = "Record added"
        End If
        If blnEdit = True Then
'i know i need to fix this but to what?
            Dim editrow As DataRow = DsSQL1.Dem.NewRow
            editrow("first") = txtFirst.Text
            editrow("last") = txtLast.Text
            editrow("ssn") = txtSSN.Text
            editrow("dob") = txtBday.Text
            DsSQL1.Dem.Rows.Add(editrow)
 
        End If
 
        LockText()
        GoNavigation()
        btnSave.Enabled = False
        btnAdd.Text = "&Add"
        blnIsDirty = True
        btnEdit.Enabled = True
        btnDelete.Enabled = True
 
 
    End Sub

i just have question what is blnEdit ? which control
here is the code

VB Code:

Dim strSQLSelect As String = "SELECT * FROM TableName"
        Dim adapter As New OleDbDataAdapter(strSQLSelect, conn)
        Dim ds As New DataSet
        adapter.Fill(ds, "TableName")
        ' Modify the in-memory records in the DataSet
        Dim tbl As DataTable = ds.Tables("TableName")
        tbl.PrimaryKey = New DataColumn() _
                         { _
                           tbl.Columns("PrimaryKey") _
                         }
        Dim row As DataRow = tbl.Rows.Find(PrimaryKey)
 
        row.Item("Filed1") = Txtfiled2.Text
        row.Item("Feild2") = Txtfiled2.Text
.......
       
        ' Reconnect the DataSet and update the database
        Dim cb As New OleDbCommandBuilder(adapter)
        
        adapter.Update(ds, "TableName")

blnedit is declared at the modular level and is set to true or false in the btnedit procedure.

ok this is really odd. when I dim the adapter as new oledbadapter and oldecommand are not defined. If I already have my connection with my tables established in the server explorer through the wizard will this code chage?

if u r trying to connect to access so plz import

VB Code:

Imports System.Data.OleDb

and if u r using sql

VB Code:

Imports System.Data.SqlClient

and u have to change the adapter and command into sqladapter and sqlcommand

Quote:

---

Originally Posted by nana_81

if u r trying to connect to access so plz import

VB Code:

Imports System.Data.OleDb

and if u r using sql

VB Code:

Imports System.Data.SqlClient

and u have to change the adapter and command into sqladapter and sqlcommand

---

thanks for that import statement, it helped a bit. although I am getting errors. I modded the code I got earlier because I already had my dataset and such defined and loaded at form load.

VB Code:

'i have a problem with this code
 Dim row As DataRow = tbl.Rows.Find(PrimaryKey)'it says primarykey 
'isnt declared

u should specify ur primary key. if ur primary key in the table is ID so write that

I tried substituting primarykey with the name of my pk field, tried adding a ",field". do i have to declare it or what do you mean?

DataRowCollection.Find() requires the primary key value of the row you want to find. If you have three rows with primary key values 1, 2 and 3 and you want to find the middle row, you use <TableName>.Rows.Find(2)

Quote:

---

Originally Posted by jmcilhinney

DataRowCollection.Find() requires the primary key value of the row you want to find. If you have three rows with primary key values 1, 2 and 3 and you want to find the middle row, you use <TableName>.Rows.Find(2)

---

how does this help me edit my row? should I make a new variable that has a value of the pk when you are on that record and put for example: table.row.find(intPK)?

here is the code

VB Code:

UserID=txtUserID.text
Dim conn As OleDbConnection = New OleDbConnection
        conn.ConnectionString = strCn
        'Open(connection)
        If conn.State <> ConnectionState.Open Then
            conn.Open()
 
        Dim strSQLSelect As String = "SELECT * FROM Users"
        Dim adapter As New OleDbDataAdapter(strSQLSelect, conn)
        Dim ds As New DataSet
        adapter.Fill(ds, "Users")
        ' Modify the in-memory records in the DataSet
        Dim tbl As DataTable = ds.Tables("Users")
        tbl.PrimaryKey = New DataColumn() _
                         { _
                           tbl.Columns("UserID") _' UserID is the primary key in Users table
                         }
        Dim row As DataRow = tbl.Rows.Find(IDUser)' variable i declared and i stored the required UserID
 
        row.Item("UserID") = IDUser
        row.Item("UserName") = TxtName.Text
      row.Item("UserNameArabic") = TxtArabic.Text
        row.Item("UserNotes") = TxtNotes.Text
 
        ' Reconnect the DataSet and update the database
        Dim cb As New OleDbCommandBuilder(adapter)
        'Connect()
        adapter.Update(ds, "Users")
 
        If conn Is Nothing Then
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
            ' Dispose connection
            conn.Dispose()
        End If

i hope this clear

what is strCn

i matched this line with mine
UserID=txtUserID.text
did you declared userid as string?
now i have a procedure at my form closing that is supposed to finish the update. for example it looks good when i hit the save button. then i have a trigger that if i do change something it asks me update my dataset again. will this interfere or is worthless code?

StrCn my string connection ,access is saved in bin folder

VB Code:

Dim strCn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "\DataBaseName.mdb"

IDUser

VB Code:

Dim IDUser As String

UserID field ,in my database ,its DataType is Text
is that helpful i wish :confused:

I am using sql. I already got everythign to work with access. maybe it is more friendly, i dunno. another problem is with this code it is opening another connection when i am saving therefore dismissing any changes and reloading the conneciton all over again. what i think the problem is that with

VB Code:

Dim tbl As DataTable = DsSQL1.Tables("dem")
            tbl.PrimaryKey = New DataColumn() _
                            { _
                             tbl.Columns("ssn") _
                         }
            Dim row As DataRow = DsSQL1.Dem.Rows.Find(ssn)

this set of code it is not finding and changing the record. my primary key is varchar in my table. i dont know how to convert it in vb. i dont know why they made editing records so complicated.

if u face many problems using this why. why dont u use an update statment and then excute it.