Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Update SQL Database in SQL from datagrid

Posted on 2005-04-18
2
Medium Priority
?
238 Views
Last Modified: 2010-04-07
I am having trouble updating a database using a simple datagrid update function, I would be very grateful if someone can shed some light on it. I am not getting an error when i click update, it just reverts to what was in the database previously.

Here is the code. I've simplified the updateCommand for ease of understanding and chrisjones is a record in the users table.

---------

    Private Sub dgUsers_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgUsers.UpdateCommand

        general.UpdateUsers("chrisjones", "Chris 2", False, False, False, False, False, False, False, True, False, True, False)

    End Sub

--------

    Public Shared Function UpdateUsers(ByVal username As String, ByVal fullname As String, ByVal HOU As Boolean, ByVal T01 As Boolean, ByVal T02 As Boolean, ByVal T03 As Boolean, ByVal T04 As Boolean, ByVal T05 As Boolean, ByVal T06 As Boolean, ByVal T07 As Boolean, ByVal T08 As Boolean, ByVal T09 As Boolean, ByVal INT As Boolean)

        Dim Connect As New SqlConnection(DB())
        Dim Adapter As SqlDataAdapter = New SqlDataAdapter

        Dim myCommand As SqlCommand = New SqlCommand


        myCommand.CommandText = "UPDATE users SET fullname = @fullname, HOU = @HOU, [01] = @Param10, [02] = @Param11, [03] = @Param12, [04] = @Param13, [05] = @Param14, [06] = @Param15, [07] = @Param16, [08] = @Param17, [09] = @Param18, INT = @INT " & _
                                "WHERE " & _
                                "(username = @username)"

        myCommand.Connection = Connect

        Adapter.UpdateCommand = myCommand

        Adapter.UpdateCommand.Connection.Open()

    End Function


-------------

What am I doing wrong?
0
Comment
Question by:Jonsie
  • 2
2 Comments
 
LVL 23

Expert Comment

by:b1xml2
ID: 13804847
It is extremely important to understand how the Adapter updates the data in Microsoft SQL Server

References:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbcondatabaseupdates.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondbdataadapterclassupdatetopic5.asp

To use the Update method, you need to pass in data rows and the RowState is used to determine whether the Update, Insert or Delete Commands are used if at all.

0
 
LVL 23

Accepted Solution

by:
b1xml2 earned 500 total points
ID: 13804939
What you have done has nothing to do with the Update() method.

Public Shared Function UpdateUsers(ByVal username As String, ByVal fullname As String, ByVal HOU As Boolean, ByVal T01 As Boolean, ByVal T02 As Boolean, _
      ByVal T03 As Boolean, ByVal T04 As Boolean, ByVal T05 As Boolean, ByVal T06 As Boolean, ByVal T07 As Boolean, ByVal T08 As Boolean, ByVal T09 As Boolean, ByVal INT As Boolean) As Boolean

      Dim connection As New SqlConnection(DB())
      Dim command As SqlCommand = connection.CreateCommand()
      command.CommandText = "UPDATE users SET fullname = @f1, HOU = @h1, [01] = @p1, [02] = @p2, [03] = @p3, [04] = @p4, [05] = @p5, [06] = @p6, [07] = @p7, [08] = @p8, [09] = @p9, INT = @i1 WHERE (username = @u1)"
      command.Parameters.Add("@f1",fullname)
      command.Parameters.Add("@h1",HOU)
      command.Parameters.Add("@p1",T01)
      command.Parameters.Add("@p2",T02)
      command.Parameters.Add("@p3",T03)
      command.Parameters.Add("@p4",T04)
      command.Parameters.Add("@p5",T05)
      command.Parameters.Add("@p6",T06)
      command.Parameters.Add("@p7",T07)
      command.Parameters.Add("@p8",T08)
      command.Parameters.Add("@p9",T09)
      command.Parameters.Add("@i1",INT)
      command.Parameters.Add("@u1",username)
      
      connection.Open()
      Dim count As Integer = command.ExecuteNonQuery()
      connection.Close()
      
      Return (count = 1)
 End Function
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Loops Section Overview
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

572 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