How to be able to insert text into the textboxs and for them to go into a database?

[RELiNQUiSH]

Hello

Basically I want to to be able to insert things into my textboxes on vb.net and for them to go into my microsoft access file and into the customers table when I click the save button. I tried doing it myself I didn't succeed my work kept on erroring with this:[imgnothumb]http://i56.tinypic.com/ilxaok.jpg[/imgnothumb]

Heres my code:

Public Class frmCustomers

    Private OleDbInsertCommand1 As System.Data.OleDb.OleDbCommand
    Private OleDbConnection1 As System.Data.OleDb.OleDbConnection
    Private OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter



Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand

        OleDbConnection1 = New System.Data.OleDb.OleDbConnection

        OleDbInsertCommand1.CommandText = "INSERT INTO [Customer] (forename, surname, dob, housenumber, address 1, address 2, postcode, contact)  VALUES ('" & txtForename.Text & "', '" & txtSurname.Text & "', '" & txtHousenumber.Text & "', '" & txtAddress1.Text & "', '" & txtAddress2.Text & "', '" & txtPostcode.Text & "', '" & txtContact.Text & "')"

        OleDbInsertCommand1.Connection = Me.OleDbConnection1

        OleDbConnection1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; " _
      & "Data Source=E:\Ian Griffiths\Kit-tactic.accdb"

        Try

            OleDbConnection1.Open()

            OleDbInsertCommand1.ExecuteNonQuery()

        Catch ex As OleDb.OleDbException

            MessageBox.Show(ex.ToString)

        End Try

        OleDbConnection1.Close()

    End Sub

End Class

Now my database is called: Kit-tactic.accdb
The fields I hold are: forename, surname, dob, housenumber, address 1, address 2, postcode, contact

Thanks for any help :)

[Kalen Viljoen]

You can try:
DATABASE.Tables (TABLENAME).Row s(ROWNAME).Item (POSTION) = TEXTBOX.Text

[RELiNQUiSH]

So take out all the code and just try that?

[Kalen Viljoen]

Here is some code I used for a database app, it adds the new data to the data set then updates the database: (Bare in mind you still need to declare the objects and fill the data adapter)

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow

        dsNewRow = ds.Tables("CrystalDirectory").NewRow()
        PrimaryKey = PrimaryKey + 1

        dsNewRow.Item("ID") = PrimaryKey
        dsNewRow.Item("CrystalName") = txtCrystalName.Text
        dsNewRow.Item("CrystalColour") = txtCrystalColour.Text
        dsNewRow.Item("CrystalAppearence") = txtCrystalAppearance.Text
        dsNewRow.Item("CrystalRarity") = txtCrystalRarity.Text
        dsNewRow.Item("CrystalSource") = txtCrystalSource.Text
        dsNewRow.Item("CrystalAttributes") = txtCrystalAttributes.Text

        ds.Tables("CrystalDirectory").Rows.Add(dsNewRow)

        da.Update(ds, "CrystalDirectory")

        MaxRows = MaxRows + 1

        If chbConfirmUpdate.Checked = True Then

            MsgBox("Your Data Has Been Saved!", , "Crystal ADDer Data Update")

        End If

[RELiNQUiSH]

 If chbConfirmUpdate.Checked = True Then

MsgBox("Your Data Has Been Saved!", , "Crystal ADDer Data Update")

End If

I don't understand that bit of code there, what do I need to assign it as? Ive got the rest working but if I remove that section, I get these errors 'Warning 1 Variable 'da' is used before it has been assigned a value. A null reference exception could result at runtime. E:\Ian Griffiths\MyPro gramNEW\MyProgr am1\Form1.vb 36 49 MyProgram1' I get these errors for da, MaxRows and PrimaryKey

[Kalen Viljoen]

That if statement is just code I had, that if a check box was checked it would notify the user of the save. You should delete it. MaxRows and PrimaryKey are specific for my application you can delete it. All variable must be assigned a value before they can be accessed. So you are getting those errors because 'MaxRows = MaxRows +1' Cannot be called because it never had a value to begin with, you would need to give it a value at some point before you call it. eg, MaxRows = 5. You have to declare all the objects. EG:

Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql as string

Then fill the data set (you would need to do this before you access any of the objects I call it in form load.

    Dim cb As New OleDb.OleDbCommandBuilder(da)

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        'dbSource = "Data Source = C:\Users\Kails\Desktop\CrystalDirectoryADD.mdb"
        dbSource = "Data Source = " + Application.StartupPath() + "\DataBase\CrystalDirectoryADD.MDB"

        con.ConnectionString = dbProvider & dbSource

        con.Open()

        Sql = "SELECT * FROM DirectoryADD"
        da = New OleDb.OleDbDataAdapter(Sql, con)
        da.Fill(ds, "CrystalDirectory")

        con.Close()

Sorry if the code is abit messy, its just copied from one of my old programs. So you have to adapt it to yours.

[RELiNQUiSH]

Right, after making the changes I ran the program I now get this error.

"A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>MyProgram1.vshost.exe</AppDomain><Exception><ExceptionType>System.Data.OleDb.OleDbException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Not a valid file name.</Message><StackTrace>   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at MyProgram.frmCustomers.btnSave_Click(Object sender, EventArgs e) in E:\Ian Griffiths\MyProgramNEW\MyProgram1\Form1.vb:line 44
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message&amp;amp; m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.ButtonBase.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.Button.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp;amp; m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp;amp; msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.Run(ApplicationContext context)
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
   at MyProgram.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>System.Data.OleDb.OleDbException: Not a valid file name.
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at MyProgram.frmCustomers.btnSave_Click(Object sender, EventArgs e) in E:\Ian Griffiths\MyProgramNEW\MyProgram1\Form1.vb:line 44
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message&amp;amp; m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.ButtonBase.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.Button.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp;amp; m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp;amp; msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.Run(ApplicationContext context)
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
   at MyProgram.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>"

Also I don't understand SQL, I only know very basic skills of Visual Basic as i'm studying it on a Level 3 course in College. So if you could explain to me about the SQL bit as well i'd be much appreciated.

Thanks

[Kalen Viljoen]

The problem may be that the filename you have inserted for your database is not correct. Bare in mind if you are running your app from VB the startup path will be in the bin folder. So for VB testing it is best to give a full app Path(eg C:\database.mdb ) The sql command is there to tell the adapter what information to fetch from the table. You could use it to only find dates in a certain range for example. Or only a certain field. In my code it fetches everything.

Here is a fully working form I just put together to explain it to you. You need to create a form(Form1) two text boxes(TextBox1 and TextBox2) and a Button(Button1)

Public Class Form1

    'Global Declarations
    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Private Declarations
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        Dim sql As String

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:\DATABASE.mdb" 'The location of your database

        con.ConnectionString = dbProvider & dbSource
        con.Open() 'Open the database

        'Fetch the data Wanted
        sql = "SELECT * FROM TABLENAME" 'In this case I am selected everything from a table called 'TABLENAME'
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "YOURNAME") 'Fill the dataset with the information from your table then nickname it 'YOURNAME'

        con.Close()

    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'Private Decalrations
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow

        dsNewRow = ds.Tables("YOURNAME").NewRow() 'Open a new row in the data set

        dsNewRow.Item("FIELDNAME1") = TextBox1.Text 'Add the text in textbox1 to FIELDNAME1
        dsNewRow.Item("FIELDNAME2") = TextBox2.Text 'Add the text in textbox2 to FIELDNAME2

        ds.Tables("YOURNAME").Rows.Add(dsNewRow) 'Save the new row to the data set

        'Update the TABLE with the data set information
        'If this is not done the changes are not saved to the table
        'Changes to the dataset only last until the application is closed
        'Updating the data adapter saves the changes to the table in your database
        da.Update(ds, "YOURNAME")

    End Sub

End Class

If you copy that code directly and create a Database(C:\DAT ABASE.MDB) With a Table(TABLENAME ) containing two fields(FIELDNAM E1, and FIELDNAME2) it will work. then you can get a handle on why it all works and adapt it to your application.

[RELiNQUiSH]

Okay, thanks so much Kalen :) i'll test it then get back to you soon :)