Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium



Posted on 2014-11-12
Medium Priority
Last Modified: 2014-11-13
I have successfully updated sql tables everytime within VB until now. i LOOK AT the VB6 app that I am upgrading and it's
sSQL = "UPDATE Transactions SET TransDate = '" & txtDate & "', Pos='" & _
         txtPos & "', Name = '" & Replace(cmbName.Text, "'", "''") & _
         "', Details = '" & Replace(txtDetails, "'", "''") & "', TeamID = '" & txtTeamID & "', TeamID2 = '" & txtTeamID2 & "' WHERE PK = " & Adodc1.Recordset("PK")

Open in new window

There is only one table I'm dealing with here and I thought updating the data  and using WHERE PK  = @PK WOULD WORK BUT i GET
primary key error
SO in a similar table the column PlayerID is unique so I tried this
 Public Sub UPDATEDATA()
        Dim strSPORTCODE As String = CStr(Sportscode)
        Dim strName As String = CStr(txtName.Text)
        Dim strDATE As Date = CDate(txtDate.Text)
        Dim strDetails As String = CStr(txtDetails.Text)
        Dim strPlayerID As Integer = CInt(txtPlayerID.Text)
        Dim strTeamID As Integer = CInt(txtTeamID.Text)
        Dim STRpk As Integer = CInt(txtPK.Text)
        Dim strPos As String = CStr(txtPos.Text)

        Dim thisConnection As New SqlConnection("Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus")
            ' Sql Update Statement
            Dim updateSql As String = _
               "UPDATE Transactions " & _
               "SET Pos=@Pos, TeamID = @TeamID, " & _
               "TransDate = @TransDate, Name = @Name, Details = @Details WHERE PlayerID = @PlayerID "

            ' Create Data Adapter

            ' Get the Data Table

            ' Display Rows Before Changed

            ' Update Employees
            ' 1. Create Command
            Dim UpdateCmd As New SqlCommand(updateSql, thisConnection)

            ' 2. Map Parameters

            ' 2.1 City
            With UpdateCmd

                .Parameters.AddWithValue("@Pos", strPos)

                .Parameters.AddWithValue("@TeamID", strTeamID)

                .Parameters.AddWithValue("@PlayerID", strPlayerID)
                .Parameters.AddWithValue("@Details", strDetails)
                '.Parameters.AddWithValue("@PK", STRpk)
                .Parameters.AddWithValue("@TransDate", strDATE)
                ' .Parameters.AddWithValue("@SportCode", strSPORTCODE)
                .Parameters.AddWithValue("@Name", strName)

            End With
            ' 2.2 EmployeeId

            UpdateCmd.Connection = thisConnection

            ' Update employees

            ' Display Rows After Alteration

        Catch ex As SqlException
            ' Display error
            MessageBox.Show("Error: " & ex.ToString())
            MessageBox.Show("Record Updated!")

        End Try
    End Sub

Open in new window

But this somehow deletes the record LUCKLY it's a test enviroment
Question by:powerztom
  • 3
  • 2
  • 2
LVL 29

Accepted Solution

sammySeltzer earned 1000 total points
ID: 40439239
Just curious powerztom,
why do you have @PK and @sportcode that you are not using?

Here try this on both sets of update query.

On the first one, do debug like:

Response.Write  (updateSql )

Once you have done this, run your code again, copy the raw code and test in your SSMS.

Do the same for the sql like:

sSQL = "UPDATE Transactions SET TransDate = '" & txtDate & "', Pos='" & _
         txtPos & "', Name = '" & Replace(cmbName.Text, "'", "''") & _
         "', Details = '" & Replace(txtDetails, "'", "''") & "', TeamID = '" & txtTeamID & "', TeamID2 = '" & txtTeamID2 & "' WHERE PK = " & Adodc1.Recordset("PK")

Response.Write (sSQL)

Once you test the code, it will tell you exactly what you are doing wrong.

First of all, I hope you are not actually using a fieldname called PK. You know you can't do that.
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40439248
In your first query the value of PK comes from Adodc1.Recordset("PK"). What is it in your upgraded code?

With your 2nd query, it's not clear how it can delete anything when your code does not have any DELETE at all.

Regarding your parameters, in vb.net probably the easiest way is to have something like this:

sSQL = string.format("UPDATE Transactions SET TransDate = '{0}', Pos='{1}'", txtDate.text,txtPos.text)

Open in new window

LVL 29

Expert Comment

ID: 40439792
vadimrapp1, when you attempt to update a record with a value from another field, quite often the update occurs but leaves your screen looking as though you performed a delete.

And the way he is using his parameterized query is fine except I would have avoided the additional casting.

Something like this would have been sufficient:

                .Parameters.AddWithValue("@PlayerID", txtPlayerID.Text)
                .Parameters.AddWithValue("@Details", txtDetails.Text)
                '.Parameters.AddWithValue("@PK", txtPK.Text)
                ' .Parameters.AddWithValue("@SportCode", strSPORTCODE)
                .Parameters.AddWithValue("@Name", txtName.Text)

Open in new window

The one effective way I have been able to resolve this is to run a debug similar to what I showed him.

Then run that code on his sql server management studio.

When he does this, the problem will reveal itself.

This eliminates any unpredictability.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 40439875
This vb6 app was created by some other Bozo who doesn't work here. so that's why PK AS a field was created but honestly I  thought you create PK Primary Key on the master or parent table.
when you say use Response.Write (sSQL)
Response.End() that's aspx right? or can I use that in sql management studio ? I'll try and see what happens.
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 1000 total points
ID: 40439911
Speaking of debugging, I think better way is to create and test queries in separate dataset , which will have visual designer with the ability to preview the query - rather than create everything by yourself in code. The designer will also create all parameters as strong-typed members of the object, very convenient. So instead of all this, you would have

dim myUpdateCommand as new MyDatasetTableAdapters.UpdateCommand1
dim result = myupdatecommand.GetData(@TransDate:=txtDate.Text,                    _
@PlayerId:=txtPlayerID.Text, ..... )

Author Comment

ID: 40440054

this whole upgrade uses no data binding or data designed code But in this case I was thinking yesterday tableadapter cause there is something weird here I don't have patience to try and spend a couple days on this I'll give you and sammySeltzer points in a little bit.

Author Closing Comment

ID: 40441074
I'm gonna work with SR Engineer tomorrow this is something that the update statement I used in previous  work Works fine except this Transactions table has a PK as a field. sammySeltzer I'm glad that you explained to me how you can update and have that record disappear from datagridview emulates a DELETE. i'LL Post what we did tomorrow. For now I appreciate your help the two of you.

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question