Row does not get inserted to DB 1

[Miked811]

Hi,

Please kindly help me. It does not add the row I inserted:

Dim conn As New OleDb.OleDbConnection
Dim da As OleDb.OleDbDataAdapter
Dim ds As New DataSet
Dim sSQL As String
Dim sConnString As String

For Each fleDailyFiles In arrDailyFiles

  sConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\DNC.mdb"
  conn.ConnectionString = sConnString
  conn.Open()

  da = New OleDb.OleDbDataAdapter("SELECT * FROM DNC ORDER BY PhoneNum ASC", conn)

  da.Fill(ds)

  sSQL = "INSERT INTO DNC (phonenum) VALUES ('1234567890')"

  da.InsertCommand = New OleDb.OleDbCommand(sSQL, conn)

  da.Update(ds)

  ds.GetChanges()


  'This part works fine...
  For Each Row As DataRow In ds.Tables(0).Rows
      txtDisplayStatus.Text = txtDisplayStatus.Text & (Row(0)) & vbCrLf
  Next

 conn.Close()

Thanks so much in advance...

 

[chrissie1]

What are you trying to do.

First you get all the records from DNC
Then you want to insert a record in that table with a '1234567890' phonenumber.
Then you want to display all the records on the screen?

If this is the right order of things we can help.

Otherwise explain better what you are trying to achieve.

Christiaan Baes
Belgium

My Blog

 

[Miked811]

hi,

yes, that it precisely what i want to do. the display is just secondary, it can be disregarded. my main concern is the insertion of data to the dnc table which i would like to learn.

i have tried the commandbuilder and seem not get it to write to the table too. on the other hand, is there another way of not using commandbuilder to write data to the table.

please kindly help... thanks a lot in advance...

 

[chrissie1]

Since I'm a very kind person (or so everybody keeps telling me)

Dim conn As New OleDb.OleDbConnection
Dim da As OleDb.OleDbDataAdapter
Dim ds As New DataSet
Dim sSQL As String
dim cmd as new OleDb.OleDbCommand
Dim sConnString As String

For Each fleDailyFiles In arrDailyFiles

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\DNC.mdb"
  conn.ConnectionString = sConnString
  conn.Open()

  cmd = new oledb.oledbcommand
  cmd.commandtext = "INSERT INTO DNC (phonenum) VALUES (@phonenum)"
  cmd.paramaters.add(new oledb.oledbparameter("@phonenum",oledbtype.varchar,30)
  cmd.parameters(0).value = "123456789"
  cmd.connection = conn
  cmd.executenonquery()

  da = New OleDb.OleDbDataAdapter("SELECT * FROM DNC ORDER BY PhoneNum ASC", conn)

  da.Fill(ds)

  'This part works fine...
  For Each Row As DataRow In ds.Tables(0).Rows
      txtDisplayStatus.appentex(Row(0) & controlchars.crlf)
  Next

conn.Close()
next

this was more or less how I see it. although I have no idea what the "For Each fleDailyFiles In arrDailyFiles" is doing in there.

And as usual I didn't test this so it could explode (small one, nothing serious)

Christiaan Baes
Belgium

My Blog

 

[Miked811]

hi,

it worked...the for loop there is important for my app.

here's the real deal:

For Each fleDailyFiles In arrDailyFiles

    sConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
                   Source=" & [COLOR=red]fleDailtyFiles.ToString()[/color]

    conn.ConnectionString = sConnString

    conn.Open()

    'The insert command
    ...
    ...
    cmd.ComandText = [COLOR=red]"INSERT INTO DNC IN " & 
           'C:\history.mdb' SELECT DNC.* FROM DNC"[/color]
    ...
    ...
Next

I am using System.IO to get/read files from a directory.

where:

1) arrDailyFiles = are the mdb files in an array in a
certain directory

2) fleDailyFiles = is an array individual mdb file that will
be used in the connectionString

3) When the mdb is open, all records gets appended/dumped to
the History.mdb table (please see the SQL Insert
Statement)

4) The loop goes on and reads all the mdb files in the array
and appends all the records to History.mdb.

My next question is, how do I append all records into History.mdb?

Thanks for your help.

 

[Miked811]

hey,

i got it. i commented out some lines and change the SQL statement for the insert and it worked...

question though, with "conn.Close()", will this mean that all connections to the DataSet, DataTables and DataReaders etc...will be closed and all resources will be released. i mean, im used to VB6, where i have to close all the recordsets and connections and set them to "nothing". you know what i mean?

cmd = New OleDb.OleDbCommand

cmd.CommandText = "INSERT INTO DNC IN 'C:\history.mdb' 
                  SELECT DNC.* FROM DNC"

'cmd.CommandText = "INSERT INTO DNC (phonenum) VALUES 
                   (@phonenum)"

'cmd.Parameters.Add(New OleDb.OleDbParameter("@phonenum", 
                   OleDb.OleDbType.VarChar, 30))

'cmd.Parameters(0).Value = "2222222222"

cmd.connection = conn

cmd.executenonquery()

 

[chrissie1]

dataset and datatables are diconnected which means that once filled they stay filled no matter what happens to the connection. The datareader however needs the connection and is similar to the recordset of VB.

you should dispose all the objects that have a dispose method.

so conn.dispose after conn.close and that should be good.

setting things to nothing is good for nothing and won't help. Calling dispose will.


Christiaan Baes
Belgium

My Blog

 

[Miked811]

hey Christiaan, thanks a lot for your help...my app is good to go for now and will add some more functionalities...

 

[chrissie1]

I see you have never used the "thank ..." link. Perhaps you should in future.

Christiaan Baes
Belgium

My Blog