PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VS 2010 Access Database write problem.-VBForums
Results 1 to 7 of 7

Thread: Access Database write problem.

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2011
    Posts
    4

    Access Database write problem.

    I am having problems writing to my .accdb database. Here is the code, any input would be welcome. I am also trying to avoid using a pure MYSQL.

    VB.net Code:
    1. Sub Add_to_database()
    2.         Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\Homework_Database.accdb"
    3.         Dim myConnection As OleDbConnection = New OleDbConnection()
    4.         myConnection.ConnectionString = connString
    5.  
    6.         Dim cmdString As String = "Insert INTO Homework (HomeworkID, Name, Description, StartDate, EndDate, MaxMarks, ClassID) " & "VALUES (@HomeworkID, @Name, @Description, @StartDate, @EndDate, @MaxMarks, @ClassID)"
    7.         Dim da As New OleDbDataAdapter
    8.         Dim accCommand As New OleDbCommand
    9.         Dim intinsert As Integer
    10.  
    11.         accCommand.Connection = myConnection
    12.         acccommand.commandtype = CommandType.Text
    13.         acccommand.CommandType = cmdString
    14.         InsertParameters(acccommand)
    15.         intinsert = acccommand.ExecuteNonQuery()
    16.  
    17.         If intinsert = 0 Then
    18.             MessageBox.Show("The data insertion has failed")
    19.             Exit Sub
    20.         End If
    21.     End Sub
    22.  
    23.     Private Sub InsertParameters(ByRef cmd As OleDbCommand)
    24.         cmd.Parameters.Add("@HomeworkID", OleDbType.Char).Value = "1"
    25.         cmd.Parameters.Add("@Name", OleDbType.Char).Value = txt_name.Text
    26.         cmd.Parameters.Add("@Description", OleDbType.Char).Value = txt_desc.Text
    27.         cmd.Parameters.Add("@StartDate", OleDbType.Char).Value = dtp_start.Text
    28.         cmd.Parameters.Add("@EndDate", OleDbType.Char).Value = dtp_end.Text
    29.         cmd.Parameters.Add("@MaxMarks", OleDbType.Char).Value = txt_maxmarks.Text
    30.         cmd.Parameters.Add("@ClassID", OleDbType.Char).Value = 1
    31.     End Sub

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,607

    Re: Access Database write problem.

    You're having a problem. You have some code. What's the problem? You haven't explained what the actual issue. Does the code not compile? Does it run but throw an exception? What is the error message, if there is one?

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,382

    Re: Access Database write problem.

    I suspect there's eels in his hovercraft.

    But considering that he's using an Access database, I'm glad to hear he's trying to avoid using pure MySQL.

    One thing that does stand out though. Access doesn't support named parameters. You can use the names when adding the parameter values... but the SQL itself needs to be corrected.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,454

    Re: Access Database write problem.

    Try changing
    Code:
            accCommand.Connection = myConnection
            accCommand.CommandType = CommandType.Text
            acccommand.CommandType = cmdString
    To

    Code:
            accCommand.Connection = myConnection
            accCommand.CommandType = CommandType.Text
            accCommand.CommandText = cmdString

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2011
    Posts
    4

    Re: Access Database write problem.

    My specific problem is that my access database is not updating. I am not getting any errors, and I have not been able to notice anything by inspecting the outputs at runtime.

    Also. I was told to use parameters like that by My Computing teacher as she has apparently done it before... which is why i am a bit confused as to who is right..

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,454

    Re: Access Database write problem.

    Quote Originally Posted by Lexwebb View Post
    My specific problem is that my access database is not updating. I am not getting any errors, and I have not been able to notice anything by inspecting the outputs at runtime.

    Also. I was told to use parameters like that by My Computing teacher as she has apparently done it before... which is why i am a bit confused as to who is right..
    Did you make the change I indicated?
    Do you have Option Strict = On

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,382

    Re: Access Database write problem.

    And that's what I was trying to tell you... Access doesn't use named parameters.... if you change the parameter names to the proper place holder (a "?") then it might start working.

    Code:
    Dim cmdString As String = "Insert INTO Homework (HomeworkID, Name, Description, StartDate, EndDate, MaxMarks, ClassID) " & "VALUES (?, ?, ?, ?, ?, ?, ?)"
    Also, you may want to look at the link in my sig "I swear I saved my data, where'd it run off to?" ... as it deals with the mysterious disappearance of data.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width