Save and updation with one button

[desperateboy]

Hi friends,
I want to do two things with one button insert data into database and if username already exist that time, run update query.
I am facing error like incorrect syntax near R and invalid attempt to read data.
I have mentioned coding and attached image of error.In this sright is permission given to user like sright=R&W&D Concatenate the strings.

Please help to solve becaz i have already late to make this.

Thanks

 

[Never]

'Assuming all controls are not binded

Private Sub 1btnDoesEmAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 1btnDoesEmAll.Click
     Dim pos as Integer
     pos = bsUserAccounts.Find(User, txtUser.Text)
     If pos > 0 then
          bsUserAccounts.Position = pos
     Else
          bsUserAccounts.AddNew()
          bsUserAccounts("User") = txtUser.Text
     End If

     bsUserAccounts("Designation") = cboDesignation.Text
     bsUserAccounts("Sex") = cboDesignation.Text
     .
     .
     .
     bsUserAccounts.EndEdit()
     taUserAccounts.Update(dsUserAccounts.dtUserAccounts)
End Sub

 

[desperateboy]

if u have some examples on dataset ,would be helpful.

 

[Never]

Private Sub GingerDoesEmAll()
        Dim dv As DataView = dt.DefaultView
        Dim dr As DataRowView

        dv.Sort = "User"
        Dim pos As Integer = dv.Find(txtUser.Text)

        If pos > -1 Then
            dr = dv.Item(pos)
        Else
            dr = dv.AddNew()
            dr("User") = txtUser.Text
        End If

        dr("Designation") = cboDesignation.Text
        dr("Sex") = cboSex.Text
        .
        .
        .
        dr.EndEdit()
        da.Update(ds)
    End Sub

 

[cjard]

I'd have the database do this. You don't say what DB youre using, which is a shame because if it'd been e.g. Oracle I'd have simply said "use a merge statement"

However, your current problem is that youre trying to run an update query on a Command that currently has an open DataReader. Kinda like the error message says. Close your reader, then attempt the update

 

[desperateboy]

when we update dataset that changes happen or not.I know not enough about dataset.

Any alternative of dataset to use.


Thanks

 

[frederick]

You can not run a query inside of a query. Create a function that returns a value 1 or a value 0. Inside that function, do a check to see if a user already exists.

Function CheckForUser(ByVal name As String)
        Dim value As Integer = 0
        'do your query here referencing the variable "name"
        Return value
    End Function

In you update code, do the following:

If CheckForUser(Me.txtName.Text) = 1 Then
            MsgBox("The user already exists.", MsgBoxStyle.Information, "Error")
            Exit Sub
        Else
            'run update
        End If

 

[cjard]

You can not run a query inside of a query.

Well, i'd beg to differ on that note, but in this context, and within the capabilities of the OP, i'd agree

Create a function that returns a value 1 or a value 0. Inside that function, do a check to see if a user already exists.

I'd actually not bother with that.. instead:

Try
  Insert()
Catch Exception
  Try 
    Update()
  Catch ex as Exception
    Messagebox.Show("Problem: " & ex.Message)
  End Try
End Try

PK is designed to prevent dupe, sowe can insert and if it errors because it's there, we update instead.

If more than 50% of records will exist already:

If Update() = 0 Then
  Try 
    Insert() 
  Catch Exception ex 
    Messagebox.Show("Problem: " & ex.Message)
  ENd Try
End If