I would like to make a listbox only appear if there are results returned by the SQL select statement.
I want this to be assessed on a click event of a button before the listbox is rendered.
I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?
Hi,
I'm using SqlDataSource control.
Is there a way to know how many records a query has returned?
I want to show the number of rows returned by a select query in the result.
e.g. select policy,policynumber,datecreated,Firstname, '-' as recordcount from policy
If it returns 5 rows, then the 'recordcount' need to show '5' in all row in the result
OutPut
0y96788,HGYG564,29/07/2013,SAM,5
FJUFBN7,JLPIO67,29/07/2013,Test,5
...
..
..
How can i get this number in the result.
Hi there,
I¡¦ve got a table with the following as well as other info:
User ID
DirectoryTypeID (int)
Region ID (int)
I need to run a query where I could get the region ID, then, in the second column, I¡¦d get all distinct directory types within that region. For example, if I run the query:
subRegionAreaID directoryTypeID
--------------- ---------------
3 1
3 2
3 3
3 9
If need these results to be:
subRegionAreaID directoryTypeID
--------------- ---------------
3 1, 2, 3, 9
Is this possible?
Many Thanks!! ļ
Grazi
Hi all,
I am writing a select query which produces huge xml data.Now i want to read that data from my web application a save it as xml file.
How can i do that.
I am using asp.net vb.net.
hi all
Any day, another question. I love you guys.
I want to select a subset of data from a dataset returned by either another subquery or a union.
e.g. this is how i would do it in oracle, but i have no idea how this can be done in mssql or whether it's possible at all.
select * from
(
select col1, col2, col3 from table1
union
select col1, col2, col3 from table 2
)
where col1 = 'blah'
in essence oracle treats the data returned by the subquery as a table that it would select from.
how would i do the same in mssql?
thank you
James :)
Hi,
I have SQL database 2000 which has one table Sheet1, I retrieved the columns in the ListBox, then chosed some of them and moved it to ListBox2.
The past scenario worked great, and I checked the moved values, it was succesfully moved, but when I tried to copy the values in ArrayList to do a select statement it didn't worked at all.
public string str;protected void Button3_Click(object sender, EventArgs e)
{ArrayList itemsSelected = new ArrayList(); string sep = ",";
//string str;for (int i = 0; i < ListBox2.Items.Count; i++)
{if (ListBox2.Items[i].Selected)
{
itemsSelected.Add(ListBox2.Items[i].Value);
}
int itemsSelCount = itemsSelected.Count; // integer variable which holds the count of the selected items.
str = ListBox2.Items[i].Value + sep;
Response.Write(str);
}
SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=stat_rpt;Integrated Security=True;Pooling=False");
String SQL1 = "SELECT " + str + " from Sheet1"; SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon);
SqlCommandBuilder CB = new SqlCommandBuilder(Adptr);DataTable Dt = new DataTable();
Adptr.Fill(Dt);
//return Dt;
GridView1.DataBind();
SqlCon.Close();
}
I did some changes and the new error message is
Incorrect syntax near the keyword 'from'.
Thank you
Hi everyone, not sure if a this topic has been covered yet (a have been looking all day), but as I am still very new to this, my problem is as follows:
In the Try .. Catch block below, data is posted from a form and the SqlCommand.ExecuteScalar() statement returns a Unique Job ID.
I am attempting to populate a subordinate table for qualifications which are selected from a ListBox, but rather than using qualification titles, I am using the values.
My problem is that only one value (the first) gets posted multiple times, when multiple values are selected.
Looking at the For Each loop in the inner Try Catch block, I am wondering whether there is some sort of Index pointer that needs to be incremented, in order to establish new values further down the list.
I have seen no evidence that this is the case, save for the fact that the value stalls on just the first.
Any help would be appreciated.
===== CODE ===
Try
C4LConnection.Open()
JobPostingID = SqlJobPost.ExecuteScalar()Response.Write("<br />Selected Item: " & Qualifications.SelectedItem.Value)
Try
' Multiple Qualification EntriesSqlQualPost.Parameters.Add(New SqlParameter("@JobPostingID", SqlDbType.Int))
SqlQualPost.Parameters("@JobPostingID").Value = Int32.Parse(JobPostingID)SqlQualPost.Parameters.Add(New SqlParameter("@QualificationID", SqlDbType.Int))
SqlQualPost.Parameters("@QualificationID").Value = Int32.Parse(Qualifications.SelectedItem.Value)
If Qualifications.SelectedIndex > -1 ThenFor Each Item In Qualifications.Items
If Item.Selected ThenResponse.Write("<br />SelectedItem Value: " & Qualifications.SelectedItem.Text)
QualPostingID = SqlQualPost.ExecuteScalar()SqlQualPost.Parameters("@QualificationID").Value = Int32.Parse(Qualifications.SelectedItem.Value)
Response.Write("<br />Selected Item: " & Qualifications.SelectedItem.Value)
End If
Next
End IfCatch Exp As SqlException
failJobPost = True
lblError.Visible = TruelblError.Text = "Could not add qualifications <br />" & Exp.Message
End Try
failJobPost = FalseCatch Exp As SqlException
failJobPost = True
lblError.Visible = TruelblError.Text = "Error: could not post job to database <br />" & Exp.Message
Finally
C4LConnection.Close()
End Try
Hi,
I have an ASP.NET form that stores it's data in MSDE but I just added a multi-select ListBox to the form and I'm having a hard time coming up with a way of writing that data to the database. Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there. I like the second option, but I'm not sure how to loop through each value and write it to the database table.
I grab the values for the selection as follow:
foreach (ListItem lstItem in lbAttendees.Items)
{
if (lstItem.Selected == true)
{
grpList.Add(lstItem.Value.ToString());
}
}
but I'm not sure on what to do next and could use some help.
Thanks
Germano
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
hello forum,
I need to grab a string value from a list box in from a web form, and pass it to a sql select command statement where that value is equal toall values in a database table(sql 2000).
example
zip code list box3315433254 845788547535454 selected value is 85475
I am putting that value in a string like this:
dim string_zip as stringstring_zip = zip_ListBox.text
Question, how do i pass that value to sql stament, i am using this but does not work.
SqlCommand1 = New SqlCommand("SELECT zip FROM table WHERE zip = string_zip", SqlConnection1)
I have a problem selecting fields from a table where fields are equal to user input from a listbox. example
listbox of zip codes:
33023[red]22300[/red]39844[red]29339[/red]23883[red]38228[/red]
user wants to retreive highlight zip codes from database.connection working perfect.Thank you for your help.
I have records which are like below.
create table testedit
(
editid int
,Tguid varchar(20)
,ttime numeric(4,2)
,numApp int
,numOrg int
,custid varchar(1)
)
INSERT INTO testedit values(1,'ABC',12.52,40,11,'Z')
INSERT INTO testedit values(2,'ABC',12.52,500,33,'Z')
INSERT INTO testedit values(3,'ABC',12.53,500,33,'Z')
Out of this records I would like to select only the 1st and the 3rd record. ie. My result should only have the below rows
1,'ABC',12.52,40,11,'Z'
3,'ABC',12.53,500,33,'Z'
Any help?
I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.
View 3 Replies View RelatedThe following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE spUnitsbyUnitID
@unitid int
AS
SELECT
E.camid, E.camname, E.cammodel, E.unitid,
D.contactid, D.Contactfname, D.Contactlname, D.Contactphone, D.Contactcell, D.Contactcompany, D.unitid,
C.videoserverid, C.videoservermac, C.videoserveruser, C.videoserverpass, C.videoservermodel, C.videoserverip, C.unitid,
B.radioid, B.radioip, B.radiomac, B.radioessid, B.radiouser, B.radiopass, B.unitid,
A.unitid, A.unitcity, A.unitname, A.unitalias, A.unitdeploydate, A.unitpickupdate, A.unitattatchedcams, A.unitenabled
FROM tbl_units as A
INNER JOIN tbl_radios as B ON A.unitid = B.unitid
INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid
INNER JOIN tbl_contacts as D on A.unitid = D.unitid
INNER JOIN tbl_cameras as E on A.unitid = E.unitid
WHERE A.UnitID = @unitID
GO
-----------------------------------------------------------------------------------------------------
I have a simple report with a group header, details and footer. How do I display a message to inform users when no data is returned when they enter a parameter which returns no records - at the moment my report returns a blank screen, it would be more useful if I could output the following
'Parameter Name' does not exist.
Hi,I feel stupid for posting this question but I cannot find out how to create an SQL statement to return records that have been added today.My database table has a field called calldate which hold the date and time of the record added to the database i.e. "02/08/2005 16:55:41".Please can someone let me know how I can search to find all records added today. I thought it would be something like it, but I assume I need to format the calldate so it's just 02/08/2005.Select *From TableWhere CallDate = GetDate()ThanksBrett
View 9 Replies View Relateddear friend,
i run a sql command like this
select * from table_name
what i want is that if no. of records found are greater then 500 than it should stop the query and only show those 500 records dont go further and stop the process
waiting for reply
ashish bhatnagar
I have a problem. In my database I have the following numbers available:
101
104
105
110
111
112
113
114
What I need is to get a select query with records and sequentials numbers after it like:
101 0
104 1 (the number 105)
105 0
110 4 (the numbers 111,112,113,114)
111 3 (the numbers 112,113,114)
112 2 (the numbers 113,114)
113 1 (the numbers 114)
114 0
How can I do It?
Hi,
This is my 1st thread. Hopefully somebody can help me with the full-text serach.
I enabled full text serach on sql server 2k and created a catalog for a table
I tried using the following sql statement but it doesn't seem to return any records although I used a like statement and got some records. Can anybody answer why this is so? Thanks!
Select * from table1 where contains (field1, '"ESTATE PLAN"')
Regards,
Ronald
The following statement gives the error that multiple records are returned. I am attempting to pick one weight out of a group of three, the three have the same ResidentID and different dates in the table variable @WeightReportRaw. If I hard code the ResidentID, ResidentID = 6, it works fine. The variable @intMonth3 passes in a month number ans dosen't seem to be a problem.
What am I missing, Thanks in advance for any help.
update tblResidentWeightsTemp set FirstWeight =
(select RWeight from @WeightReportRaw as W where ResidentID = W.ResidentID and month(W.RWeightDate) = @intMonth3)
I'm trying to put the total number of records returned from from a query in the bottom of our report. I don't want to do a count(*) in my sql stmt.
thanks.
How can i get a Count of the number of records returned
Here is my code
Im using VS2005 connected to SQL Compact v3.1
myCmd.CommandType = Data.CommandType.Text
myCmd.CommandText = "Select * From tblParts"
Dim myDataAdapter As SqlCeDataAdapter = New SqlCeDataAdapter(myCmd)
Dim myDataSet As DataSet = New DataSet()
myDataAdapter.Fill(myDataSet)
TblPartsDataGrid.DataSource = myDataSet.Tables(0)
Hi Friends,
I have created the procedure in sql server 2005 for retriving email addresses from table based on date_expiry and concatinating all email addresses in to @tolist as shown below
Declare @tolist varchar(8000)
set @tolist = ''SELECT @tolist = @tolist + ';' + COALESCE(email, '')
FROM awc_register
WHERE DATEDIFF(day, date_expiry, GETDATE())='3'print @tolist
set @tolist = substring(@tolist, 2, len(@tolist))
and I passed this @tolist to another procedure which should send mails for email addresses present in @tolist variable.
Problem is I need to send the mail to each email address separatley.( not bulkly)
Thanks,
Good day!
I just can't figure out how I can display only the top record for the duplicate records in my table.
Example:
Table1
Code Date
01 10/1/05
01 10/2/05
01 10/3/05
02 9/9/05
02 9/9/05
02 9/10/05
My desired result would be:
Table1
Code Date
01 10/1/05
02 9/9/05
Thanks.
Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance
View 1 Replies View RelatedI have a function that opens a connection to an SQL database, issues a
SELECT command, and reads the records with an OleDbDataReader. As the
records are read, any that match certain criteria are deleted with a
DELETE command. Simplified example code is shown below:
Dim dbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd.CommandText = "SELECT * FROM [User] ORDER BY UserID"
dbCmd.Connection.Open()
Dim reader as OleDb.OleDbDataReader = dbCmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
If reader("SomeColumn") = SomeCalculatedValue Then
Dim dbCmd2 As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd2.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd2.CommandText = "DELETE FROM [User] WHERE UserID = " + reader("UserID")
dbCmd2.Connection.Open()
dbCmd2.ExecuteNonQuery()
dbCmd2.Connection.Close()
End If
End While
reader.Close()
This code worked well with an MS Access database, but when I changed to
SQL Server, I get a database timeout error when attempting to do the
DELETE. I suspect the reason is that the connection the reader has open
has the record locked so it cannot be deleted.
The SQL connection string I am using is something like this:
UserDbConnString = "Provider=SQLOLEDB; Server=(Local); User ID=userid; Password=password; Database=dbname"
The connection string I used for MS Access included the property
"Mode=Share Deny None". I wonder if there is some similar way to tell
SQL Server to allow editing of records that are open for reading with
an OleDbDataReader.
Any help would be appreciated.
In my database I have an Audit table, that keeps track of teams worked upon the same record in a workflow.I need to find out how many records have been returned to the first team for correction ?The column 'Status' is numbered from 1 to 6 and Column 'EditTime' saves the time when record has been edited.how many records have been returned for correction and identify those records.
View 9 Replies View RelatedHi, all experts here,
Thank you very much for your kind attention.
I got a problem with the records returned by inner join query. It is weird as in table a I with records r1, and in table b with records r2 (r1>r2), but the returned record number r3 is greater than r1? That is so strange. As the reasonable records returned should be less than r1?(inner join on attribute a). What probably is the cause of the problem? I am looking forward to hearing from you and thank you very much.
With best regards,
Yours sincerely,
Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!
View 6 Replies View RelatedI have date field in table and data contain 2014-08-09 11:13:03.340
when I use smalldatefield I am getting 2014-08-09 11:13:00 I should have got 2014-08-09 11:13:03
Why the 03 is trimming in smalldatefield.
how do I use select query to get 2014-08-09 11:13:03 from original Date records
I would like to use sp_send_dbmail, but I only want to send mail if there are any records returned.
I have found some solutions, but you must always first check if there is any record and later you can call "sp_send_dbmail" and within you must again query database for results.
What I want to do is to query database just once, because I dont want to use server performance two times. Query is bit complicated.
Time Price Volume
090048 510 6749
090122 510 2101
090135 510 1000
090204 505 2840
090213 505 220
090222 505 1260
090232 505 850
090242 505 200
090253 510 1200
090313 510 570
090343 510 250
090353 510 160
Criteria
Retrieve records with independent price and its total volume per minute
SELECT SUBSTRING(st,1,4) AS Ttime,d_price AS Price,SUM(l_cum) AS Volume FROM cmd4
WHERE sd='20060717' AND serial='0455'
GROUP BY SUBSTRING(st,1,4),d_price,l_cum
Result of the above query: -
Time Price Volume
0900 510 6749
0901 510 1000
0901 510 2101
0902 505 200
0902 505 220
0902 505 850
0902 505 1260
0902 505 2840
0902 510 1200
0903 510 160
0903 510 250
0903 510 570
THE FOLLOWING RESULT STILL NOT A TOTAL FOR A MINUTE]
E.G
0901 510 1000
+
0901 510 2101
=
0901 510 3101 <- I NEED THIS
Can any one advice or give me tips over this. Please