|
This is my multi word search SQL, but I also need to find the selling price of each item according to what market ID they have and then lookup their price for that market id . The first SELECT finds the Market ID which is then used by the 2nd SELECT to find the market_price.
Can these 2 Select statements be placed within the large SQL in order to find the market price? If so, can someone tell me how to do it?
strSQL = "Select Market_ID from CUSTOMER where ID = @CustID"
strSQL = "Select * from MARKET_PRICE where PART_ID = @PartID and MARKET_ID = @MarketID"
strSQL = "SELECT DISTINCT dbo.PART.ID as ID,
'strSQL = "SELECT dbo.PART.ID as ID,
strSQL = strSQL & "dbo.PART.DESCRIPTION, "
strSQL = strSQL & "dbo.Part.PRODUCT_CODE, "
strSQL = strSQL & "UNIT_PRICE AS Price, "
strSQL = strSQL & "dbo.PART.COMMODITY_CODE, "
''strSQL = strSQL & "dbo.part_model.model_number, "
strSQL = strSQL & "dbo.PART.STOCK_UM "
''strSQL = strSQL & "dbo.PART_CROSSREF.CrossrefID "
strSQL = strSQL & "FROM dbo.PART "
strSQL = strSQL & "INNER JOIN "
strSQL = strSQL & "dbo.PART_CROSSREF ON dbo.PART.ID = dbo.PART_CROSSREF.ID "
strSQL = strSQL & "INNER Join "
strSQL = strSQL & "dbo.PART_MODEL ON dbo.PART.PRODUCT_CODE = dbo.PART_MODEL.PRODUCT_CODE "
strSQL = strSQL & " WHERE ((PART.ID like '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
For CountLoopsForSQL = 0 To UBound(KeywordsForSearch)
strKeyWord = KeywordsForSearch(CountLoopsForSQL)
If strKeyWord <> "" Then
' Search for the words in the "ID" field
strSQL = strSQL & " OR (dbo.PART_CROSSREF.crossrefID LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
strSQL = strSQL & " OR (dbo.PART_CROSSREF.ID LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
' Search for the words in the "Product_code" field
strSQL = strSQL & " OR (dbo.Part.Product_code LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
' Search for the words in the "Product_code" field
strSQL = strSQL & " OR (dbo.Part.description LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
' Search for the words in the "Commodity_code" field
strSQL = strSQL & " OR (dbo.Part.Commodity_code LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
End If
Next
strSQL = strSQL & " and (UNIT_PRICE is not null) and (Part.User_9 = 'Y'))"
Thanks
|
|
|
|
|
Select * from MARKET_PRICE where PART_ID = @PartID and MARKET_ID =(Select Market_ID from CUSTOMER where ID = @CustID) ????
|
|
|
|
|
I appreciate the feedback but I had never used a Select within a Select and so not only did I not know what syntax was acceptable, I also didn't know where I could put the Selects. I have since found that I can place them almost anywhere I want.
However, your example did show me a way to use a Select in a way I didn't know it could be done. That will be useful info for the future.
|
|
|
|
|
Morning all you hungover ghouls, goblins and ghosts.
Does anybody know what SQL Server 2005 Developer Edition includes to justify its 2.8GB download? The other editions, Enterprise included, are a good deal smaller. I know the licensing terms of Developer but am wondering what extra packages come inside it.
regards,
Paul Watson
South Africa
Colib and ilikecameras.
K(arl) wrote:
oh, and BTW, CHRISTIAN ISN'T A PARADOX, HE IS A TASMANIAN!
|
|
|
|
|
Paul Watson wrote: Does anybody know what SQL Server 2005 Developer Edition includes to justify its 2.8GB download?
Dunno... It is still downloading. 
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
The disc contains the x86, x64 and Itanium versions of the software. If you sum the three DVD images for the separate Enterprise Edition DVDs they're larger than the single Developer Edition DVD.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I have a store procedure WriteToDatabase with 2 input parameters inVal1 (type int) and strVal2 (type varchar(80)).
How do I call this store procedure in VC++ 6.0 code???
Please help! Thanks a lot for your time.
|
|
|
|
|
Hi,
i need help in using ADO 2.5 objects with VB 6.0 (i know there're a lot newer version but i need to use this one...)
i'm not an expert in DB programming but i almost go crazy, maybe i'm just too stupid...
1st)
why does Recordset.RecordCount always return -1, even if it DOES contain several records?
i read somewhere that it's neccessary to first call Recordset.MoveLast and set Recordset.CursorLocation
to adUseClient, but this makes no difference at all
2nd)
<br />
Dim uDB as Connection<br />
Dim rs as Recordset<br />
<br />
Set uDB = new Connection<br />
Set rs = new Recordset<br />
<br />
uDB.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source = " & db_directory<br />
If uDB.state <> adStateOpen Then<br />
MsgBox "Opening database failed."<br />
Exit Sub<br />
End If<br />
<br />
For i = 0 to X<br />
rs.Open "SELECT * FROM " & TableName & " WHERE ID = " & CStr(id), uDB, adOpenDynamic, adLockOptimistic<br />
<br />
'update some fields<br />
rs.Update<br />
rs.Close<br />
Next i<br />
this works well for the first loop pass, but raises an "sql syntax error in select statement" up from the second pass. why?
3rd)
<br />
On Error Goto ErrHandler<br />
rs.Filter = "ID = " & CStr(id)<br />
immediately exits my sub without jumping to the error handler, but
<br />
On Error Resume Next<br />
rs.Filter = "ID = " & CStr(id)<br />
works fine and also selects the correct records. why ?
4th)
<br />
rs.Open "SELECT * FROM " & TableName, uDB, adOpenDynamic, adLockBatchOptimistic<br />
For i = 0 To X<br />
rs.AddNew<br />
'update new field<br />
rs.Update 'also tried without calling Update...<br />
Next i<br />
rs.UpdateBatch<br />
shows now error but simply does nothing  ... why?
(while opening the recordset with adLockOptimistic and only calling Update works fine)
5th) are there any restrictions in general on adding / altering data with recordsets that were
opened with a select statement?
6th)
<br />
For i = 0 To X<br />
'do something<br />
sqlCmd = "UPDATE " & TableName & " SET field1 = '" & strField1 & "', field2 = '" & strField2 & "' WHERE ID = " & CStr(id) & " AND LANGUAGE_ID = " & CStr(langId)<br />
uDB.Execute sqlCmd<br />
Next i<br />
this works also fine in the first pass but returns a "sql syntax error in update statement" up from the second pass...!??
thx a lot for any answers.
daniel.
|
|
|
|
|
I cant answer all your questions but i can give you a few ideas.
1. Yes this is correct. ADO recordcount is not like it was in the older DAO. It basically returns the recordcount to the current cursor location so yes you need to call recordset.movelast before you can get a count. It should work for you like this.
Just as a side issue which may help i prefer to set all the recordset properties before opening the recordset. Its more readable eg
set rs = new recordset
rs.cursorlocation = adUseCLient
rs.cursortype = adOpenDynamic
rs.locktype = adLockOptimistic
rs.open sql,connection
2 & 6. try to look at it logically. Define a string variable to hold the SQL eg
dim sSQL as string
sSQL = "SELECT * FROM " & TableName & " WHERE ID = " & CStr(id)
rs.open ssql,connection
you can then set a breakpoint and see what the sql actually contains. Also i would probably set the recordset to a new recordset then set it to nothing within the loop since you are actually creating a new recordset.
3. Do you have any code in the errorhandler to report the Error? What is the error? It could be more of a structure problem if it works without the error handling.
4. OK. You seem to be a bit confused about the use of batch uptates etc.
I am pretty sure you need a client side cursor to use the batch updates. If you use updatebatch you need to use adlockbatchoptimistic as the lock type. I suggest you find some examples on the web but in general the flow should be.
set rs = new recordset
rs.cursorlocation = adUseCLient
rs.cursortype = adOpenDynamic
rs.locktype = adLockBatchOptimistic
rs.open sql,connection
for i = 0 to 9
rs.add
rs.fields(0) = Value
rs.fields(1) = value
next
rs.marshaloptions = admarshallall ' or admarshallmodifiedonly
rs.updatebatch
5. None specific to using SQL unless you have joins
Its been a while since i used ADO but i hope i have given you some ideas.
Jon
|
|
|
|
|
Hi,
i am new to SQL-Server and Web Technology. i have just joined a company and its my first task to do. So, Please Help me immediately.
I have to send a Http Post Request to a server with 4 parameters from a Stored Procedure. I got a Stored procedure on google which use Http post method from Stored Procedure. but i dont know how to send Parameters alongwith it on server and how to get back the status message(e.g. error message if failed).
the stored procedure is:
****************************************************************************
CREATE procedure HTTP_POST( @sUrl varchar(200), @response varchar(8000)
out)
As
Declare
@obj int
,@hr int
,@status int
,@msg varchar(255)
exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @obj OUT
if @hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
failed', 16,1) return end
exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end
exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL,
'Content-Type', 'application/x-www-form-urlencoded'
if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed'
goto eh end
exec @hr = sp_OAMethod @obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end
exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
if @hr <>0 begin set @msg = 'sp_OAMethod read status failed' goto
eh
end
if @status <> 200 begin set @msg = 'sp_OAMethod http status ' +
str(@status) goto eh end
exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
if @hr <>0 begin set @msg = 'sp_OAMethod read response failed' goto
eh end
exec @hr = sp_OADestroy @obj
return
eh:
exec @hr = sp_OADestroy @obj
Raiserror(@msg, 16, 1)
return
GO
****************************************************************************
the parameters which i have to send are:
variable name ----- Parameter value
------------------------------------------------------------
name -> any character String
msisdn -> any numeric string (Like Mobile No.)
toAddress -> any numeric string (Like Mobile No.)
message -> any character string (Like SMS Message)
-------------------------------------------------------------
Actually i have to send these information on a server which will send SMS to any mobile. so, plz can any one help me.....
Thanx in Advance....
-- modified at 13:25 Monday 31st October, 2005
|
|
|
|
|
I wish it was a joke, but our DBA passed away. Till such time we find a new DBA, I am the man. I have this script:
exec sp_droprole 'SAM_USER'
exec sp_addrole 'SAM_USER'
exec sp_droprole 'Manage_Presentation'
exec sp_addrole 'Manage_Presentation'
grant select , insert,delete , update on Presentation to Manage_Presentation
grant select , insert,delete , update on Presentation_Menu_Link to Manage_Presentation
grant select , insert,delete , update on PresentationOwner to Manage_Presentation
grant select , insert,delete , update on PresentationSlide to Manage_Presentation
grant select , insert,delete , update on PresentationSlideOverride to Manage_Presentation
grant select , insert,delete , update on PresentationSlideVerticalMenu to Manage_Presentation
exec sp_addrole 'Manage_Slides'
grant select , delete , update on dbo.Slide to Manage_Slides
grant select , delete , update on dbo.Slide_Menu_Link to Manage_Slides
grant select , delete , update on dbo.SlideComment to Manage_Slides
grant select , delete , update on dbo.SlideFile to Manage_Slides
grant select , delete , update on dbo.SlideType to Manage_Slides
grant select , delete , update on dbo.SYSFile to Manage_Slides
exec sp_addrolemember 'SAM_USER','Manage_Presentation'
exec sp_addrolemember 'SAM_USER','Manage_Slides'
--create user section of the database
exec sp_addlogin @loginame = 'Sameer',@passwd = 'Sameer',@defdb = 'SAM_DEV'
exec sp_grantdbaccess 'SameerHanda'
exec sp_addrolemember 'SAM_USER','SameerHanda'
Well all well and good, but when I signon as Sameer and do the following
select * from Presentation, I ge this error:
SELECT permission denied on object 'Presentation', database 'SAM_DEV', owner 'dbo'.
Well what am I missing. Any help would be greatly appreciated
Thanks
Sameer
PS: RIP Kev Dawg 
|
|
|
|
|
You've added the Manage_Presentation and Manage_Slides roles to the SAM_USER role. I think you intended it the other way around. You've also specified a different username in the sp_grantdbaccess and sp_addrolemember calls than you did in the sp_addlogin call.
I think you simply need to change
exec sp_addrolemember 'SAM_USER','Manage_Presentation'
exec sp_addrolemember 'SAM_USER','Manage_Slides' to
exec sp_addrolemember 'Manage_Presentation','SAM_USER'
exec sp_addrolemember 'Manage_Slides','SAM_USER'
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
 Cool thanks it seems to have worked, I am a c# guy being forced to act as a DBA for the time being. Good experience none the less.
|
|
|
|
|
If your DBA died (sorry to hear that) and you are the acting DBA, I would be more than a little concerned about what happens if your DB dies too.
Murphy's Law would suggest that since your DBA died, you are at much higher risk than normal that your SQL Server is going to crash sometime in the near future too.
If it does and you are not prepared for it, you could be in for a lot of nasty suprises.
If I were in your position, I would run full backups on all the databases and especially the master database immediately.
And if you don't know a lot about what you are doing, until you get a new DBA I would advise not worrying so much about the media cost and keep making regular full backups of any databases in active use on a pretty regular basis.
Your DBA probably has some kind of transactional backup system implemented. If you don't have his expertise, and your server dies tomorrow, you could really be in a jam.
|
|
|
|
|
It's a good thing that I am familair with Oracle DBA stuff. Then again DB's are DB's its just a different way of doing things.
I have already made backups of our MDF file, the transaction logs. I am not sure where the redo logs of Sql server are. Working on figuring that out.
|
|
|
|
|
Hello everybody,
I wanted to transfer XML data to SQL Server using MS Sql Server DTS. But there is no connection object for XML data in MS Sql Server 2000. So without using DOM object how can i transfer XML data to MS Sql Server ?
|
|
|
|
|
Hi,
I'm currently developing a forge where all the receipts is stored in a mySQL database. The players suppose to insert their items within the forge, and activate it.
When the forge activates, the script goes through each item located within, and get the unique id for each item. An example of a result would be:
item1 item4 items5 item8
I want to check if the field 'components' contains these id's. If so, the row should be returned.
I've tried the following statement, but will it work if the order were differnt?
SELECT FROM `recept` WHERE `komponenter` LIKE 'item1%item4%item5%item8'
|
|
|
|
|
it will work... notice that a select don't know the order of the records in a table... if you want to sort the, use after your where statement the ORDER BY keywords...
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
Thank you for your reply!
So if komponenter contains the ID's arranged in the following order:
item1 item5 item3 item2 item4
... a SQL statement like this will find the row?
SELECT `...` FROM `recept` WHERE `komponenter` LIKE '%item5%item4%item3%item2%item1%'?
The reason I'm asking instead of just trying, is the fact that each time I want to try something out, I have to update the server via a console-interface - after I've uploaded the 25 meg new version of the game.
|
|
|
|
|
nop, you don't have it.
SELECT komponenter
FROM recept
WHERE komponenter LIKE 'item%'
ORDER BY komponenter ASC;
this will return :
item1<br />
item2<br />
item3<br />
item4<br />
item5
well, tell me, i don't know what database you're using in your SW, but you might have tool to test SQL statements before updating your whole code and test it each time you change your query...
(Entreprise Manager with SQL Server 2000, SQL*Plus with Oracle DB...)
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
You may also want to consider the IN Syntax if you have the full id:
SELECT * FROM MyTable WHERE MyField IN('Value1','Value2','Value3')
|
|
|
|
|
OK i've used just Microsoft Access database for my application made in c#.
Now i want to know how can use mySQL database.
My questions are the following:
1)How can create a new mysql database within my c# application? (using just sintax with .NET objects or using VisualStudio tools)
2)What i need installed on my (for developing) computer?
3)What is needed installed on the final user computer?
4)I know how create and manageing database in mySQL(using the daemon) so i want to know what are the differences with mySQL and SQL.
Thanks.
|
|
|
|
|
Sasuko,
You may want to visit MySQl's site and look at their .net connector in the tools section.
You may just need to distribute the .dll file with your application.
I hope this is a start for you 
Paul
|
|
|
|
|
so the final user doesn't need the mysql server installed on its pc?
And where can i find the database (where is the file?) i've created before and i want to distribuite with my application?
What directory have i to put the database in during the installation?
|
|
|
|
|
The user should not need to have a mySQL server installed on hir or her machine. Your application should point to the server in the connection string. You'll need to dig around with your mySQL server installation for the database file. If there is an existing database server, you should not have to install a database file anywheres.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
