Solved

NEED HELP UPDATING A SQL DATABASE FROM VB WIN FORM

Posted on 2014-11-12
7
211 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

777 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