How To Retrive Correct Data
Nov 26, 2007
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....
so if i do Select * From TimeCardID = '1'
it should return
TimeCardExpenseID
2
3
4
Any help on how to get this done....
View 4 Replies
ADVERTISEMENT
May 8, 2008
Good day., please help me,in a formview control, i set it in Insert Mode, so it should display info from table 1 but when i click on the insert button, it will insert it in table 2.btw, table 1 and table 2 are in the same database?? how about if they are not in the same database?how?please help me,Thanks.,SALAMAT PO.,
View 3 Replies
View Related
Dec 15, 2006
How to retrive data from EXCL file by using SSRS.(My data is not sql server)
View 1 Replies
View Related
Apr 24, 2002
Hi,
Does anyone how to use SELECT statement data from two different SQL Server ?
Thanks in advance
Wilson
View 2 Replies
View Related
Jun 6, 2005
I have a locally installed MS SQL server 2000. I am trying to retrieve the contents of the table onto a datagrid. But when I use the user name and password, It gives the following errorSystem.Data.SqlClient.SqlException: Login failed for user 'aroop'. Reason: Not associated with a trusted SQL Server connection. string connectionString = "server=(local); uid=aroop; pwd=abcdef; database=mydb"; string commandString = "select * from mytable"; SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, connectionString);
DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet);
dgEmployees.DataSource = dataSet; dgEmployees.DataBind();Can someone please let me know what to do about this?
View 1 Replies
View Related
Aug 19, 2006
hi,
i need to code for retrieving single data from sqldatasource control. i need the full set of coding
connecting
retrieving data in text box
editing data to the table
updating data to the table
deleting data to the table
i want to fetch "single field data"
any one who know the code please post reply or send it to my mail senthilonline_foryou@rediffmail.com
View 1 Replies
View Related
Nov 6, 2006
hello dear friends
I am trying to save a binary file to database with the following code:
public static void memorystreamToDb()
{
MemoryStream mst = new MemoryStream();
UnicodeEncoding u = new UnicodeEncoding();
string Textn = "Test";
byte[] b = u.GetBytes(Textn);
mst.Write(b ,0,Textn.Length );
BinaryReader reader = new BinaryReader(mst);
byte[] file = reader.ReadBytes((int)mst.Length);
using (SqlConnection connection = new SqlConnection("Some Connection String"))
{
SqlCommand command = new SqlCommand("INSERT INTO temp (examplefile) Values(@File)", connection);
command.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = file;
connection.Open();
command.ExecuteNonQuery();
}
reader.Close();
mst.Close();
}
or with the other method from a real file (not memory stream)
public static void Addfile(string path)
{
CommonMethods_class k = new CommonMethods_class();
byte[] file = GetFile(path);
using (SqlConnection connection = new SqlConnection(k.Get_connection_string()))
{
SqlCommand command = new SqlCommand("INSERT INTO temp (examplefile) Values(@File)", connection);
command.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = file;
connection.Open();
command.ExecuteNonQuery();
}
}
and after running each of them seams that the file have been saved; but I can not retrive the files. I have tried some solutions from msdn but inside the created file is empty. the point that i really look for it is to just working with memory not in the disk before saving. and then retriving each field that I want.
looking forward your points
thank you in advance
View 2 Replies
View Related
Sep 9, 2015
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
[code]...
View 3 Replies
View Related
Dec 20, 2004
Hey,
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.
Any suggestions?
Thanks
View 11 Replies
View Related
May 9, 2007
when i retreive the data from the client machine, it is not fething the latest data.
(The server is enterprise edition and the client machine we have sql server express)
The results seems to be flextuating. like some time (01.00 -12.00) it show correct data and some time (say 12.01 - 24.00 ) it does not
this issue happens for other objects (sp and fns )also created in database. appears and disappears alternatively
View 5 Replies
View Related
May 30, 2007
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?
Thanks!
View 3 Replies
View Related
Nov 15, 2012
I have two table one call Employee and the other table call Target Ratio. They are related via FK EmployeeID
tblEmployee
EmployeeID FirstName LastName
1 John Doe
tblTargetRatio
TargetRatioID EmployeeID EffectiveDate Ratio
1 1 1/1/2012 8
2 1 6/1/2012 5
3 1 9/1/2012 7
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)
View 2 Replies
View Related
Nov 30, 2005
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.
View 7 Replies
View Related
Apr 21, 2008
Hi folks,
I'm a little new to SQL programming but I'm learning. :)
I need to do a join on a table where I exclude records that have more than one urn (unique record number).
In the example table below I want to exclude both entries in the cr_urn column of 49074 & 49075.
cr_urn proc_urn crs_seq crp_seq crp_site
49073233311NULL
49074205111NULL
49074261512NULL
49075128011NULL
490752185121945
49076233311NULL
49077233311NULL
490781145121875
To get to this ->
cr_urn proc_urn crs_seq crp_seq crp_site
49073233311NULL
49076233311NULL
49077233311NULL
490781145121875
View 3 Replies
View Related
Jun 10, 2015
Consider the below script
CREATE TABLE #TEMP(Id int,CreatedBy varchar(30),ModfiedBy varchar(30))
CREATE TABLE #TEMP2 (ID int,SearchedBy varchar(30))
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
Only the first record gets updated...
View 9 Replies
View Related
Apr 29, 2015
I have the two following locations.
They're both towns in Australia , State of Victoria
Fitzroy,-37.798701, 144.978687
Footscray,-37.799736, 144.899734
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:
Fitzroy, 0xE6100000010C292499D53BE642C0A7406667511F6240
Footscray, 0xE6100000010C89B7CEBF5DE642C02D23F59ECA1C6240
In my SQL Query, I have the following which works out the distance between both towns. Geo being my Geography column
DECLARE @s geography = 0xE6100000010C292499D53BE642C0A7406667511F6240 -- Fitzroy
DECLARE @t geography = 0xE6100000010C89B7CEBF5DE642C02D23F59ECA1C6240 -- Footscray
SELECT @s.STDistance(@t)
The result I get is
6954.44911927616
I then looked at formatting this as in Australia we go by KM so after some searching I found two solutions one for Miles and the other KM
So I changed Select statement to look like this
select @s.STDistance(@t)/1000 -- format to KM
My result is then
6.95444911927616
When I go to google maps and do a direction request between the locations provided above it says 10.2km (depending on traffic)
Now I'm new to this spatial data within SQL, why would I get a different result from google maps?
Also I would like to round this number so its easier to use within my where statement so I'm using Ceiling as shown here:
SELECT CEILING(@s.STDistance(@t)/1000)
Is ceiling the correct way to go?
Reason I need to round this is because we are allowing the end user to search by radius so if they pass in 50km I will then say
Where CEILING(@s.STDistance(@t)/1000) < 50
View 2 Replies
View Related
Feb 2, 2008
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.
Please help me to fix this problem. Thanks.
View 6 Replies
View Related
Nov 3, 2007
Hi
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
View 9 Replies
View Related
May 14, 2015
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?
View 7 Replies
View Related
Jul 26, 2007
SqlCommand sqlCommand = new SqlCommand(spName, Conn);
sqlCommand.CommandType = CommandType.StoredProcedure;sqlCommand.Parameters.Add("@XMLString", SqlDbType.Xml).Value = XMLstring;
sqlCommand.Parameters["@XMLString"].Direction = ParameterDirection.Input;sqlCommand.Parameters.Add("@DerivedFileName", SqlDbType.VarChar,50);
sqlCommand.Parameters["@DerivedFileName"].Direction = ParameterDirection.Output;
int test = sqlCommand.ExecuteNonQuery();
View 1 Replies
View Related
Apr 24, 2008
My execution fails .
Cannot we retrive xml from stored procedures.
CREATE PROCEDURE USP_XML_TEST (@no int ,@var xml output)
AS
BEGIN
SET NOCOUNT ON;
select @var = details_xml from student where student_id = @no
END
GO
View 1 Replies
View Related
Jul 20, 2005
I use select @@identity to return @@identity from my store procedure,but I could not retrive it from my Visual basic code, like variable=oRS.fields.item(0).value, it always says item can not be found....
View 3 Replies
View Related
Apr 24, 2008
My execution fails .
Cannot we retrive xml from stored procedures as output .
Blow is my code.
CREATE PROCEDURE USP_XML_TEST (@no int ,@var xml output)
AS
BEGIN
SET NOCOUNT ON;
select @var = details_xml from student where student_id = @no
END
GO
View 5 Replies
View Related
Jul 7, 2006
Hello,I would like to keep some values as session variables while the user is loged in, but i am missing some part of how to implement it.This is what I have:<script runat="server">
Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String = "SELECT users.username, users.password, users.FirstName, users.LastName, users.CompanyId, Company.CompanyName, users.SecurityLvl FROM users LEFT OUTER JOIN Company ON users.CompanyId = Company.CompanyId WHERE (users.password = @Password) AND (users.username = @Username)"
conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance")
cmd = New SqlCommand(cmdString, conn)
cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50)
cmd.Parameters("@Username").Value = Me.Login1.UserName
cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50)
cmd.Parameters("@Password").Value = Me.Login1.Password
conn.Open()
Dim myReader As SqlDataReader
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If myReader.Read() Then
FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False)
Else
'Response.Write("Invalid credentials")
End If
myReader.Close()
End Sub
</script> I would like to know how can I get now the "user.FirstName" and pass it to a session variable???how should I code it? thanks,
View 1 Replies
View Related
Jan 10, 2007
Hi Guys, I have this SqlDataSource, that counts some records and sets it in "NotStartedBugs". How do I retrive "NotStartedBugs" programmatically?
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT (SELECT COUNT(*) AS Expr1 FROM tickets WHERE (TicketType = 'Bug') AND (TicketStatus = 'Not Started')) AS NotStartedBugs"></asp:SqlDataSource>
View 1 Replies
View Related
Feb 28, 2007
Hello, inside of my SP i want to execute another SP, something like:
EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID
this function Forum_DeleteBoard returs one row with 3 columns as a table, how do i get the first column of that table into a variable so i can check if it was ok or not(it returns just one row with 3 columns).
Columns it returns:QResult , Threads , Answers
SELECT @isok = QResult FROM EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID ?
or how do you get it?
Patrick
View 1 Replies
View Related
May 28, 2007
Hi frds,
My question is how should i store a .DOC file in sql 2005 as image field and how to retrive the Doc stored in Image format
Thanks & regards
Sithender.s
View 3 Replies
View Related
Jan 7, 2008
I have a table called Image1 and i have stored the image in SQL server 2005 with a feiled called picture
table name ---- Image1
field-- picture (data type image)
please let me know the code step by step code how to Retrive Images from SqlServer in ASP .NET webpage, please help me ....
View 19 Replies
View Related
Jan 9, 2008
hai everybody!!!!
am developing one application in visual 2005..and sql server.
so how to write the coding for searching one record from a database table according to the id..and to display it
View 1 Replies
View Related
Jan 9, 2008
hai everybody!!!!!
am working wirh visual 2005 and sql server()asp.net).....so i need the coding in VB for searching one record from a database table according to the id..and want to display it.....
View 2 Replies
View Related
Mar 27, 2008
hi all,i want to print row numbers from the select command, how to retrive row numbers in mssql2003
View 1 Replies
View Related
Jul 12, 2004
This is DataSet retriving code from IBUYSPY Events Module.
public DataSet GetEvents(int moduleId) {
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
SqlDataAdapter myCommand = new SqlDataAdapter("GetEvents", myConnection);
// Mark the Command as a SPROC
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter parameterModuleId = new SqlParameter("@ModuleId", SqlDbType.Int, 4);
parameterModuleId.Value = moduleId;
myCommand.SelectCommand.Parameters.Add(parameterModuleId);
// Create and Fill the DataSet
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
// Return the DataSet
return myDataSet;
}
My Question is
1) Is this ideal way to retrive data?
2) Will it return connection back to pool?
View 4 Replies
View Related
Aug 28, 2004
i m new 4 asp.net
i retrive data from sql database.
now,
i want to put that retrived data into array.
how could i do this?
i am use vb.net
plz anyone give any idea.
it's urgent
thanks in advance.
View 1 Replies
View Related
