Member Avatar for ogimy 
My situation is to update the balance in database.In database i have 3 column which is user name, pin code, and the balance.i have three customer, each customer have user name, pin code, and balance.The question is how to update balance of customer first, how to update customer second, and how to update customer third and so on?using vb  2003.please someone help me.newbie..
  • 3 Contributors
  • 6 Replies
  • 115 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Oxiegen

Dani AI

Generated

A few clarifications and a safe, working pattern for updating multiple balances (VB.NET / VS2003-compatible). was right that a loop is appropriate; 's VB attempt was close but had some typos and still used string concatenation. The example below shows a safer approach: parameterized UPDATE, a single prepared command reused inside a transaction, and simple error handling so updates are atomic and not vulnerable to SQL injection.

Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    ' Example input arrays — replace with values from your UI or data source
    Dim usernames() As String = {"user1", "user2", "user3"}
    Dim pincodes() As String = {"pin1", "pin2", "pin3"}
    Dim newBalances() As Decimal = {100D, 200D, 300D}

    Dim connString As String = "Server=YOUR_SERVER;Database=YOUR_DB;User Id=YOUR_USER;Password=YOUR_PASS;"
    Using cn As New System.Data.SqlClient.SqlConnection(connString)
        cn.Open()
        Dim tx As System.Data.SqlClient.SqlTransaction = cn.BeginTransaction()
        Try
            Dim cmd As New System.Data.SqlClient.SqlCommand( _
                "UPDATE test123 SET balance = @balance WHERE username = @username AND pincode = @pincode", cn, tx)
            cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@balance", System.Data.SqlDbType.Decimal))
            cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@username", System.Data.SqlDbType.NVarChar, 50))
            cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@pincode", System.Data.SqlDbType.NVarChar, 50))

            Dim i As Integer
            For i = 0 To UBound(usernames)
                cmd.Parameters("@balance").Value = newBalances(i)
                cmd.Parameters("@username").Value = usernames(i)
                cmd.Parameters("@pincode").Value = pincodes(i)
                Dim affected As Integer = cmd.ExecuteNonQuery()
                If affected = 0 Then
                    ' No matching row — handle/log as needed (wrong PIN or missing user)
                End If
            Next
            tx.Commit()
        Catch ex As Exception
            tx.Rollback()
            Throw
        Finally
            cn.Close()
        End Try
    End Using
End Sub

Notes and troubleshooting:

  • Always use parameters instead of concatenating values into SQL to prevent injection and quoting errors. See SqlCommand and SqlParameter for details: SqlCommand and SqlParameter.
  • Include pincode in the WHERE clause to avoid changing the wrong account and to validate the PIN.
  • Use transactions for batch updates so partial updates don't leave data inconsistent.
  • Check ExecuteNonQuery() return value to detect missing rows and log or notify accordingly.
  • For production, consider moving the logic into a stored procedure to reduce round-trips and centralize validation.

Recommended Answers

All 6 Replies

Member Avatar for Renukavani

If u know how many records want to update, go for loop and update it one by one with condition.

Member Avatar for ogimy

If u know how many records want to update, go for loop and update it one by one with condition.

Can you show me example
Member Avatar for Renukavani

Try this,

string InsertQuery =string.Empty;
        SqlConnection con1 = new SqlConnection("Specify the connection string details");
        con1.Open();
        SqlDataAdapter ada = new SqlDataAdapter("Select username from test123", con1);
        DataSet ds = new DataSet();
        ada.Fill(ds);
        SqlCommand cmd1 = null;
        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
        {
            InsertQuery = "Update test123 set balance=" + (10+j) +" where username = '" + ds.Tables[0].Rows[j]["username"].ToString() + "'";
            cmd1 = new SqlCommand(InsertQuery, con1);
            cmd1.ExecuteNonQuery();
        }
        con1.Close();
Member Avatar for ogimy 
i still get error.can u give me full code begin Private sub until End sub.
Member Avatar for Renukavani

I writen in C#, plz try to change into vb.net

protected void btnInsert_Click(object sender, EventArgs e)
    {
        string InsertQuery = string.Empty;
        SqlConnection con1 = new SqlConnection("Data Source=***;Initial Catalog=master; Persist Security Info=True; User ID=***; Password=****");//ConfigurationManager.ConnectionStrings["MasterConnectionString"].ConnectionString);
        con1.Open();
        SqlDataAdapter ada = new SqlDataAdapter("Select username from test123", con1);
        DataSet ds = new DataSet();
        ada.Fill(ds);
        SqlCommand cmd1 = null;
        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
        {
            InsertQuery = "Update test123 set balance=" + (10 + j) + " where username = '" + ds.Tables[0].Rows[j]["username"].ToString() + "'";
            cmd1 = new SqlCommand(InsertQuery, con1);
            cmd1.ExecuteNonQuery();
        }
        con1.Close();
    }
Member Avatar for Oxiegen

Here it is in VB.NET.
Remember to change all *** into YOUR data source, username and password.

Private Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnInsert.Click
	Dim InsertQuery As String = String.Empty
	Dim con1 As SqlConnection = New SqlConnection("Data Source=***;Initial Catalog=master; Persist Security Info=True; User ID=***; Password=***")
	con1.Open()
	Dim ada As SqlDataAdapter = New SdlDataAdapter("SELECT username FROM test123", con1)
	Dim ds As DataSet = New DataSet()
	ada.Fill(ds)
	Dim cmb1 As SqlCommand = Nothing
	
	For j As Integer = 0 To ds.Tables(0).Rows.Count - 1
		InsertQuery = "UPDATE test123 SET balance = " & (10 + j) & " WHERE username = '" & ds.Tables(0).Rows(j).Item("username").ToString() & "'"
		cmd1 = New SqlCommand(InsertQuery, con1)
		cmd1.ExecuteNonQuery()
	Next
	con1.Close()
End Sub
Edited by Oxiegen because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.