I am designing a program for myself that uses a database as back end storage and vb.net code for the GUI. I have a degree in
software engineering, so I know the basics of programming and OO design, but only have a beginners knowledge of visual basic (as we were taught java on the course).
Background of program
The main stay of the visual basic side is wind form using combo boxes and textboxes for input and selection. Most of these are
dragged and dropped through the ide interface rather than handed coded.
I am also using MS SQL express 2008 for the backend and have created a table called operationsTable within it.I have set up a dataset control through the ide (MS Visual Basic Express 2008) by dragging and dropping on to the form. I have also been able to bind the data from the operationsTable held in the dataset to a combobox on the form, which works well.
The problem seems to come when i have added a new row to the dataset and i try and update it back to the datasabase. I can't work out why the dataset is updating ,but the database is not.
Here is the code i have used to insert data from the user into the dataset and try and update the database.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 'the connection string to the database file' Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\StudentODPLogBook.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True") Dim cmd As New SqlCommand 'insert SQL command' cmd.CommandText = "Insert Into OperationsTable (OperationName) Values (@OperationName)" 'Takes the text box data as the parameter to be stored in the dataset' cmd.Parameters.AddWithValue("@OperationName", TextBox4.Text) 'Open connection ,execute sql command and close connection' Try cn.Open() cmd.Connection = cn cmd.ExecuteNonQuery() cn.Close() 'if problem with connection or insertion, exception caught and relayed to user' Catch x As Exception MsgBox(x) End Try MsgBox("Inserted " + TextBox4.Text) ' shows succesful insertion of data into dataset' 'refils dataadapter with new data from dataset' SqlDataAdapter1.Fill(OperationsDS1) 'updates operations table in the database from the operations dataset. Try SqlDataAdapter1.Update(OperationsDS1.Tables("OperationsTable")) Catch d As Exception MsgBox(d) End Try End Sub
I have tried searching for solutions on the internet for the last few days and cant seem to find any solutions to the problem. I also have two books both wrox publications, one on visual basic and the other visual basic and databases,but no lick with those either.
I would be so grateful if some one could point me in the right direction , as i understand that you should use the update method to post the data back to the database ( as i have done in my program) but it doesnt seem to update the database at all.