<TABLE class='cdb-AltRow2' id='Table1' style='Z-INDEX: 101; LEFT: 189px; POSITION: absolute; TOP: 20px' cellSpacing='1' cellPadding='1' width='300' border='0'>
<TR>
<TD>ProductID</TD>
<TD><asp:textbox id='txtProductId' runat='server' Enabled='False'></asp:textbox></TD>
</TR>
<TR>
<TD>Quantity per unit</TD>
<TD><asp:textbox id='txtQuantity' runat='server'></asp:textbox></TD>
</TR>
<TR>
<TD>UnitPrice</TD>
<TD><asp:textbox id='txtUnitPrice' runat='server'></asp:textbox></TD>
</TR>
<TR>
<TD></TD>
<TD><asp:button id='btnUpdate' runat='server' Text='Update'></asp:button></TD>
</TR>
<TR>
<TD></TD>
<TD><asp:label id='lblError' runat='server' Visible='False'></asp:label></TD>
</TR>
</TABLE>
<asp:listbox id='ListBox1' style='Z-INDEX: 102; LEFT: 8px; POSITION: absolute; TOP: 12px' runat='server' AutoPostBack='True'></asp:listbox>
VB.NET
Dim productid As String
Dim strSQL As String
Dim strConn As String
Dim dr, dr1 As SqlDataReader
Dim mycn As SqlConnection
Dim mycmd As SqlCommand
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
strConn = 'server=localhost;uid=sa;database=northwind;pwd=;'
lblError.Visible = False
’ Put user code to initialize the page here
If Not Page.IsPostBack Then
mycn = New SqlConnection(strConn)
strSQL = 'Select * from Products'
mycmd = New SqlCommand(strSQL, mycn)
mycn.Open()
dr = mycmd.ExecuteReader(CommandBehavior.CloseConnection)
ListBox1.DataSource = dr
ListBox1.DataTextField = 'ProductID'
ListBox1.DataValueField = 'ProductID'
ListBox1.DataBind()
End If
End Sub ’Page_Load
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Try
lblError.Visible = False
Dim updateCmd As String = 'UPDATE Products SET QuantityPerUnit = @QuantityPerUnit ,' + 'UnitPrice = @UnitPrice where ProductId=@ProductId'
Dim cn As New SqlConnection(strConn)
Dim myCommand As New SqlCommand(updateCmd, cn)
myCommand.Parameters.Add(New SqlParameter('@QuantityPerUnit', txtQuantity.Text))
myCommand.Parameters.Add(New SqlParameter('@UnitPrice', Convert.ToDouble(txtUnitPrice.Text)))
myCommand.Parameters.Add(New SqlParameter('@ProductId', Convert.ToInt16(txtProductId.Text)))
cn.Open()
myCommand.ExecuteNonQuery()
lblError.Visible = True
lblError.Text = 'Record Updated successfully'
Catch ex As Exception
lblError.Visible = True
lblError.Text = ex.Message
End Try
End Sub ’btnUpdate_Click
Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
productid = ListBox1.SelectedItem.Value
txtProductId.Text = productid.ToString()
strSQL = 'Select * from Products where productid =' + productid
mycn = New SqlConnection(strConn)
mycmd = New SqlCommand(strSQL, mycn)
mycn.Open()
dr1 = mycmd.ExecuteReader(CommandBehavior.CloseConnection)
If dr1.Read() Then
txtProductId.Text = dr1('ProductId').ToString()
txtQuantity.Text = dr1('QuantityPerUnit').ToString()
txtUnitPrice.Text = dr1('UnitPrice').ToString()
Else
Response.Write('No data found')
End If
End Sub ’ListBox1_SelectedIndexChanged
C#
string productid;
string strSQL;
string strConn;
SqlDataReader dr,dr1;
SqlConnection mycn;
SqlCommand mycmd;
private void Page_Load(object sender, System.EventArgs e)
{
strConn ='server=localhost;uid=sa;database=northwind;pwd=;';
lblError.Visible =false;
// Put user code to initialize the page here
if (!Page.IsPostBack )
{
mycn = new SqlConnection(strConn);
strSQL ='Select * from Products';
mycmd = new SqlCommand (strSQL, mycn);
mycn.Open ();
dr=mycmd.ExecuteReader(CommandBehavior.CloseConnection );
ListBox1.DataSource = dr;
ListBox1.DataTextField ='ProductID';
ListBox1.DataValueField ='ProductID';
ListBox1.DataBind ();
}
}
private void btnUpdate_Click(object sender, System.EventArgs e)
{
try
{
lblError.Visible =false;
string updateCmd = 'UPDATE Products SET QuantityPerUnit = @QuantityPerUnit ,'
+ 'UnitPrice = @UnitPrice where ProductId=@ProductId';
SqlConnection cn = new SqlConnection (strConn);
SqlCommand myCommand = new SqlCommand(updateCmd, cn);
myCommand.Parameters.Add(new SqlParameter('@QuantityPerUnit', txtQuantity.Text ));
myCommand.Parameters.Add(new SqlParameter('@UnitPrice', Convert.ToDouble( txtUnitPrice.Text )));
myCommand.Parameters.Add(new SqlParameter('@ProductId', Convert.ToInt16 ( txtProductId.Text)));
cn.Open ();
myCommand.ExecuteNonQuery ();
lblError.Visible =true;
lblError.Text ='Record Updated successfully';
}
catch(Exception ex)
{
lblError.Visible =true;
lblError.Text =(ex.Message );
}
}
private void ListBox1_SelectedIndexChanged(object sender, System.EventArgs e)
{
productid = ListBox1.SelectedItem.Value ;
txtProductId.Text =productid.ToString ();
strSQL = 'Select * from Products where productid =' + productid ;
mycn = new SqlConnection(strConn);
mycmd = new SqlCommand (strSQL, mycn);
mycn.Open ();
dr1=mycmd.ExecuteReader(CommandBehavior.CloseConnection );
if(dr1.Read() )
{
txtProductId.Text = dr1['ProductId'].ToString ();
txtQuantity.Text = dr1['QuantityPerUnit'].ToString ();
txtUnitPrice.Text = dr1['UnitPrice'].ToString ();
}
else
{
Response.Write ('No data found');
}
}
Permalink
