5/15/2024 8:33:08 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss using the SalesLogix OLE DB Provider from external applications (including usage of built-in provider procedures). View the code of conduct for posting guidelines.
|
|
|
|
Inserting records with lots of fields
Posted: 01 Dec 08 11:25 AM
|
Hello,
I'm using the provider with 7.2, and am hitting a problem with inserts.
So far, I've been using prepared statements, like example 1 below, and this works fine. The problem comes when inserting into large tables (Eventix anyone?  ) At which point keeping track of all the ?'s and parameter add sequence gets somewhat risky. Trouble is, I can't seem to get .add to work, is that limited by the provider, or by me making errors? :D
So, couple of questions really:
1. Are prepared statements like Ex.1 the best way?
2. Can dataadapters be used like Ex.2?
3. Can I use named-parameters instead of a big list of "?" ?
Thanks,
Mark
Example 1 (works, but unscalable with 80+ fields):
Sql = "INSERT INTO mytable (field1, field2, field3) VALUES (?, ?, ?)"
Dim cmd As New OleDbCommand(Sql, _con)
cmd.Parameters.AddWithValue("@", MyVal1)
cmd.Parameters.AddWithValue("@", MyVal2)
cmd.Parameters.AddWithValue("@", MyVal3)
cmd.ExecuteNonQuery()
Example 2 (can't get this to work)
Sql = "SELECT * FROM EVXEVRESV WHERE 1=0"
Dim da As OleDbDataAdapter = New OleDbDataAdapter(Sql, _con)
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
Dim ds As New DataSet
da.Fill(ds, "EVXEVRESV")
Dim dr As DataRow = ds.Tables("EVXEVRESV").NewRow
dr("EVXEVRESVID") = EVXEVRESVID
dr("FIELD1") = Val1
dr("FIELD2") = Val2
...
dr("FIELDn") = Valn
ds.Tables("EVXEVRESV").Rows.Add(dr) |
|
|
| |
| |
|
Re: Inserting records with lots of fields
Posted: 02 Dec 08 4:22 AM
|
Brilliant! That got me in the right direction, thanks a lot 
For future reference, here's a working example. (The connection is already established elsewhere, and referenced by slx._con, the Account and Contact that are passed in simply contain the fields for those, and EVXEVRESVID is a primary key that I looked up previously)
Private Sub AddFreeReservation(ByVal EVXEVRESVID As String, ByVal Account As AccountClass, ByVal Contact As ContactClass)
Dim Sql As String = ""
Dim cmd As New OleDbCommand(Sql, slx._con)
Dim Sql As String = "SELECT * FROM EVXEVRESV WHERE 1=0"
Dim da As OleDbDataAdapter = New OleDbDataAdapter(Sql, slx._con)
Dim cb As OleDbCommandBuilder New OleDbCommandBuilder(da)
Dim ds As New DataSet
da.Fill(ds, "EVXEVRESV")
Dim dr As DataRow = ds.Tables("EVXEVRESV").NewRow
dr("EVXEVRESVID") = EVXEVRESVID
dr("CREATEUSER") = Me.fld_CREATEUSER
dr("CREATEDATE") = Me.fld_CREATEDATE
dr("MODIFYUSER") = Me.fld_MODIFYUSER
dr("MODIFYDATE") = Me.fld_MODIFYDATE
dr("RESVMADEBYCONTACT") = String.Format("{0} {1}", Contact.FirstName, Contact.LastName)
dr("RESVMADEBYCONTACTID") = Contact.ContactID
dr("RESVTYPE") = ""
dr("RESVSTATUS") = ""
dr("RESVMADEBYACCOUNT") = Account.Account
dr("RESVMADEBYACCOUNTID") = Account.AccountID
dr("REVIEWREQUIRED") = "F"
dr("REVIEWTYPE") = ""
dr("REVIEWDESC") = ""
dr("REVIEWBYDATE") = DBNull.Value
dr("REVIEWUSER") = ""
dr("REVIEWUSERID") = ""
dr("RESVFOLDERNAME") = String.Format("{0}, {1} | {2}", Contact.LastName, Contact.FirstName, Account.Account)
dr("CURRENCYTYPE") = "GBP"
dr("COMMENTS") = ""
ds.Tables("EVXEVRESV").Rows.Add(dr)
da.Update(ds.Tables("EVXEVRESV").GetChanges)
ds.AcceptChanges()
End Sub
|
|
|
|
Re: Inserting records with lots of fields
Posted: 04 Dec 08 12:51 PM
|
Am a little lost on this, but am liking what I see!
1. what is the Me. object? Where/How is it established?
2. While we're on Me.....since this is going through the SLX OLE DB Provider, why are You updating CreateDate, et al? not necessary.
3. how do you get those pretty references to Account, Contact.....is that through a Global class object ? How do you set that up?
Thanks! |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|