Solved

NEED HELP UPDATING A SQL DATABASE FROM VB WIN FORM

Posted on 2014-11-12
7
205 Views
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")
       
        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
0
Comment
Question by:powerztom
  • 3
  • 2
  • 2
7 Comments
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 250 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 )
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
 
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

0
 
LVL 28

Expert Comment

by:sammySeltzer
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("@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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:powerztom
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.
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 250 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, ..... )
0
 

Author Comment

by:powerztom
ID: 40440054
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
 

Author Closing Comment

by:powerztom
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.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now