Grab rsPubname from different table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Grab rsPubname from different table

Author

Topic

jarv
Posting Yak Master

131 Posts

Posted - 2006-02-10 : 08:56:43

I would like to be able to show which pub I'll be drinking in at what time I can show the person and the time and date but not the name of the pub cos that is in a different table
[url]http://www.mutedesigns.co.uk/bpubs[/url]
user:test
pass:test

select a 25th Feb from left box you see there!

<%
Dim rsDate__MMColParam
rsDate__MMColParam = "1"
If (Request.QueryString("eventDate") <> "") Then 
  rsDate__MMColParam = Request.QueryString("eventDate")
End If
%>
<%
Dim rsDate
Dim rsDate_numRows

Set rsDate = Server.CreateObject("ADODB.Recordset")
rsDate.ActiveConnection = MM_pubs1_STRING
dtEventDate = request.form("eventDate1")
rsDate.Source = "SELECT p.*, o.pub_name FROM pub2 AS p LEFT JOIN pub AS o ON p.pub_id = o.pub_id WHERE p.[eventDate]=#Feb 25, 2006#"
rsDate.CursorType = 0
rsDate.CursorLocation = 2
rsDate.LockType = 1
rsDate.Open()

rsDate_numRows = 0
%>

jarv
Posting Yak Master

131 Posts

Posted - 2006-02-12 : 08:25:33

please help!!!

jarv
Posting Yak Master

131 Posts

Posted - 2006-02-12 : 08:40:32

I have joined 1 table with a query to count the number of events there actually are!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 08:41:48

Can you post the table DDL some sample data and expected result ?

----------------------------------
'KH'
everything that has a beginning has an end

jarv
Posting Yak Master

131 Posts

Posted - 2006-02-12 : 08:42:59

DDL?if you take a look, you can't really expect any kind of reult now?!

jarv
Posting Yak Master

131 Posts

Posted - 2006-02-12 : 08:43:51

it now shows: Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/bpubs/result.asp, line 21

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 08:44:00

I did take a look. Unfortunately I don't quite understand what you want.

----------------------------------
'KH'
everything that has a beginning has an end

jarv
Posting Yak Master

131 Posts

Posted - 2006-02-12 : 09:11:08

ok, if you log in and see now, select 25th Feb 2006 and click find you will see that there are some events but I can't add where the event is for that day cos the list of pubs is in the other table?!
code at the moment is:

rsDate.Source = "SELECT * FROM pub2"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 09:20:35

this is what i see

quote:
jarv is going to to

to drink

Saturday, February 25, 2006
at: 00:00

what is the table name for the list of pubs ? use inner join it with the pub2.

----------------------------------
'KH'
everything that has a beginning has an end

jarv
Posting Yak Master

131 Posts

Posted - 2006-02-12 : 09:33:31

sorry I am a novice! Inner join? the table is pub1 do you know the SQL for this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 09:37:31

if pubid is the pk for both table

SELECT * 
FROM   pub2 p2 inner join pub1 p1
       on p2.pubid = p1.pubid

----------------------------------
'KH'
everything that has a beginning has an end

jarv
Posting Yak Master

131 Posts

Posted - 2006-02-12 : 09:42:04

thanks but now I get:

Unterminated string constant

/bpubs/result.asp, line 9

rsDate.Source = "SELECT *
-------------------------^

jarv
Posting Yak Master

131 Posts

Posted - 2006-02-12 : 09:50:56

ok sorted that bit, just all needed to be on 1 line?!
now I get:
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/bpubs/result.asp, line 13

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 01:41:11

The column name may be wrong or you didnt supply values to the columns

Madhivanan

Failing to plan is Planning to fail

jarv
Posting Yak Master

131 Posts

Posted - 2006-02-13 : 14:19:19

result.asp how it is at the moment (working but not showing rsPubname from other table)

<%
Dim rsResult
Dim rsResult_numRows

Set rsResult = Server.CreateObject("ADODB.Recordset")
rsResult.ActiveConnection = MM_pubs1_STRING
dtEventDate = request.form("eventDate1")
if isDate(dtEventDate) then
  dtEventDate = cDate(dtEventDate)
else
  dtEventDate = date()  '  If not valid look for today
end if

rsResult.Source = "SELECT *  FROM pub2  WHERE [eventDate]=#" & dtEventDate & "#"
rsResult.CursorType = 0
rsResult.CursorLocation = 2
rsResult.LockType = 1
rsResult.Open()

rsResult_numRows = 0
%>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-13 : 14:24:45

Are you using MS Access or SQL?

Either way, you should be writing the SQL statements and working on that first, and then incorporating that into your application code. Never debug and troubleshoot your SQL using your application. If it is in Access, open up MS Access and work on your SELECT; it it is SQL Server, use Query Analyzer. Only when you are confident with your select, and how an INNER JOIN works, should you be then attempting to use it in your code.

jarv
Posting Yak Master

131 Posts

Posted - 2006-02-13 : 14:40:09

i am using Access, if i open up my database, do i join the 2 tables? if so how? and what do i do?! I only really need rsPubname from pub1 table there is also a query or 2 that have been joined I am using a datbase made from the UEForum from www.webthang.co.uk

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-13 : 15:47:34

This is a Microsoft SQL Server forum.

You should ask your questions in the MS Access forum, and also check out a decent book on MS Access and/or beginning SQL. I am not sure how you expect to write an application that queries a database if you are not yet familiar with the basics; it will pay off if you shut down your appplication, fire up MS Access alone, read some tutorials, and figure out how it works before you start trying to write applications using it.

jarv
Posting Yak Master

131 Posts

Posted - 2006-02-14 : 01:59:23

thanks will do that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources