editing an sql row

[citizenstitch]

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

'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

[nana_81]

i just have question what is blnEdit ? which control
here is the 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")

[citizenstitch]

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

[citizenstitch]

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?

[nana_81]

if u r trying to connect to access so plz import

Imports System.Data.OleDb

and if u r using sql

Imports System.Data.SqlClient

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

[citizenstitch]

if u r trying to connect to access so plz import

Imports System.Data.OleDb

and if u r using sql

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.

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

[nana_81]

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

[citizenstitch]

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?

[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)

[citizenstitch]

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)?

[nana_81]

here is the 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

[citizenstitch]

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?

[nana_81]

StrCn my string connection ,access is saved in bin folder

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

IDUser

Dim IDUser As String

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

[citizenstitch]

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

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.

[nana_81]

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