Red = If ID due date was overdue, i.e. if last task completed after the ID end date(2014-06-18 00:00:00).
AMBER= If any task in the ID is overdue but completed before the ID end date(2014-06-18 00:00:00)
Green = If all tasks were completed on time.
I am looking for the logic to implement the AMBER for the whole ID, because the TASK_ID 3 is overdue, but completed before the ID end date (2014-06-18 00:00:00).
Basically like the topic says, I need to populate a filename with data from other columns and im having a tough time doing it.
Code:
UPDATE nice_cls_calls_0027 SET vcarchivepath = 'G:Nice Storageicestorage.safeautonet.netSafe Auto Task - Logger (807101)2006_feb_1SC_807101_'start_time'_'=stop_time'_'=channel'_'=cls_call_id'.aud'
I need start_time, stop_time, channel, and cls_call_id to populate the filename. The first 2 are datetime fields in the db and no conversion is needed for them, just raw data. The remaining two are smallint and raw data is needed there also. Any help would be greatly appreciated. TY
I'm looking for an efficient way to populate derived columns when Iinsert data into a table in SQL Server. In Informix and PostgreSQLthis is easily done using the "for each row..." syntax, but all I'vebeen able to come up with for SQL Server is the following:create table testtrigger(id integer unique, b integer, c integer)gocreate trigger testtrigger_ins on testtriggerfor insert asupdate testtrigger set c = (select ...some_function_of_b... fromtesttrigger t1,inserted t2where t1.id = t2.id)where id in (select id from inserted);gowhere id is testrigger's unique id field, and c is a field derived fromb.This seems terribly inefficient since each insert results in an extraselect and update. And if the table is large and unindexed (which itcould be if we are bulk loading) then I would imagine this would bevery slow.Are there any better ways of doing this?Many thanks,...Mike Dunham-Wilkie
Hi, I have a master/detail scenario whereby to populate a formview, the user selects a row in the gridview. The SqlDataSource used to populate the formview needs to check 2 columns from the gridview, ZRef (which is the SelectedValue of the Gridview) and ZName (a string). From searching the forums it looks like I have to programmatically assign the SelectParameters in order to get ZName from the gridview. Seems fair enough, so I set the parameters in the 'Selecting' event of the SqlDataSource. (Code shown below). However the formview never shows. In debug I can see that the values I'm setting in the SelectParameters.Add are correct; and if I set the default values for those parameters in the SqlDataSource itself, everything works fine. Can anyone point me to some sample VB code that does this - I'm sure this must be a common situation. Thanks. Protected Sub sdsOff_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Dim tmpSDS As SqlDataSource Dim tmpLBL As Label tmpSDS = CType(FormView1.FindControl("sdsOff"), SqlDataSource) If tmpSDS Is Nothing Then Server.Transfer("ErrorOnPage.htm") End If tmpLBL = Me.GridView1.Rows(GridView1.SelectedIndex).FindControl("lblName") If tmpLBL Is Nothing Then Server.Transfer("ErrorOnPage.htm") End If tmpSDS.SelectParameters.Clear() tmpSDS.SelectParameters.Add("ZRef", GridView1.SelectedValue) 'In debug, this shows 7 - which is correct tmpSDS.SelectParameters.Add("ZName", tmpLBL.Text) 'In debug, this shows 'Fred Bloggs' - which is correct End Sub
I'm trying to write a stored procedure that will parse XML attributes and populate columns within a DB with the stripped data. I'm a complete novice who prior to this week knew nothing about SQL commands, My understanding at least is that I need to perform a bulk insert.
Example XML file:
<?xml version="1.0" encoding="ISO-8859-1"?> <!DOCTYPE Asset_Collection SYSTEM "Asset_Collection.dtd"> <Asset_Collection> <Collection_Metadata Name="Asset Collection" Description="Random XML Feed Test"
[Code] ....
Table/Columns which need to be inserting into: Table: TABLE_A
I want to take the contents from a table of appointments and insert theappointments for any given month into a temp table where all theappointments for each day are inserted into a single row with a columnfor each day of the month.Is there a simple way to do this?I have a recordset that looks like:SELECTa.Date,a.Client --contents: Joe, Frank, Fred, Pete, OscarFROMdbo.tblAppointments aWHEREa.date between ...(first and last day of the selected month)What I want to do is to create a temp table that has 31 columnsto hold appointments and insert into each column any appointments forthe date...CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2nvarchar(500), Day3 nvarchar(500), etc...)Then loop through the recordset above to insert into Day1, Day 2, Day3,etc. all the appointments for that day, with multiple appointmentsseparated by a comma.INSERT INTO#Appointments(Day1)SELECTa.ClientFROMdbo.tblAppointments aWHEREa.date = (...first day of the month)(LOOP to Day31)The results would look likeDay1 Day2 Day3 ...Row1 Joe, PeteFrank,FredMaybe there's an even better way to handle this sort of situation?Thanks,lq
Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
Sorry if this is to basic but I am just starting out. Any help is appreciated.
Basically I am attempting to populate a listbox with items from a MSSQL DB so the user can select either one or multiple items in that listbox to search on.
I am trying to update a name column in the following way: (I wrote a description, but I think this visual is easier to understand).
This is what I have:
name1 name2 address etc
Bob null 123 street
Sue null 123 street
Jack null ABC circle
This is what I want:
name1 name2 address etc
Bob Sue 123 street
Jack null ABC circle
I'm just trying to get 2 names on the same row if they have the same address and get rid of the spare row. Name2 is currently null. Seems simple enough but I don't know how to do it in SQL. I can do it in FoxPro, but that doesn't help me here.
I added an index to a SQL Server 2005 table. How do I populate that index? I thought it might be automatically populated but the operation to add the index happened so quickly that I don't think it could have done it that quickly. Also, I expected faster performace when selecting rows based on the indexed column, but performance remains the same.
Here is what I have and (somewhat understand). I’m using Visual Web Developer 2005 Express Edition and have setup my application to use form authentication (which automatically creates the ASPNETDB.MDF file with several default tables and views). I’m using the CreateUserWizard which is fine…but I need to collect additional information like (firstname, lastname, address…and on..). What I’ve done. I’ve created a tabled named UserProfile and set UserId as the primary key (uniqueidentifier). I then setup a 1-to-1 relationship between aspnet_Users and UserProfile (which I think is correct). On my UpdateContactInfo.aspx page (where users go to update their personal information) I use a hidden label control (UserValue) to receive the UserId during the page_load event as below:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load UserValue.Text = Membership.GetUser().ProviderUserKey().ToString() End Sub Now with the UserID available I need to populate the UserProfile table with the UserId, firstname, lastname, address of the currently logged in user. How can I do this and am I on the right track..?
Hi i'm pretty new to this, how do i connect to my database a put all the values from one column into the arraylist. Any help and a easy example would be nice Thanks in advance Richard
Hi, I want to populate the array with a single column values from database(sqlserver 2000)Ex. name ageaaaa 23bbbb 43cccc 18 Now i want to populate the array with name field values. Please anyone guide me how to do this.Thanks in advanceBala
Using the SqlDataReader, I am retrieveing records from a SQL Server 2005 DB table. Since I am using the SqlDataReader to retrieve the records from the DB, I have to use the Read method of the SqlDataReader like this:Dim sqlReader As SqlDataReaderWhile(sqlReader.Read) Response.Write(sqlReader.GetValue(0) & "<br>") Response.Write(sqlReader.GetValue(1) & "<br>") Response.Write(sqlReader.GetValue(2) & "<br>")End WhileThe records retrieved can only be accessed inside the While loop. I want to access the records outside the While loop as well. Is there anyway by which I can do this, maybe by populating the recordset in an array variable & then using it outside the While loop?
In my data access layer class I have Populate methods on the bottom.One of the the objects, color, is an ArrayList, how do I write that? private Product PopulateProduct(IDataReader r) { Product product = new Product(); product.BrandId = Convert.ToInt32(r["brandId"]); product.BrandName = Convert.ToString(r["brandName"]); product.Color = whatGoesHere? (r["color"]); return product; } thanks
Hi there!I'm trying to populate automactly a record in a table. I've tried this: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ProducaoLigacao %>" SelectCommand="UPDATE [Producao] SET [Prodas] = @Prodas WHERE ([ProdUserID] = @ProdUserID)"> <UpdateParameters> <asp:Parameter DefaultValue="1" Name="ProdUserID" Type="Int32" /> <asp:Parameter DefaultValue="999" Name="Prodas" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> Is this possible? Thanks in advance!
I have been attempting to populate a dropdown (combo) box from a SQL query (SQLConnector) in Web Matrix and I have so far been unsuccessful. I have been able to populate a DataGrid just fine from the query, so then I limited it down to one colum (so itd be in the dropdown box) and set all the properties I could find (DataSource, DataMember, DataTextField, DataValueField) (and in all possible combinations) for that dropdown box to the SQL stuff (SQLConnector for the DataSource and then used the dropdowns for the others), and nothing shows up in my dropdown box. How can I make this populate off of my SQL query properly?
Additionally I will need to do the same thing for a Checkbox List so if it is the same and you know how to do that and could let me know for that too that'd be great.
I have a dropdownlist that is populated from a DB table. I would like to also include any other values that might be in another table. How do I combine these two queries, so I can get distinct values from both tables combined?SELECT Category FROM dbo.TABLE1 GROUP BY Category
Can someone tell me what I am doing wrong... I am trying to populate some textboxes from a table by doing a SELECT statment. Any help would be greatly appreciated T.
Code: Dim myConnection As SqlConnection Dim myCommand As SqlDataAdapter myConnection = New SqlConnection("Server=.SQLEXPRESS;AttachDbFilename=|DataDirectory|CustomerInfo.mdf;Integrated Security=True;User Instance=True") myCommand = New SqlDataAdapter("SELECT * FROM [CustomerSalesNum] WHERE [WorkSheetID]= '" & lblRWorkSheetID.Text & "'", myConnection) Dim ds As DataSet = New DataSet("CustomerSalesNum") ds = New DataSet("CustomerSalesNums") tbUPPRice.Text = ds.Tables["CustomerSalesNums"].Rows[0]["Price"].ToString();
The field OrderDate contains BOTH Date and Time information. What I want to do is populate the fields (in bold above) from the OrderDate field... I have tried all that I could think of but nothing works. The last and the best try that I gave was the {B}following[/B] but that too does not work. Can you PLEASE help. Many thanks in advance:
Insert ORDERS (OrderDateONLY, OrderDayName, OrderMonth, OrderDayOfMonth, OrderWeekofYear) select d, datename (dw, d), datename (mm, d), Year (d), datepart (ww, d) from (select convert (char (8), OrderDate, 112)as d from ORDERS ) as x
I am trying to populate a column with date/time info, I am doing this so I can use getdate() on the data for other computations (the format is mon/day/yr/hours/mins, i was told only data having mon/day/yr format can be used with getdate()). The code below says that dateyest is an invalid object name. Actually, any kind of insert I do reports the same error, no matter what column it is (securty on my account is all set, as well as for the stored proc.). I must be overlooking something very simple, right? thanks for the help!
DECLARE @dateYest datetime DECLARE Cur668 CURSOR Keyset FOR SELECT cast(date1 as datetime) dt FROM baclosetable OPEN Cur668
fetch next from cur668 into @dateyest
WHILE @@FETCH_STATUS = 0 BEGIN if @dateyest is not null Insert dateyest VALUES (@dateYest) fetch next from cur668 into @dateyest END CLOSE Cur668 DEALLOCATE Cur668
I'm using SQLServer2000/Access 2000 .adp. The .adp has a form with 2 combo boxes. The 1st combo box is bound to a stored procedure and loads fine when the form loads. I then set the 2nd combo box's RowSource passing the value of the first into the stored proc:
I want a trigger in db aaa to fire when table a_aaa is updated in server a and table b_bbb in db bbb in server b to be populated with data. I know how to write a trigger if fired and the result stays in one server with one db. But I don't know how to do it if between two servers and two db.
a win server 2003 standard Edition sql Server 2000 db:aaa table: a_aaa column:a_aaa_a
b win server 2003 standard Edition sql Server 2000 db:bbb table:b_bbb column: b_bbb_b
It's not working because the symtax is incorrect and because I am not sure how to do it between two servers. If it is not correct, where am I wrong? Where should each line be located?? et cetera...... Can anyone help?
Thanks in advance.
CREATE TRIGGER [enddate_changed_on_alert] ON [dbo].[USER_DATA]
FOR INSERT, UPDATE AS
BEGIN SET NOCOUNT ON;
DECLARE @EndDate as DATE DECLARE @CompCode as VARCHAR(15) DECLARE @PhoneGMSM as VARCHAR(10) DECLARE @PhoneALERT as VARCHAR(10)
SELECT @PhoneALERT=phone1 from AUSSMEDEVIQ1.QDB_Test.USER_DATA; SELECT @PhoneGMSM=PHONE1 from AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
UPDATE AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ SET EXT4 = "111" WHERE AUSSMEDEVIQ1.QDB_Test.USER_DATA.@Phone = AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
INSERT INTO @CategoryTable (ColID, ColCategory, ColValue) SELECT 0, LEFT(RawCollectionData,CHARINDEX(':',RawCollection Data)), LTRIM(SUBSTRING(RawCollectionData,CHARINDEX(':',Ra wCollectionData)+1,255)) FROM Collections_Staging
--Assign an ID to each block of data for each occurance of 'Reason:'
DECLARE @ID int SET @ID = 1 UPDATE @CategoryTable SET [ColID] = CASE WHEN ColCategory = 'Reason:' THEN @ID - 1 ELSE @ID END, @ID = CASE WHEN ColCategory = 'Reason:' THEN @ID + 1 ELSE @ID END
--Then put the data together
SELECT --cast to Nvarchar for MSAccess a.ColID, CAST(a.ColValue as Nvarchar(30)) AS OrderID, COALESCE(CAST(b.ColValue as Nvarchar(30)),'') AS SellerUserID, COALESCE(CAST(c.ColValue as Nvarchar(100)),'') AS BusinessName, COALESCE(CAST(d.ColValue as Nvarchar(15)),'') AS BankID, COALESCE(CAST(e.ColValue as Nvarchar(15)),'') AS AccountID, COALESCE(CAST(SUBSTRING(f.ColValue,CHARINDEX('$',f .ColValue)+1,500)AS DECIMAL(18,2)),0) AS CollectionAmount, COALESCE(CAST(g.ColValue as Nvarchar(10)),'') AS TransactionType, CASE WHEN h.ColValue LIKE '%Matching Disbursement%' THEN NULL ELSE CAST(h.ColValue AS SmallDateTime) END AS DisbursementDate, --COALESCE(h.ColValue,'') AS DisbursementDate, CASE WHEN i.ColValue LIKE '%Matching Disbursements%' THEN NULL WHEN CAST(LEFT(REVERSE(i.ColValue),4)AS INT) > 1000 THEN CAST(i.ColValue AS SmallDateTime) WHEN LEFT(REVERSE(i.ColValue),4) = '1000' THEN NULL END AS ReturnDate, --COALESCE(i.ColValue,'') AS ReturnDate, COALESCE(CAST(j.ColValue as Nvarchar(4)),'') AS Code, COALESCE(CAST(k.ColValue as Nvarchar(255)),'') AS CollectionReason
FROM @CategoryTable a LEFT JOIN @CategoryTable b ON b.ColID = a.ColID AND b.ColCategory = 'Seller UserId:' LEFT JOIN @CategoryTable c ON c.ColID = a.ColID AND c.ColCategory = 'Business Name:' LEFT JOIN @CategoryTable d ON d.ColID = a.ColID AND d.ColCategory = 'Bank ID:' LEFT JOIN @CategoryTable e ON e.ColID = a.ColID AND e.ColCategory = 'Account ID:' LEFT JOIN @CategoryTable f ON f.ColID = a.ColID AND f.ColCategory = 'Amount:' LEFT JOIN @CategoryTable g ON g.ColID = a.ColID AND g.ColCategory = 'Transaction Type:' LEFT JOIN @CategoryTable h ON h.ColID = a.ColID AND h.ColCategory = 'Disbursement Date:' LEFT JOIN @CategoryTable i ON i.ColID = a.ColID AND i.ColCategory = 'Return Date:' LEFT JOIN @CategoryTable j ON j.ColID = a.ColID AND j.ColCategory = 'Code:' LEFT JOIN @CategoryTable k ON k.ColID = a.ColID AND k.ColCategory = 'Reason:'
WHERE a.ColCategory = 'Order ID:'
***************************************
This statement parses. I can preview the data. I've tried to set up both an OLE DB destination to a SQL Server table and an MS Access table destination (Jet). In either case, the data will not populate the tables. I set up a Data Viewer, and no data appears in the Viewer. With the Access destination, I have the package set up to run in 32 bit mode.
If data appears in the preview, then why doesn't the data appear in the data viewer, and why will the data not populate either of the destination tables?
The field OrderDate contains BOTH Date and Time information. What I want to do is populate the fields (in bold above) from the OrderDate field... I have tried all that I could think of but nothing works. The last and the best try that I gave was the following but that too does not work. Can you PLEASE help. Many thanks in advance:
Insert ORDERS (OrderDateONLY, OrderDayName, OrderMonth, OrderDayOfMonth, OrderWeekofYear) select d, datename (dw, d), datename (mm, d), Year (d), datepart (ww, d) from (select convert (char (8), OrderDate, 112)as d from ORDERS ) as x
Now I have to populate the fact & dimension tables by writing sql scripts. Now I have already populated the dimension tables by writing sql script, But I have to populate the fact table taking into account, here I am facing problem in wriring sql script
(i) unit_price is taken from the book base table with reference to the isbn (ii) sales_quantity is taken from the order_detail.quantity with reference to table cust_order(via orderid & orderdate) (iii) discount_price is determined dependent on the quantity. if the quantity > 20 then discount 20 %(i.e discount_price = 0.8 * unit_price). if quantity < 10, no discount i.e normal price. if quantity between 10 and 20, discount 10%. Note that the quantity is determined based on each order of each customer, thus if the same book appears at multiple positions in an order, those positions shall be grouped together. This could happen because the pk of the order_detail table is order_id + item no, not order_id + isbn (iv) sales_amount is sales_quantity * discount_price
Hi all,I have two tables tbl_A and tbl_B.Table Definitions:tbl_A (field_1, field_2, field_3)tbl_B (field_1, field_2)I would like to populate tbl_B when a record is inserted into tbl_A.Note that tbl_A.field_1 = tbl_B.field_1 and tbl_A.field_2 =tbl_B.field_2 .How do I write a trigger on tbl_A to get the corresponding recordinserted into tbl_B?RegardsJune.....
