How do I append a row to SQL results?

I have a problem with a dropdowncontrol. It is databound, but I need to add "select..." to be the first item in the dropdown. Here is the SQL:

SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValue

So the problem I am having is I can't just make an item in the dropdownlist called "select..." and then use appenddatabounditems="true". I'm using ajax and it keeps appending stuff over an over without resetting. So I think I'm going to have to do this within the sql.

So maybe that was more information than you needed to know. Anyone know how to make the first row of my SQL results be "select..." or whatnot with a value of 0.

You need to do this kind of thing at the front end.

|||

You can do this with a UNION. Something like this:

"SELECT 0, 'Select...'
UNION
SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValue

The number of fields in the first SELECT has to match the second, so adjust that as needed. And I've put in line breaks here to make it clearer what is happening, but it is all one string in what I assume is VB.NET. Adjust as needed for your code.

Also, you might need to use UNION ALL instead of just UNION, depending on your data.

Put the 'Select...' in the field location where the data used to display the item in the dropdown list.

Make sense?

Don

|||

bbaxter:

I have a problem with a dropdowncontrol. It is databound, but I need to add "select..." to be the first item in the dropdown. Here is the SQL:

SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValue

So the problem I am having is I can't just make an item in the dropdownlist called "select..." and then use appenddatabounditems="true". I'm using ajax and it keeps appending stuff over an over without resetting. So I think I'm going to have to do this within the sql.

So maybe that was more information than you needed to know. Anyone know how to make the first row of my SQL results be "select..." or whatnot with a value of 0.

Try this:

1-- Get The Result Set2SELECT Col1, Col2, Col33From MyTable45UNION-- to append67SELECT'MyRowValueForCol1','MyRowValueForCol2','MyRowValueForCol3'-- columns value of the row you want to append to the result set8FROM MyRowTable-- if you are geeting this row from table OR you can ignor this line if the values are hard coded9

Good luck.

|||

You can have the resultset modified using one of the above techniques, that should work.

You can also turn on append databound items and turn off viewstate (Viewstate will prevent the multiple additions as well as cut down the size of your page and the response you get back).

Or you can just add this type of code:

Sub ddlMyDropdown_databound(sender as object,e as object) handles ddlMydropdown.databound

ctype(sender,dropdown).items.insert(0,new listitem("Select...",""))

end sub

sql

How can I use SqlDataSource to read a row?

The new databound controls are great, but somehow doing the simpler things has gotten harder.The code is in a function that is passed pollid. I want to use SqlDataSource to get the smallest value of answerid where pollid = the value passed for pollid. For example:

"Select top from PollAsnwers where pollid=" & PollId & " order by AnswerId"

I can set up the SqlDataSource for a data reader to fill a GridView control, but that isn't what I want to do. In fact, I can't find anything on SqlDataSource that doesn't also involve the GridView control.

I have so far:

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myReader As SqlDataReader

myConnection = New SqlConnection()
myConnection.ConnectionString = ConfigurationManager.ConnectionStrings("UserPolls").ConnectionString

myCommand = New SqlCommand()
myCommand.CommandText = "Select top from PollAsnwers where pollid=" & PollId & " order by AnswerId"
myCommand.CommandType = CommandType.Text
myCommand.Connection = myConnection
myCommand.Connection.Open()

Now assuming I have a single row of data, how do I read the value of Answerid? If I have this wrong, what should I have?

Diane

select top 1 AnswerId from..... order by AnswerId.

|||

Already did that - it's in the example I posted. What I don't know is how to read the data from the record. In classic ASP, if I had a recordset called rs, I would do:

Dim newId as integer

newId = rd("AnswerId")

How do I do this with a SqlDataSource?

Diane

|||

I found an article that helped. A less than perfect but working solution is:

Dim userpoll As New UserPolls
SqlDataSource1.SelectCommand = "Select Top 1 [PollId], [AnswerId] from [PollAnswers] where pollid=" & PollId & " order by AnswerId"
Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
For Each dr As DataRow In dv.Table.Rows
AnswerId = dr("AnswerID")
Next

The For..Next is not needed, but since I'm only returning one record, it won't hurt.

|||

Just a quick addition to what yo already have: you should be using Parameterized Queries. Do a search for it in these forums and you will find how/why to use parameterized queries. In brief, to avoid SQL Injection attacks.

|||

A search turned up three results - your post here, another post by you and one more that also recommends using parameterized quieries. Can you direct me to a tutorial or article?

Diane

|||

Sure: Here's a few:

http://aspnet101.com/aspnet101/tutorials.aspx?id=1

http://dotnetjunkies.com/WebLog/dinakar/articles/74220.aspx

http://davidhayden.com/blog/dave/archive/2005/10/24/2528.aspx

If you are wondering how/where I got them ... Goooooooglee.....