I am passing a parameter and executing two tables and grabbing it's data.. In the future I will put the code into a store-procedure.
--Exec Table 1 declare @id varchar(20), @MEMBER_ID varchar(20) set @id=null set @MEMBER_ID ='55555' --ie. 55555
Select id from emp Where MEMBER_ID = @MEMBER_ID
--Okay, Next I need to execute another table and pass in the id --that was selected from the emp table.
SELECT EMAIL FROM moreInfo WHERE id = @id
Currently, the emp table displays ie. 100 records that matches the member id 55555.. But the second select is empty.. And I need to display email data for the 100 records that were selected from the emp table..
I hope is it not confusing what I am trying to do..
Am trying to find a way to insert/update/delete data in a SQL mobile database on a Windows CE 5.0 device FROM a desktop PC.
This situation is completely stand alone, no network (apart form device/desktop), no GPRS etc etc etc.
I've looked at RDA but i dont believe it fits my app. (pulling data from a 2005 server that doesnt exist doesnt really help me much, push can't be used without a pull which kills the idea.)
The goal is a UI on the desktop that can manipulate data in the SQL mobile Database.
I've tried all i can find/think off in relation to this but to no avail.
My latest attempt has been using the simplest method possible (using a VS wizard datasource to the devices DB and tryign to whack that on a form) but this just creates a "Path not found. Check the directory for the database [Path = Mobile Device/ce_swipe/TestDB.sdf".
HiI have to tables one called GROUPS and one called ACCOUNTIn table Group I have the follwing fieldsGroupid, AccountFrom, AccountToIn table Account I haveAccountNo, Name etc.Records in Groups:P1, 1001, 1002P1, 1005, 1007P1, 1010, 1010P1, 1007, 1012Now I want to have the corresponding AccountNo from ACCOUNT (from rangeAccountFrom..AccountTo), that is the following result:1001100210051006100710081009101010111012If f.x. 1008 doesn't exist in ACCOUNT this should not be listed. Theresult will be used in another view.My problem is that I also get every other record from table ACCOUNT.Do anyone out there have a solution on my problem ?BR/Jan
I have a table that is returning rows from a table query. It seems I have done it before but I cannot seem to get the right procedure to obtain the values. I will paste in the code below in which you will see my bad attempts at accomplishing what I need.
Dim uid As String Dim pw As String Dim em As String, fn, ln, mi As String Dim par As String Dim Field, n, j As Integer Dim JJ As Integer
I have a pretty big SQL query with a one to many sort of relationship... There are client accounts that we're reporting on. Each account has four different historical categories attached. Each historical category can have maybe fifty entries in each, sorted by date. I need to figure out how to grab the unique accounts and show only the three most recent results per each historical subcategory with the account... the three most current results from each of the four subcategories, displayed by the parent client account number. I've created four views to isolate the subcategories as a start, thinking I could bring them into a parent query ... but my question would be... how do I generate JUST the top three historical transactions by account number? If I select TOP 3, I get only the 3 newest in the MASTER LIST, not per client account, which is what I need to do. Does this make sense? I could really use a good SQL helping hand with this one. Thank you!
Hi, First post here :) Subject might not make sense, so allow me to explain. I have a table, tbl_finalized. A unique record consists of an emp_id, building, and school_year. I have another table, (call it table2) where emp_id, building, and school_year are fields as well. What I'm trying to do is... grab all the emp_id's from table 2 ( along with the school_year and building ) and then see if it exists in tbl_finalized. If it does, I don't want it. I want all the usernames in table2 that don't exist in tbl_finalized.
Here's an example: table2 contains the following record: emp_id: xxx, building: 333, school_year: 2008 emp_id: zzz, building: 333, school_year: 2008 emp_id: yyy, building: 444, school_year: 2008
tbl_finalized has the following records: emp_id: yyy, building: 333, school_year: 2008 emp_id: xxx, building: 333, school_year: 2007 emp_id: xxx, building: 333, school_year: 2008
The result I desire is: emp_id: zzz, building: 333, school_year: 2008 emp_id: yyy, building: 444, school_year: 2008
I hope this makes sense. If not, please ask and I will do my best to explain. I have light understanding of joins, but not sure how to get the difference like I desire in this case... TIA.
HelloI want to write a stored procedure (using Enterprise Manager) that can grabthe digits that are inbetween the two dashes (-) in strings like:123-150-401-123-832-4215-61The digits to the left, right and inbetween the dashes could be any length,so a static "get the 5th, 6th and 7th digit" stored procedure won't work.Many thanks,--Chris Michaelwww.INTOmobiles.comDownload 100s of ringtones, wallpapers & logos every month for only £1.50per week
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 temp table with a column of all the same number. When I run my select statement I need it to bring back all the other data and just the single instance of the column with all the same numbers. How would I do this in my select statement?
For example, here is my table:
Here1One There1Two Up1Three Down1Four Back1Five
I need to bring back everything from column 1 and 3, but just the single instance of 1 from column 2 since it contains all the same number. All the data is collected in a single select statement.
I'm a newbie so I'll explain what I'm trying to achieve the best I can ...
I'd like to essentially loop through a SQL table to display the correct results. The workflow is the user query's the database and returns records (by property ID). In the return there are duplicate records being returned - in this case, two property owners returned with the same property ID.
How would I loop through the SQL statement in the application (code) to identify when the property id's are the same and display only one owner for that property?
Code Block SELECT tce.TimeCardID, tce.TimeCardExpenseID, tc.DateCreated, e.LoginID, e.FirstName + ' ' + e.LastName AS FullName, tce.ExpenseAmount, tce.ExpenseDescription, op.ProjectName, op.ProjectDescription, ec.ExpenseCode FROM OPS_TimeCards tc JOIN OPS_Employees e ON e.EmployeeID = tc.EmployeeID JOIN OPS_TimeCardExpenses tce ON tc.TimeCardID = tce.TimeCardID Join OPS_Projects op ON op.ProjectID = tce.ProjectID Join OPS_ExpenseCodes ec ON ec.ExpenseCodeID = tce.ExpenseCodeID WHERE e.LoginID = 'jross' ORDER BY tc.DateCreated DESC
this query returns me the correct data....but i need to tweak the query so it does not duplicate rows....My tce.TimeCardID is a PK in its table and so is TimeCardExpenseID...but the problem is U can have many TimeCardExpenseID's for one timecard so my results look like
TimeCardID TimeCardExpenseID 1 2 1 3 1 4
I want my query to return the "TimeCardID" but i just want that one ID to represent all the TimeCardExpenseID's...but i can not get it to work and have no clue....
My question is how can I query tblTargetRatio table to return correct record for the following cases:
1 EmployeeID = 1 and Date = 03/12/2012 (Expecting Ratio = 8) 2 EmployeeID = 1 and Date = 10/10/2012 (Expecting Ratio = 7) 3 EmployeeID = 1 and Date = 08/12/2012 (Expecting Ratio = 5)
Hi, I have one column of data which is 15.678 but in the excel, i format it to 15.68 ( two decimal place, so in excel i should see 15.68), when i am trying to import the data from excel to sql server by using odbc connection, it still getting 15.678, how can i get the data from 15.678 to 15.68 ( what i see is wat i get). Thanks for help.
Ok I think I will need to use a temp table for this and there is no code to share as of yet. Here is the intent.
I need to insert data into two tables (a header and detail table) the Header Table will give me lets say an order number and this order number needs to be placed on the corresponding detail lines in the detail table.
Now if I were inserting a single invoice with one or more detail lines EASY, just set @@Identity to a variable and do a second insert statement.
What is happening is I will be importing a ton of Invoice headers and inserting those into the header table. The details are already in the database across various tables and and I will do that insert based on a select with some joins. As stated I need to get the invoice number from IDENTITY of the header table for each DETAIL insert.
I am assuming the only way to do this is with a loop... Insert one header, get identity; Insert the detail table and include the IDENTITY variable, and repeat.
INSERT INTO #TEMP VALUES(1,'James',NULL) INSERT INTO #TEMP VALUES(1,'James','George') INSERT INTO #TEMP VALUES(1,'James','Vikas')
INSERT INTO #TEMP2(ID) VALUES(1) INSERT INTO #TEMP2(ID) VALUES(1) INSERT INTO #TEMP2(ID) VALUES(1)
Now i want to get the result as
;WITH CTE AS( SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,* FROM #TEMP ) SELECT CASE WHEN RowNum=1 THEN CREATEDBY WHEN RowNum > 1 THEN ModfiedBy END FROM CTE
But when i convert this select to update, i am missing something...
My update is
;WITH CTE AS( SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,* FROM #TEMP ) UPDATE #TEMP2 SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY WHEN RowNum > 1 THEN ModfiedBy END FROM CTE WHERE #TEMP2.ID=CTE.ID
After running geography::Point(Latitude, Longitude , 4326) on the latitude and longitude provided for each location, my Geography column for each row is populated with the following:
Hi, I am working on a .dll which need to access .sdf ( sql server mobile db). In my project, I added a reference "System.Data.SqlServerCe.dll". The dll is located in C:Program FilesMicrosoft Visual Studio 8Common7IDE. I am able to compile the project. Then I created a Unit project for this dll file. In Unit project, I added the same reference. Now the magic things happen. The compiling was failed. I got "Error 1 The type 'System.Data.Common.DbConnection' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. C:VS2005MobileDBUnitMobileDBProjectUnitMobileDB.cs 50 13 UnitMobileDBProject".
I don't understand how come the same .dll could pass in a dll project but failed in unit test project. And I never saw the System.Data.SqlServerCe display in ".Net" Section of Add reference. But in my office, I installed same version of VS2005 for software tester. I could see the System.Data.SqlServerCe display in the ".Net" section of Add Reference.
I am currently developing my first database driven application and I have stumbled over some quite simple issue. I'll describe my database design first: I have one table named images(id (identity), name, description) and one table named albums (id, name, description). Since I'd like to establish a n:n connection between these, I defined an additional table ImageInAlbum (idImage, idAlbum). The relation between these tables works as expected (primary keys, foreign keys appear to be ok).
Now I'd like to insert data via a stored procedure in sql server 2005 and I'm not sure how this procedure will look like. To add a simple image to a given album, I am trying to do the following: * Retrieve name, description from the UI * Insert a new row into images with this data * Get the ID from the newly created row * Insert a new row into "ImageInAlbum" with the ID just retrieved and a fixed Id from the current album.
I know how I would do the first two things, but I am not used to Stored Procedures syntax yet to know how to do the other things.
Any help is appreciated ... even if it means telling me that I am doing something terribly wrong
I am using a BCP format file to import a CSV file. The file looks like the following:
"01","02"
The format file looks like the following:
6.0Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â SQLCHARÂ Â Â Â 0Â Â Â Â Â 0Â Â Â Â Â Â """Â Â Â Â Â Â Â Â 0Â Â Â "" 2Â Â Â Â Â SQLINTÂ Â Â Â Â Â 0Â Â Â Â Â 0Â Â Â Â Â Â "",""Â Â Â Â Â 1Â Â MROS 3Â Â Â Â Â SQLINTÂ Â Â Â Â Â 0Â Â Â Â Â 0Â Â Â Â Â Â "" "Â Â Â 2Â Â MROF
When both the two fields are set to SQLCHAR data types the data imports successfully without the quotes as 01 and 02. These fields will always be numbers and I want them as integers so I set the data type to int in the database and SQLINT in the format file. The results was that the 01 became 12592 and the 02 became 12848. where these numbers are coming from?
I am working on a school project and have come up against a bit of a sticking point. I am supposed to be creating a very basic OMS, the teacher themselves have said they do not know how to do this (in previous years it has all be done via Access) but apparently I am a lucky one to be doing it in SQL this year.
One of the user requirements of the OMS is to fill a data grid with product name and the product details which I have the query for or rather I have created a view for, which is then queried from a stored procedure.
CREATE VIEW [dbo].[v_stock] AS SELECT tab_products.productname, tab_productdetails.description, tab_productdetails.image, tab_productdetails.price, tab_productdetails.stock FROM tab_productdetails INNER JOIN tab_products ON tab_productdetails.productid = tab_products.productid
The problem I am having is then returning the data from this query into a data grid, I think the reason is because when I attached the stored procedure to a table and then call that procedure via the table adapter there is a mismatch of the schema - specifically the table it is attached to does not contain the column "productName".
I am thinking I need to create a temporary table to fill the data grid with - however, I am not sure how I would create a temporary table.
Is there something I am missing or not done correctly. As far as I can tell the queries work as when I preview them they produce the expected results.
Hi, I was wondering how it is posible to join three data sets from different data flows into one txt file. Let's explain a little more:
I have 3 dataflows. Each of them connect to sql server and and by a SQL command, they bring data into SSIS.
Each SQL command differ between them. So each data set have different columns (they dont have the same format). Also the amount of columns differ between each one.
What I need is to join the three data sets into one txt file. How can I do this? It is posible to join them with different data set formats into a txt file?
Is this the best way to join different data? It is better to use as many OLE DB Sources are needed instead of different data flows? Thanks for your help!
Does anyone know if it is possible to point data that underwent the "merge join" transformation (in one data flow) to the following data flow? I don't want to recreate all that merging, sorting and calling the same sources again in the following data flow if the data that I am using exists in the previous data flow. The merged data is simply too big to export to an excel file, so does anyone have any ideas? Thanks!
I tried checking to see if the point at which the reader was, that if it was the record I am looking for to go ahead and add the table data to a label. But for some reason it's only taking the first record in the database and not the one I thought I was at.[CODE] public void UpdateMaleHistLbl() { SqlConnection conn = new SqlConnection("Server=localhost\SqlExpress;Database=MyFamTree;" + "Integrated Security=True"); SqlCommand comm = new SqlCommand("SELECT * FROM FatherHistTable, MotherHistTable, UsersTable WHERE UsersTable.UserName = @usrnmeLbl ", conn); comm.Parameters.AddWithValue("@usrnmeLbl", usrnmeLbl.Text); conn.Open(); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { string usr = reader["username"].ToString(); usr = usr.TrimEnd(); string pss = reader["password"].ToString(); pss = pss.TrimEnd(); if (usrnmeLbl.Text == usr) { if (hiddenpassLbl.Text == pss) { maleHistLbl.Text = reader["GG_Grandfather"] + " > "; maleHistLbl.Text += reader["G_Grandfather"] + " > "; maleHistLbl.Text += reader["Grandfather"] + " > "; maleHistLbl.Text += reader["Father"] + " > "; maleHistLbl.Text += reader["Son"] + " > "; maleHistLbl.Text += reader["Grandson"] + " > "; maleHistLbl.Text += reader["G_Grandson"] + " > "; maleHistLbl.Text += reader["GG_Grandson"] + "<br /><br />"; } } break; //exit out of the loop since user found } reader.Close(); conn.Close(); }}[/CODE]Thanks in advance
I need joining 2 tables and putting the result into a new table.
Table export has the following columns: Period, Actual, Company, Currency, Account, Amount, a,b,c,d,e,f,g,h,i,j,k
Table cc_split has the following columns: Company, Fun_Account, CC_Account, x, y, Split
The new table called export2 has exactly the same values as export i.e. : Period, Actual, Company, Currency, Account, Amount, a,b,c,d,e,f,g,h,i,j,k
So what I need to join from export and split into export2 are company, cc_account (that shall go into account column of export2 together with the account of export) and the amount, which shall be ( split column * amount column of export), the rest values shall be the same.
So what I have so far is
Code: INSERT INTO EXP_DATA_2(PERIOD, ACTUALITY, COMPANY, CURRENCY, ACCOUNT, AMOUNT, EXTDIM1, EXTDIM2, EXTDIM3,EXTDIM4,JOURNAL_TYPE,C_COMPANY, COMPANY,C_DIM, TRANAMOUNT, TRANCURR,REGION, TRANSF_DATE) SELECT COMPANY, CC_ACCOUNT, SPLIT FROM EXP_DATA INNER JOIN CC_SPLIT ON EXP_DATA.COMPANY=CC_SPLIT.COMPANY AND EXP_DATA.ACCOUNT = CC_SPLIT.CC_ACCOUNT WHERE EXP_DATA.PERIOD IN ('1102', '1103', '1104')]
So at the end basically table exp_data 2, shall have the same values as exp_data but the account column shall include the same values of account column of exp_data plus the values of cc_account of cc_split table. And also the amount column shall be the multiplication of the column split from cc_split * amount of exp_data
I have realised that I need an alias, maybe a for exp_data and b for cc_split.
i have 2 tables not connected in any way but both have orderid filed (same filed). In one table this filed (and onther one) are keys, the second table dose not hace a key at all. The same order_id CAN repeat itself in each table. When i try to join the tables (some rows just in one table and some in both): Select tab1.name, tab1.orderid, tab2.sku from tab1 inner join tab2 on tab1.orderid=tab2.orderid The result i get is duplicate. each row is multiple. What I'm doing wrong?
Table One is an older database and has the column employee id, which would always consist of first letter of the last name an underscore and a numeric value. So for example, data looks like
employeeID R_12345678 S_5555555 T_777777 U_7777
Our new data structure simply removed the letter & underscore.
employeeID 12345678 5555555 777777 7777
Now my question is, how could I join on oldDB.employeeID to newDB.employeeID since the data is very similar, but not exactly the same?
I have three tables that I am trying to join in order to produce some report output. I have tried numerous ways, but they all don't give the results I need to provide.
Below is the table creation, some inserts for data and the output I am looking for.
Detail_Acct_By_Day: EMPName | Acct_Num | Channel | Medium | Date | Rev $ | Clicks | Impressions BobSmith | X0000005 | US | Net | 2008-05-11 | 500.50 |240 | 500 BobSmith | X0000005 | US | Cable | 2008-05-11 | 23.75 | 0 | 0
My latest query, which doesn't work is: select S.EmpName, S.acct_num, R.RevenueDate as Date, coalesce(R.Channel, P.channel)as channel, coalesce(R.medium, P.medium), GrossRev, coalesce(P.clicks, ''), coalesce(P.impressions, '') FROM SalesAcct AS S left outer join Revenue as R on S.acct_num = R.acct_num left outer join Perf as P on S.acct_num = P.acct_num
Yields two rows...but the Medium duplicates and the Clicks and Impressions also get duplicated when they should be zero.
BrokersCode BrokerName 100 John 101 muller 102 ABC
Now my requirement is to show broker Name (John, Muller, ABC) against their codes in table Booking. i tried with left outer join, but this is not working after join in result it shows more rows in booking table as original. (No of records in booking table should be same after join as before, the thing is only to show brokers names) .
