database

[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..

[Renukavani]

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

[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

[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();

[ogimy]

i still get error.can u give me full code begin Private sub until End sub.

[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();
    }

[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