need SQL Query Help

I need help with a query,
i have two tables,
name Qty orderID Store
Paper 1000 101 New York
Paper 2000 101 Chicago
Pen 2000 102 New York
Pen 5000 102 Chicago
table two
Purchase
orderID Qty price Date
101 100 $4 7/1/05
101 200 $5 7/15/05
101 360 $3.6 8/5/05
101 150 $5.2 8/30/05
102 400 $6 7/2/05
102 300 $6.5 7/12/05
102 500 $5 8/3/05
for the result, I only want to know the last purchase on each like:
Paper 150 $ 5.2 8/30/05
Pen 500 $ 5. 8/3/05
What should I do in this case? Thanks a lot for your help.
MichaelThe following example may get you going. It does not handle time,
change the convert code accordingly.
Since both tables have multiple equivalent orderids, a composite where
will need to be built to ensure uniqueness.
select t1.name, t2.qty, t2.price, t2.date
from table2 t2
inner join table1 t1 on t1.orderid = t2.orderid
where str(t2.orderid) + convert(varchar, t2.date, 112) in
(
select top 1 str(t2.orderid) + convert(varchar, t2.date, 112)
from table2 t2
order by date desc
)
mli wrote:
> I need help with a query,
> i have two tables,
> name Qty orderID Store
> Paper 1000 101 New York
> Paper 2000 101 Chicago
> Pen 2000 102 New York
> Pen 5000 102 Chicago
> table two
> Purchase
> orderID Qty price Date
> 101 100 $4 7/1/05
> 101 200 $5 7/15/05
> 101 360 $3.6 8/5/05
> 101 150 $5.2 8/30/05
> 102 400 $6 7/2/05
> 102 300 $6.5 7/12/05
> 102 500 $5 8/3/05
> for the result, I only want to know the last purchase on each like:
> Paper 150 $ 5.2 8/30/05
> Pen 500 $ 5. 8/3/05
> What should I do in this case? Thanks a lot for your help.
> Michael

Need some help with MSDE 2000

I have to create an application using MSDE, I install the software on my pc
and it is working fine but my problem is that I have to store the database
on the J drive of my LAN and must be able to access if from my local PC or
any other pc connected to the Lan. When I put the database on my pc I can
access the the tables, but when I put the database on the J drive of the LAN
the MSDE client on my PC does not find the databases. Is there a way to tell
the MSDE client on my PC to go to the J drive on the lan and find the
databases? The data must be store on the J drive of the Lan. I would really
appreciate some help here as I am very new to MSDE. In ms access I just had
to set the path to the database and that was it.
thanks in advance.
On Sat, 12 Jun 2004 01:37:34 -0400, Patrick Blackman
<caribsoft@.videotron.ca> wrote:

> the MSDE client on my PC does not find the databases. Is there a way to
> tell the MSDE client on my PC to go to the J drive on the lan and find
> the
> databases?
You should install MSDE on the machine where it runs, not on another PC
into a mapped drive. That's just what my feeling tells me. There might ne
network installation guides on msdn.microsoft.com.
Furthermore, if you need to access MSDE over the net, make sure
authentication (hint: SECURITYMODE=SQL, for instance) is set up properly
as well as "firewalls" are disabled or at least let the appropriate ports
through (I can't recall the MSDE listening port).
HTH,
Stefan
Give a man fire and he is warm for a day.
Set him on fire and he is warm for the rest of his life.

Need some help about DTS

I have create a DTS package, This job is fist connect to database, and
then excute a SQL query, then use the VB ActiveX to create a procedure
to store the sql result to a text file. I ran the package there is no
porblem with query.But the second step for output the result to text
file have problem. The test fiel become 200mb big and cannot open. I
would like to know if there something wrong with my programming.
'***************************************
*******************************
' Visual Basic ActiveX Script
'***************************************
*********************************
Function Main()
On Error Resume Next
Dim strRecord
Dim strEmailBody
Dim objFSO
Dim objStream
Dim objResults
Const OUTPUT_FILE = "c:\CompanyList.txt"
Const fsoForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objResults = DTSGlobalVariables("gResults").Value
Set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, True)
'Loop through the records and output each one
'to a file.
While Not objResults.EOF
strRecord = "Relatiecode #" & objResults.Fields(0).Value & _
"JA/NEE" & objResults.Fields(1).Value
objStream.WriteLine (strRecord)
objStream.WriteBlankLines (1)
objResults.MoveNext
Wend
If Err.Count = 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End FunctionHi
"superbaby@.gmail.com" wrote:

> I have create a DTS package, This job is fist connect to database, and
> then excute a SQL query, then use the VB ActiveX to create a procedure
> to store the sql result to a text file. I ran the package there is no
> porblem with query.But the second step for output the result to text
> file have problem. The test fiel become 200mb big and cannot open. I
> would like to know if there something wrong with my programming.
> '***************************************
*******************************
> ' Visual Basic ActiveX Script
> '***************************************
*********************************
> Function Main()
> On Error Resume Next
> Dim strRecord
> Dim strEmailBody
> Dim objFSO
> Dim objStream
> Dim objResults
> Const OUTPUT_FILE = "c:\CompanyList.txt"
> Const fsoForWriting = 2
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> Set objResults = DTSGlobalVariables("gResults").Value
> Set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, True)
> 'Loop through the records and output each one
> 'to a file.
> While Not objResults.EOF
> strRecord = "Relatiecode #" & objResults.Fields(0).Value & _
> "JA/NEE" & objResults.Fields(1).Value
> objStream.WriteLine (strRecord)
> objStream.WriteBlankLines (1)
> objResults.MoveNext
> Wend
>
> If Err.Count = 0 Then
> Main = DTSTaskExecResult_Success
> Else
> Main = DTSTaskExecResult_Failure
> End If
> End Function
>
You don't seem to be closing the file and setting your cleaning up the
objects by setting them to nothing for examples see
http://www.sqldts.com/303.aspx
I am not sure if it will not solve the problem though!!
John

Need some feedback. What do you think about storing my data like

Hi,
I need to store a hierarchy in my database (a tree)
I have 3 types of data: Cases, Groups and Users. I can have any
combination of them.
Check the following link ('Groups', 'Cases', 'Users' and 'Tree' will be
tables in my database):
http://www.lemforever.com/temp/tree.jpg
What do you guys think? Do you think is a good solution? I still want to
have a relational database
and I don't want to violate any integrity rules.
Thanks a lot.I just realized that I could have used my previous post for asking that.
Sorry about that.
If you have any feedback please use either one.
Thanks

need helps in this store procedure?

Hi Everyone,

I appreciate if you can help on the following procedure. When I ran it in my project in Visual Studio, the compiler always complained that it expected a parameter "@.stud_id" that was not provided. What was wrong with this procedure?

Thank you for your help.

a123.

ALTER Procedure registerstudent
(
@.email nvarchar(100),
@.student_password nvarchar(10),
@.first_name nvarchar(25),
@.last_name nvarchar(25),
@.address nvarchar(255),
@.city nvarchar(50),
@.province nvarchar(25),
@.country nvarchar(50),
@.phone_no_cell nvarchar(25),
@.phone_no_home nvarchar(25),
@.hour_rate int,
@.status char(1),
@.stud_id int output

)
As

INSERT INTO student
(
email,
student_password,
first_name,
last_name,
address,
city,
province,
country,
phone_no_cell,
phone_no_home,
hour_rate,
status

)
VALUES
(

@.email,
@.student_password,
@.first_name,
@.last_name,
@.address,
@.city,
@.province,
@.country,
@.phone_no_cell,
@.phone_no_home,
@.hour_rate,
@.status

)

SELECT
@.stud_id = @.@.identity

Did you add an output SqlParameter to your code?

Ryan

|||

Need to know how you execute the sp in your code BTW, usually we use SCOPE_IDENTITY() instead of @.@.identity. For the differences, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp

need help: new at store procedure

HI,
I want to create Store Procedure that Do:
I have 2 tables (A,B) and i want to know if theres records equal
between A.id1 in B.id2 or in B.id3
I want to call this Store Procedure from Asp (need to know how)
I work with SQL Server.
Regardsmoshe wrote:
> HI,
> I want to create Store Procedure that Do:
> I have 2 tables (A,B) and i want to know if theres records equal
> between A.id1 in B.id2 or in B.id3
> I want to call this Store Procedure from Asp (need to know how)
> I work with SQL Server.
> Regards
Create a SQL login and add the user to your database. Let's assume you call
the login "asplogin" with a password of "nigolpsa"
In Query Analyzer, run this script:
Create PROCEDURE CompareData AS
SET NOCOUNT ON
IF EXISTS (SELECT * FROM A INNER JOIN B ON A.id1=B.id2) OR
EXISTS (SELECT * FROM A INNER JOIN B ON A.id1=B.id3)
RETURN 1
ELSE
RETURN 2
go
GRANT EXECUTE ON CompareData TO asplogin
go
In ASP (I'm assuming you mean classic ASP since you did not specify
ASP.Net), follow the procedure described here to add the ADO type library to
your application's global.asa file :
http://www.aspfaq.com/show.asp?id=2112
Then create a page with this code (untested - there may be typos or syntax
errors. This should give you the idea. Look up the correct syntax in the ADO
reference at
http://msdn.microsoft.com/library/e...pireference.asp)
:
<%
dim cn, cmd
set cn=createobject("adodb.connection")
set cmd=createobject("adodb.command")
With cmd
.CommandType=adCmdStoredProc
.CommandText="CompareData"
set .ActiveConnection = cn
.Parameters.Append .CreateParameter("RETURN_VALUE", _
adInteger,adParamReturnValue)
end with
cn.Open "Provider=SQLOLEDB; " & _
"Data Source=YourServerName; " & _
"Initial Catalog=YourDatabaseName;" & _
"User ID=asplogin;Password=nigolpsa"
cmd.Execute
If cmd.Parameters(0) = 1 Then
Response.Write "Matching records exist"
Else
Response.Write "No matching records exist"
End if
cn.close: set cn=nothing
%>
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Need help, error with store proc

I am trying to do an insert with a SPand I get the following error: "The name 'A2LA Website' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."
When iParentID=30 and strTexte="A2LA Website"

What an I doing wrong?

Table:
ID int(identity)
ParentID int
Text nvarchar(50)
Valeur nvarchar(50) Allow Null
Ordre int Allow Nulls

 Public Sub addItemToDdSelection(ByVal strTable As String, ByVal iParentID As Int16, ByVal strTexte As String)
 Dim connect As New SqlConnection(strConnection)
 Dim cmdSelect As New SqlCommand("AddDropDownContent", connect)
 Dim paramReturnValue As SqlParameter
 Dim strError As String
 cmdSelect.CommandType = CommandType.StoredProcedure
 'PARAM
 cmdSelect.Parameters.Add("@.intParentID", iParentID)
 cmdSelect.Parameters.Add("@.strTexte", strTexte)
 Try
 connect.Open()
 cmdSelect.ExecuteNonQuery()
 connect.Close()
 Catch ex As Exception
 strError = ex.Message
 Finally
 If connect.State = ConnectionState.Open Then
 connect.Close()
 End If
 End Try
 End Sub
ALTER PROCEDURE dbo.AddDropDownContent
(
@.intParentID int,
@.strTexte varchar(50)
)
AS
EXEC ('INSERT INTO DropDownMenus (ParentID, Texte) VALUES(' + @.intParentID + ',"' + @.strTexte + '")')

(1) when you add parameters, specify their type/lengths too
xample :

cmdSelect.Parameters.Add("@.intParentID", iParentID)

should be

cmdSelect.Parameters.Add(New SqlParameter("@.intParentID",SqlDbType.int))
cmdSelect.Parameters("@.intParentID").Value = Trim(iParentID)

and
(2) you dont need dynamic sql to insert. you can modify your insert stmt as

Create Procedure ...
AS
SET NOCOUNT ON
 INSERT INTO DropDownMenus (ParentID, Texte) VALUES ( @.intParentID ,@.strTexte )
SET NOCOUNT OFF

hth|||Thanks for your reply this is really helping. A few questions,
-Trim(iParentID) is used in case the id is enter from a text box I guess?
-What is the role of the line "SET NOCOUNT ON" and "SET NOCOUNT OFF"?|||>>-Trim(iParentID) is used in case the id is enter from a text box I guess?

yes. you might also want to do a

Convert.ToInt16(Trim(iParentId))

to make sure you are sending in an integer type value. Its a good idea to validate user input.

>>What is the role of the line "SET NOCOUNT ON" and "SET NOCOUNT OFF"?

read up BOL (Books On Line)

hth