insert record into DB via gateway object??

You can use OpenRecordset to send INSERT and UPDATE commands as well.

Hi Jason, thanks for your reply. What is the syntax for using this as an alternative to using ADO? Could you please share an example of what would be needed in the code to insert the new record into the RMS database, for example assume that I declare a global variable, glbRecSet and set it equal to the following:

Set glbRecSet = session.Database.OpenRecordset('INSERT SQL string', True)

How do I take the recordset that is created or 'opened' here, and commit it to the database?

Thank you, Kevin

"Jas> You can use OpenRecordset to send INSERT and UPDATE commands as well. > > --

Hi Kevin,

INSERT statements won't return a record set, they just execute and return an empty set. However, I also believe that the recordset returned by OpenRecordset() is read-only (I could be wrong though, as I've never really tried; I've always just written my changes back using SQL INSERT, UPDATE, and DELETE commands, not through ADO methods such as Recordset.Update())

Also, I'm pretty sure you can access all of the tables through various QSRules objects without using the OpenRecordset method. Open up the VS Object Browser and take a look at QSRules.SessionClass.Database. Personally I've always done everything through OpenRecordset, and just verified (or assumed) my commands were issued properly.

Hi Jason

according to the CG, the OpenRecordSet can be either Read-Only or Read-Write, depending on how your set the AsReadOnly parameter. So far I have my code set up just the same way as if I am opening a recordset in order to return data from the DB, this is what I have thus far:

Public Function Process(session As Object) As Boolean 'Dim posSession As QSRules.SessionClass Dim posSession As Object Set posSession = session With posSession.Transaction glbsqlstr = "INSERT INTO C_CustMsg (Number, Message) VALUES (glbNbr, glbMsg) Set glbRecSet = session.Database.OpenRecordset(glbsqlstr, False) End With Process = True End Function

I'm not sure if this is the right way to go about this but as you can see I'm trying to insert a new record into a custom DB table that I created. I really want to avoid using ADO within my code, because I don't want to have to include my DB username and password in the project's code. However I'm unsure of how to take the recordset that gets created by the INSERT statement, and actually commit it to the database. Any ideas? Or is there an easier way of doing this that you would be willing to share?

Thank you, Kevin

You don't have to do anything to the recordset. If this is going to work at all, the record will already be inserted.

There's another function that takes an ADO Command object and executes it. If the OpenRecordset method is not performing the Insert currently, you could try that one. It has a strange name, but I think part of it was "ExecuteCommand" - check the object browser...

Hi Glenn, I assume that you are talking about either:

session.database.rsExecuteUpdateCommand(C As Command, sql As String)

or:

session.database.rsExecuteSQL(C As Command, sql As String)

nothing in the cg about either one of them, have you used either of these in an application before with any success? I'm assuming that I could just use this in my previous code in place of the OpenRecordSet function...and setting my glbRecSet variable equal to one of these would then execute my INSERT statement?

However, what would I need to use for the command parameter for these functions?

Thanks, Kevin

"Glenn Adams [MVP - Retail Mgmt]" wrote:

sorry i'll try to clarify that a little better, does the command parameter that the function is asking for, referring to some kind of ADO command object and if so is there something in qsrules that i need to use as the parameter?

thanks, kevin

Justed wanted to post back and let you know that the OpenRecordSet function does indeed work for writing the data to the database, it was not working for me before because I must have had an error in my SQL string, but the code syntax was/is correct. OpenRecordSet will work

Thank you Glenn and Jason for your help

Kevin

"ksk> sorry i'll try to clarify that a little better, does the command parameter