updating data from a win form into a ms sql server in vb.net

I am having a problem updating data for example textboxes into sql table here is the error catching I got
I know it's ugly below this is my call actually I made two update statements but niether one works

Error: System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable "@PlayerID".
   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() in C:\Users\tpowers\Desktop\sdds .net\SDDSPlayers\SDDSPlayers\frmPlayerupdate.vb:line 68
ClientConnectionId:40daf353-3aa8-442a-964a-80c838cdd231

Open in new window



 Sub UpdateRows()
        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)
        Try
            ' 1. Create Command
            ' 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, Born = @Born, College = @College, BioHTML=@BioHTML WHERE PlayerID = @PlayerID"

            Dim UpdateCmd As New SqlCommand(updateSql, thisConnection)

            ' 2. Map Parameters
            thisConnection.Open()
            With UpdateCmd
                .Parameters.Add("@FirstName", _
                   SqlDbType.Char, 30, strFirstName)
                .Parameters.Add("@LastName", _
                  SqlDbType.Char, 25, strLastname)
                .Parameters.Add("@TeamID", _
                                   SqlDbType.Char, 3, strTeamID)
                .Parameters.Add("@Jersey", _
                  SqlDbType.VarChar, 3, strJersey)
                .Parameters.Add("@Position", _
                                  SqlDbType.VarChar, 15, strPosition)
                .Parameters.Add("@Status", _
                  SqlDbType.Char, 2, strStatus)
                .Parameters.Add("@Height", _
                 SqlDbType.Char, 5, strHeight)
                .Parameters.Add("@Weight", _
                                  SqlDbType.Char, 5, strWeight)

                .Parameters.Add("@Born", _
                 SqlDbType.SmallDateTime, 15, CStr(strBorn))
                .Parameters.Add("@College", _
                  SqlDbType.Char, 20, strCollege)
                .Parameters.Add("@BioHTML", _
                 SqlDbType.Text, 18, strBioHTML)






            End With
            UpdateCmd.ExecuteNonQuery()

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

        thisConnection.Close()
    End Sub

Open in new window

 and the 2nd one is

 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 thisConnection As New SqlConnection("Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus")
        ' 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, Born = @Born, College = @College, BioHTML=@BioHTML WHERE PlayerID = @PlayerID"

        Try
            ' 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.Add("@FirstName", _
                   SqlDbType.Char, 30, strFirstName)
                .Parameters.Add("@LastName", _
                  SqlDbType.Char, 25, strLastname)
                .Parameters.Add("@TeamID", _
                                   SqlDbType.Char, 3, strTeamID)
                .Parameters.Add("@Jersey", _
                  SqlDbType.VarChar, 3, strJersey)
                .Parameters.Add("@Position", _
                                  SqlDbType.VarChar, 15, strPosition)
                .Parameters.Add("@Status", _
                  SqlDbType.Char, 2, strStatus)
                .Parameters.Add("@Height", _
                 SqlDbType.Char, 5, strHeight)
                .Parameters.Add("@Weight", _
                                  SqlDbType.Char, 5, strWeight)

                .Parameters.Add("@Born", _
                 SqlDbType.SmallDateTime, 15, CStr(strBorn))
                .Parameters.Add("@College", _
                  SqlDbType.Char, 20, strCollege)
                .Parameters.Add("@BioHTML", _
                 SqlDbType.Text, 18, strBioHTML)






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

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

            ' Display Rows After Alteration

        Catch ex As SqlException
            ' Display error
            Console.WriteLine("Error: " & ex.ToString())
        Finally
            ' Close Connection
            thisConnection.Close()

        End Try
    End Sub

Open in new window


Thanks EE
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.

MlandaTCommented:
In the first one, I see you have a "WHERE PlayerID = @PlayerID" BUT you are not supplying that parameter in your "2. Map Parameters" section. Just make sure that you are adding the Parameter there.

I'm not sure whether the error you posted above applies also to the second code sample as well?
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
powerztomAuthor Commented:
Thank 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
.NET Programming

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.