Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: Inserting into a database

    Hello,

    I've written the following code to add a new record to a database:

    Code:
    Public Shared Sub AddTopicUser(ByVal UserID As Integer, ByVal TopicCode As String)
    
            Dim RowsAffected As Integer
    
            Dim ATUDCONN As New OleDbConnection
            ATUDCONN.ConnectionString = DBCONNSTR
    
            Dim ATUDCMD As New OleDbCommand("INSERT INTO tblSubscriptions (UserID, TopicCode, Completed) Values (@UserID, @TopicCode, @Completed)", ATUDCONN)
    
            Dim Completed As String = "No"
    
            With ATUDCMD.Parameters
                .AddWithValue("@UserID", UserID)
                .AddWithValue("@TopicCode", TopicCode)
                .AddWithValue("@Completed", Completed)
            End With
    
            Try
    
                ATUDCONN.Open()
    
                RowsAffected = ATUDCMD.ExecuteNonQuery()
    
                ATUDCONN.Close()
                ATUDCONN.Dispose()
    
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
    
        End Sub
    I recieve no errors from the code, but when i open the database after - the record has not been added.

    I have checked that the values being passed as parameters to the function are valid. I was curious as to whether I was formatting the OleDbCmd correctly, but the value of RowsAffected is 1 at runtime which means that it's communicating with the database.

    Any ideas when they're not being added to the db?

    Ty.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I have no idea regarding your code (seems like the complicated route to go) but to add a record using a function, I usually write code as such (ADO coding):

    Function AddTopicUser()
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTableName"
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    rs.addnew
    rs!SomeField = Variable1Name
    rs!SomeField2 = Variable2Name
    ....
    rs.update
    rs.close
    set rs = nothing
    end function

    or via passing parameters to the function...
    Function AddTopicUser(UserID as variant, TopicCode as variant, Completed as variant)
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTableName"
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    rs.addnew
    rs!UserID = UserID
    rs!TopicCode = TopicCode
    rs!Completed = Completed
    rs.update
    rs.close
    set rs = nothing
    end function
    Last edited by pkstormy; 04-13-10 at 19:29.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •