SQL Statement in local database not adding a record

JohnJackson

New member
Joined
Jun 1, 2016
Messages
2
Programming Experience
1-3
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)


VB.NET:
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,397
Location
Sydney, Australia
Programming Experience
10+
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

New member
Joined
Jun 1, 2016
Messages
2
Programming Experience
1-3
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. :p


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.
 
Last edited:
Top Bottom