How can I write to an external table within a breakout?

[indikad]

I want to write to an external ( user created table - within the JIWA company databse)

my code below did not work.

any hints ? Thanks very much.

Code: Select all

With SalesQuoteObject.Database
        rHwnd = .StatementOpen(.ConnectionWrite, , , 0)
        sqlInsert = "INSERT INTO  key_QO_LineCustomValues (InvoiceHistoryID) "
        sqlInsert = sqlInsert & " 'ABCD1234' "

        If .ExecuteSQL(CInt(rHwnd), sqlInsert) Then
            MsgBox "good"
        End If
        .StatementClose CInt(rHwnd)

    End With


then I tried to use teh folowing and it gives me an overflow error - I suspect the sql user does not let me do inserts, sice if I replace this users with sa it worked.

Code: Select all

sqlInsert = "INSERT INTO  MyTable (InvoiceHistoryID) "
    sqlInsert = sqlInsert & " VALUES ('ABCD1234') "
    With SalesQuoteObject.Database
       MsgBox "server = " &  .ServerName & " db = " &  .DatabaseName
        sCon = "Driver={SQL Server};Server=" & .ServerName & ";Database=" & .DatabaseName & ";UID=" & .SQLLoginName & ";PWD=" & .SQLLoginPassword
        MsgBox sCon
        cn.Open sCon
        cn.Execute sqlInsert
    End With
Set cn = nothing


[Mike.Sheen]

Indika,

Your first snippet does not commit the transaction. I assume when you say it did not work, it did not return any errors, but the row was not written to the table ?

Try this (I just added the .Commit, error handling, and the VALUES clause)

Code: Select all

With SalesQuoteObject.Database
        rHwnd = .StatementOpen(.ConnectionWrite, , , 0)
        sqlInsert = "INSERT INTO  key_QO_LineCustomValues (InvoiceHistoryID) "
        sqlInsert = sqlInsert & " VALUES ('ABCD1234') "

        If .ExecuteSQL(CInt(rHwnd), sqlInsert) Then
            MsgBox "good"
        Else
            MsgBox "Error : " & .ErrorMessage
        End If

        .Commit

        .StatementClose CInt(rHwnd)

    End With


then I tried to use teh folowing and it gives me an overflow error - I suspect the sql user does not let me do inserts, sice if I replace this users with sa it worked.

Code: Select all

sqlInsert = "INSERT INTO  MyTable (InvoiceHistoryID) "
    sqlInsert = sqlInsert & " VALUES ('ABCD1234') "
    With SalesQuoteObject.Database
       MsgBox "server = " &  .ServerName & " db = " &  .DatabaseName
        sCon = "Driver={SQL Server};Server=" & .ServerName & ";Database=" & .DatabaseName & ";UID=" & .SQLLoginName & ";PWD=" & .SQLLoginPassword
        MsgBox sCon
        cn.Open sCon
        cn.Execute sqlInsert
    End With
Set cn = nothing


Of course, the SQL User would need permissions to INSERT into the table MyTable - have you set this ?

If you prefer to work with ADO directly, the Jiwa ADO connection can be accessed via the Database.ADOConnectionObject, thus eliminating your need to create a connection yourself.

[indikad]

Thanks Mike. I will try this code.

[Mike.Sheen]

Thanks Mike. I will try this code.

Did that work for you ?

[indikad]

Hi Mike,

The modification I was writing evolved in to something that did not need writing to the table so I did not try this starightaway.

I tried it today and ( as you pointed out) get thrownback because the sql user does not have writing rights to my table. I would rather use the ADOConnection , so that I dont have to worry about setting permissions etc.

Thanks very much for this. you code will anyhow be usefull for writing to Jiwa tables.