Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

oledbAdapter commandText and parameters? With an Autonumber Primary Key?

Posted on 2005-04-16
13
Medium Priority
?
775 Views
Last Modified: 2008-01-09
Can someone please illustrate commandText and Parameters for oledb Select, Insert, Update, and Delete commands based on the following Access db table:

TableA has:
FieldID(autonumber primary key)
Field1 (text)

Thank you!
0
Comment
Question by:majnun
  • 6
  • 5
12 Comments
 
LVL 5

Expert Comment

by:maralans
ID: 13799288
Here are some examples that you may find helpful.


Private Sub CreateSubscribers()
        'set connection criteria.
        Dim conn As New OleDb.OleDbConnection( _
            "Provider = Microsoft.Jet.OleDb.4.0;" & _
            "Data Source=c:\Nads\NadsDatabase.mdb")
        Dim Cmd As New System.Data.OleDb.OleDbCommand
        conn.Open()

        'create and populate the subscribers table.
        With Cmd
            .Connection = conn
            .CommandText = ("SELECT sRemarks, sAcctNum, sFname&' '&sLname AS sFullName, sPhone," & _
            "sHseNum&' '& IIF(IsNull(sHseFrac),'',sHseFrac&' ') & IIF(IsNull(sPfx),'',sPfx&' ')" & _
            "&sStreet&' '& IIF(IsNull(sSfx),'',sSfx&' ') AS sFullAddress," & _
            "sAptNum, sCity, sState, sZip, sPubId, sFodID, sCopies, sAtz," & _
            "sActive, sActiveID, sReason, sInst, sMsg, sMailDate, sRouteNum,sSun," & _
            "sMon, sTue, sWed, sThu, sFri, sSat  INTO Subscribers IN" & _
            "'c:\nads\nadsdatabase.mdb' FROM [TempAsl];")
            On Error Resume Next
            .ExecuteNonQuery()
        End With

        'add a column with primary key.
        With Cmd
            .Connection = conn
            .CommandText = ("ALTER TABLE Subscribers ADD COLUMN sId autoincrement;")
            On Error Resume Next
            .ExecuteNonQuery()
        End With

With Cmd
            .Connection = conn
            .CommandText = ("UPDATE Subscribers SET Subscribers.sRouteNum = '000000000';")
            On Error Resume Next
            .ExecuteNonQuery()
        End With

 'close the connection.
        conn.Close()

  ' Open an oledb connection and update Subscribers table.
        Dim conn2 As New OleDb.OleDbConnection( _
                      "Provider = Microsoft.Jet.OleDb.4.0;" & _
                      "Data Source=c:\Nads\NadsDatabase.mdb")
        Dim cmd2 As New System.Data.OleDb.OleDbCommand
        conn2.Open()

  With cmd2
            .Connection = conn2
            .CommandText = ("INSERT INTO History IN 'c:\nads\nadsdatabase.mdb'" & _
            "SELECT sAcctNum, sRemarks, sFodId," & _
             "sHseNum&' '& IIF(IsNull(sHseFrac),'',sHseFrac&' ') & IIF(IsNull(sPfx),'',sPfx&' ')" & _
             "&sStreet&' '& IIF(IsNull(sSfx),'',sSfx&' ') AS sFullAddress," & _
            " sAptNum, sReason, sMsg, sMailDate FROM [TempMail]")
            On Error Resume Next
            .ExecuteNonQuery()
        End With

  'delete all 'stop' customers in TempMail where SubAcctNum in TempMail and Subcribers match
        With cmd2
            .Connection = conn2
            .CommandText = ("DELETE FROM(TempMail)WHERE (((TempMail.sRemarks) Like 'STOP') AND ((TempMail.sAcctNum) In (SELECT sAcctNum FROM Subscribers)))")
            On Error Resume Next
            .ExecuteNonQuery()
        End With

 conn2.Close()

0
 
LVL 23

Accepted Solution

by:
b1xml2 earned 2000 total points
ID: 13801828
Imports System.Data
Imports System.Data.OleDb

Public NotInheritable Class MyTable
      
      Private Sub New()
      End Sub
      
      Private Const ConnectionString As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=c:\data.mdb"

      Private Shared Function NewConnection() As OleDbConnection
            Return New OleDbConnection(MyTable.ConnectionString)
      End Function

      Private Shared Function NewInsertCommand(ByVal connection As OleDbConnection) As OleDbCommand
            Dim cmd As OleDbCommand = connection.CreateCommand()
            cmd.CommandText = "INSERT INTO Table1([Field1]) VALUES(?)"
            cmd.Parameters.Add("Field1",OleDbType.VarWChar,50,"Field1")
            Return cmd
      End Property
      
      Private Shared Function NewSelectCommand(ByVal connection As OleDbConnection) As OleDbCommand
            Dim cmd As OleDbCommand = connection.CreateCommand()
            cmd.CommandText = "SELECT FieldID,Field1 FROM Table1 WHERE FieldID = ?"
            cmd.Parameters.Add("FieldID",OleDbType.Integer,0,"FieldID")
            Return cmd
      End Property
      
      Private Shared Function NewUpdateCommand(ByVal connection As OleDbConnection) As OleDbCommand
            Dim cmd As OleDbCommand = connection.CreateCommand()
            cmd.CommandText = "UPDATE Table1 SET Field1 = ? WHERE FieldID = ?"
            cmd.Parameters.Add("Field1",OleDbType.VarWChar,50,"Field1")
            cmd.Parameters.Add("FieldID",OleDbType.Integer,0,"FieldID")
            Return cmd
      End Function
      
      Private Shared Function NewDeleteCommand(ByVal connection As OleDbConnection) As OleDbCommand
            Dim cmd As OleDbCommand = connection.CreateCommand()
            cmd.CommandText = "DELETE Table1 WHERE FieldID = ?"
            cmd.Parameters.Add("FieldID",OleDbType.Integer,0,"FieldID")
            Return cmd
      End Function
      
      Public Shared Function NewTable() As DataTable
            Dim table As New DataTable("MyTable")
            Dim column As DataColumn = table.Columns.Add("FieldID",GetType(System.Integer))
            column.AutoIncrement = True
            column.AutoIncrementSeed = -1
            column.AutoIncrementStep = -1
            column.AllowDBNull = False
            column.Unique = True
            table.PrimaryKey = New DataColumn () { column }
            column = table.Columns.Add("Field1",GetType(System.String))
            Return table
      End Function
      
      Public Shared Function NewAdapter() As OleDbDataAdapter
            Dim connection As OleDbConnection = NewConnection()
            Dim cmdSelect As OleDbCommand = NewSelectCommand(connection)
            Dim cmdInsert As OleDbCommand = NewInsertCommand(connection)
            Dim cmdUpdate As OleDbCommand = NewUpdateCommand(connection)
            Dim cmdDelete As OleDbCommand = NewDeleteCommand(connection)
            Dim adapter As New OleDbDataAdapter(cmdSelect)
            adapter.InsertCommand = cmdInsert
            adapter.DeleteCommand = cmdDelete
            adapter.UpdateCommand = cmdUpdate
            Return adapter
      End Function
End Class

Call like so:

Dim table As DataTable = MyTable.NewTable()
Dim adapter As OleDbDataAdapter = MyTable.NewAdapter()
adapter.SelectCommand.Parameters(0).Value = 15
adapter.Fill(table)
'existing row - which we shall assume is the first row
'mark it as deleted so that the delete command can fire
table.Rows(0).Delete()
'do some changes
'add new row
Dim newRow As DataRow = table.NewRow()
newRow("Field1") = "Hello World!"
table.Rows.Add("Field1")
'another way to add
table.Rows.Add(New Object() {Nothing,"Hello Again"}
'now open connections
adapter.SelectCommand.Connection.Open
adapter.Update(table.GetChanges())
adapter.SelectCommand.Connection.Close
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13801846
Whatever you do, do not use the code that maralans has provided. Try to learn how to do it the ADO.NET way and avoid using stuff as shown in maralans code:
>>
 ("INSERT INTO History IN 'c:\nads\nadsdatabase.mdb'" & _
            "SELECT sAcctNum, sRemarks, sFodId," & _
             "sHseNum&' '& IIF(IsNull(sHseFrac),'',sHseFrac&' ') & IIF(IsNull(sPfx),'',sPfx&' ')" & _
             "&sStreet&' '& IIF(IsNull(sSfx),'',sSfx&' ') AS sFullAddress," & _
            " sAptNum, sReason, sMsg, sMailDate FROM [TempMail]")
<<

Uglyyyyyyyyy!!! and does not show comprehension of ADO.NET
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:majnun
ID: 13818348
b1xml2:

Thank you for your excellent post, it is so far just what I was looking for... however I have a few questions.

Your solution looks similar to what I have been doing, except I have been using datasets so that I can use datarelations to keep different tables "synced up" as the user moves through the form's bindingcontext...

I'm still unclear about how the primary key column in code works with access (which is really what prompted me to ask the question in the first place)...

It looks like your code is doing the job of creating the primary key, and not access... is this the case? If on the other hand Access has fieldID set to be a primary key, won't it complain when you try insert a row without setting a value for it (since it is required)? And in either case, how does the above code deal with multiple users, since each will have a seperate snapshot of the database, how does the column we create know how to deal with it?

I think I might switch over to GUIDs instead of primary keys, but I want to first understanding how to deal with them, so that my solution can handle them if required.

Thanks for your continued assistance!
0
 

Author Comment

by:majnun
ID: 13819843
How would I use your code with a dataset so I can get use from a form's binding context?
0
 

Author Comment

by:majnun
ID: 13819881
Also I'm confused how it is possible to add a column to a table with the same name as a column that the select statement also "adds" to it.

Maybe my concept of what is going on is fundementally flawed...
0
 

Author Comment

by:majnun
ID: 13820072
b1xml2:

You totally rock man...

I adapted your code (which I asked for in a simple direct way which you so kindly illustrated) to my current project, the main problem of which was I wasn't sure how to deal with the primary key column.

So I set that columns properties thus:
.AutoIncrement = True
.AutoIncrementSeed = -1
.AutoIncrementStep = -1
.AllowDBNull = False
.Unique = True

And it works as expected.

I had tried it before but wasn't sure how to set the seed and step... perhaps you can explain the rationale behind the -1 values for those properties.

Thanks again.

If you can offer insight into the questions listed above in this and previous comments I'd greatly appreciate it.

I'll then close out the question and award you the well earned points.

Thanks!
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13820631
The rationale between the -1 is that it allows you to easliy differentiate between records you have just created (but have yet to submit to the database) against records that exist in the database (and which you have retrieved via the DataAdapter)

.AutoIncrementSeed = -1 <-- the ID starts from -1
.AutoIncrementStep = -1 <---  The ID increments -1 for every subsequent record added.
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13820640
GUIDs are better in that when you create a new record, you know the primary key, whereas with the approach mentioned, you;d need to merge the results from the database to the current row being affected.
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13820644
when you insert the records, the only values of interest to Access would be what is specified in the INSERT command's SQL Statement....
0
 

Author Comment

by:majnun
ID: 13824484
Makes sense...

And is the above method multi-user "safe"?

I'd imagine yes, since access makes all the primary keys anyway correct?
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13824502
yep...and also the transaction....!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question