ADO/ADO.NET - Insert into Quickbook

Hello All,

could you help me to insert a value into Quickbooks using ADODB. just go through the following

==========

'sSQL = "SELECT Name FROM Customer WHERE (((Customer.Name)='" & frmysql.vcustomername & "'));"
                oConnection = CreateObject("ADODB.Connection")
                oRecordset = CreateObject("ADODB.Recordset")                             oConnection.Open(sConnectString)
                oRecordset.open(sSQL, oConnection, adOpenStatic, adLockOptimistic)

==========

this is being working fine i mean getting values form the quickbooks when i use SELECT query but Now I could able to execute/open the INSERT query in sSQL variable in the above oRecordset.open(sSQL, oConnection, adOpenStatic, adLockOptimistic) I've tried with oRecordset.execuate (sSQL, oConnection, adOpenStatic, adLockOptimistic) also..but i cudn't able to go further its falling out error could anyone help me...

Sam

Hi, Use following example as a reference.

dim conn as ole::adodb.connection
dim rs as ole::adodb.recordset
dim cs as c
dim cs_admin as c
dim recordsAffected as n = 0

sql = "INSERT INTO Customer (Name, ParentRefFullName,Companyname,Billaddressaddr1,"\
"Contact,Billaddressaddr2,Billaddressaddr3, Billaddresscity, Billaddressstate, Billaddresspostalcode)" \
"VALUES('" + jobname + "','" + companyname + "','" + companyname + "','" + address1 + "'"\
",'" + contact + "','" + address2 + "','" + address3 + "','" + city + "','" + state + "','" + postalcode + "')"

-Paresh

Hi,

I think this is what you want.
http://doc.qodbc.com/qodbc/20/tables/table_info_us96ee.html?tables_id=2

Click on the table you want the info on. There is a column called Req Ins which I believe denotes whether the field is required for an record insert. I could be wrong about that but I know the info you need is located here.

Hopefully Al will see this and chime in as he knows this stuff inside and out.

-Paresh

Hi,

Inserting fields into QuickBooks Letters
Predefined QuickBooks Letter fields are available from the QuickBooks toolbar.

Viewing the QuickBooks toolbar
When you choose Edit Letter or Create Letter from the Write Letters wizard, Word opens with the QuickBooks toolbar visible near the top of the window. If you have closed the toolbar and want to redisplay it:

1. Display the View menu, and then choose Toolbars.

2. Select the QuickBooks toolbar for the type of letter you're using.

The QuickBooks toolbar represents the choices you made when you entered information in the Write Letters wizard.

Using the QuickBooks toolbar
If you are preparing one of the preformatted letters, such as a collection letter, your Word document will appear with the fields already filled in. However, if you are creating a different kind of letter, use this procedure to insert fields.

1. Display the View menu, then choose Toolbars and select the appropriate toolbar.

2. Choose the toolbar that contains the names you want to use. Your choice will appear in the upper right area of your document. You can move it to a more convenient place, or add it to the Microsoft toolbar so that it doesn't overlay your document.

3. Place the cursor at any point in the QuickBooks Letter where you want to insert a field.

4. Set the QuickBooks field from a the drop-down menu in the QuickBooks letter fields toolbar.

The selected field appears at the insertion point. In the final letter, QuickBooks information will appear where you've inserted the field. Remember to add a space between fields just as you would if you were manually entering the information.

5. Depending on the kind of letter you are creating, format and edit the letter.

Format the fields as you would in any Word document. Your formatting selections will appear in the final letter.

Saving your work:
If you edit an existing letter, remember to save it with a different name so that your edited versions are preserved if you have to re-install QuickBooks. If you save an edited letter with the same name as the original one, your changes will be lost when you re-install.

-Paresh

hi

not sure but u can try this

'Insert Customer/Job and Invoice Records into QuickBooks Pro using QODBC driver for ODBC interface

option strict
dim qb_customer_job as p
dim qb_invoice_line as p
dim invoice_line_cnt as n
dim jobname as c
dim companyname as c
dim fullname as c
dim contact as c
dim city as c
dim state as c
dim postalcode as c
dim address1 as c
dim address2 as c
dim address3 as c
dim customfieldother as c
dim sql as c
dim list_id as c
dim filter_string as c
dim query.filter as c
dim qry as p
dim rec_count as n
dim user_string as c

qb_customer_job = table.open("qb_customer_job.dbf",FILE_RW_SHARED)
filter_string = "User_id = '" + gUser + "'"
query.filter = filter_string
qry = qb_customer_job.query_create()
rec_count = qry.records_get()

if rec_count = 1 then
    companyname = trim(qb_customer_job.Companyname)
    jobname = trim(qb_customer_job.Name)
    fullname = companyname + ":" + jobname
    address1 = trim(qb_customer_job.Billaddressaddr1)
    address2 = "ATTN: " + trim(qb_customer_job.Billaddressaddr2)
    contact = trim(qb_customer_job.Billaddressaddr2)
    address3 = trim(qb_customer_job.Billaddressaddr3)
    city = trim(qb_customer_job.Billaddresscity)
    state = trim(qb_customer_job.Billaddressstate)
    postalcode = trim(qb_customer_job.Billaddresspostalcode)
    customfieldother = trim(qb_customer_job.Customfieldother)
else
    ui_msg_box("QuickBooks","More than one record for user on Customer Job table")
    qb_customer_job.close()
    end
end if
qb_customer_job.close()

'********************************** Add Customer Job

dim conn as ole::adodb.connection
dim rs as ole::adodb.recordset
dim cs as c
dim cs_admin as c
dim recordsAffected as n = 0

sql = "INSERT INTO Customer (Name, ParentRefFullName,Companyname,Billaddressaddr1,"\
"Contact,Billaddressaddr2,Billaddressaddr3, Billaddresscity, Billaddressstate, Billaddresspostalcode)" \
"VALUES('" + jobname + "','" + companyname + "','" + companyname + "','" + address1 + "'"\
",'" + contact + "','" + address2 + "','" + address3 + "','" + city + "','" + state + "','" + postalcode + "')"

'********************************** debug(1)
user_string = "User ID=" + gUser
if gCompanyCode = "AL" then
    cs_admin = gAccessConnectString
else
    cs_admin = gPeckConnectString
end if
cs = stritran(cs_admin, "User ID=admin", user_string)

on error goto error_connection
conn.Open(cs)
on error goto 0 ' turn off error trap

hourglass_cursor(.t.)

on error goto error_sql
conn.execute(sql,recordsAffected)
on error goto 0 ' turn off error trap

if recordsAffected = 1 then ' one record has been added
    rs.open("select * from customer where name = '" + jobname + "'", conn)
    list_id = rs.fields.item("listid").value
    dim itemref as c
    dim itemdesc as c
    dim itemamount as n
   
    'adding invoice detail
    qb_invoice_line = table.open("qb_invoice_line.dbf",FILE_RW_SHARED)
    query.filter = filter_string
    qry = qb_invoice_line.query_create()
    rec_count = qry.records_get()
    if rec_count > 0 then
        qb_invoice_line.fetch_first() 'invoice line detail
        while .NOT. qb_invoice_line.fetch_eof()
                itemref = Trim(qb_invoice_line.Itemref_fullname)
                itemdesc = Trim(qb_invoice_line.Desc)
                itemamount = qb_invoice_line.Amount
                recordsAffected = 0

'********************************** Insert Invoice detail into QuickBooks cache (FQSaveToCache = True)

                invoice_line_cnt = invoice_line_cnt + 1
                if itemdesc = "" then
                    if itemref <> "" then
                        sql = "INSERT INTO InvoiceLine (Invoicelineitemreffullname, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache)"\
                         "VALUES('" + itemref + "'," + itemamount + "," + itemamount + ",1)"
                     end if
                else
                    if itemref <> "" then
                        sql = "INSERT INTO InvoiceLine (Invoicelineitemreffullname, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache)"\
                         "VALUES('" + itemref + "','" + itemdesc + "'," + itemamount + "," + itemamount + ",1)"
                    else
                        sql = "INSERT INTO InvoiceLine (InvoiceLineDesc, FQSaveToCache)"\
                         "VALUES('" + itemdesc + "',1)"
                     end if
                 end if
                 on error goto error_invoice_insert
                conn.execute(sql,recordsAffected)
                on error goto 0 ' turn off error trap
                if recordsAffected = 1 then
                    qb_invoice_line.fetch_next()
                else
                    ui_msg_box("QuickBooks","Invoice Detail not posted to QuickBooks")
                    qb_invoice_line.close()   
                    conn.close()
                    delete conn
                    delete rs
                    end
                end if
        end while
    else
        ui_msg_box("QuickBooks","Zero records on invoice detail table")
        qb_invoice_line.close()
        conn.close() ' close Connection object and any active Recordset objects associated with the connection
        delete conn
        delete rs
        END
    end if

'********************************** Insert Invoice Header - this will trigger QuickBooks to
automatically save invoice detail also (held in cache)

    if gCompanyCode = "AL" then
        sql = "INSERT INTO Invoice (CustomerRefListId, PONumber, Customerreffullname, Billaddressaddr1,"\
         "Billaddressaddr2,Billaddressaddr3, Billaddresscity, Billaddressstate, Billaddresspostalcode, FOB)" \
         "VALUES('" + list_id + "','" + customfieldother + "','" + fullname + "','" + address1 + "'"\
         ",'" + address2 + "','" + address3 + "','" + city + "','" + state + "','" + postalcode + "','" + jobname + "')"
    else
        sql = "INSERT INTO Invoice (CustomerRefListId, PONumber, Customerreffullname, Billaddressaddr1,"\
         "Billaddressaddr2,Billaddressaddr3, Billaddresscity, Billaddressstate, Billaddresspostalcode, FOB)" \
         "VALUES('" + list_id + "','" + jobname + "','" + fullname + "','" + address1 + "'"\
         ",'" + address2 + "','" + address3 + "','" + city + "','" + state + "','" + postalcode + "','" + customfieldother + "')"
    end if   
    recordsAffected = 0
    on error goto error_invoice_insert
    conn.execute(sql,recordsAffected)
    on error goto 0 ' turn off error trap
    if recordsAffected = 1
        ui_msg_box("QuickBooks",str(invoice_line_cnt) + " invoice items posted to QuickBooks")
    else
        ui_msg_box("QuickBooks","Invoice header not posted to QuickBooks")
    end if
    qb_invoice_line.close()   
else
    ui_msg_box("QuickBooks","Customer Job Not Added")
end if

hourglass_cursor(.f.)

conn.close() ' close Connection object and any active Recordset objects associated with the connection
delete conn
delete rs
end

error_connection:
on error goto 0
ui_msg_box("QuickBooks","Unable to connect to QuickBooks")
delete conn
delete rs
end

error_sql:
on error goto 0
ui_msg_box("QuickBooks","Customer Job already exists or Customer Name mis-match; delete customer job or sync customer name and try again")
conn.close()
delete conn
delete rs
end

error_invoice_insert:
on error goto 0
ui_msg_box("QuickBooks","Item category does not exist in QuickBooks: Invoice not posted")
conn.close()
delete conn
delete rs
end

also chk this

http://www.qodbc.com/default.htm
http://msgboard.alphasoftware.com/alphaforum/showthread.php?t=70306

http://idnforums.intuit.com/messageview.aspx?catid=3&threadid=4026

http://en.allexperts.com/q/Using-MS-Access-1440/QuickBooks-MS-Access.htm

all d best

silna

try executing with connection object,
oConnection.Execute sSQL

You cannot execute Insert/Update/delete statements with recordset object, you can only execute select statements with that.