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


Update data to MS SQL USING VB.NET

Posted on 2014-09-02
Medium Priority
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
  • 4
  • 3
LVL 18

Expert Comment

ID: 40299382
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

ID: 40299558
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

        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

ID: 40299602
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")
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.


Author Comment

ID: 40301068
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

UnifiedIS earned 2000 total points
ID: 40301128
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

ID: 40301146
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

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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 (http://www.ecb.europa.eu/stats/exch…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

873 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