NEED HELP UPDATING A SQL DATABASE FROM VB WIN FORM

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")
       
        Try
            ' 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

            thisConnection.Open()
            UpdateCmd.Connection = thisConnection
            UpdateCmd.ExecuteNonQuery()

            ' Update employees


            ' Display Rows After Alteration

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

        End Try
    End Sub

Open in new window


But this somehow deletes the record LUCKLY it's a test enviroment
powerztomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sammySeltzerCommented:
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 )
Response.End()

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)
Response.End()

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vadim RappCommented:
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

0
sammySeltzerCommented:
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("@TransDate",txtDate.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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

powerztomAuthor Commented:
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.
0
Vadim RappCommented:
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, ..... )
0
powerztomAuthor Commented:
VADIMRAPP1,

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.
0
powerztomAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.