[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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
0
powerztom
Asked:
powerztom
1 Solution
 
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
 
powerztomAuthor Commented:
Thank You
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now