Save and updation with one button

desperateboy

Active member
Joined
Dec 11, 2007
Messages
30
Programming Experience
Beginner
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

VB.NET.JPG
 

Attachments

  • source.txt
    5.1 KB · Views: 21
'Assuming all controls are not binded

VB.NET:
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
 
Last edited by a moderator:
VB.NET:
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
 
Last edited by a moderator:
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
 
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.

VB.NET:
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:
VB.NET:
If CheckForUser(Me.txtName.Text) = 1 Then
            MsgBox("The user already exists.", MsgBoxStyle.Information, "Error")
            Exit Sub
        Else
            'run update
        End If
 
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:

VB.NET:
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:

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