How to bound dropdown box to players table with pa

method · June 15th, 2005, 11:22 AM

Hi expert. i got a web form that you can see its picture in the links below. I
want some one teach me how i can bound my drop down box to players table and loads playerno value
in the drop down box. Right now the form only loads value to text boxes from penalties table(dropdown box is empty).
i want the drop down box shows the playerno value corespondented to paymentno by default then allow me to select other values.
I be happy if some one show me how i can accopmplish this task.Thank u and looking forward to your reply.

Best wishes

method

http://i5.photobucket.com/albums/y18...od007/form.jpg ( form in design view)

http://i5.photobucket.com/albums/y18...d007/form2.jpg ( form in action )

http://i5.photobucket.com/albums/y18...7/19932c0e.jpg ( db reletionshon)

Here is my code:

Code:

Imports System.Data
Imports System.Data.SqlClient
Public Class dropdowntextbox
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.DataSetforpenalties1 = New updatemachrecord.DataSetforpenalties
        CType(Me.DataSetforpenalties1, System.ComponentModel.ISupportInitialize).BeginInit()
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "PENALTIES", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("PAYMENTNO", "PAYMENTNO"), New System.Data.Common.DataColumnMapping("PLAYERNO", "PLAYERNO"), New System.Data.Common.DataColumnMapping("PEN_DATE", "PEN_DATE"), New System.Data.Common.DataColumnMapping("AMOUNT", "AMOUNT")})})
        Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT PAYMENTNO, PLAYERNO, PEN_DATE, AMOUNT FROM dbo.PENALTIES WHERE (PAYMENTNO " & _
        "= @paymentno)"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@paymentno", System.Data.SqlDbType.SmallInt, 2, "PAYMENTNO"))
        '
        'SqlInsertCommand1
        '
       .........................
        '
        'SqlUpdateCommand1
        '
        Me.SqlUpdateCommand1.CommandText = "UPDATE dbo.PENALTIES SET PAYMENTNO = @PAYMENTNO, PLAYERNO = @PLAYERNO, PEN_DATE =" & _
        " @PEN_DATE, AMOUNT = @AMOUNT WHERE (PAYMENTNO = @Original_PAYMENTNO) AND (AMOUNT" & _
        " = @Original_AMOUNT OR @Original_AMOUNT IS NULL AND AMOUNT IS NULL) AND (PEN_DAT" & _
        "E = @Original_PEN_DATE OR @Original_PEN_DATE IS NULL AND PEN_DATE IS NULL) AND (" & _
        "PLAYERNO = @Original_PLAYERNO); SELECT PAYMENTNO, PLAYERNO, PEN_DATE, AMOUNT FRO" & _
        "M dbo.PENALTIES WHERE (PAYMENTNO = @PAYMENTNO)"
        Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PAYMENTNO", System.Data.SqlDbType.SmallInt, 2, "PAYMENTNO"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PLAYERNO", System.Data.SqlDbType.SmallInt, 2, "PLAYERNO"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PEN_DATE", System.Data.SqlDbType.DateTime, 4, "PEN_DATE"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@AMOUNT", System.Data.SqlDbType.Float, 8, "AMOUNT"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PAYMENTNO", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PAYMENTNO", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_AMOUNT", System.Data.SqlDbType.Float, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "AMOUNT", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PEN_DATE", System.Data.SqlDbType.DateTime, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PEN_DATE", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PLAYERNO", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PLAYERNO", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlDeleteCommand1
        '
        Me.SqlDeleteCommand1.CommandText = "DELETE FROM dbo.PENALTIES WHERE (PAYMENTNO = @Original_PAYMENTNO) AND (AMOUNT = @" & _
        "Original_AMOUNT OR @Original_AMOUNT IS NULL AND AMOUNT IS NULL) AND (PEN_DATE = " & _
        "@Original_PEN_DATE OR @Original_PEN_DATE IS NULL AND PEN_DATE IS NULL) AND (PLAY" & _
        "ERNO = @Original_PLAYERNO)"
        Me.SqlDeleteCommand1.Connection = Me.SqlConnection1
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PAYMENTNO", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PAYMENTNO", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_AMOUNT", System.Data.SqlDbType.Float, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "AMOUNT", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PEN_DATE", System.Data.SqlDbType.DateTime, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PEN_DATE", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PLAYERNO", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PLAYERNO", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=HAYATTLAPTOP;packet size=4096;user id=web;data source=""(local)"";pe" & _
        "rsist security info=True;initial catalog=teniss2;password=web"
        '
        'DataSetforpenalties1
        '
        Me.DataSetforpenalties1.DataSetName = "DataSetforpenalties"
        Me.DataSetforpenalties1.Locale = New System.Globalization.CultureInfo("en-US")
        CType(Me.DataSetforpenalties1, System.ComponentModel.ISupportInitialize).EndInit()

    End Sub
    Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
    Protected WithEvents DropDownList1 As System.Web.UI.WebControls.DropDownList
    Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox
    Protected WithEvents TextBox3 As System.Web.UI.WebControls.TextBox
    Protected WithEvents Label4 As System.Web.UI.WebControls.Label
    Protected WithEvents Label3 As System.Web.UI.WebControls.Label
    Protected WithEvents Label2 As System.Web.UI.WebControls.Label
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Protected WithEvents DataSetforpenalties1 As updatemachrecord.DataSetforpenalties
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region
    Dim MyConnection As SqlConnection

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        MyConnection = New SqlConnection("server=localhost;database=teniss2;uid=web;pwd=web;")
        If Not IsPostBack Then


            SqlSelectCommand1.Parameters(0).Value = Convert.ToInt16(Request.QueryString("PAYMENTNO"))

            SqlDataAdapter1.Fill(DataSetforpenalties1, "PENALTIES")

            TextBox1.DataBind()

            TextBox2.DataBind()

            TextBox3.DataBind()



        End If
    End Sub

End Class

jbenson001 · June 15th, 2005, 12:34 PM

You bind it just like you did with the textboxes. First run your sql command, fill a dataset and then ddl.databind(). In the properties of the ddl, specify a value column and a data column.

method · June 15th, 2005, 12:51 PM

Quote:

quote:Originally posted by jbenson001
You bind it just like you did with the textboxes. First run your sql command, fill a dataset and then ddl.databind(). In the properties of the ddl, specify a value column and a data column.

Thank u for u reply.This is the way i bind my text boxes.
1)created the 3 text boxes
2)draged the sql data adpater and followed the on screen instructions...
3)created a new datasat by right clicing on the sqldatadapter connection
4)right clicked on each text box and went to its properties
and click on databindings and select the approperte feild for each text box
5)used the code on load.

If i want bind my dropdown box . should i drage a new data
adapter and make new data set and connectioin ?

what should i put in sql command to refrence to
players table when i drag new adapter?
The problem here is that text box and drop down box each binds
to diffrent tables and i do not know how to create the binding
for the drop down box. i be happy if u explain a little more.Thanks

jbenson001 · June 15th, 2005, 01:13 PM

You do need a new data adapter and dataset. You can use the same connection that you used in the other adapter. You sqlstatment must pull back some sort of ID column and some sort of text column that you wnat to display. In the properties of the ddl , set your properties, datavaluefield, datatextfield from the dataset.

method · June 15th, 2005, 01:29 PM

Quote:

quote:Originally posted by jbenson001
You do need a new data adapter and dataset. You can use the same connection that you used in the other adapter. You sqlstatment must pull back some sort of ID column and some sort of text column that you wnat to display. In the properties of the ddl , set your properties, datavaluefield, datatextfield from the dataset.

Thanks for u reply. how i can construct my sql statement with out makeing a new dataset or without destroying it?

jbenson001 · June 15th, 2005, 01:44 PM

Create the SQL to pull the columns you need. Then fill the existing dataset.
dataadpter.fill(dataset, "tablename")

method · June 15th, 2005, 02:02 PM

Quote:

quote:Originally posted by jbenson001
Create the SQL to pull the columns you need. Then fill the existing dataset.
dataadpter.fill(dataset, "tablename")

Thanks for u reply. I know i need to add this code to my onload part

Code:

SqlDataAdapter1.Fill(DataSetforpenalties1, "PLAYERS")

            Me.DropDownList1..DataBind()

like this
[code]
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        MyConnection = New SqlConnection("server=localhost;database=teniss2;u id=web;pwd=web;")
        If Not IsPostBack Then

            SqlSelectCommand1.Parameters(0).Value = Convert.ToInt16(Request.QueryString("PAYMENTNO"))

            SqlDataAdapter1.Fill(DataSetforpenalties1, "PENALTIES")

            TextBox1.DataBind()

            TextBox2.DataBind()

            TextBox3.DataBind()

           SqlDataAdapter1.Fill(DataSetforpenalties1, "PLAYERS")

            Me.DropDownList1..DataBind()

        End If
    End Sub

[/code}

but when i right click on the drop down box and go to databindings i do not see the players name . i only see the penaltis table! where shold i put my sql statement ?

jbenson001 · June 15th, 2005, 02:16 PM

You need a second data adapter with the new sql statment and use that to fill the players table. right now you are filling the players table with the same data.