SQL Statement in local database not adding a record

[JohnJackson]

This is one of those I don't know what the heck I'm doing posts. It's my last/final project in VB.Net and we never learned how to access the local database in the code, we only learned how to access it using grid views in the toolbox.




Information:

• No errors but the values are not being added to the database.
• The database is a simple two column's with the names Number (Primary key, type number, set to not auto) and Conjecture (type checkbox)

Public Class Form1


    Dim connection As OleDb.OleDbConnection


    Sub connectToDatabase()


        connection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Goldbach.accdb;Persist Security Info=True")
        connection.Open()
    End Sub


    Private Sub recordGoldbachNumber(ByVal number As Long, ByVal goldbach As Boolean)


        Dim q As String = "INSERT INTO Goldbach([Number], Conjecture) VALUES (" + CStr(number) + ", " + CStr(goldbach) + ");"


        Dim cmd As New OleDb.OleDbCommand(q, connection)
        cmd.ExecuteNonQuery()
    End Sub


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'GoldbachDataSet.Goldbach' table. You can move, or remove it, as needed.
        Me.GoldbachTableAdapter.Fill(Me.GoldbachDataSet.Goldbach)
        connectToDatabase()


        recordGoldbachNumber(8, False)


    End Sub
End Class

 

[jmcilhinney]

First of all, if you have a typed DataSet then use it and if you're not going to use it then don't create one. All your interactions with your database should be through your GoldbachDataSet and its associated table adapters. If you need to be able to do something that isn't catered for in the generated DataSet, make the appropriate change(s) in the DataSet designer.

That aside, the data most like IS being saved to the database but you're just not looking for it properly. What value is being returned by ExecuteNonQuery? If it's not zero then the code is working as it should. In that case, follow the first link in my signature to learn how local data files are managed and what you're doing wrong.

 

[JohnJackson]

First of all, if you have a typed DataSet then use it and if you're not going to use it then don't create one. All your interactions with your database should be through your GoldbachDataSet and its associated table adapters. If you need to be able to do something that isn't catered for in the generated DataSet, make the appropriate change(s) in the DataSet designer.


That aside, the data most like IS being saved to the database but you're just not looking for it properly. What value is being returned by ExecuteNonQuery? If it's not zero then the code is working as it should. In that case, follow the first link in my signature to learn how local data files are managed and what you're doing wrong.

You were right on the dot with that link. Very surprised, thank you so much. Yes, well I followed an online tutorial, just trying to emulate what they did and I have no real understanding of what all this is and couldn't find the right resources. I know, that's all just excuses but I was trying to slap something together 'cause I'm in a rush. I don't like VB, it all looks so confusing and strange.


Anyways through this confusing syntax and fuddling around for a while I found something that worked. I don't think it's good code in the slightest but the solution for me was 1. Me.GoldbachTableAdapter.Fill(Me.GoldbachDataSet.Goldbach) should be after the recordGoldbachNumber method call in form1_load, this fills the dataset so I am able to see it in the datagrid view.
2. Changing the database properties from always save to save if newer


So frankly I thought nothing was happening for the longest time so I was looking at the SQL statement in utter confusement but two things were happening; 1. the datagrid wasn't being refilled after I added the value so I was not seeing it. And 2. The database was saving over itself on application close.


Then I refactored, added the rest and all is well in vb land (to my standards of not knowing really what I was doing the entire time). Not being rude of-course, I think the syntax is strange because I never took the time to learn it properly.

Anyways this is solved, thanks again.