Private Sub updateLastLineItem() Dim rsLastItem As DAO.Recordset Dim rsPO As DAO.Recordset Set rsLastItem = CurrentDb.OpenRecordset("qry_PO_Networks_LastLineItem") Set rsPO = CurrentDb.OpenRecordset("tbl_PO_Infos") If Not rsLastItem.EOF Then rsLastItem.MoveFirst Do While Not (rsLastItem.EOF) rsPO.FindFirst "[PONumber] = '" & rsLastItem!PONumber & "'" If Not rsPO.NoMatch Then rsPO.Edit rsPO!LastLineItemSAP = rsLastItem![MaxOfLineItem] rsPO!LastLineItemCMA = rsLastItem![MaxOfLineItem] rsPO.Update End If rsLastItem.MoveNext Loop End If rsLastItem.Close Set rsLastItem = Nothing rsPO.Close Set rsPO = Nothing End Sub
<pre> private void updateLastLineItem1() { /* Private Sub updateLastLineItem() Dim rsLastItem As DAO.Recordset Dim rsPO As DAO.Recordset Set rsLastItem = CurrentDb.OpenRecordset("qry_PO_Networks_LastLineItem") Set rsPO = CurrentDb.OpenRecordset("tbl_PO_Infos") If Not rsLastItem.EOF Then rsLastItem.MoveFirst Do While Not (rsLastItem.EOF) rsPO.FindFirst "[PONumber] = '" & rsLastItem!PONumber & "'" If Not rsPO.NoMatch Then rsPO.Edit rsPO!LastLineItemSAP = rsLastItem![MaxOfLineItem] rsPO!LastLineItemCMA = rsLastItem![MaxOfLineItem] rsPO.Update End If rsLastItem.MoveNext Loop End If rsLastItem.Close Set rsLastItem = Nothing rsPO.Close Set rsPO = Nothing End Sub */ // connecting to the database //https://msdn.microsoft.com/en-us/library/ms254507(v=vs.100).aspx // using a DataAdapter or DataReader //https://msdn.microsoft.com/en-us/library/ms254931(v=vs.100).aspx //encrypting/decrypting connection strings in a web.config or app.config file //https://msdn.microsoft.com/en-us/library/89211k9b(VS.80).aspx //connection strings sometimes contain a backslash "\" character so you might need the "@" prefix to ensure the "\" is treated as a string literal instead of an escape character sequence (e.g. "\r" = carriage return in many c-like languages) System.String connectionString = @"server=localhost;integrated security=true;database=pubs"; //NOTE: if you are not using a ms sql server database, try using System.Data.OleDb instead of System.Data.SqlClient // putting our c# code in a using statement ensures the connection is closed and resources are disposed with no memory leaks using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString)) { //open the sql connection to the ms sql server database sqlConnection.Open(); //declare a new SqlCommand object System.Data.SqlClient.SqlCommand qry_PO_Networks_LastLineItem; //instantiate a new SqlCommand object qry_PO_Networks_LastLineItem = new System.Data.SqlClient.SqlCommand(); //set the Connection, CommandType and CommandText properties qry_PO_Networks_LastLineItem.Connection = sqlConnection;//our active "using" connection qry_PO_Networks_LastLineItem.CommandType = System.Data.CommandType.StoredProcedure;//change this when using a literal SQL string instead of a stored procedure qry_PO_Networks_LastLineItem.CommandText = "qry_PO_Networks_LastLineItem";//the name of the stored procedure or literal SQL string as applicable //declare and instantiate a new System.Data.SqlDataAdapter object in one line System.Data.SqlClient.SqlCommand tbl_PO_Infos = new System.Data.SqlClient.SqlCommand(); //set the Connection, CommandType and CommandText properties tbl_PO_Infos.Connection = sqlConnection;//our active "using" connection tbl_PO_Infos.CommandType = System.Data.CommandType.StoredProcedure;//change this when using a literal SQL string instead of a stored procedure tbl_PO_Infos.CommandText = "tbl_PO_Infos";//the name of the stored procedure or literal SQL string as applicable //declare and instantiate a new System.Data.SqlDataAdapter object in one line System.Data.SqlClient.SqlDataAdapter rsLastItemSqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter(qry_PO_Networks_LastLineItem); //we could again declare and instantiate a new System.Data.SqlDataAdapter object in one line //System.Data.SqlDataAdapter rsPOSqlDataAdapter = new System.Data.SqlDataAdapter("tbl_PO_Infos", sqlConnection); //but for clarity we use two lines //declare a new SQLDataAdapter object System.Data.SqlClient.SqlDataAdapter rsPOSqlDataAdapter; //instantiate a new SQLDataAdapter object passing in the name of the stored procedure and sql connection object rsPOSqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter(tbl_PO_Infos); //an ADO Recordset object most closely resemembles an ADO.NET DataTable object //declare and instantiate a new System.Data.DataTable object System.Data.DataTable rsLastItem = new System.Data.DataTable(); //we could again declare and instantiate a new System.Data.DataTable object //System.Data.DataTable rsPO = new System.Data.DataTable(); //but again for clarity we use two line //declare a new DataTable object System.Data.DataTable rsPO; //instantiate a new DataTable object rsPO = new System.Data.DataTable(); //fill the DataTable object named rsLastItem with the results from running the assigned stored procedure sql command "qry_PO_Networks_LastLineItem" rsLastItemSqlDataAdapter.Fill(rsLastItem); //fill the DataTable object named rsPO with the results from running the assigned stored procedure sql command "tbl_PO_Infos" rsPOSqlDataAdapter.Fill(rsPO); //we could use a foreach loop //foreach (System.Data.DataRow rsLastItemDataRow in rsLastItem.Rows){ foreach (System.Data.DataRow rsPODataRow in rsLastItem.Rows){} } //or we can use a traditional for loop or while loop System.Int32 numberOfRowsInRsLastItemDataTable = rsLastItem.Rows.Count; System.Int32 currentRowNumberInRsLastItemDataTable = 0; System.Int32 numberOfRowsInRsPODataTable = rsPO.Rows.Count; System.Int32 currentRowNumberInRsPODataTable = 0; while (currentRowNumberInRsLastItemDataTable < numberOfRowsInRsLastItemDataTable) { while (currentRowNumberInRsPODataTable < numberOfRowsInRsPODataTable) { if (rsLastItem.Rows[currentRowNumberInRsLastItemDataTable]["PONumber"] != rsPO.Rows[currentRowNumberInRsPODataTable]["PONumber"]) { rsPO.Rows[currentRowNumberInRsPODataTable]["LastLineItemSAP"] = rsLastItem.Rows[currentRowNumberInRsLastItemDataTable]["MaxOfLineItem"]; rsPO.Rows[currentRowNumberInRsPODataTable]["LastLineItemCMA"] = rsLastItem.Rows[currentRowNumberInRsLastItemDataTable]["MaxOfLineItem"]; } //same thing as currentRowNumberInRsPODataTable = currentRowNumberInRsPODataTable + 1; currentRowNumberInRsPODataTable += 1; } //same thing as currentRowNumberInRsLastItemDataTable +=1; currentRowNumberInRsLastItemDataTable = currentRowNumberInRsLastItemDataTable + 1; } //utilize the automated update mechansism System.Data.SqlClient.SqlCommandBuilder sqlCommandBuilder = new System.Data.SqlClient.SqlCommandBuilder(rsPOSqlDataAdapter); rsPOSqlDataAdapter.AcceptChangesDuringUpdate = true; //update the database rsPOSqlDataAdapter.Update(rsPO); } }
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)