Access Insert Query does not inserting
Access Insert Query does not inserting
Access Insert Query does not inserting
Access Insert Query does not inserting
Access Insert Query does not inserting
Access Insert Query does not inserting Access Insert Query does not inserting Access Insert Query does not inserting Access Insert Query does not inserting Access Insert Query does not inserting Access Insert Query does not inserting Access Insert Query does not inserting Access Insert Query does not inserting
Access Insert Query does not inserting Access Insert Query does not inserting
Access Insert Query does not inserting
Go Back  Xtreme Visual Basic Talk > > > Access Insert Query does not inserting


Reply
 
Thread Tools Display Modes
  #1  
Old 01-20-2012, 04:48 AM
sp33dy sp33dy is offline
Newcomer
 
Join Date: Jun 2011
Posts: 10
Default Access Insert Query does not inserting


Edit by Moderator: Split from this thread

Hello Communety,

I used the code from "jagoh87" for my project. I'm building a project using Visual Studio 2010 and Visual Basic.net and I have a Windows Access 2007 MDB (database) and I would like to insert data. The data to pick is no problem, but to insert data into the database does not work.

I get no error message, the code works, but when I open the database, then there entered the data does not exist.

The inserted data will be displayed in my datagridview, but not as it appears in the database.

Any idea why the data is not stored in the Access database?


Thank you very much.

Felix

My code:

Code:
 Dim cnJetDB As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Datenbank\Access-Datenbank.mdb;Persist Security Info=True")

        cnJetDB.Open()
        ' Create a DataAdaptor With Insert Command For inserting records
        Dim oleDa As New OleDbDataAdapter("Select * from Personaldaten", cnJetDB)

        ' Command to Insert Records
        Dim cmdInsert As New OleDbCommand()
        ' cmdInsert.CommandText = "INSERT INTO tblOpen (Technician, UserName) VALUES (?)"
        cmdInsert.CommandText = "INSERT INTO `Personaldaten` (`Geschlecht`, `Vorname`, `Nachname`, `Accountnamen`, `Position`, `Laufwerk`, `Ordner`, `wie_Benutzer`, `Programme`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
        cmdInsert.Connection = cnJetDB
        cmdInsert.Parameters.Add(New OleDbParameter("Geschlecht", OleDbType.VarChar, 40, "Geschlecht"))
        cmdInsert.Parameters.Add(New OleDbParameter("Vorname", OleDbType.VarChar, 40, "Vorname"))
        cmdInsert.Parameters.Add(New OleDbParameter("Nachname", OleDbType.VarChar, 40, "Nachname"))
        cmdInsert.Parameters.Add(New OleDbParameter("Accountnamen", OleDbType.VarChar, 40, "Accountnamen"))
        cmdInsert.Parameters.Add(New OleDbParameter("Position", OleDbType.VarChar, 40, "Position"))
        cmdInsert.Parameters.Add(New OleDbParameter("Laufwerk", OleDbType.VarChar, 40, "Laufwerk"))
        cmdInsert.Parameters.Add(New OleDbParameter("Ordner", OleDbType.VarChar, 40, "Ordner"))
        cmdInsert.Parameters.Add(New OleDbParameter("wie_Benutzer", OleDbType.VarChar, 40, "wie_Benutzer"))
        cmdInsert.Parameters.Add(New OleDbParameter("Programme", OleDbType.VarChar, 40, "Programme"))
        oleDa.InsertCommand = cmdInsert

        ' Create a DataTable
        Dim dtTest As New DataTable()
        oleDa.Fill(dtTest)

        Dim drTest As DataRow
        ' Add Rows to the Table
        drTest = dtTest.NewRow
        drTest("Geschlecht") = combogeschlecht.SelectedItem
        drTest("Vorname") = txtvorname.Text
        drTest("Nachname") = txtnachname.Text
        drTest("Accountnamen") = account
        drTest("Position") = ComboBox1.SelectedItem
        drTest("Laufwerk") = ComboBox2.SelectedValue
        drTest("Ordner") = ComboBox3.SelectedValue
        drTest("wie_Benutzer") = txtwieBenutzer.Text
        drTest("Programme") = CheckboxString
        dtTest.Rows.Add(drTest)

        ' Update the Data
        oleDa.Update(dtTest)

        ' Release the Resources
        cmdInsert.Dispose()
        cmdInsert = Nothing
        cnJetDB.Close()
        cnJetDB.Dispose()
        cnJetDB = Nothing

Last edited by Flyguy; 01-20-2012 at 04:53 AM. Reason: Please start your own threads for asking questions.
Reply With Quote
  #2  
Old 01-20-2012, 04:57 AM
DrPunk's Avatar
DrPunkAccess Insert Query does not inserting DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

I don't think the your INSERT command should have inverted commas.

Also, I'm not entirely sure this is why it doesn't work, but your parameters don't match up with anything in the query. Your parameters should have a name in the query and then the name of the parameter matches that in the query string. Maybe that doesn't make sense so an example should explain what I'm trying to say..
Code:
Dim cmdInsert As New OleDbCommand()

' No inverted commas and a name for the parameter. Parameter names have "@" at the front of them.
cmdInsert.CommandText = "INSERT INTO Personaldaten (Geschlecht) VALUES (@Geschlecht)"

' The name of the parameter matches the parameter name in the query
cmdInsert.Parameters.Add(New OleDbParameter("@Geschlecht", OleDbType.VarChar, 40, "Geschlecht"))
__________________
There are no computers in heaven!
Reply With Quote
  #3  
Old 01-20-2012, 07:32 AM
sp33dy sp33dy is offline
Newcomer
 
Join Date: Jun 2011
Posts: 10
Default

Hello DrPunk,

thank you for your answer, but:

1.
The insert statement has been automatically generated by Visual Studio.

I have created the Insert statement using the Query Builder in Visual Studio.

Quotes have been removed, but it still exists the problem.

Code:
cmdInsert.CommandText = "INSERT INTO Personaldaten (Geschlecht, Vorname, Nachname, Accountnamen, Position,Laufwerk, Ordner, wie_Benutzer, Programme) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
2.
This means that this syntax is not correct, since the query does not recognize this generator for Access Database.
Code:
cmdInsert.CommandText = "INSERT INTO Personaldaten (Geschlecht) VALUES (@Geschlecht)"

3.
Even with this line of code it does not work.
Code:
cmdInsert.Parameters.Add(New OleDbParameter("@Geschlecht", OleDbType.VarChar, 40, "Geschlecht"))

Now i have a new Version, but these stores are no values ​​in the database, too.

Code:
Dim dtTest As DataTable = New DataTable()
        oleDa.Fill(dtTest)

        Dim drTest As DataRow
        '' Add Rows to the Table
        drTest = Me._Access_DatenbankDataSet.Personaldaten.NewRow
        drTest("Geschlecht") = combogeschlecht.SelectedItem
        drTest("Vorname") = txtvorname.Text
        drTest("Nachname") = txtnachname.Text
        drTest("Accountnamen") = account
        drTest("Position") = ComboBox1.SelectedItem
        drTest("Laufwerk") = ComboBox2.SelectedValue
        drTest("Ordner") = ComboBox3.SelectedValue
        drTest("wie_Benutzer") = txtwieBenutzer.Text
        drTest("Programme") = CheckboxString
        'dtTest.Rows.Add(drTest)
        'Me._Access_DatenbankDataSet.Personaldaten.Rows.Add(drTest)
        Me._Access_DatenbankDataSet.Personaldaten.AddPersonaldatenRow(combogeschlecht.SelectedItem, txtvorname.Text, txtnachname.Text, account, ComboBox1.SelectedItem, ComboBox2.SelectedValue, ComboBox3.SelectedValue, txtwieBenutzer.Text, CheckboxString)
        ' Update the Data
        'Me._Access_DatenbankDataSet.PersonaldatenTableAdapter.Update(Me._Access_DatenbankDataSet.Personaldaten)
        oleDa.Update(Me._Access_DatenbankDataSet.Personaldaten)
Reply With Quote
  #4  
Old 01-20-2012, 08:43 AM
sp33dy sp33dy is offline
Newcomer
 
Join Date: Jun 2011
Posts: 10
Default

I'm now working on a further code, but it is the same problem that the data is not stored in the database, although I can see it in the Gridview. There must be something that forces the storage? I slowly desperate. Thank you in advance for your help.


Code:
        Dim Mycn As OleDbConnection
        Dim Command As OleDbCommand
        Dim icount As Integer
        Dim SQLstr As String


        Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data  Source=|DataDirectory|\Datenbank\Access-Datenbank.mdb;Persist Security Info=True")
        mycn.Open()

        SQLstr = "INSERT INTO [Personaldaten] ([Geschlecht], [Vorname], [Nachname], [Accountnamen], [Position],[Laufwerk], [Ordner], [wie_Benutzer], 
        [Programme]) VALUES ('" & combogeschlecht.SelectedItem & "','" & txtvorname.Text & "','" & txtnachname.Text & "','" & account & "',
        '" & ComboBox1.SelectedItem & "','" & ComboBox2.SelectedValue & "','" & ComboBox2.SelectedValue & "','" & txtwieBenutzer.Text & "',
        '" & CheckboxString & "')"

        Command = New OleDbCommand(SQLstr, mycn)
        icount = Command.ExecuteNonQuery
        MessageBox.Show(icount)
        Mycn.Close()
Reply With Quote
  #5  
Old 01-23-2012, 08:33 AM
sp33dy sp33dy is offline
Newcomer
 
Join Date: Jun 2011
Posts: 10
Default

one can help me please?

I get no further.

why no data is stored? : (

Code:
 
             Dim db As New _Access_DatenbankDataSetTableAdapters.PersonaldatenTableAdapter            

             Dim newPersonalRow As _Access_DatenbankDataSet.PersonaldatenRow

            newPersonalRow = Me._Access_DatenbankDataSet.Personaldaten.NewPersonaldatenRow()

            newPersonalRow.Geschlecht = combogeschlecht.SelectedItem
            newPersonalRow.Vorname = txtvorname.Text
            newPersonalRow.Nachname = txtnachname.Text
            newPersonalRow.Accountnamen = account
            newPersonalRow.Position = ComboBox1.SelectedItem
            newPersonalRow.Laufwerk = ComboBox2.SelectedValue
            newPersonalRow.Ordner = ComboBox3.SelectedValue
            newPersonalRow.wie_Benutzer = txtwieBenutzer.Text
            newPersonalRow.Programme = CheckboxString


            Me._Access_DatenbankDataSet.Personaldaten.Rows.Add(newPersonalRow)

            db.Update(Me._Access_DatenbankDataSet.Personaldaten)
Reply With Quote
  #6  
Old 01-24-2012, 02:14 PM
Orca44 Orca44 is offline
Centurion
 
Join Date: Nov 2004
Location: Cape Town, South Africa
Posts: 124
Default

I cannot think why none of the various methods you attempted did not work, but I
were in your shoes, I would start from the beginning and try to work out exactly
were the problem appears.

Can you read from the database correctly? If you enter a row into the database
by hand, does your application read that row correctly?

Can you write to any other tables? You could try creating a different table with
only one field to verify that you can write to the database.

Can you write to a different database with the same structure? It might be that
a corruption occurred in the database.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Access Insert Query does not inserting
Access Insert Query does not inserting
Access Insert Query does not inserting Access Insert Query does not inserting
Access Insert Query does not inserting
Access Insert Query does not inserting
Access Insert Query does not inserting Access Insert Query does not inserting Access Insert Query does not inserting Access Insert Query does not inserting Access Insert Query does not inserting Access Insert Query does not inserting Access Insert Query does not inserting
Access Insert Query does not inserting
Access Insert Query does not inserting
 
Access Insert Query does not inserting
Access Insert Query does not inserting
 
-->