Parse every SQL row in table, and return value in col 2 based on search for ref in col 1

Question

User1104155786 posted

Hi All

Hoping somebody can assist with this one. A quick overview of the result of the code and what i need it to return;

1. Code is for tapping into PRTG using the PRTG API
2. Taps into PRTG API to return a device ID unique to the device being monitored

ID	V1C_Reference	Device_ID
1	V1C12345	09876
2	V1C23456	BLANK
3	V1C34567	BLANK

The end result is that a unique number is returned which is mapped to the V1C reference. this works but only for 1 row.
I need the code to go through every SQL row in the table "Circuit" and add the unique number to the Device_ID column for each V1C reference

I have a snippet of code below which works well. It returns the desired result. but again, just for one V1C reference and not every row. Where "var V1C_Reference" is the text for the search to perform in PRTG, but i know this needs to be dynamic

Hoping this explains well enough

Regards

---

protected void update_DB()
{
string conString = @"Data Source=.\SQLEXPRESS;xxxxx";
SqlConnection con = new SqlConnection(conString);

try
{
con.Open();

var V1C_Reference = "V1Cxxxxxxx";

var PRTG_SENSOR_ID = PRTG_Auth.GetSensors(Property.Name, FilterOperator.Contains, V1C_Reference).FirstOrDefault();
var PRTG_DEVICE_ID = PRTG_SENSOR_ID.ParentId;

SqlCommand sqlupd = new SqlCommand("UPDATE Circuit SET Device_ID = @devID WHERE V1C_Reference = @V1C_Reference", con);

sqlupd.Parameters.Clear();
sqlupd.Parameters.AddWithValue("@devID", PRTG_DEVICE_ID);
sqlupd.Parameters.AddWithValue("@V1C_Reference", V1C_Reference);

sqlupd.ExecuteNonQuery();
}
catch (SqlException ee)
{
MessageBox.Show(ee.Message);
}
finally
{
con.Close();
con.Dispose();
}
}

Answer 1

User665608656 posted

Hi SW_GGR,

According to your description, if you want to update multiple rows at once, then you need to use a loop in the update method, and the parameters need to be dynamic.

I created an example using gridview to display data and update multiple rows of data. You can refer to the following writing:

<form id="form1" runat="server">
        <div>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            <asp:Button ID="Button1" runat="server" Text="Update" OnClick="Button1_Click" />
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>
        </div>
    </form>

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindData();
            }

        }
        public void BindData()
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ResumeInfoConnectionString"].ConnectionString))
            {
                conn.Open();
                string sql = "select * from Customers";
                SqlDataAdapter ad = new SqlDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                ad.Fill(ds, "NewUsers");
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            string constr = ConfigurationManager.ConnectionStrings["ResumeInfoConnectionString"].ConnectionString.ToString();
            using (SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                foreach (GridViewRow row in GridView1.Rows)
                {
                    using (SqlCommand cmd = new SqlCommand("UPDATE Customers SET Name = @Name, Country = @Country WHERE CustomerId = @CustomerId"))
                    {
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@CustomerId", Convert.ToInt32(row.Cells[0].Text.ToString()));
                        cmd.Parameters.AddWithValue("@Name", TextBox1.Text.ToString());
                        cmd.Parameters.AddWithValue("@Country", TextBox1.Text.ToString());
                        cmd.ExecuteNonQuery();
                    }
                }
                con.Close();
            }
            BindData();
        }

Here is the result of this demo:

Best Regards,

YongQing.

Answer 2

User1104155786 posted

Thank you YongQing

I have used the foreach reference to achieve the result needed based on your reply

Regards