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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.


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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

765 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