how do i update fields in 2 tabels on the same time

I have to tabels in an access db

User

  • userid
  • username
  • useractiv

Userinfo

  • userinfoid
  • userrealname
  • userphone

i then get the info from some asp:text fields to the update handler, i know how to do this for one table but not for 2.

normally i use this

    Dim strSQL As String = ""
    strSQL = "" & _
    "UPDATE Userinfo " & _
    "SET userrealname = @therealname, userphone = @theuserphone " & _
    "WHERE userinfoid =" & Session("theeditid") & ""

    Using connection As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString)
        Using updatecmd As OleDbCommand = New OleDbCommand(strSQL, connection)
            updatecmd.CommandType = CommandType.Text
            updatecmd.Parameters.AddWithValue("@therealname", OleDbType.VarChar).Value = TextBox1.Text
            updatecmd.Parameters.AddWithValue("@theuserphone", OleDbType.VarChar).Value = CKEditor1.Text
            Try
                updatecmd.Connection.Open()
                Dim i As Integer = CInt(updatecmd.ExecuteNonQuery())
                If i = 0 Then
                    Session("editsucces") = "NoMatch" 'no rows were updated because none matched the criteria
                End If
            Catch ex As Exception
                Session("editsucces") = "DBerror" 'Something went wrong, such as the database was unavailable
            End Try
        End Using
    End Using

So my question is now, how can i add the fields from the user tabel !? The user.userid is the same number as in userinfo.userinfoid.

EDIT........EDIT........EDIT.......EDIT..........EDIT.........EDIT..........EDIT

So this code is ok, or do u recoment that i changes some of it !?

Dim strSQL As String = ""
    strSQL = "" & _
    "UPDATE Users INNER JOIN Userinfo ON Users.UserID = Userinfo.UserID " & _
    "SET Users.Username = [@uname], Users.Password = [@upass], Users.UserActiv = [@uactiv], Userinfo.UserRealName = [@urname], Userinfo.UserEmail1 = [@umail], Userinfo.UserDOB = [@udob], Userinfo.UserPhone1 = [@uphone1], Userinfo.UserPhone2 = [@uphone2], Userinfo.UserPhone3 = [@uphone3], Userinfo.UserYear = [@uyear], Userinfo.UserSick = [@usick] " & _
    "WHERE Users.UserID = [@uid]"

    Using connection As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString)
        Using updatecmd As OleDbCommand = New OleDbCommand(strSQL, connection)
            updatecmd.CommandType = CommandType.Text
            updatecmd.Parameters.AddWithValue("@uname", OleDbType.VarChar).Value = userinput1.Text
            updatecmd.Parameters.AddWithValue("@upass", OleDbType.VarChar).Value = userinput2.Text
            updatecmd.Parameters.AddWithValue("@uactiv", OleDbType.VarChar).Value = "Y"
            updatecmd.Parameters.AddWithValue("@urname", OleDbType.VarChar).Value = userinput3.Text
            updatecmd.Parameters.AddWithValue("@umail", OleDbType.VarChar).Value = userinput4.Text
            updatecmd.Parameters.AddWithValue("@udob", OleDbType.VarChar).Value = userinput5.Text
            updatecmd.Parameters.AddWithValue("@uphone1", OleDbType.VarChar).Value = userinput6.Text
            updatecmd.Parameters.AddWithValue("@uphone2", OleDbType.VarChar).Value = userinput7.Text
            updatecmd.Parameters.AddWithValue("@uphone3", OleDbType.VarChar).Value = userinput8.Text
            updatecmd.Parameters.AddWithValue("@uyear", OleDbType.VarChar).Value = userinput9.Text
            updatecmd.Parameters.AddWithValue("@usick", OleDbType.VarChar).Value = usertextarea.Text
            Try
                updatecmd.Connection.Open()
                Dim i As Integer = CInt(updatecmd.ExecuteNonQuery())
                If i = 0 Then
                    Session("editsucces") = "NoMatch" 'no rows were updated because none matched the criteria
                End If
            Catch ex As Exception
                Session("editsucces") = "DBerror" 'Something went wrong, such as the database was unavailable
            End Try
        End Using
    End Using

    Response.Redirect("default.aspx", False)

Answers


It should be possible to create a join to update both tables. For example:

sSQL = "UPDATE [User] INNER JOIN userinfo " _
& "ON User.UserID = userinfo.userinfoid SET " _
& "[User].UserName = [uname], userinfo.userrealname = [urname] " _
& "WHERE [User].UserID = [uid]"

cmd.ActiveConnection = cn
cmd.CommandText = sSQL
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter( _
    "uname", adVarChar, adParamInput, 50, "user name")
cmd.Parameters.Append cmd.CreateParameter( _
    "urname", adVarChar, adParamInput, 50, "user real name")
cmd.Parameters.Append cmd.CreateParameter( _
    "uid", adInteger, adParamInput, , 2)
cmd.Execute

From what is understood, I think there is one to one correspondence between user and userinfo tables. (considering user.userid is same as userinfo.userinfoid and they appear to be primary keys in their respective tables). So, the two tables should be merged into a single table.

Now, if you want to update the columns in two tables, you would run two update statements one on each table. If you want to ensure atomicity, you would run the two update statements in a single transaction. Does this make sense or am I missing something?


You should using transaction. It is common approach to do many changes in database atomically. Good example for your case http://www.dreamincode.net/forums/topic/186402-transaction-on-access-database-adonet/

Using con As New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString)
  Dim cmd As New OleDbCommand("", con)
  Dim tra As OleDbTransaction = Nothing
  Dim ct1 As String = ""
  Dim ct2 As String = ""

  Try
    con.Open()
    tra = con.BeginTransaction
    cmd.Transaction = tra

    strSQL = "" & _
    "UPDATE User " & _
    ' set fields needed to update in table USER
    "WHERE userid =" & Session("theeditid") & ""
    cmd.CommandText = strSQL
    ' set parameters for user update
    cmd.ExecuteNonQuery()

    strSQL = "" & _
    "UPDATE Userinfo " & _
    "SET userrealname = @therealname, userphone = @theuserphone " & _
    "WHERE userinfoid =" & Session("theeditid") & ""
    cmd.CommandText = strSQL
    cmd.Parameters.Clear()

    ' set parameters for userinfo update
    cmd.ExecuteNonQuery()

    ' Complete transacton
    tra.Commit()

    ' i think here should be closing cmd

    Catch ex As Exception
      MsgBox("The data could not be saved.", MsgBoxStyle.Critical, "Error")
      Try : tra.Rollback() : Catch : End Try
    End Try
End Using

Need Your Help

parse array with JSON from xmlhttp.responseText

php javascript arrays json

Im trying to get php array to js using ajax and encoding by JSON in PHP and decoding in JS but unable to get the desired result.

does backend path length affect frontend speed?

ms-access

We're all running Access 2010 over a WAN from multiple cities across the US (eg, frontends are in Phoenix, Chicago, Boston and Albany NY, backend is in Phoenix)

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.