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.
I need to append text to the end of my sql query results.For instance, my query returns a list of user names infirstname.lastname. I need to add @yahoo.com to each record. So theend result should be Join Bytes!. What should byselect statement look like?Any help?
I have created a DTS package which transfers data from a SQL 2000server table to a text file. Each time the package is executed thedata in the text file is replace with the new data from the currentexecution of the package. Can DTS append new text to a text file andnot replace it each time the package executes. Is there an option orseeting for this? I havent seen one. Can anyone help me out on this.Thank you,Brett
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100 SELECT * FROM Customer WHERE CountyId IN ( SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId) THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist. My current SQL statement is as follows. SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))" So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated. Thanks, James.
Hi All, I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
I want to create a stored procedure that will take filtered entries from one table and insert them into another table. I have created stored procedures using variables but what is the best way of taking data from one table to another?
i hav column in table which already conatins data, now i want to append some more data to it.how do i do it so that earler content does not get deleted
Hi I'd like to create a table on our SQL server that I can append records to when running a query.
What i intend to do is create a new table with the same fields as the query then when the query runs i want it to append the results to this new table. Is this possible? How would I go about creating the table, primary key etc?
I've tried doing this through an access front end but its not efficient and was a bit of a struggle to be honest.
Also, we need other people who may not have access to enterprise manager to be able to run this append query. Danny
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
How do I append data on an update? I have a table with a field that is nVarchar(1000) and the initial insert is a few sentences. If I wanted to add to that row using an update statement and without starting at the end of the sentences, how would I write that?
Update table set fieldname = 'more data' where value = @variable
instead of
Update table set fieldname = 'initial data more data' where value = @variable
and the 'more data' appends to the initial data... hmmm
My Problem is I have 2 views --> 2 Databases (2 Products) but there are same fields (Same structure) and I have to created the report by Crystal Reports to compare the Quatity of all product in my Company So how Can I combine them (2 views with the same recoed but not the same data) thanks for helping me Kate
I need to copy data from one SQL table to another SQL table. Is is possible to use DTS to Append and update data from one table to another....along the line of using a Microsoft Access append or update query?
I have a huge table with data.I run a procedure everyday to update the table's data with our daily current sales.Let's say the table's primary index is product type.The procedure recreates a skeleton of the table to make sure that it will add only those records whose product type is new to our database thus ignoring the rest of the records due to duplicate key violation error.Now this used to work in access where I used to get a message saying that only 200 out of 4000 records were added,3800 were ignored due to violation key errors. But in SQL Server, no records at all are being added.Is there a way to overcome this problem?? I tried using "set xact abort" but it only worked in case of foreign key violation but not primary key. I would really appreciate your inquiry. Thank you.
Hi guys I have a quick Q: I have two tables: tblOne ID/ PageName / Desc /URL 12/Home Page/This is the home page/www.fff.com tblTwo ID/ Name /Link 34/News Page/www.bbb.ie
I wish to create a new table or stored procedure to append one table(which has 4 colums) onto the other(which has only 3 columns) giving the following results: ID / Name /Link 12/Home Page/www.fff.com 34/News Page/www.bbb.ie
I would highly appreciate help to : I have two databases that are of the same schema but in one-database-A the records are a bit older than the other database-B. I would lilke to sync the records from database-B to database-A appending the records to database-A. The schemas have constraints,primary keys and other defaults in common.
What is the best way to do this task? I tried to use DTS but it fails throwing error Primary key violations on the tables.
I have a bcp command that works fine except that I want it to over write what is in the table rather than append to it. Is there a way to do this or another method I should try?
Hello all,Number of rows in a table was increased significantly. Is there a way to seewhether data was inserted in to the table, or whether data was appended tothe table?Thanks in advance,Do.--Message posted via http://www.sqlmonster.com
What would be the steps to create a SSIS job to append records from an ODBC table to a SQL table, adding only the records from the source that do not already exist in the destination?
I have another post on this subject, with a good suggestion for the approach, but I need some more detailed instructions for implementing it:
Have you considered to use a Lookup task in your data flow to check if the row already exists in the destination table and then use the error output (no matches) for inserting only non existing rows? Notice that the error output of the lookup task needs to be set as 'redirect rows' in order to get this behavior
I have a ChartReport that make a lot of chart, one for each page of report, this use as datasource myDataSet_Charts. I have a SummaryReport that make a table, only a page report, this use as datasource myDataSet_Charts too.
I have need to produce a report that have in the first page the SummaryReport output, and in append the ChartReport output.
Hi, I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number(). I would like to give my users to option of removing individual people from this list but cannot find a way to do this. I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message. Is there any way to do this? I am still new to stored procedures so any advice would be helpful. Thanks
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
I have one cloumn with 10 rows and I want to read all those value to textbox, or another way I want to append all value to each other, I think I have to loop through it, so how can I do this. I have this column in SQL table. some plese tell me how to do this thank you maxmax
I try to get the value Pos1 from the SQLQuery from SQL Server 2005 Express, and set to iPos1 variable.
webcodefile1.vbDim myConnection As SqlConnection = _ New SqlConnection(ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString)Dim myCommand As SqlCommand = New SqlCommand("GetRandomPosition", myConnection) myCommand.CommandType = CommandType.StoredProcedure myCommand.Parameters.Append(CmdPuzzle.CreateParameter("@Pos1", adTinyInt, adParamOutput)) <---- in aspnet, the Append is not being used, what should i use instead? iPos1 = MyCommand("@Pos1")
When calling my SELECT statement, I would like to have Column name and Data type as the first record. Is this possible to do? I would like to make a SP that would select all data from any table(with a header row) with just:
_sp_getData ' table_name'
I looked at sysobjects, and syscolumns tables, but was unable to put it all together. I have over 200+ tables, so creating variables for every column is very cumbersome.
Something like this:
select 0 AS SortCol , cast(min(case ordinal_position when 1 then column_name end) as varchar) as col1, cast(min(case ordinal_position when 2 then column_name end) as varchar) as col2, cast(min(case ordinal_position when 3 then column_name end) as varchar) as col3, cast(min(case ordinal_position when 4 then column_name end) as varchar) as col4, cast(min(case ordinal_position when 5 then column_name end) as varchar) as col5, cast(min(case ordinal_position when 6 then column_name end) as varchar) as col6, cast(min(case ordinal_position when 7 then column_name end) as varchar) as col7, cast(min(case ordinal_position when 8 then column_name end) as varchar) as col8, cast(min(case ordinal_position when 9 then column_name end) as varchar) as col9 from information_schema.columns where table_name = 'authors' union all select 1 , au_id, au_lname, au_fname, phone, address, city, state, zip, cast(contract as varchar) from authors
We have a file where my co-employee backup's our database. The problem is that the file is getting too large because he always use APPEND when he backup's. Is there a way to delete some of the appended backup instances? Thank you very much for your help.
hi, I am in the process of creating an append query that sends the fields of an open form from my ms access 2000 db to a table in ms sql 2000. I've successfully linked the ms sql tables and created the query to pull data from my form but when i run the append i get a key violation error and nothing gets appended. I know which field is the primary key in ms sql i just don't know how to change my access table to suit, any ideas?
To start I've made my append query simple and it just pulls the contact name. Here's my sql statement if that helps. CODE INSERT INTO dbo_PatientMaster ( PatientFirstName, PatientLastName, PatientAccountNumber ) SELECT [Forms]![Contacts]![FirstName] AS Firstname, [Forms]![Contacts]![LastName] AS [Last], [Contacts]![PatientAccountNumber] AS [Patient Account Number] FROM Contacts, dbo_PatientMaster GROUP BY [Forms]![Contacts]![FirstName], [Forms]![Contacts]![LastName], [Contacts]![PatientAccountNumber]; In access the patient account number is empty and I just created that field in my access table today.
I have another table with the following structure (Basically this table will contain a subset of coloumns of Table1)
Table2 ------- Dept Field1 Field2
Now using a query I would like see all the records with all coloumns in Table1 plus all the records in Table2 appended
i.e if Table1 row is
IT F1 F2 F3 F4 F5
and if Table2 row is
IT F11 F22 Sales F12 F23
I would like to see a result set with the following structure
Resultset
IT F1 F2 F3 F4 F5 IT F11 F22 NULL NULL NULL Sales F12 F23 NULL NULL NULL
Can some body explain me how to do this with a query. I tried using union but it requires identical coloumns on both ends( Ofcourse, we can acheive this by having Field3,Field4 and Field5 as blank columns in Table 2 but I don't wanna do that as my original tables are too huge to handle this).
Is there a way to do so on the fly in SQL Server 2000? In other words, a field has the latest update date for the table and we wish to use this date as part of the table name. If so, please provide an example.
