Update data to MS SQL USING VB.NET

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"

        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.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
            thisConnection.Open()

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

            ' Display Rows After Alteration

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

        End Try
    End Sub

Open in new window

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.

UnifiedISCommented:
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.
0
powerztomAuthor Commented:
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)
       
        Try





            cmd.Connection = sqlConnection1

            sqlConnection1.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            txtPlayerNote.Text = ex.Message & ex.StackTrace & ex.ToString
        End Try
        sqlConnection1.Close()
    End Sub

Open in new window

0
UnifiedISCommented:
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")
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

powerztomAuthor Commented:
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
0
UnifiedISCommented:
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:
UpdateCmd.executenonquery

You might have to open your connection, then you would do
thisConnection.open
UpdateCmd.connection = thisConnection
UpdateCmd.ExecuteNonQuery
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:
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()
ClientConnectionId:11ff1d2a-3baf-47f8-bbf0-0a22bcdc598d

Open in new window

0
powerztomAuthor Commented:
UnifiedIS:  worked like a charm. Thanks for all your help You made my this project alot easier now. Cheers to 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.