Retrieve values from sql server 2005

[nandhini21]

Hi pple

I need help...
How to retrieve data from a table in sql server 2005? I used databound for my dropdownlist in my web form.
I hope my explanation is clear.

Please help...advance thanks..

nandhini

[SheSaidImaPregy]

a few ways to do it:

1. Bind it to a control, as you have with the dropdownlist. You would need to set up your connection, pull the information, put it in a "reader" and then assign the datasource and bind.

2. Assign it to variables within your subs or functions.
For this you need to do the steps above and assign it to a reader, then do a simple command "while readername.Read()" and then assign your controls with the values: "tbFirstName.Text = readername("columnname")". Then after all your variables get assigned, add the end command "end while" and close your reader with "readername.close".

Ex:

Dim commandname As New SqlCommand( "SELECT * FROM Users", connectionname )

if connectionname.State = ConnectionState.Closed then connectionname.Open()

Dim readername As SqlDataReader = commandname.ExecuteReader()

if readername.HasRows then
  while readername.Read()
    tbFirstName.Text = readername("FirstName")
    tbLastName.Text = readername("LastName")
    ltlDateJoined.Text = readername("DateJoined")
  end while
else
  ltlError.Text = "Sorry, there are no users in your database. Go recruit some little ones."
end if

readername.Close

if connectionname.State = ConnectionState.Open then connectionname.Close()

Simple enough, right?

[nandhini21]

Hi...thanks alot for the reply.
I kind of get it. But what if I have to use dropdownlist.

I mean.. Lets say I've stored values like ,"IT","GET" and "IMI" in the databound. Once the user select it, it sould be stored in the database.
what im doing is, the user enters a id number and selects a option from the dropdownlist.Once chosen they will be redirected to another page. So the selected info should be stored in the database. I'm using asp.net/visual basic.
Please help me..im new to this,...thanks alot..

nandhini

[ericstenson]

hi nandhini- ok, populating a dropdownlist from a database and inserting a record into the database from a dropdownlist list, programatically, are very similar. in your case:

Dim conn1 as new data.sqlclient.sqlconnection("YOUR CONNECTION STRING")
Dim InsertString as string
InsertString = "Insert into YOURTABLE (Column1, Column2) VALUES ('" & textbox1.text & "','" & dropdownlist1.selectedvalue & "')"
Dim InsertCommand as new Data.SqlClient.SqlCommand(InsertString, Conn1)
Conn1.open
InsertCommand.ExecuteScalar
Conn1.close

then do your server.transfer or whatever else you are doing to redirect.

Go ahead any copy the connection string from the wizard if you want.

IF you are just try to retrieve the values of the dropdown of the next page, just pass the value.

Hope this helps.

[nandhini21]

thanks ericstenson..i will work it out and get back to you..

Thanks alot for the help pple..

[SheSaidImaPregy]

eric!!! that's bad lol. Use parameters to pass values, don't inject them automatically into the query string! Ima find your table names and do a DROP :)

anyway, if you wish to save the values into a database, you need to have the dropdownlist autopostback set to true and set the onSelectedItemChange (I think that's it) set to the sub it calls.

Then within your sub, connect and insert into the database the information you need. However, you should only connect to the database when needed to. Hence, if the information isn't "top secret" then pass it to the next page where more questions are asked, THEN insert into the database.

To transfer to another page while saving information, you have many options:

1. User "server.transfer" and retrieve the values from the viewstate. server.transfer saves all values from the current page and transfers it to the next. However, you cannot transfer it a second time.
page 1 -> page 2 = OK (viewstate page 1 and 2)
page 1 -> page 2 -> page 3 = Bad (only viewstates from page 2 and page 3).

2. store it in the session variable. Just one variable, multiple values so on the next page you can store the session variable in an event variable (DIM variablename As String = Session("variable")) then do a string.split and make it into an array. Then use the values as you need and set the session("variable") to nothing.

3. querystring - if it isn't special data and you don't mind people reading your URL for the information, then pass it. It uses no server stuff to store it which means better practice. It's just completely insecure.

4. Store the current values in an XML file and read it out on the next page.

[nandhini21]

Hi pple..

Thanks for your help. But I cant get it. Can you show me some coding on how it goes. Let me elaborate more on my doubt.

My connection string is stored in the web config. The values for the dropdownlist are stored in one table itself, just the values.

Im using databound, So, how am i suppose to connect the databoud to the database and retrieve the values from the table.

Any questions? Plz help me, thanks alot!!

[ericstenson]

vice versa. you post your code. it is unclear exactly what you are trying to do:

are you trying to populate a dropdownlist from a database or are you trying to insert the selected value into a database for use on the next page.

since you seem not to be very experienced, try creating a "NEW DATA SOURCE" for the dropdownlist. This will trigger a wizard that will help you get the values. Next, on the properties tab of the dropdownlist, set the DataBoundText and DataBoundValue field to the corresponding column in the table.

Best of luck.

[SheSaidImaPregy]

You call your connection string and open it. Then use a reader to grab the information. Then, with the read, bind the information to the dropdownlist:

Dim conn As New SqlConnection( ConnectionManager.AppSettings("connectionstring") )
Dim cmdSelect As New SqlCommand( "SELECT ColumnName, ColumnValue FROM TableName ORDER BY ColumnName", conn )

conn.Open()

Dim dtrReader As SqlDataReader = cmdSelect.ExecuteReader()

If dtrReader.HasRows Then
  ddlname.DataSource = dtrReader
  ddlname.DataTextField = "ColumnName"
  ddlname.DataValueField = "ColumnValue"
  ddlname.DataBind()
Else
  response.write("no data to bind to dropdownlist")
End If

dtrReader.Close()
conn.Close()