create sqldatasource through coding

[johnny.g]

hi all i m using asp.net 2005 and sql server 2005...i have a dropdown list in my page...i have a table called as details...all i want to do is bind the fname from details table to dropdownlistbox...but the thing is tht i want to each and every step through coding...is it possible to do so....pls let me know how to do it,,,,,,,frm whr do i strt ...
thnks in advance

[serkan sendur]

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlDataSource mySource = new SqlDataSource("Data Source=SERKAN\\MYSQLSERVER2005;Initial Catalog=deneme;Integrated Security=True;Pooling=False", "select * from names");
            myDropDown.DataSource = mySource.Select(DataSourceSelectArguments.Empty);
            myDropDown.DataValueField = "PKID";
            myDropDown.DataTextField = "name";
            myDropDown.DataBind();
        }
    }

[johnny.g]

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlDataSource mySource = new SqlDataSource("Data Source=SERKAN\\MYSQLSERVER2005;Initial Catalog=deneme;Integrated Security=True;Pooling=False", "select * from names");
            myDropDown.DataSource = mySource.Select(DataSourceSelectArguments.Empty);
            myDropDown.DataValueField = "PKID";
            myDropDown.DataTextField = "name";
            myDropDown.DataBind();
        }
    }

coool mann,,,,thts working fine,,,,,well i m using a connection string called as connectionstring2,,,,,,,,,,how can i use this connection string instead of writing the whole connection on pageload ,,,thnks for ur help,,

[johnny.g]

guys how can i use the same code without using sql datasource,,,i came to know tht we can use sql data reader and sql command and other such things,,,,is it possible,,,,let me know,,
thnks in advance

[bala24]

Yes you can. Follow these steps.

1).Create a query which fetches the fname from details table.
2).Call cmd.ExecuteReader with this query.
3).Now bind the dropdownlist from the datareade.

For this step you can use the following code.

.DataSource = .DataBind()

[johnny.g]

Yes you can. Follow these steps.

1).Create a query which fetches the fname from details table. 2).Call cmd.ExecuteReader with this query. 3).Now bind the dropdownlist from the datareade.

For this step you can use the following code.

hi bala,,,well i m not able to do it,,,,
here is my code

If Not Page.IsPostBack Then
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim dr As SqlDataReader
Dim str1 As String
Dim strcon As String

str1 = "select * from details"
strcon = "Data Source=PC-1;Initial Catalog=jay;user id=;pwd=;Trusted_Connection=yes"
conn = New SqlConnection(strcon)
conn.Open()
cmd = New SqlCommand(str1, conn)

dr = cmd.ExecuteReader()
DropDownList1.DataSource = dr
DropDownList1.DataValueField = "srno"
DropDownList1.DataTextField = "fname"
DropDownList1.DataBind()
______________________________________
it gives an error...any suggestions,,i have written ths code on page load,,,reply asap

[bala24]

Well, your sql query is fetching all columns from the table and your dropdownlist cannot be bound to such a recordset.
What you need to do is modify your sql query to fetch only fname as in

select fname from details

[johnny.g]

Well, your sql query is fetching all columns from the table and your dropdownlist cannot be bound to such a recordset. What you need to do is modify your sql query to fetch only fname as in

select fname from details

no its not workin bala,,,,well i have tried another code,,here it is

dbconn.Open()
        If Not Page.IsPostBack Then
            dbadap = New SqlDataAdapter("select fname from details", dbconn)
            dbadap.Fill(ds, "details")
            DropDownList1.DataSource = ds.Tables("details").DefaultView
            DropDownList1.DataBind()
            DropDownList1.SelectedValue = ""
            MsgBox(DropDownList1.SelectedValue)
            DropDownList1.DataValueField = "srno"
            DropDownList1.DataTextField = "fname"
        End If

well in this code,,the dropdown list gets filled with system.data.datarowview

i want tht to fill with fname...reply asap

[bala24]

Try this.

http://bytes.com/forum/thread259618.html

[johnny.g]

Try this.

http://bytes.com/forum/thread259618.html

thnks dude ,,,will go thr tht link n let u know if it wrks for me,,,,
by the way i found a the code tht i wanted finally,,,here it is ,its very simple using only sql data reader,,,here it is

Dim conn = New SqlConnection("Data Source=(local);Initial Catalog=(databasename);user id=;pwd=;Trusted_Connection=yes")
        conn.open()
        Dim dr As SqlDataReader
        Dim cmd = New SqlCommand("select * from details", conn)
        dr = cmd.ExecuteReader()
        DropDownList1.DataSource = dr
        DropDownList1.DataTextField = "fname"
        DropDownList1.DataValueField = "srno"
        DropDownList1.DataBind()

well this works for me,,thnks for all ur help

[bala24]

Well glad to help.
Do remember to set this thread as Solved so that others could benefit from this solution.

[johnny.g]

Well glad to help. Do remember to set this thread as Solved so that others could benefit from this solution.

,,yup hav done tht,,,thnks 1ce again

[johnny.g]

thnks dude ,,,will go thr tht link n let u know if it wrks for me,,,, by the way i found a the code tht i wanted finally,,,here it is ,its very simple using only sql data reader,,,here it is

Dim conn = New SqlConnection("Data Source=(local);Initial Catalog=(databasename);user id=;pwd=;Trusted_Connection=yes")
        conn.open()
        Dim dr As SqlDataReader
        Dim cmd = New SqlCommand("select * from details", conn)
        dr = cmd.ExecuteReader()
        DropDownList1.DataSource = dr
        DropDownList1.DataTextField = "fname"
        DropDownList1.DataValueField = "srno"
        DropDownList1.DataBind()

well this works for me,,thnks for all ur help

dude is there ne way we can get the result by editing the line

DropDownList1.DataSource = dr

i dont want to use datasource at all,is it possible,,,,reply asasp

[bala24]

Well one way is to loop through the datareader and get the values in each row and add it to dropdownlist.
This involves the following steps.

While end of datareader create list item instance add fname of row(i) to listitem text add srid of row(i) to listitem value add listitem to dropdownlist end while

[johnny.g]

Well one way is to loop through the datareader and get the values in each row and add it to dropdownlist. This involves the following steps.

well bala ,,it was a bouncer to me,,can u explain me with the exact code..
reply asap,,thnks in advance

[bala24]

Do fill in the rest of the details

Dim li As ListItem

If rd.HasRows Then
Do While rd.Read
	
        li = New ListItem
        li.Text= rd.Item("fname")
        li.Value = rd.Item("srid")
        <dropdownlist>.Items.Add(li)
Loop

rd.close()

End If

[johnny.g]

Do fill in the rest of the details

Dim li As ListItem

If rd.HasRows Then
Do While rd.Read
	
        li = New ListItem
        li.Text= rd.Item("fname")
        li.Value = rd.Item("srid")
        <dropdownlist>.Items.Add(li)
Loop

rd.close()

End If

mind blowing maaannnnn,,,,,solved,,,thnks,,,hav added to ur reputation,,
thnks 1ce again

[jpramac]

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlDataSource mySource = new SqlDataSource("Data Source=SERKAN\\MYSQLSERVER2005;Initial Catalog=deneme;Integrated Security=True;Pooling=False", "select * from names");
            myDropDown.DataSource = mySource.Select(DataSourceSelectArguments.Empty);
            myDropDown.DataValueField = "PKID";
            myDropDown.DataTextField = "name";
            myDropDown.DataBind();
        }
    }

This is a great sample. I've been searching google for an hour to find a good sample for creating a sqldatasource in a class. Thank you!!