Access Database write problem.

[Lexwebb]

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.

Sub Add_to_database()
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\Homework_Database.accdb"
        Dim myConnection As OleDbConnection = New OleDbConnection()
        myConnection.ConnectionString = connString
 
        Dim cmdString As String = "Insert INTO Homework (HomeworkID, Name, Description, StartDate, EndDate, MaxMarks, ClassID) " & "VALUES (@HomeworkID, @Name, @Description, @StartDate, @EndDate, @MaxMarks, @ClassID)"
        Dim da As New OleDbDataAdapter
        Dim accCommand As New OleDbCommand
        Dim intinsert As Integer
 
        accCommand.Connection = myConnection
        acccommand.commandtype = CommandType.Text
        acccommand.CommandType = cmdString
        InsertParameters(acccommand)
        intinsert = acccommand.ExecuteNonQuery()
 
        If intinsert = 0 Then
            MessageBox.Show("The data insertion has failed")
            Exit Sub
        End If
    End Sub
 
    Private Sub InsertParameters(ByRef cmd As OleDbCommand)
        cmd.Parameters.Add("@HomeworkID", OleDbType.Char).Value = "1"
        cmd.Parameters.Add("@Name", OleDbType.Char).Value = txt_name.Text
        cmd.Parameters.Add("@Description", OleDbType.Char).Value = txt_desc.Text
        cmd.Parameters.Add("@StartDate", OleDbType.Char).Value = dtp_start.Text
        cmd.Parameters.Add("@EndDate", OleDbType.Char).Value = dtp_end.Text
        cmd.Parameters.Add("@MaxMarks", OleDbType.Char).Value = txt_maxmarks.Text
        cmd.Parameters.Add("@ClassID", OleDbType.Char).Value = 1
    End Sub

[jmcilhinney]

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?

[techgnome]

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

[kareninstructor]

Try changing

        accCommand.Connection = myConnection
        accCommand.CommandType = CommandType.Text
        acccommand.CommandType = cmdString

To

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

[Lexwebb]

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..

[kareninstructor]

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

[techgnome]

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.

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