hi i m having problem


i m using MSDE i m trying to use session object to
work with it. i dont know how to do it
can someone help.....here is what i have written:

ALTER PROCEDURE reportjobposted

(

@emuser varchar (20)

)

AS

SELECT Jobs.jid, Jobs.joposted, Jobs.jocutoff, Jobs.jotitle, Jobs.jostatus, count(Applications.causer) as "# of Applicants"

FROM Jobs, Applications

WHERE Jobs.jid=Applications.jid and @emuser="Session("EmployerName")"

group by jobs.jid, jobs.joposted, jobs.jocutoff, jobs.jotitle, jobs.jostatus

order by Jobs.jid


i have passed the value in the "EmployerName" in the session from a text box in another page.
i m trying to diplay in a data grid all the above fields where table jobs and table applications jid is equal and
the emuser field in Jobs table = to what the session holds.
Please help me
Thank you
Shams






Posted On: Tuesday 20th of November 2012 03:01:01 AM Total Views:  322
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




help with variable problem in IF statement?

hi, i'm completely stumped with this one. It is a simple test (via IF) for booking where it decides which path to follow. Or not so simple, even.. It keeps taking me to same bit. Have tried putting form value into variable. Tried rearranging various ways. The available is definatley being calculated properly and have it displayed. What can be going wrong with form (just added 'id' there too) Is there something missing or do i have to convert to integer format or something The two "booked" and "places" fields are long integers, no indexing, not autoincrements or anything. Have tried for ages with this and is holding me back from the rest of code and final few pages of my site...which is VERY frustrating.
VIEWS ON THIS POST

144

Posted on:

Tuesday 20th November 2012
View Replies!

date problem

... here is my code Set con = server.creteobject ("ADODB.Connection") con.Open "mydb" resultupdate = "INSERT INTO db (date) VALUES (#"&now()&"#)" con.Execute resultupdate But it give error -- Syntax error in INSERT INTO statement. any idea\t thx
VIEWS ON THIS POST

111

Posted on:

Tuesday 20th November 2012
View Replies!

SELECT string truncation problem!

Hi Pals, Need some help. I have big sql which is more than 4000 characters. Basically the sql is formed by UNION operator. For that reason i have declared the datatype as VARCHAR(MAX). and then tried to assign the value to a variable. and when i am trying to print the SQL using PRINT / SELECT , the sql is getting truncated. What i have done is instead of printing i have used SELECT LEN('SELECT ..........'). Then i am getting output as 8000 chars which can easily be accomodated by VARCHAR(MAX) datatype (i.e 2^31 chars.) Why is the sql is getting truncated What could be the problem How could we resolve this problem Do we need to take 2 string variables and do that or else do we have any other solution for this. Basically i am inserting the result fetched by the big SELECT stmt inside my stored procedure. Any Thoughts Any suggestions will be greatly appreciated.
VIEWS ON THIS POST

160

Posted on:

Tuesday 20th November 2012
View Replies!

Must be able to reduce this code, just not sure of SQL's full potential yet

this works but, there must be a better way of doing it. Basically if iPid does not have a value then do as planned and use iPage. Otherwise use iPid, in effect it is if a querystring exists using ASP. But as you can see I am having to duplicate all of the code again, any ideas on reducing this Code: CREATE PROCEDURE spGetPage @iPage int, @iPid int AS IF @iPid = "" SELECT tblPages.pageID, tblPages.pageMetaID, tblPages.pageTemplateID, tblMeta.metaDescription, tblMeta.metaKeywords, tblTemplate.templateTitle, tblTemplate.templatePath, tblPages.pageH1, tblPages.pageH2, tblPages.pageContent, tblPages.pageSecure, tblPages.pageActive FROM tblPages INNER JOIN tblMeta ON tblPages.pageMetaID = tblMeta.metaID INNER JOIN tblTemplate ON tblPages.pageTemplateID = tblTemplate.templateID WHERE (tblPages.pageSecure = 0) AND (tblPages.pageActive = 1) AND (tblPages.pageID = @iPage) ELSE SELECT tblPages.pageID, tblPages.pageMetaID, tblPages.pageTemplateID, tblMeta.metaDescription, tblMeta.metaKeywords, tblTemplate.templateTitle, tblTemplate.templatePath, tblPages.pageH1, tblPages.pageH2, tblPages.pageContent, tblPages.pageSecure, tblPages.pageActive FROM tblPages INNER JOIN tblMeta ON tblPages.pageMetaID = tblMeta.metaID INNER JOIN tblTemplate ON tblPages.pageTemplateID = tblTemplate.templateID WHERE (tblPages.pageSecure = 0) AND (tblPages.pageActive = 1) AND (tblPages.pageID = @iPid)
VIEWS ON THIS POST

123

Posted on:

Tuesday 20th November 2012
View Replies!

Sql 2000 relationship diagram

hi, I have a sql2000 database on a server and I'm trying to see the relationship between the tables to understand how it works. In the "diagrams" section, I don't see anything. do I run the Diagram Wizard to get that kind of info I thought that when the database was designed, a diagram was created automatically.
VIEWS ON THIS POST

96

Posted on:

Tuesday 20th November 2012
View Replies!

Ideas on creating ARchive Table

I need some ideas on how to archive some of the data that I am capturing in a table. I have this table that has the following columns. KPI ID, KPiNAME, KPITYPE, WEEK1, WEEK2, WEEK3, WEEK4. My table displays last four weeks of data. What I want to do is to create a sub page for this main page where users can go and produce archive reports. Any ideas on how I can archive them to display the report.
VIEWS ON THIS POST

193

Posted on:

Tuesday 20th November 2012
View Replies!

How to solve sudden replication problem...

We're running a replicationprocess for months now, and 2 days ago it broke down, for no appearant reason I can detect. The setup is this: A SQL2K server has a publication defined on a database, consisting of several dozen tables. It is a transactional publication, running continuously. There is 1 subscriber, a SQL2005. Both servers run with Win2003 and all have the latest servicepacks. Up until 2 days ago there was hardly a problem. Then I received errormessages on being unable to load into a specific table. That table had been changed on that day, namely, 1 column was changed from CHAR(13) to CHAR(12). I do not know if this has any relation with the problem we experience. Currently the databases are structural the same. I could not get the replicationprocess to work and deleted all, publication on server1 and subscription on server2. I tried to setup a new transactional publication, which is not a problem, however, I cannot create a succesful subscription. I receive the message: The process could not bulkcopy into table 'Tablename', where tablename is the changed table... Again, both tables in publisher and subscruiber are the same in all respects. When I remove the offending table from the publication, the same message now with another tablename... Removing this table will result in again this message with another tablename... I am really flabbergasted. Any idea where to look at TIA FreeHansje
VIEWS ON THIS POST

135

Posted on:

Tuesday 20th November 2012
View Replies!

Ambiguous column problem

Can someone tell me why I'm getting this error: [Informix][Informix ODBC Driver][Informix]Ambiguous column (client_id). Joing 3 different tables: Client, case_defendant, client_address by way of column "client_id." It has always been my understanding that you differentiate identical column names in multiple tables with dot notation (table.column_name). So why doesn't this work There's probably a much nicer way of laying this out... Here's the entire select statement: Code: sql2="SELECT client.client_id, client.first_name, client.last_name, client.file_out_to, " & _ "case_defendant.client_id, case_defendant.case_defendant_id, client_address.client_id, client_address.address1, " & _ "adjudicatd_offense.case_defendant_id, adjudicatd_offense.citation_id, citation.citation_id, citation.title, " & _ "citation.description " & _ "FROM client " & _ "JOIN case_defendant ON client.client_id = case_defendant.client_id " & _ "JOIN client_address ON client.client_id = client_address.client_id " & _ "JOIN adjudicatd_offense ON case_defendant.case_defendant_id = adjudicatd_offense.case_defendant_id " & _ "JOIN citation ON adjudicatd_offense.citation_id = citation.citation_id " & _ "WHERE client.client_id = " & rs("pacts") & " ORDER BY " & Sort
VIEWS ON THIS POST

116

Posted on:

Tuesday 20th November 2012
View Replies!

what you think about MSSQL

Hi Everybody I am new to big databases. Please help me to understand the concepts behind those. I heard everybody saying about million $$$ MSSQL. Is it really very expensive to have website with MSSQL database. Ill not hosting my website in house. Ill be hosting through other hosting companies and I find out that there is not a big money difference for hosting a website for ASP.NET with Access or ASP.NET with MSSQL I am planning to develop a website ASP.NET with MSSQL. ASP.NET is very powerful language. This website will be high volume website. I am developing this website to start a new business. I do not have a big amount of money for my budget. Please give me suggestions and ideas from you experience. Am I doing right thing here or not
VIEWS ON THIS POST

160

Posted on:

Tuesday 20th November 2012
View Replies!

Database architect

I have to create database handling plan for travel companies software, problem is that I have to make database plan for reservation system here seat should be available after, first reservation destination completed. example stops {A, B, C, D, E, .....} first reservation is from stop A to C Now if i search for available seat, from stop C to E then it should be show the seat which is otted to first reservation how I can handle these kind of database
VIEWS ON THIS POST

88

Posted on:

Monday 26th November 2012
View Replies!

Is this possible?

First of all I hope I am posting this in the right area. If not please feel free to correct me. I'm a commodities trader and I am trying to link real time data from a trading program called Esignal to a Microsoft Excel spreadsheet. As it turns out there is just to much information for excel to handle. I thought I might be able to use Access to help me sort through the data. I don't think i can link Access to my trading program (Esignal) So here is my question. Can I link Excel to Access and then link Access back to Excel I hope that makes since. Lance Eller
VIEWS ON THIS POST

126

Posted on:

Monday 26th November 2012
View Replies!

Why do i get this relationship error?

Hi , how are you all I get the following error when i try to create a relationship between two tables: 'tblProductTypes' table saved successfully 'tblProducts2Displays2Stores' table - Unable to create relationship 'FK_tblProducts2Displays2Stores_tblProductTypes'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblProducts2Displays2Stores_tblProductType s". The conflict occurred in database "intranet", table "dbo.tblProductTypes", column 'productTypesID'. ..but i dont understand why The datatypes are both the same, so surely this should not happen
VIEWS ON THIS POST

213

Posted on:

Monday 26th November 2012
View Replies!

Which mode SQL Server is running???

Hi Everyone, After connecting to the instance, as a user/ developer can i know in which mode my SQL Server is running (i.e MULTI_USER mode or Single user mode). Is there any query to get this information.
VIEWS ON THIS POST

232

Posted on:

Monday 26th November 2012
View Replies!

Name searching

Hi all, I'm develping a name searching module in asp.net and sqlserver 2000 The Modules seraches a name aganist the table "Customers" on columns "FirstName" , "MiddleName" And "LastName" Can i use Soundex to this matching, since the content will also be in arabic, a search for "Kamel" should return all the following: - Alkamel - Elkamell - Al Kamel - El Kamel - AlKamal - ElKamal - Al Kamal - El Kamal Please provide your thoughts abt soundex and full text search
VIEWS ON THIS POST

130

Posted on:

Monday 26th November 2012
View Replies!

Getting rows which share the same field value

I've inherited a legacy database and I need to query it for all rows which share the same UserID (non-unique column) Eg. Imagine I have this as my list of UserIDs 1 1 2 3 4 5 6 7 7 I want it to return all the rows with userids of '1' or '7', because they are repeated. Is there any way of doing this
VIEWS ON THIS POST

195

Posted on:

Monday 26th November 2012
View Replies!

Updating hindi data through java program

Hi everybody, We are trying to update some hindi data in sql server 2000 database through a java program. The datatype of the target column is nvarchar. When we try to update the data using normal update query through sql analyzer, we are able to update hindi data by prefixing 'N' with the string literal to be updated. But through a java program, when i try to run the same query, '\t\t' gets inserted into DB. Can anybody suggest what could be the possible reason for this and the workaround. Hoping for an early response
VIEWS ON THIS POST

127

Posted on:

Monday 26th November 2012
View Replies!

Comma delimited string in nvarchar, matching with user id

Okay heres the deal (I hope I can explain this clearly enough while being as breif as I can): I created a trouble ticket system (ASP site using MS SQL) where the trouble tickets were assigned to only one person, but now I need tickets to be assigned to multiple people. So in my ticket table I changed the assigned_to field to nvarchar and the user id's are comma delimited. On the users home page, when they sign into the site I show currently open tickets assigned to that user. Initially my SQL statement looked like this: strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, users u, business b where u.user_id = t.assigned_to and t.assigned_to = "& session("user_id")&" and t.status = 'In Progress' and b.business_id = t.business_id" Now as the field type has changed I went looking for something to help me sort thru the comma delimited field to find tickets assigned to the logged in user and am somewhat close with this: strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, users u, business b where u.user_id = t.assigned_to and (CHARINDEX(',"& session("user_id") &",',t.assigned_to)>0 OR CHARINDEX('"& session("user_id") &",',t.assigned_to)=1 OR CHARINDEX(',"& session("user_id") &"',t.assigned_to)=LEN(t.assigned_to)-1) and t.status = 'In Progress' and b.business_id = t.business_id order by b.name, t.ticket_id" or strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, users u, business b where u.user_id = "& session("user_id") &" and (PATINDEX('%,"& session("user_id") &",%',t.assigned_to)>0 OR PATINDEX('%"& session("user_id") &",%',t.assigned_to)=1 OR PATINDEX('%,"& session("user_id") &"%',t.assigned_to)=LEN(t.assigned_to)-1) and t.status = 'In Progress' and b.business_id = t.business_id order by b.name, t.ticket_id" neither of which work that well as it will return records that aren't assigned to that user at all and I am nto sure why. I just found charindex() and patindex() and barely know how to use them properly. Please help me as I am at my wits end.
VIEWS ON THIS POST

239

Posted on:

Monday 26th November 2012
View Replies!

Whats going in this statement?

In my new job I've inherited a job-tracking system that displays stats on work done etc... I've somehow found myself working with SQL server for the first time ever and would really appreciate it if anyone could explain to me what is happening in the statement below (in regards to what the SQL functions, CAST\FLOOR\FLOAT are doing) [highlight="sql"]SELECT distinct (select sum(tc.hoursspent) from taskcue tc where tc.email = t.email and tc.archive = 1 ) as hoursspent, (select count(tc.taskid) from taskcue tc where tc.email = t.email and tc.archive = 1 ) as noOfTasks, (100*(select count(tc.taskid) from taskcue tc where tc.email = t.email and tc.archive = 1 and CAST(FLOOR(CAST(datecompleted AS FLOAT))AS DATETIME) '2006-01-01' order by NoOfTasks desc [/highlight]
VIEWS ON THIS POST

131

Posted on:

Monday 26th November 2012
View Replies!

How to create a trigger for this constraint.

Hi every body.Could any sql sever expert show me how i can create a sql server 2000 trigger for this constraaint as this is my first time trying to create a trigger. I got many tables in my sql server. I want the trigger fires when a user tries to enter hours for an employee in hours worked table if he did not enter amount for same employee and same project in hourly wages table.(in another word amount should be enterd before hours)I want the trigger sends a massage and tells the user to enter amount before entering hours
VIEWS ON THIS POST

136

Posted on:

Monday 26th November 2012
View Replies!

Which Version of SQL Server Should I Install?

I would like to be able to install Microsoft SQL Server onto my laptop and desktop computers. I did a little research and found out that the latest available is version 2000. But I am not sure if the Enterprise edition is more than I need. Can someone please help me to decide which one to get (I really want to get something installed prior to version 2005 comes out). Also, do you have some suggestions on where I can learn more about the product and how to use it
VIEWS ON THIS POST

75

Posted on:

Monday 26th November 2012
View Replies!