Process multiple SQL Results / DataSet / SQL-Connection

berbericz

Hello Experts,



I'm a beginner in development with Coresuite customize.



I would like to develop a Function-Button on Sales Order which calculates freight-costs dependent on the weight of all Items in Sales Order.



Unfortunately the total weight (of all rows) of Sales Order is calculated in separated window and not stored in database (Table INV1).



Therefore I have to create a SQL-Query to get the total weight of each row (-> multiple SQL results).



How can I store and process the result with Coresuite Customize?



I tried to do that with a dataset and SQLDataAdapter but it doesn't work (although successfully parsed). I receive following error-message when clicking on function button.



[CODE]

Error in coresuite customize Rule (XYZ):

System.InvalidOperationException: Die ConnectionString-Eigenschaft wurde nicht initialisiert.

bei System.Data.SqlClient.SqlConnection.PermissionDemand()

bei System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

bei System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

bei System.Data.SqlClient.SqlConnection.Open()

bei COR_Utility.Helper.Run_FB_COR_CUS_00000013(CustomizeEvent pVal)

[/CODE]



part of my development:



[CODE]

try

{

// get Document Number

string docnum = customize.UI.Components.TextEdit.GetFromUID(pVal.Form, "8").Value;



// create sql selecting all lines quantity, weight and total weight of line

string sql = string.Format("SELECT T0.[Quantity] * T0.[Weight1] AS LineSum FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocEntry] = T1.[DocEntry] and T1.[DocNum]= '{0}'", docnum);



// execute sql and get result

System.Data.DataSet dataset = new System.Data.DataSet();



System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection();



sqlConnection.Open();



System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(sql, sqlConnection);



System.Data.SqlClient.SqlDataAdapter sqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter(sql, sqlConnection);



sqlDataAdapter.Fill(dataset);



if (dataset.Tables[0].Rows.Count > 0)

{

System.Text.StringBuilder output = new System.Text.StringBuilder();

int i = 0;



foreach (System.Data.DataRow existingRow in dataset.Tables[0].Rows)

{

i = i + 1;

MessageBox.Show(existingRow.ItemArray.ToString()+ i + ". runde", "OK");

}

}



}

catch (Exception ex)

{

customize.Messaging.StatusBar.WriteError("Error in coresuite customize Rule (ZEBM_DOC:0002: Calculate and add Freights): " + ex.ToString());

customize.Messaging.Debug.WriteMessage("Error in coresuite customize Rule (ZEBM_DOC:0002: Calculate and add Freights): " + ex.ToString(), customize.Messaging.Debug.DebugLevel.Exception);

}

[/CODE]



Do I need the "SwissAddonFramework.Utils.SQL" for Sql-Connection? or is it possible to do that via System.Data.SqlConnection?



Thanks for your help!



GB

Paolo Manfrin

Hi,

the problem here is that you are using a SqlConnection where the connectionstring is not initialized.



You might find helpfull the following commands:



[CODE]DataTable oDataTable = new DataTable("my datatable");





string connectionString = SwissAddonFramework.B1Connector.GetB1Connector().SQLConnectionString.ConnectionString;





using (SqlConnection oSqlConn = new SqlConnection(connectionString))

{

oSqlConn.Open();



using (SqlDataAdapter oSDA = new SqlDataAdapter(query, oSqlConn))

{

oSDA.Fill(oDataTable);

}

}

[/CODE]





ht

paolo

berbericz

Hi Paolo,



thanks for your help - I'm able to calculate the total weight of sales order!



With this calculated weight I would like to store a Amount in header-Freights (Freight charges) of Sales order.



How can I do that?



Thanks in advance,

GB

Paolo Manfrin

With something similar to:





[color=#000000]customize[/color][color=#666600].[/color][color=#000000]UI[/color][color=#666600].[/color][color=#660066]Components[/color][color=#666600].[/color][color=#660066]TextEdit[/color][color=#666600].[/color][color=#660066]GetFromUID[/color][color=#666600]([/color][color=#000000]pVal[/color][color=#666600].[/color][color=#660066]Form[/color][color=#666600],[/color][color=#000000] [/color][color=#008800]"targetID"[/color][color=#666600]).[/color][color=#660066]Value = "myfreightValue"[/color]



hth

paolo

berbericz

Thank you with that code I'm able to save calculated value in a field on sales order (where the function button is).



BUT how can I put/save the value in the Amount-field (and maybe also freight code and other required info) of the separated window "freight charges"?



The "Freight Charges" window is "Form = 3007".



Both does not work:

[CODE]

Matrix.GetFromUID(pVal.Form, "3007").GetValue("2", pVal.Row - 1)= totalsum.ToString();

//OR

customize.UI.Components.TextEdit.GetFromUID(pVal.Form, "3007").Value = totalsum.ToString();

[/CODE]



Do I have to open the freight window in the background before via a command?



It would be great if you could give me some advices. Thanks!

Gerrit

Paolo Manfrin

Hi Gerrit,

yes whe you use UI component the form must be on focus



You have to:

1. open the form

2. set the focus on that form

3. set the value



hth

paolo

berbericz

Hi Paolo,



sorry but I have to say that it's not easy for me to develop these steps with little c# and "SwissAddonFramework" knowledge.



Could you please give me one code-example for step 1 and 2?



Thank you very much!

GB

berbericz

hey Paolo,



okay it was not so complicated as I thought! I could understand the princip.



Thanks for your great help.



Regards,

GB