Update data to MS SQL USING VB.NET

Posted on 2014-09-02
Last Modified: 2014-09-03
i CANNOT GET DATA TO UPDATE TO OUR MS SQL SERVER USING VB.NET. i TRIED A TABLEADAPTER BUT That was the wrong approach here is my code I put a try catch to see the error however no error appears.

 Public Sub UpdateData()
        Dim strFirstName As String = CStr(txtFname.Text)
        Dim strLastname As String = CStr(txtLname.Text)
        Dim strTeamID As String = CStr(txtTeamID.Text)
        Dim strJersey As String = CStr(txtJersey.Text)
        Dim strPosition As String = CStr(cmbPosition.Text)
        Dim strStatus As String = CStr(cmbStatus.Text)
        Dim strHeight As String = CStr(txtHeight.Text)
        Dim strWeight As String = CStr(txtWeight.Text)
        Dim strBorn As Date = CDate(dtp1.Value)
        Dim strCollege As String = CStr(txtCollege.Text)
        Dim strBioHTML As String = CStr(txtPlayerNote.Text)
        Dim intPlayerID As Integer = CInt(txtPlayerID.Text)
        Dim thisConnection As New SqlConnection("Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=xxxxx")
        ' Sql Select Query 
        Dim sql As String = "SELECT * FROM NFLPlayers WHERE Status ='A' AND Position <> ''"

        ' Sql Update Statement
        Dim updateSql As String = _
           "UPDATE NFLPlayers " & _
           "SET FirstName = @FirstName, LastName = @LastName, TeamID = @TeamID, Jersey=@Jersey, Position = @Position, " & _
           "Status = @Status, Height = @Height, Weight = @Weight, College = @College, BioHTML=@BioHTML WHERE PlayerID = @PlayerID"

            ' Create Data Adapter
            Dim da As New SqlDataAdapter
            da.SelectCommand = New SqlCommand(sql, thisConnection)

            ' Create and fill Dataset
            Dim ds As New DataSet
            da.Fill(ds, "NFLPlayers")

            ' Get the Data Table
            Dim dt As DataTable = ds.Tables("NFLPlayers")

            ' 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("@Firstanasme", strFirstName)
                .Parameters.AddWithValue("@LastName", strLastname)
                .Parameters.AddWithValue("@TeamID", strTeamID)

                .Parameters.AddWithValue("@Jersey", strJersey)

                .Parameters.AddWithValue("@Position", strPosition)

                .Parameters.AddWithValue("@Status", strStatus)

                .Parameters.AddWithValue("@Height", strHeight)
                .Parameters.AddWithValue("@Weight", strWeight)

                .Parameters.AddWithValue("@College", strCollege)

                .Parameters.AddWithValue("@BioHTML", strBioHTML)

                ' .Parameters.AddWithValue("@PlayerID", intPlayerID)

            End With
            ' 2.2 EmployeeId
            Dim idParam As SqlParameter = _
               UpdateCmd.Parameters.Add("@PlayerID", _
               SqlDbType.Int, 32, "PlayerID")
            idParam.SourceVersion = DataRowVersion.Original

            ' Update employees
            da.UpdateCommand = UpdateCmd
            da.Update(ds, "NFLPlayers")

            ' Display Rows After Alteration

        Catch ex As SqlException
            ' Display error
            txtPlayerNote.Text = ("Error: " & ex.ToString())
            ' Close Connection

        End Try
    End Sub

Open in new window

Question by:powerztom
    LVL 18

    Expert Comment

    Looks like you have a type on this parameter:
     With UpdateCmd
                    .Parameters.AddWithValue("@Firstanasme", strFirstName)

    You are only trapping SQLEXceptions,
    add another "catch" for exception to see if something else is failing.

    This doesn't look right either:
    UpdateCmd.Parameters.Add("@PlayerID", _
                   SqlDbType.Int, 32, "PlayerID")
    When do you assign a value to this parameter?
    It is the only part of the where clause so if @PlayerID = 0, then the update might just be updating zero rows.
    You should be able to put a breakpoint on the code just before the update call and expose the commandtext.

    Author Comment

    I wrote this based on a MSDN PAGE For updating data to a ms sql database and it deletes the record somehow it is using UPDATE Not Delete I have no idea any suggestions

    Public Sub UPDATETABLE()
            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus")
            Dim cmd As New System.Data.SqlClient.SqlCommand
            cmd.CommandType = System.Data.CommandType.Text
            cmd.CommandText = "UPDATE NFLPLAYERS SET FirstName = @FirstName, LastName = @LastName, TeamID = @TeamID, Jersey=@Jersey, Position = @Position, " & _
               "Status = @Status, Height = @Height, Weight = @Weight, Born = @Born, College = @College, BioHTML=@BioHTML WHERE PlayerID = @PlayerID"
            cmd.Parameters.AddWithValue("@FirstName", txtFname.Text)
            cmd.Parameters.AddWithValue("@LastName", txtLname.Text)
            cmd.Parameters.AddWithValue("@TeamID", txtTeamID.Text)
            cmd.Parameters.AddWithValue("@Jersey", txtJersey.Text)
            cmd.Parameters.AddWithValue("@Position", cmbPosition.SelectedText)
            cmd.Parameters.AddWithValue("@Status", cmbStatus.SelectedText)
            cmd.Parameters.AddWithValue("@Born", dtp1.Text)
            cmd.Parameters.AddWithValue("@Height", txtHeight.Text)
            cmd.Parameters.AddWithValue("@Weight", txtWeight.Text)
            cmd.Parameters.AddWithValue("@College", txtCollege.Text)
            cmd.Parameters.AddWithValue("@BioHTML", txtPlayerNote.Text)
            cmd.Parameters.AddWithValue("@PlayerID", txtPlayerID.Text)
                cmd.Connection = sqlConnection1
            Catch ex As Exception
                txtPlayerNote.Text = ex.Message & ex.StackTrace & ex.ToString
            End Try
        End Sub

    Open in new window

    LVL 18

    Expert Comment

    That's different code, which piece are we working on?
    In the initial code, I don't see where the @PlayerID parameter is being assigned a value
    you have it remmed out ' .Parameters.AddWithValue("@PlayerID", intPlayerID)

    The you have this piece in 2.2--what is this supposed to do?
    UpdateCmd.Parameters.Add("@PlayerID", _
                   SqlDbType.Int, 32, "PlayerID")

    Author Comment

    Well I tried 3 different attempts at coding for a update. I posted them that way someone may see one of the three and say OK this one is close you just need to tweak this. I  tried .Parameters.AddWithValue("@PlayerID", intPlayerID) but that didn't work so I remmed it out for 2.2 what is supposed to happen is that Players ID WHICH IS AN INTEGER WILL Update that given PlayerID. WHAT I am unsure of is the last piece of code it deletes record and not updates I use the word UPDATE yet it DELETES.
    IF you have any suggestions I SURE WELCOME THEM Thanks
    LVL 18

    Accepted Solution

    This looks like it should be used instead of the 2.2 piece:
    .Parameters.AddWithValue("@PlayerID", intPlayerID)

    They way you are creating the sql statement to update, you don't need to use the sqldataadapter (your da variable).
    You can get rid of these lines:
              ' Update employees
                da.UpdateCommand = UpdateCmd
                da.Update(ds, "NFLPlayers")
    This method would be if you were modifying the dataset and trying to post updates back to the database.  You already have set the commantext property of your sqlcommand so you really only need to do you "executenonquery" like you have in the second example.
    You can use this:

    You might have to open your connection, then you would do
    UpdateCmd.connection = thisConnection

    Author Comment

    I set breakpoints and each parameter has the correct data but then I get this

    Error: System.Data.SqlClient.SqlException (0x80131904): Parameterized Query '(@FirstName char(30),@LastName char(25),@TeamID char(3),@Jersey ' expects parameter @FirstName, which was not supplied.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at SDDSPlayers.frmPlayerupdate.UpdateRows()

    Open in new window


    Author Closing Comment

    UnifiedIS:  worked like a charm. Thanks for all your help You made my this project alot easier now. Cheers to you

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    729 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

    22 Experts available now in Live!

    Get 1:1 Help Now