Crazy Selection Help
Aug 21, 2007
Hi everyone -
I have two tables, one table (A) that holds a field called location that
has the partial path information of the file
and the second table (B) holds the full path including the filename
I only want to display the records from the partial path (A)
table that do not reside in the second (B) table
Table A =
imagefilename, description, directory, companyname, location
"96.jpg","test","Prog2006_02","Marey, John","Prog2006_0296.jpg"
"274.JPG","disney","Prog2006_07","Amy Gross","Prog2006_07274.JPG"
"570.jpg","sdfsdf","Prog2007_06","Lof3,"Test3","Prog2007_06570.jpg"
"850.JPG","222","Prog2007_08","Malis, Mark","Prog2007_08850.JPG"
Table B =
imagefilename
"d:webdatawebuploadsqfimagesProg2006_0296.jp g"
"d:webdatawebuploadsqfimagesProg2006_03112.p df"
"d:webdatawebuploadsqfimagesProg2006_03127.j pg"
I was thinking about using a cross join with a like condition,
but it fails (go figure)
could someone offer a little help to the query that i need to
perform??
thanks
tony
View 6 Replies
ADVERTISEMENT
Oct 17, 2007
ok I will post no code this time. I need help I have tried a half of dozen methods and they all only work half way. I need to create a search page that searches on all keywords in a textbox. I have tried split functions, like commands, freetext catalogs.
I want to go with the free text search.
How?
Has anyone used a freetext search in asp or c#? please post some code I re arrange it or let me know how to pass the freetext search param to the sql server.....
Please.....
View 3 Replies
View Related
Oct 5, 2005
Hey all,I need to import an excel file into a SQL table. Users are able to modify and save the excel file. I have a stored procedure that calls a structured storage file which does the import. Both the excel file and the structured storage file reside in the same location. The first time that the stored procedure fires the DTS it works fine, but if the data in the excel file is overwirtten by a user it appears that the import does not function.Am I missing something here?Here is the SPROC:CREATE PROCEDURE [dbo].[importEmployees] AS
DECLARE @object intDECLARE @hr int
--create a package objectEXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUTif @hr <> 0BEGIN print 'error create DTS.Package' RETURNEND
EXEC @hr = sp_OAMethod @object, 'LoadFromStorageFile', NULL, 'C:importEmployees.dts', ''IF @hr <> 0BEGIN print 'error LoadFromStorageFile' RETURNEND
EXEC @hr = sp_OAMethod @object, 'Execute'IF @hr <> 0BEGIN print 'Execute failed' RETURNEND
GO
View 1 Replies
View Related
Aug 7, 2001
Hiya!
I have a VB6 app using ADO to connect to SQL Server 7. On one form I am doing two consecutive inserts and I want them to be wrapped in a transaction for integrity. My code looks like this:
connection.errors.clear
connection.begintrans
connection.execute statement 1
connection.execut statement 2
if connection.errors.count > 0 then connection.rollbacktrans else: connection.committrans.
On the BeginTrans line I get the following error:
-2147168227
"Transaction cannot start because more than one ODBC connection is in use". My NativeError is 0, I searched BOL and couldn't find anything informative so I think this is an ADO error only. Also, running sp_who in SQL showed everything was OK. So what's causing this error?
Thanks,
Sarah
View 1 Replies
View Related
Jul 14, 2004
Hi! Have you ever wanted to make life easier? Well, so I am :-)
I have to understand old database ported to ms sql server 2000. Well, the problem is that there are no primary keys at all, no constraints. Database logic is thrown onto triggers and Delphi applications. All fields that should be primary key are in people heads...just mentally :(
Well, you'll say try to find equal fields in different databases... well i was trying to do this. It's impossible...
Any ideas except social engineering?
Thanx
View 3 Replies
View Related
Feb 6, 2008
Let me please start off by saying if your response to this question is:
"That's retarded! Why are you doing it that way! Do it this way!"
Please refrain from answering my query. With that out of the way, here I go.
I have attached a Sql Server 7.0 db (.mdf and .ldf file) to an instance of MSSQL 2000. When this attach happens, MSSQL2000 automatically upgrades it.
I then detach the db, and place the modified files back into the directory the Server 7.0 instance looks for. Obviously the format of the db files (.mdf and .ldf) have changed following the upgrade. I need to know if there is anything I can do (script or programmatically) to modify the files so that my Sql Server 7.0 instance can read them properly when the engine goes online.
Thank you in advance for any tidbits you can provide
View 7 Replies
View Related
May 9, 2008
Hi,
Is this at all possible to do. I have been stuck for a couple of hours trying.
SELECT DISTINCT ItemTag,
ItemBinLocation
FROM VInventory
WHERE
(
SELECT
CASE x
WHEN 'x'
THEN (LEFT(ItemBinLocation, 1) = 'abc'
WHEN 'y'
THEN LEFT(ItemBinLocation, 3) = 'efg'
END
FROM dbo.OtherTable
)
???
I need some Yak Milk please!
View 1 Replies
View Related
Sep 18, 2006
I keep getting this stupid error, if
any of you have time to kill and could look at this I would really appreciate
it.
This checks for a login in the
database and if it already exists it makes it unique.
In the error message, €˜ssewell504€™
was the value returned after the proc ran, the last 3 digits will be different
each time it€™s ran.
Thanks guys and take
care!Here's the error:
"Syntax error converting the varchar value 'ssewell504' to a column of datatype int"
ALTER proc
[dbo].[spx_CONS_CheckLoginUnique]
(
@SourceLogin varchar(25) = 'ssewell',
@TargetLogin varchar(25) = 'ssewell'
)
as
begin
declare @ReturnedValue int
declare @AlreadyInUse int
declare @AppendedValue varchar(3)
declare @Range int
set @Range = 999
set @AlreadyInUse = 1 -- set flag
--if target login and source login are
the same...we need to change target
login
if @SourceLogin =
@TargetLogin
begin
while @AlreadyInUse = 1 -- this stays eq to 1 until target login
is unique in DB
begin
Set @ReturnedValue = CONVERT(int, (@Range+1)*Rand()) -- get random
num
Set @AppendedValue = CAST(@ReturnedValue as varchar(3)) -- cast value to
varchar
set @TargetLogin =
@TargetLogin + @AppendedValue --
append to target login
--if by chance this login is already in
there(taken), keep going until it's
not
if EXISTS(select Login_Name from dbo.SECR_Login where Login_Name =
@TargetLogin)
begin
set @AlreadyInUse = 1
--set flag to keep
looping
end
else
begin
set
@AlreadyInUse = 0 --set flag to exit out of loop, our login is now
unique
end
end
end
return
@TargetLogin
end
View 3 Replies
View Related
Apr 29, 2007
Hello,I have two tables, Articles and Comments, with the following columns:Articles - [ArticleId] (PK), [ArticleTitle], [ArticleText], [ArticlePubDate]Comments - [CommentId] (PK), [ArticleId] (FK), [CommentTitle], [CommentText]I need to display on a web page the articles published during the last week and their comments.What I need is:1. Get Articles (DONE) 2. Get Comments for each ArticleThe solution I see are:1. Create a Stored procedure that somehow outputs 2 tables: Articles and Comments associated with those articles2. Create 2 Stored procedures: The first one outputs the articles. The second output all comments given an article ID In this case, while the data is being displayed on the page it will load the comments for each article. The problem is that I will have many round trips to the server.I know how to use (2) but this would give me many round trips to the database.Could someone help me out with this?Thanks,Miguel
View 4 Replies
View Related
Mar 5, 2004
I posted this scenario earlier and ndinakar said my code worked on (I guess) his own machine but has not worked on mine! Does that not sound funny? I need to ge through with this simple but frustrating stuff in time. I've implemented more complex snippets before this but I just can't figure the problem out.
Can anyone out there tell me what I could be doing wrongly? I wrote a simple stored procedure called 'proc_insert_webuser' in a database that has been moved to a remote server called LAGOS-NTS3. I executed it from SQL Query Analyser and it worked. When, however, I executed the same stored procedure from ASP.Net it gives an impression that a new record has been inserted and even displays the newly generated ID column to me. On checking the base table I found only blank columns with only the ID column having the generated IDs!
I'm perplexed. I checked the code and can't possibly spot the error. Can anyone help. Found below are snippets used.
CREATE PROCEDURE proc_insert_webuser
(
@applicantidnumeric(18) output,
@firstnamevarchar(18),
@midname varchar(18),
@lastname varchar(50),
@secretcodevarchar(18),
@my_errnumeric(18) output
)
AS
IF (SELECT COUNT(ApplicantID) FROM tbl_webuser WHERE Firstname = @firstname AND Midname = @midname AND Lastname = @lastname) = 0
BEGIN
begin transaction
INSERT INTO tbl_webuser (Firstname, Midname, Lastname, SecretCode)
VALUES (@Firstname, @Midname, @Lastname, @SecretCode)
commit transaction
SET @applicantid = @@IDENTITY
END
ELSE
BEGIN
RAISERROR('Error! Execution aborted. A matching user profile exist.', 16, 1)
SELECT @my_err = @@ERROR
END
RETURN
Then the ASP.Net stuff:
Sub PostData()
'connection string production
Dim cstring As String = "User ID = sa; Password = ; database = E-Recruitment; Server = LAGOS-NTS3; Connect Timeout = 60"
'connection instantiation
Dim cnx As SqlConnection = New SqlConnection(cstring)
Try
'open connection
cnx.Open()
'instantiates and execute a command object
Dim cmd As SqlCommand = New SqlCommand
With cmd
.Connection = cnx
.CommandText = "proc_insert_webuser"
.CommandType = CommandType.StoredProcedure
Dim param1 As New SqlClient.SqlParameter("@applicantid", 0)
param1.DbType = DbType.Single
param1.Direction = ParameterDirection.Output
.Parameters.Add(param1)
Dim param2 As New SqlClient.SqlParameter("@firstname", txtFirstNM.Text)
param2.DbType = DbType.String
param2.Direction = ParameterDirection.Input
.Parameters.Add(param2)
Dim param3 As New SqlClient.SqlParameter("@midname", txtMidNM.Text)
param3.DbType = DbType.String
param3.Direction = ParameterDirection.Input
.Parameters.Add(param3)
Dim param4 As New SqlClient.SqlParameter("@lastname", txtLastNM.Text)
param4.DbType = DbType.String
param4.Direction = ParameterDirection.Input
.Parameters.Add(param4)
Dim param5 As New SqlClient.SqlParameter("@secretcode", txtPWD.Text)
param5.DbType = DbType.String
param5.Direction = ParameterDirection.Input
.Parameters.Add(param5)
Dim param6 As New SqlClient.SqlParameter("@my_err", 0)
param6.DbType = DbType.Single
param6.Direction = ParameterDirection.Output
.Parameters.Add(param6)
.ExecuteNonQuery()
'pick the auto-number
Dim strAppID = .Parameters("@applicantid").Value
'notifies the user of record success
lblMessage.Text = "Profile created. Your ApplicantID is : " & strAppID & " Kindly take note of your UserID. You'll need it for future logins."
End With
Catch ex As Exception
'notifies the user of any error(s)
lblMessage.Text = Err.Description & " originating from " & Err.Source
Finally
cnx.Close()
End Try
End Sub
==================
I'm calling this procedure from the click event of a command button. What could be happening to the code? I have done similar things and got positive results! Any hint from anyone out there?
View 6 Replies
View Related
Aug 29, 2001
Hi
I have and issue with a query, I tried every thing but I couldn't find why. Here is the issue, I have one big DB1 with about 30 tables, the query based on a join of 5 tables, I have the right index in place, when I run the query it will take about a minute and 20 sec. But I have another DB2 on the same server and I imported just the 5 tables form DB1 with the same index, when I run the same query it will take 6 seconds.
I did drop and recreate the indexes and drop and recreate the statistics but still the same thing no difference.
I'm running SQL7 SP3.
Do you have any idea what might be the issue here?
Thanks for your help
View 4 Replies
View Related
Oct 5, 2006
Hi all,
I am having a serious problem developing my reports within VS2005. For no reason, on one of my report it seems the parameters have got a life of there own.
There is a collection of 4 parameters whose values change randomly (internal, hidden, multivalue). Regardless of what I set them to, once I click save some change, when I alter 1 parameter, the values of one of the other parameters change while the settings for the parameter I changed to not apply.
What is going on here, I have been developing reports without issue however this is becoming a major concern. Please help!
Kind regards
Taz
View 2 Replies
View Related
Nov 29, 2007
Hi Guys, I have a syntax error in my SQL SELECT Query that is driving me crazy. All I am doing is adding a conditional with a literal value and that is breaking the query.This Query Works: SELECT
Quantity,
CAST(Gallons AS VARCHAR(10)) + ' ' + Style,
Colour
FROM
ORDER_TANK_LINK
LEFT JOIN TANKS
ON TANKS.TankID = ORDER_TANK_LINK.TankID
LEFT JOIN COLOURS
ON COLOURS.ColourID = ORDER_TANK_LINK.ColourID
This One is Broken:SELECT
Quantity,
CAST(Gallons AS VARCHAR(10)) + ' ' + Style,
Colour
FROM
ORDER_TANK_LINK
ON ORDER_TANK_LINK.OrderID = 1
LEFT JOIN TANKS
ON TANKS.TankID = ORDER_TANK_LINK.TankID
LEFT JOIN COLOURS
ON COLOURS.ColourID = ORDER_TANK_LINK.ColourID
Obviously I haven't explained the design of the tables, but because of the fact that I have another query that is so similar working perfectly, I didn't think you'd need it.
The exact error is "Incorrect Syntax Near the Keyword 'ON'". OrderID is definately a value of the ORDER_TANK_LINK Table and is of type int.
Thanks in advance!
View 4 Replies
View Related
Apr 16, 2008
I'm trying to do a basic update query which is working on other pages but not on this page. Dim uid As Integer = CInt(Session("uid"))
Dim cmd As New SqlCommand("UPDATE [cvdata] SET [jobCompanyName] = @inputJobCompanyName WHERE [user_id] = @uid", strConn)
With cmd.Parameters
cmd.Parameters.AddWithValue("@inputJobCompanyName", inputJobCompanyName.Text)
cmd.Parameters.AddWithValue("@uid", uid)
End With
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
The funny thing is that if i remove inputJobCompanyName.Text and add a custom value (for example "test") it works.So it doesn't seem to read my updated textfield or something im clueless.Kind regards,
Mark
View 2 Replies
View Related
Jul 17, 2007
First; thanks for looking.
On SQL Server 2000. IBM X series, dual 2GHz Xeon processors, 3GB RAM, RAID 5 DB array and separate Mirrored drive for OS, Windows 2000 Server.
Note: I should have moved the log files to the mirrored volume - I only just noticed it wasn't like that ... oops.:o It only has the OS and backup files (different logical volumes).
Problem: I have a Bill of Material system. It has a batch (Stored Procedure) that explodes Order Line Items. Each line has about 40 components.
The SP has 5 major steps to explode the Assembly, figure out colors/sizes to get the actual part numbers, assign quantities, update flags on the line item table (that has 3,500,000 rows but is not a clustered index - fill factor 85% on the unique autonumber index I use for the updates). The SP is jam packed with User Function calls, sub-Stored Procedures, and (yes) some cursors (on small declared temp tables created for each Order Line processed).
The execution plan for the whole batch of 100 is humongous - like 1000 pages, however nearly all steps are about 16 - 40 milliseconds to execute.
It normally takes 13 seconds to process 100 line items (3900 assembly rows created). However; about every 20th run, it takes 2 or 3 minutes ON THE VERY SAME LINE ITEMS. No programming changes! Run back-to-back, or with a minute or two delay. Then running it again, back to 13 seconds. Sometimes it takes 25 or so seconds, but that oddball 3 minutes is a killer.
I put some timing INSERTs into a temporary log file to localize the problem.
So; here's the REALLY weird thing. The part that experiences the intermittent 2 minute delay is a VERY SIMPLE call to a SP that INSERTs to the tbBATCHs table and gets the identity and passes it back with an Output variable. The tbBATCHs table only has about 8 columns. Nobody else uses it so it's not locking. It has an "If Exists" selection following the Insert to see if another batch is in progress not yet flagged as complete, but's not the issue because it's never true (hard coded values for testing forces this).
I have a Standby Server (duplicate config of main server) so I ran it there. Same issue.
If I loop 100 times on the tbBATCHs INSERT SP, it is pretty quick (like 2 seconds for all 100).
It's never on the first run, so I think it may be some sort of Log File catch-up.
Also; I DO NOT have Transaction Logging in place yet.
Ideas?
View 5 Replies
View Related
May 29, 2008
I need some serious help with this query...
SELECT *
FROM dbo.wce_contact
WHERE (Mail1Date IS NOT NULL)
AND (Mail2Date IS NULL)
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
or(IDStatus LIKE '')
or (NOT (Task LIKE '%x%'))
or (ExpressEmail LIKE '%@%')
or (WebSite NOT LIKE '')
or (Unsubscribe = 0)
or (Artwork LIKE '')
Basically the parts in red work best when they use the 'or' criteria.
My problem.
I run the query together black+red and get a different result than expected. Is there a way of saying 'do the black part first and its an imperative that the red part follows those rules on the black part of the query?' eg:
IDStatus must be LIKE '' when running the black part of the query
and expressemail must be like '%@%' on the black part of the query but not on red part of the query. So it doesnt matter that the expressemail must have idstatus like '' whilst having an @
Hope it makes sense??
View 11 Replies
View Related
Nov 16, 2006
Hi,
I have created a new user in my database called "acm", I also created a new schema called "acm" as well, the "acm" user is its owner, and the default schema for "acm" user is the "acm" schema.
I also created a login called "acm" (surprise surprise) and set its default database to "mydb" database.
In "mydb" database I have a table "mytable" that is owned by "acm". In my connection string I am using "acm" as the user, but when I do:
"select * from mytable" I get error ("bla bla object bla bla"), when I use "acm" as a prefix for the table name it works fine... What do I miss??
My connection string is:
Data Source=.SQLEXPRESS;AttachDbFilename="C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAccountManagerDb.mdf";Persist Security Info=True;User ID=acm;Password=acm;Connect Timeout=30;User Instance=False
Thanx,
Yuval.
View 7 Replies
View Related
Feb 19, 2008
I take it SSCE 3.5 only runs under .Net 3.5? My project was updated from VS05 to VS08 but the target is still .Net 2.0 would this be why I cant use the SQLCE namespace.
Forgive me for being noobalishous but I have been thinking that SSCE 3.5 would be a great solution for my app as I want to be able to use SQL Server Express 2005 and some other Local DB system (SSE is too bulky for my app when networking is not required). But if the prereq is that the client must have .Net 3.5 installed then I am back to square 1 because .Net 3.5 is like 195Mb install (which i tried installing as a prereq on my friend's Vista Ultimate(?) machine yesterday and it crashed it). Aaargh!! How are you supposed to develop small DB apps (3.5Mb max for app footprint) in the VS environment that don't require massive installation prereqs???
Dont get me wrong I love VS but surely there has to be a way to minimise prereq overheads.
View 5 Replies
View Related
Sep 22, 2007
I built a new computer which has intel e6750 core duo 2.66 GHZ cpu. Firstly I installed windows XP with sp2 and installed whole patches.Then install Microsoft office 2007 and install microsoft visual studio 2005 I got an error with sql express 2005 like below: And After I install windows XP again. with no patches first ı install VS2005 but got same error. Then I change my windows release, I install windows XP with SP1 then I tried but got same error. I tried to install seperate sql server express file but same error. is it about my hardware or what? Please response as soon as possible? Thanks.
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=29503&EvtType=sqlsetuplib%5csqlservice.cpp%40Do_sqlScript%40sqls%3a%3aSqlService%3a%3aStart%40x2
------------------------------
BUTTONS:
&Retry
Cancel
View 1 Replies
View Related
Sep 25, 2006
Hi everyone,
I am new to T-SQL and am trying to do some simple database for fun. Here is my problem and hope you guys can drop me some hint or solution:
OS: Win XP pro
SQL Server 2005 Express
Tool: SQLCMD in command prompt
I created a database and then create a table. Then I tried : select * from contract_Info, it gave me this:
1> CREATE TABLE Contract_Info
2> (
3> Order_No varchar(50) NOT NULL ,
4> Company varchar(255),
5> Product_Name varchar(255) NOT NULL,
6> Discount_Rate varchar(50), --Discount_Rate (%) -> Discount_Rate
7> Status varchar(50) NOT NULL,
8> Quantity varchar(50) NOT NULL,
9> Remainder varchar(50),
10> Deleted bit
11> )
12>
14>
15>
16> INSERT INTO Contract_Info values ('00000', 'DEFAULT','DEFAULT','0','Cancelle
d','0','0',0)
17>
18> go
(1 rows affected)
1> select * from contract_Info
2> go
Order_No Company
Product_Name
Discount_Rate Status
Quantity Rema
inder Deleted
-------------------------------------------------- -----------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------------ -------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- -------------------------------------------------- --------------------------
------------------------ -------------------------------------------------- ----
---------------------------------------------- -------
00000 DEFAULT
DEFAULT
0 Cancelled
0 0
0
(1 rows affected)
1>
In SQL Express Management , the output looks perfectly fine
What is missing??
Thanks for your time
View 6 Replies
View Related
Sep 19, 2006
This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).Employees table:empNameJane DoeAlton BrownJohn DoeTrainingCourse table:courseNameWeldingBrain SurgeryScuba DivingResults table:empName: courseName: completeDate:Jane Doe Welding 2/2/2002Jane Doe Brain Surgery 3/7/2005Alton Brown Scuba Diving 9/23/2004Alton Brown Welding 11/4/2004John Doe Brain Surgery 6/14/2003End result of select statement: Welding Brain Surgery Scuba DivingJane Doe 2/2/2002 3/7/2005 Alton Brown 11/4/2004 9/23/2004John Doe 6/14/2003 Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.
View 8 Replies
View Related
Feb 2, 2006
Is it just me, or are features missing in Management Studio? I mean, where is the "Export Objects" option when exporting data? Why is it so difficult to register a remote server? Why do the object views suck? And most important, why is the documentation the worst software documentation I have EVER SEEN?!
Things that were easy in Enterprise Manager with Client Network Utility and Query Analyzer seem impossible, difficult, or maybe just hidden in Management Studio. If it is there, the documentation sure doesnt tell you how to find it.
Please someone tell me that it's just me. Tell me that the features are there but Im not doing something right. I feel like putting my head in between my door opening and banging the door against it.
View 2 Replies
View Related
Oct 22, 2003
hello
in the database.....
table:1
DELIVERY_HEADER
INVOICE# PO#
1111 001,0002,0003
2222 005,0006,0007
in one invoice has many po# number i already get the po number individually using explode...
the ouput like this
001
002
003 and so on it is ok for that
but my problem is
i want to the po# should be selected i mean like a menu ..like the user select the which po# should be updated....
but i dont know how..
is there anyone can help me?
to elaborate more...
i can display the po# already without commas like in invoice# 1111
i can already display the po# number like this
001
002
003
after that i want it to be like a menu that user will only select the which po# should be updated..using arrow keys down and up....i mean just like selecting..
but i dont know how..
PLS HELP ME
thank
r3sann
View 1 Replies
View Related
Jan 17, 2006
Hi all, I am stuck on the case of many-to-many selection.
Now, I have 3 tables
Table: candidate_skills
candidate_id (PKey)
skill_id (PKey)
Table: skills
skill_id (PKey)
Table: job_skill_required
job_id(PKey)
skill_id(PKey)
Now, I want to select all cadidates who has the same skills of job_skill_required table.
I really don't know how to select it...
Please help, thank you.
View 5 Replies
View Related
Nov 16, 2007
SELECT Distinct a.group_code, a.group_name + '-'+ a.group_note As full_name ,
b.station_nbr, b.beg_eff_date, b.end_eff_date,
Round( e.C02_pct,3)As C02_pct, MAX( e.sample_date)As Highest_Sample_Date, d.station_name
from [HOU-TIES01].TIES_Gathering.dbo.group_detail a
Inner Join [HOU-TIES01].TIES_Gathering.dbo.station_group b on a.group_code = b.group_code
Inner Join [HOU-TIES01].TIES_Gathering.dbo.station_type c on b.station_nbr = c.station_nbr
Inner JOIN [HOU-TIES01].TIES_Gathering.dbo.station d ON c.station_nbr = d.station_nbr
Inner JOIN [HOU-TIES01].TIES_Gathering.dbo.gas_analysis e ON d.station_nbr = e.station_nbr
Where (b.station_nbr=e.station_nbr)and (b.group_code = a.group_code) and
(a.group_type = 'NPN')and( b.end_eff_date >=DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0))
and ( (c.type_code = 'WHM')or ( c.type_code = 'CRP'))and
(e.sample_date Between DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0)
and DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
and e.seq_nbr =
(select max(x.seq_nbr)
From [HOU-TIES01].TIES_Gathering.dbo.gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date )
and e.C02_pct =
(Select max(x.C02_pct)
From [HOU-TIES01].TIES_Gathering.dbo.gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date )
Group by a.group_code, a.group_name, a.group_note, b.station_nbr, b.beg_eff_date, b.end_eff_date, e.c02_pct, d.station_name
order by a.group_code
ok. Here is how the data looks.
Group
code station_nbr beg_eff_date end_eff_date C02 sample_date
0004 D01G000819 2007-09-01 3000-12-31 0.40 2007-10-02
0004 D01G000819 2007-09-01 3000-12-31 2.49 2007-10-09
What I am trying to do is to select the most current sample date. In this case "2007/10/09" when station_nbr and group_code are the same for both dates.
Thank You for all your help!!!!
From the Jr.in SQL
View 4 Replies
View Related
Nov 27, 2007
Hi Team:
This is a new query and I am facing a new problem. Here I am requesting all the records for a Contract Number 'GTH00001' for the specific date of 2/2/2007. I want the SUM(c.rcpt_nom_vol)AS Receipt and the b]SUM(c.rcpt_fuel)As Allocated_Fuel[/b]
But Instead I am getting 147 records for Contract Number'GTH00001'for the date of 2/2/2007.
Declare @Req_Contract_nbr char (8),
@Req_Day int,
@Req_month int,
@Req_year int
Set @Req_Contract_nbr = 'GTH00001'
Set @Req_Day = '2'
set @Req_month = '2'
Set @Req_year ='2007'
SELECT Distinct a.contract_nbr,
c.beg_eff_date, c.rcpt_dlvry_ind,
SUM(c.rcpt_nom_vol)AS Receipt, SUM(c.rcpt_fuel)As Allocated_Fuel
from dbo.contract a
Inner Join dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date)
and (c.rcpt_dlvry_ind ='R') and a.contract_nbr= @Req_Contract_nbr
and Day(c.Beg_eff_date) = @Req_Day
and (month(c.Beg_eff_date)= @Req_month )
Group by a.contract_nbr,c.beg_eff_date, c.rcpt_dlvry_ind, c.rcpt_nom_vol, c.rcpt_fuel
Data Example
Contract_nbr specific Date rcpt_nom_vol rcpt_fuel
GTH000012007-02-02 00:00:00.000R4.00.0
GTH000012007-02-02 00:00:00.000R8.00.0
GTH000012007-02-02 00:00:00.000R11.00.0
GTH000012007-02-02 00:00:00.000R12.01.0
GTH000012007-02-02 00:00:00.000R14.01.0
GTH000012007-02-02 00:00:00.000R15.01.0
GTH000012007-02-02 00:00:00.000R16.01.0
GTH000012007-02-02 00:00:00.000R16.02.0
GTH000012007-02-02 00:00:00.000R16.04.0
Thank you for all your help!!!!!!!!! I deeply appreciate all your help.
Osiris
The future depends of what we do in the present- Mahatma Gandhi
View 2 Replies
View Related
Mar 14, 2008
Hi
I have CheckBoxList to make my selection as follows:if (CheckBoxList2.SelectedValue == "")
{
strSelect = strSelect;
}else if (CheckBoxList2.SelectedValue == "1")if (strWhere == " where")
{strWhere = strWhere + " pic1 = 'true' ";
}
else
{strWhere = strWhere + " and pic1 = 'true' ";
}if (CheckBoxList2.SelectedValue == "2")if (strWhere == " where")
{strWhere = strWhere + " pic2 = 'true' ";
}
else
{strWhere = strWhere + " and pic2 = 'true' ";
}if (CheckBoxList2.SelectedValue == "3")if (strWhere == " where")
{strWhere = strWhere + " pic3 = 'true' ";
}
else
{strWhere = strWhere + " and pic3 = 'true' ";
}
which mean it will addor remove the fields depends upon the user selection, but it will only select one !!
Thanks in advance
View 3 Replies
View Related
May 30, 2008
Hi,
First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this.
Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later.
How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me.
Skills Table
CatID
SkillID
Skills
SL_ID
1
4
Skill 1
3
2
5
Skill 2
2
2
6
Skill 3
2
3
7
Skill 4
4
3
8
Skill 5
4
4
9
Skill 6
NULL
4
10
Skill 7
NULL
5
11
Skill 8
3
6
12
Skill 9
3
7
13
Skill 10
4
8
14
Skill 11
1
9
15
Skill 12
3
9
16
Skill 13
3
10
17
Skill 14
6
11
18
Skill 15
1
Skills Catagory
CATID
Org
Skillcat
1
Org1
Skill Category 1
2
Org2
Skill Category 2
3
Org3
Skill Category 3
4
Org4
Skill Category 4
5
Org5
Skill Category 5
6
Org6
Skill Category 6
7
Org7
Skill Category 7
8
Org8
Skill Category 8
9
Org9
Skill Category 9
Service Line Table
SL_ID
Service_line
1
Service Line 1
2
Service Line 2
3
Service Line 3
4
Service Line 4
5
Service Line 5
6
Service Line 6
7
Service Line 7
Thanks for all your help in advance.
Dash
View 1 Replies
View Related
Dec 26, 2005
Hi all techies
suppose a table contains these set of values
employeeId EffectiveDate
1 2004-12-01
2 2004-12-01
2 2005-11-15
3 2004-12-01
so
1. when i want to select data for the date ranges
(2005-12-01) to (2005-12-31), it should be
1 2004-12-01
2 2005-11-15
3 2004-12-01
2. when i want to select data for the date ranges
(2005-11-01) to (2005-11-30), it should be
1 2004-12-01
2 2004-12-01
2 2005-11-15
3 2004-12-01
3. when i want to select data for the date ranges
(2005-10-01) to (2005-10-31), it should be
1 2004-12-01
2 2004-12-01
3 2004-12-01What should I do
View 1 Replies
View Related
Dec 4, 2001
Hello Experts,
I would like to have a SQL Statement that to display all records in the same year, e.g., 2001. The date field is a mm/dd/yyyy, smalldatefield.
Could anyone advise the syntax ?
Thank you very much.
View 2 Replies
View Related
Feb 11, 2002
We are looking to purchase a new SQL2000 server.... Tada.... Right then, for around about the same money, we could have either 2 x PIII 1Ghz Xeon with 256Kb cache, or 2 x PIII 700Mhz Xeon with 1Mb cache.... Which choice is best for SQL Server 2000???
View 1 Replies
View Related
Feb 25, 2005
I hope I can explain this somewhat coherantly. Here goes..
I have a products database. I want to select product details for items having distinct style codes. IE if there are 20 products that different from one another in some way, and 3 different style numbers throughout them, I want to end up with 3 products selected. I don't care which ones, as long as i have a sample product for each style.
I've attemped to use GROUP BY to do this, but it's not giving me what I want. I managed to get right number of products, but the query seems to have mixed and matched the fields together in an undesireable way. I guess this kinda makes sense, since providing aggregate functions for each of the extra fields is necessary.
SO THE QUESTION IS: How do I select (number of distinct styles) products, and still keep the records in tact as they should be?
Here is a sample...
Code:
SELECT DISTINCT Min(team) as team, min(pict) as pict, min(colors) as colors, min(league) as league, min(product_description) as product_description, min(category) as category, min(subcategory) as subcategory, min(style) as style
FROM prods
WHERE (style='style1' or style='style2' or style='style3')
AND league='leaguex'
AND soldout=''
GROUP by style
ORDER by style
Thanks very much for any help!! I'm sure I'm going about this all wrong.
View 2 Replies
View Related
Oct 12, 2005
I want to select a date from a datetime type column.
When I use:
Select ExpireDate from myTable Where ExpireDate > 2005-10-12
...I don't get the correct dates
When I use:
Select ExpireDate from myTable Where ExpireDate > '2005-10-12'
...(with the apostrophe, it works correctly. Is there a way this will work correctly without the apostrophies?
View 1 Replies
View Related
