Difficult Query: Return Recordset From Concatenated Strings?
Jul 20, 2005
Hi All,
I have what seems to me to be a difficult query request for a database
I've inherited.
I have a table that has a varchar(2000) column that is used to store
system and user messages from an on-line ordering system.
For some reason (I have no idea why), when the original database was
being designed no thought was given to putting these messages in
another table, one row per message, and I've now been asked to provide
some stats on the contents of this field across the recordset.
A pseudo example of the table would be:
custrep, orderid, orderdate, comments
1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer asked
for a brown model
2, 10002, 2004-04-12, :Comment 3:Comment 4:
1, 10003, 2004-04-12, :Comment 2:Comment 8:
2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:
2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled order
So, what I've been asked to provide is something like this:
I have a table in which each of the system comments are defined.
Anything else appearing in the column is treated as a user comment.
Does anyone have any thoughts on how this could be achieved? The end
result will end up in an SQL Server 2000 stored procedure which will
be called from an ASP page to provide order taking stats.
Any help will be humbly and immensely appreciated!
I'm using SQL Datatype SqlString, so I'm not sure what is going wrong...any help? Here is a shortened version of the issue: Code and then Full Error Message.
Thanks in advance for any help
VB CODE
Code Snippet
Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub MyProc(ByVal cAddress1 As SqlString, ByVal cCity As SqlString, ByRef sName As SqlString) Dim output As SqlString Dim space As New SqlString(" ") output = System.Data.SqlTypes.SqlString.Concat(cAddress1, space) sName = output End Sub End Class Error Message
Msg 6522, Level 16, State 1, Procedure MyProc Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'MyProc': System.Data.SqlTypes.SqlTypeException: Two strings to be concatenated have different collation. System.Data.SqlTypes.SqlTypeException: at System.Data.SqlTypes.SqlString.op_Addition(SqlString x, SqlString y) at System.Data.SqlTypes.SqlString.Concat(SqlString x, SqlString y) at SqlServerProject1.StoredProcedures.MyProc(SqlString cAddress1, SqlString cCity)
Hi all, I am having a little problem to use CLR Function inside SqlServer 2005. I do not see any problem with my code in C# as well as SqlServer sides. Please help if you know the solution.
/************************C# Codes***************************/ using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;
public class ClrUDF { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString GetString(SqlString str) { SqlString tmp = new SqlString("Hello "); tmp += str; return tmp; } };
/********************************************************/ CREATE ASSEMBLY [SqlClr] AUTHORIZATION [dbo] FROM 'C:ProjectsSqlClrSln..... SqlCrl.dll' WITH PERMISSION_SET = SAFE
Create FUNCTION dbo.GetString(@str as nvarchar(20)) RETURNS nvarchar(200) AS EXTERNAL NAME SqlClr.ClrUDF.GetString
Try to use the function:
Select dbo.GetString('Jame')
and the error is throw
Msg 6522, Level 16, State 2, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'GetString': System.Data.SqlTypes.SqlTypeException: Two strings to be concatenated have different collation. System.Data.SqlTypes.SqlTypeException: at System.Data.SqlTypes.SqlString.op_Addition(SqlString x, SqlString y) at ClrUDF.GetString(SqlString str)
Expression-based connection strings are great but they are specific to each report which makes them difficult to maintain. Shared expression-based connection strings are not supported.
I'm looking for a way around this. I'm hoping to write a custom data extension, and in the custom data extension, do all the "dynamic" logic that sets the connection string (which you normally would be doing in your expression-based connection string).
I know the above can be done. What I don't know is how, inside a custom data extension, to get the value of expressions like User!UserID? (I want the connection to run under stored credentials, but I want to customize the connection string and add a property to it based on the UserID who is logged into Report Manager.)
I want a functionality such that I want to return a select query resultset and a varchar variable from a procedure. How can I achieve that,and moreover how can I fetch them in ASP??
Waiting for someone to shed a light of hope. Thanx a lot
In a nutshell, I am trying to set a combobox's row source using a stored procedure. Surely there is an easy way to do that.
I am working with SQL 2000 as my back-end, and my front-end is an Access Project. The stored procedure I am trying to run is on a different database then the one my project is connected to, but from what I can see in my de-bugging efforts, that is not the problem.
SELECT dbo.TIMS_eeLinksByName.eeLink, dbo.TIMS_eeLinksByName.Employee FROM dbo.TIMS_eeLinksByName WHERE (dbo.TIMS_eeLinksByName.eeErNum = @EmployerNum) ORDER BY dbo.TIMS_eeLinksByName.Employee
returns 169 records when I run it directly from the MS Visual Studio environment.
However whe I try to run it from VBA with the following code;
Dim sp_eeLinksByName As String Dim ConnectionString As String Const DSeeLinksByName = "SOS-1" Const DBeeLinksByName = "Insync" Const DPeeLinksByName = "SQLOLEDB"
Dim objeeLinksByNameConn As New ADODB.Connection Dim objeeLinksByNameRs As New ADODB.Recordset Dim objeeLinksByNameComm As New ADODB.Command
' Connect to the data source. objeeLinksByNameConn.Open ConnectionString
' Set a stored procedure objeeLinksByNameComm.CommandText = sp_eeLinksByName objeeLinksByNameComm.CommandType = adCmdStoredProc Set objeeLinksByNameComm.ActiveConnection = objeeLinksByNameConn
' Execute the stored procedure on ' the active connection object... ' "CurrTSCalendar" is the required input parameter, ' objRs is the resultant output variable. objeeLinksByNameConn.sp_eeLinksByName CurrTSEmployer, objeeLinksByNameRs
' Display the result. 'Debug.Print "Results returned from sp_CustOrdersOrders for ALFKI: " Select Case objeeLinksByNameRs.RecordCount Case 0 'Do Nothing Case Is > 0 'Get the Employee List objeeLinksByNameRs.MoveFirst Do While Not objeeLinksByNameRs.EOF MyControl.AddItem (objeeLinksByNameRs.Fields("eeLink") & ";" & objeeLinksByNameRs.Fields("Employee")) objeeLinksByNameRs.MoveNext Loop End Select
'Clean up. 'objRs.Close objeeLinksByNameConn.Close Set objeeLinksByNameRs = Nothing Set objeeLinksByNameConn = Nothing Set objeeLinksByNameComm = Nothing
I get an "Object Variable or With Blick Vraiable not Set"...... for the life of me I do not know why? Does anyone have any thoughts?
I am using SQL Server 2008 as a back end for a Microsoft Access front end. I have created a report that is essentially a Bill Of Lading. The detail section lists all the purchase orders that are being shipped on a single load. The problem with the Access Report is that I always need a set number of records (8) so that the layout is consistent. So, if the query returns 5 records, I need an additional 3 blank records returned with the recordset. If there are 2 records, I need an additional 6, and so on. For simplicity sake the query is:
SELECT tblBOL.PONumber FROM tblBOL WHERE tblBOL.BOLNumber=@BOLNumber;Now, I can get the results I want by using a union query for the "extra" records.
For instance, if there are 6 records returned for BOLNumber '12345', I can get the expected results by this query:
SELECT tblBOL.PONumber FROM tblBOL WHERE tblBOL.BOLNumber='12345' UNION ALL SELECT '12345',Null UNION ALL SELECT '12345',Null;
Another solution would be to create a temporary table with the "extra" records and then have only one Union statement. Not sure which is better, but I'm not really sure how to programmatically do either of these. I'm guessing I need to do it in a stored procedure. How do I programmatically create these extra records? One other note.... If there are more than 8 records, I need to return 8 of these "blank" records and none of the real records (hard to explain the reason behind this, but it has to do with the report being only a summary when there are more than 8 records while the actual records will go on a different supplemental report).
I tried to create a dynamic table, fill in it and return it as recordset. The codes as this:
Declare @tbl Table(id int, name varchar(100), age int) Insert Into @tbl(id, name, age) Values(1, 'James, Lee', 28), (2, 'Mike, Richard', 32), (3, 'Leon Wong', 29) Select * From @tbl Order By age
It works well in "SQL Query Ananizer". But return no records in ASP page.
So I've run into another problem. I've figured out how to concatenate multiple rows into a single string my only problem is using that on another query with multiple rows...Basically what I'm trying to do is pull up information for each class a student has in his/her profile and while at it pull up any prerequisite classes that are associated with a certain class. So the final query would look something like this...
StudClassID Completed Class ID Name Description Credits Prereq... rest are insignificant... 0 0 CSC200 Cool prog... blah.... 3 CSC160, CSC180
I get the concept of the coalesce and cast just i'm not understanding how to get it to work with each return on the main select...anyways below are the tables and my current query call...
Code Snippet
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Student_Classes] Script Date: 03/31/2008 01:32:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Student_Classes]( [StudClassID] [int] IDENTITY(0,1) NOT NULL, [StudentID] [int] NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CreditID] [int] NULL, [Days] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Time] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Classroom] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Grade] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Semester] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Notes] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Completed] [tinyint] NULL CONSTRAINT [DF_Student_Classes_Completed] DEFAULT ((0)), CONSTRAINT [PK_Student_Classes] PRIMARY KEY CLUSTERED ( [StudClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_ClassID] GO ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_CreditID] FOREIGN KEY([CreditID]) REFERENCES [dbo].[Credits] ([CreditID]) GO ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_CreditID] GO ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_StudentsID] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) GO ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_StudentsID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Prerequisites] Script Date: 03/31/2008 01:32:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Prerequisites]( [PrerequisiteID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Prerequisite] PRIMARY KEY CLUSTERED ( [PrerequisiteID] ASC, [ClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Prerequisites] WITH CHECK ADD CONSTRAINT [FK_Prerequisite_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Prerequisites] CHECK CONSTRAINT [FK_Prerequisite_ClassID] GO ALTER TABLE [dbo].[Prerequisites] WITH CHECK ADD CONSTRAINT [FK_Prerequisite_Prereq] FOREIGN KEY([PrerequisiteID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Prerequisites] CHECK CONSTRAINT [FK_Prerequisite_Prereq]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Credits] Script Date: 03/31/2008 01:32:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Credits]( [CreditID] [int] IDENTITY(0,1) NOT NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Credits] [tinyint] NULL, CONSTRAINT [PK_Credits] PRIMARY KEY CLUSTERED ( [CreditID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Credits] WITH CHECK ADD CONSTRAINT [FK_Credits_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Credits] CHECK CONSTRAINT [FK_Credits_ClassID]
SELECT sClass.StudClassID ,sClass.Completed ,sClass.ClassID AS 'Class ID' ,c.LongName AS 'Name' ,c.Description ,cred.Credits ,(SELECT COALESCE(@prerequisites + ', ', '') + CAST(PrerequisiteID AS varchar(7))) AS 'Prerequisites' ,sClass.Grade ,sClass.Days ,sClass.Time ,sClass.Classroom ,sClass.Semester ,sClass.Notes FROM Student_Classes sClass INNER JOIN Prerequisites preq ON preq.ClassID = sClass.ClassID INNER JOIN Classes c ON c.ClassID = sClass.ClassID INNER JOIN Credits cred ON cred.CreditID = sClass.CreditID WHERE sClass.StudentID = 0 ORDER BY sClass.ClassID ASC
I have a table where i have 4 columns in it which i need to group together and then sum up a cost column also. I want to sum up the columns where i have a parent and and child and then i want to sum up the other column where i have only a child. Example of the data is below. I think i need to do this in a sub query
ID Ind Parent Child Cost P110041012705921.8000 W11004101270595.4500 A110041012705921.8000 B110041012705916.3500 R110041012705916.3500 B0100420043.3000 P0100420043.3000 W0100420021.6500
I have a table that stores billing rates for our employees by client.Each employee can have a different billing rate for each client for aspecified period. Here are the columns in the table.eid - Employee ID#cid - Client ID#startdt - start date of billing rateenddt - end date of billing ratebrate - billing rateI need to create a script that will verify that for a given eid, and cidthat either the startdt or enddt for one billing rate, the periods donot overlap.For example, I need to be able to detect overlaps such as this:eid cid startdt enddt brate001 001 1/1/2003 12/31/2003 $50001 001 11/01/2003 04/01/2004 $75*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
suppose I have the following table:CREATE TABLE (int level, color varchar, length int, width int, heightint)It has the following rows1, "RED", 8, 10, 122, NULL, NULL, NULL, 203, NULL, 9, 82, 254, "BLUE", NULL, 67, NULL5, "GRAY", NULL NULL, NULLI want to write a query that will return me a view collapsed from"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)So I want a query that will returnGRAY, 9, 67, 25The principle is that looking from the bottom level up in each columnwe first see GRAY for color, 9 for length, 67 for width, 25 forheight. In other words, any non-NULL row in a lower level overridesthe value set at a higher level.Is this possible in SQL without using stored procedures?Thanks!- Robert
From this data I can see that serial number 0000-0000-0000-0006 Successfully completed part 1 and part 2 of the transaction, as did serial number 0000-0000-0000-0004.
Serial number 0000-0000-0000-0092 had trouble, it connected at 13:59:04 (tblremoteunitrequestID) but part 2 didnt complete, so it wasent saved in tblremoteunitrequests. The same happened at 12:15:22 but at 10:31:54 it was successful so it was saved.
I Only want to display the transactions that didnt complete, sounds easy huh?
CASE WHEN TBLRemoteUnitRequests.DateReceived BETWEEN DATEADD(SECOND,-1,TBLRemoteFeildUnitRequestID.RecordDate) AND DATEADD(SECOND,10,TBLRemoteFeildUnitRequestID.RecordDate)
THEN ' Ok'
ELSE ' Not ok'
END AS PROBLEM
FROM TBLRemoteFeildUnitRequestID LEFT OUTER JOIN TBLRemoteUnitRequests ON TBLRemoteFeildUnitRequestID.Serial = TBLRemoteUnitRequests.Serial WHERE TBLRemoteFeildUnitRequestID.RecordDate BETWEEN DATEADD(WEEK, - 2, GetDate()) AND GetDate()
ORDER BY RecordDate DESC
This kinda worked, but it caused records that satisfied the between condition to be displayed twice, once as "Ok" and once as "Not ok".
Heres a sample of the result I got:
Serial | RecordDate (1st part of transaction) | Status
0000-0000-0000-0006 2006-11-13 14:00:36.000 Ok (Duplicated) 0000-0000-0000-0006 2006-11-13 14:00:36.000 Not ok 0000-0000-0000-0004 2006-11-13 14:00:30.000 Not ok (Duplicated) 0000-0000-0000-0004 2006-11-13 14:00:30.000 Ok 0000-0000-0000-0092 2006-11-13 13:59:04.000 Not ok (Correct) (Not duplicated) 0000-0000-0000-0092 2006-11-13 12:15:22.000 Not ok (Correct) (Not Duplicated) 0000-0000-0000-0092 2006-11-13 10:31:54.000 Not ok (Duplicated) 0000-0000-0000-0092 2006-11-13 10:31:54.000 Ok 0000-0000-0000-0006 2006-11-13 10:00:29.000 Ok (Duplicated) 0000-0000-0000-0006 2006-11-13 10:00:29.000 Not ok
I have just about had enough, I have wasted an entire day on this
I'm writing a workflow management application for my work, and its somewhat complicated, here's a general idea of how it works:
- Anything that a company does is defined by a workflow. - A workflow consists of tasks. - Some tasks in a workflow can't be started until other tasks have been completed. If task A can't be started until tasks B and C are finished, then task A depends on B and C.
You might imagine that a bank has a workflow for handling a house loan. Before a bank could sign a contract with an applicant, they'd need proof of house ownership, but before they could get proof of house ownership they need an applicant's proof of identity like a driver's license or military ID.
Here's an oversimplified visual:
Each arrow points to its dependency. Each task can have multiple dependencies.
The setup above is represented in the database by a Tasks and a Dependencies table. Tasks has an ID field, and Dependencies has a TaskID and DependencyID field which are both foreign keys to Tasks.ID.
Code:
[Tasks] ID Status Name -- ------ ---- 1 Done Start Processing Loan Application 2 Done Photocopy applicant's driver's license 3 NotDone Photocopy proof of house ownership 4 NotDone Get a copy of applicant's W-2 forms 5 NotDone Perform credit check on applicant 6 NotDone Sign loan contract
Tasks has a many-to-many relationship with itself.
Here's the hard part: - A task can't be started until all of its dependencies have been completed. - after a task is completed (meanings its status is marked "done"), I need to return a list of all the new tasks that are ready to be started.
When TaskID 2 is marked "Done", then TaskID 4 is ready to begin; however, TaskID 5 is not ready to begin since it depends on 2 and 3, and 3 hasn't been completed yet.
The requirements of the query are very simple, but the implementation is difficult.
I'll post a prelimenary solution in the next post:
Hello,Here is a brief summary:Table 1 = All Accounts- with fields such as Customer ID and Account #Table 2 = Deposit Balance Table- with fields such as Account #, BalanceTable 3 = Loan Balance Table- with fields such as Account #, BalanceAll accounts are either deposit accounts or loan accounts. What I needto do is to gather information about total balances in both depositsand loans for each customer. I haven't been able to hit the right queryfor doing this. I can easily get information about one or the other,such as the following:SELECT All_Accounts.Customer_ID, COUNT (DISTINCT(Deposit_Balance_Table.Account_Number)), Sum(Deposit_Balance_Table.Balance)FROM Product_Table, Deposit_BalanceWHERE (Product_Table.Account_Number=Deposit_Balance.Acco unt_Number)GROUP BY Product_Table.Customer_ID ORDER BY 1Which will give me one row for each user, and show me the total numberof deposit accounts each customer has and a sum of the balances in eachof those accounts. I can make a similar query involving Loan Accounts.As soon as I try to draw both, however, I wind up below my depth.Something to do with the handedness of my joins, I believe. Often Iwill get one column of information (either deposits or loans), or thequery will fail because the join I'm attempting is invalid, etc. I needto take every row in the All_Accounts table, match each one to itsbalance in either the Deposit or Loan table, and then group them all bythe Customer ID and sum them, so that I can find out the totalrelationship balance per customer. Any help would be appreciated.
GO CREATE TABLE [dbo].[Product] ( [ProductId] [smallint] IDENTITY(1,1) NOT NULL CONSTRAINT PkProduct_ProductId PRIMARY KEY, [Name] [varchar](52) NOT NULL, [Type] [smallint] NOT NULL, ) For this table I have to write the querywhich willget the TOP 1 Row of each Type. I know the alternate way of doing this by union. But this is not professional. Can anyone resolve this issue?
Hi,I have a table as followingaa Text1 aa, Join Bytes!, 15267aa Text1 aa, Join Bytes!, 16598aa Text1 aa, Join Bytes!, 17568aa Text2 aa, Join Bytes!, 25698aa Text3 aa, Join Bytes!, 12258I have to write a query as follows ...SELECT DISTINCT TOP 500 fldText, fldContact, fldItemidFROM tableWHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')In the example you can see the table has rows in which text and contact ordouble but with different itemid's. Now my employer wants me to show only 1row when text and contact or the same. He doesn't mind which itemid I show.... but I have to show one.I've an idea of how to do this using a cursor and a temporary table but Iguess that will be fatal for the performance because then I have to loopthrough all selected rows, check each row with all other rows and store theprimary key in the temporary table if dedected it isn't double. AfterwardsI can execute ... SELECT ... FROM TABLE where primary key in (selecttemp_primarykey from #temptable).I hoped I could do everything in 1 "easy" SELECT but I should not know how?Any ideas are much appreciated.Thanks a lot.Perre Van Wilrijk.
If you know the answer please explain what you're doing if possible, that'll help me :)I have the following tables:CREATE TABLE [dbo].[tblUserData]( [UserCode] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DisplayName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,) ON [PRIMARY]CREATE TABLE [dbo].[tblFriends]( [UserCodeOwner] [int] NOT NULL, [UserCodeFriend] [int] NOT NULL, [createdate] [datetime] NOT NULL CONSTRAINT [DF_tblFriends_createdate] DEFAULT (getdate())) ON [PRIMARY]in tblFriends relations are stored twice, so for a relation between user 5 and 6, there will be 2 rows: 5-6 and 6-5Now, I want to get the columns (UsercodeOwner,UsercodeFriend,createdate,username,displayname) for relations that were created in tblFriends in the last 10 days for the FRIENDS of a person with usercode 5.Example:tblUserdata5 peter Petertje6 john Johnny11 simon SimonSays15 monique MontjetblFriends5 6 'createdate 30 days ago'5 11 'createdate 5 days ago'6 5 'createdate 30 days ago'6 11 'createdate 3 days ago'6 15 'createdate 7 days ago'11 5 'createdate 5 days ago'11 6 'createdate 3 days ago'15 6 'createdate 7 days ago'The resultset for a query on usercode 5 would now be (usercode1, username1, displayname1,usercode2, username2, displayname2,createdate):6 john Johnny 11 simon SimonSays 'createdate 3 days ago'6 john Johnny 15 monique Montje 'createdate 7 days ago'As you can see each relation is only returned twice even though there are always two entriesWhat would be the SQL statement, if possible without temp table..Thanks!
I am connecting to the database as following: set con = server.createobject("adodb.connection") con.open "connectionstring" set rs = con.execute("select * from tablename")
I am able to display the records but if I want to give adopenstatic to the above connection, how can I do so?
trying to search a DB for all records within a range. I have a recordset containing about 10 postcodes and I need to find all the records containing any one of those postcodes currently I have: sqlString= "SELECT aucTitle FROM tblAPAuctions WHERE aucPostalCode LIKE '%" & left(rsPostcodeResult, 4) & "%'" but I am getting 'type mismatch' error.
Could someone help me by answering the questions below?What's a cursor?What's difference between Query and View?Is a RecordSet just part of a table? Can it be part of a query of view?If the content in a table changed, is it necessary for a old recordset torenew itself by do "Requery()"?Thanks for your help!
I am using query strings to pass data from web form to web form and I have two questions. First if i use a asp:sqldatasouce to fill up a grid view and I have my select command set to a paramater that get whatever is in the query string it will not work because whatever is in the quers string gets a " ' " put in front and in the back of it. So if the query string was 5 whene it does the sql statement it sets my paramater = '5' not just 5 so its wont work. How can I fix this using the asp:sql datasource my aspx code looks like <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Rental PropertiesConnectionString %>" SelectCommand="SELECT * FROM [APARTMENTS] WHERE ([PROPERITY_ID] = @PROPERITY_ID)"> <SelectParameters> <asp:QueryStringParameter Name="PROPERITY_ID" QueryStringField="key" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> Also since i have not been able to get around this so i have been wrting code in vb.net to attact a dataset to a grid view to populate it based on the query string i would do the following in vb.net to get ride of the ' in front and behind the query string Dim y as string = "'" // " ' " key = Request.QueryString("key").trim(y.tochararray) But now i am doing another project in C# and I have re-written the above code in C# it will run but it will not take the " ' " out form infront or behind key. How does this need to be changed up? string y = "'"; key = Request.QueryString["key"].trim(y.tochararray());
Hi, I have a SQLDataSource binding to a GridView and can come to the page either with or without a query string attached: /ProjectManagement/reporting/project.aspx /ProjectManagement/reporting/project.aspx?portfolio=3 When it comes with a query string, I can see in SQL Server profiler it executes and I get all the right data. When it is an empty string, or with no "?portfolio=" on it, it won't even execute against SQL server. Any ideas? <asp:GridView ID="grid" runat="server" Width="600px" ShowHeader="false" AutoGenerateColumns="false" DataSourceID="DSportfolio" AllowSorting = "true" AllowPaging = "true"> <Columns> <asp:TemplateField> <ItemTemplate> ............. </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:SqlDataSource ID="DSportfolio" ConnectionString="<%$ AppSettings:SQLConnection1 %>" SelectCommand="uspSELECT_PROJECT" SelectCommandType="StoredProcedure" runat="server"> <SelectParameters> <asp:QueryStringParameter Name="p_PORTFOLIOID" QueryStringField="portfolio" /> </SelectParameters> </asp:SqlDataSource> Thanks, James
Hi, I am having a problem looking up querystrings in my DB, I have the following code <asp:SqlDataSource ID="SqlData_products" runat="server" ConnectionString="<%$ ConnectionStrings:ProductDatabaseConnectionString2 %>" SelectCommand="SELECT * FROM [tbl_subProduct],[tbl_subCategory],[tbl_topCategory],[tbl_Material],[tbl_topLevelProduct] WHERE numSubCategoryID = @Category OR numMaterialID = @Material OR txtOrderCode = @Keyword OR txtMovexCode = @Keyword OR txtUKMapCode = @Keyword"> <selectparameters> <asp:QueryStringParameter Name="Category" QueryStringField="SearchCatString" /> <asp:QueryStringParameter Name="Material" QueryStringField="SearchMatString" /> <asp:QueryStringParameter Name="Keyword" QueryStringField="SearchKeyString" /> </selectparameters> </asp:SqlDataSource> My Querystrings are in the VB file:- SearchCatString = Request.QueryString("txtSelCat") SearchMatString = Request.QueryString("txtSelMat") SearchKeyString = Request.QueryString("txtKeyword")These come from a previous page, where a dropdown list copies the ID of the selected item into a text box. My problem is that this is not working please help
I'm running the following SQL query from LabVIEW, a graphical programming language, using the built in capabilities it has for database connectivity:
DECLARE @currentID int SET @currentID = (SELECT MIN(ExperimentID) FROM Jobs_t WHERE JobStatus = 'ToRun'); UPDATE [dbo].[Jobs_t] SET [JobStatus] = 'Pending' WHERE ExperimentID = @currentID; SELECT @currentID AS result <main.img>
This is the analogous code to main() is a C-like language. The first block, which has the "Connection Information" wire going into it, opens a .udl file and creates an ADO.NET _Connection reference, which is later used to invoke methods for the query.
<execute query.img>
This is the inside of the second block, the one with "EXE" and the pink wire going into it. The boxes with the gray border operate much like "switch" statements. The wire going into the "?" terminal on these boxes determines which case gets executed. The yellow boxes with white rectangels dropping down are invoke nodes and property nodes; they accept a reference to an object and allow you to invoke methods and read/write properties of that object. You can see the _Recordset object here as well. <fetch recordset.img>
Here's the next block to be executed, the one whose icon reads "FETCH ALL". We see that the first thing to execute on the far left grabs some properties of the recordset, and returns them in a "struct" (the pink wire that goes into the box that reads "state"). This is where the code fails. The recordset opened in the previous VI (virtual instrument) has a status of "closed", and the purple variant (seen under "Read all the data available") comes back empty.
The rest of the code is fairly irrelevant, as it's just converting the received variant into usable data, and freeing the recordset reference opened previously. My question is, why would the status from the query of the recordset be "closed"? I realize that recordsets are "closed" when the query returns no rows, but executing that query in SSMS returns good data. Also, executing the LabVIEW code does the UPDATE in the query, so I know that's not broken either.
Let me start by asking that no one try to convince me to use Stored Procs. The examples below are a lot more simplistic then my real world code and it just gets too complicated to try to manage the quantity of SPs that I would need. I have an application that displays a lot of data and I've created a system for users to filter the data using checkboxlist controls, dropdown controls, etc. From this, I have a "core" query that selects the fields that display in my GridView. It has a base Select clause, From clause and Where clause. From this I then add more to the Where clause to apply these filter values. Here's an example "core" query: SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCode From this if a user want's to only display profiles from NC, they could select that from the CBL and the query would be modified to: SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.State IN ('NC') My code would add the last line above since the user specified that they only wanted NC profiles. This is very simple and I have this already going on with my application. Here's the problem. In order to accommodate all of the various filters, I have to inner join and left join a bunch of various tables. Many times I include tables that have no data to display or filter on and therefore impacts performance. Here's an example: SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentID From the query above, I have included the Agent table that holds the agent's contact information. One of my filters allows the user to type in an agents name to find all profiles assigned to it. Here's what that would look like: SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentIDAND Agent.Name = 'Smith, John' You can see now that it was necessary to have the Agent table already joined into the query so that when I used the agent name filter, it wouldn't crash out on me. The obvious thing would be to only include the Agent table when searching for an agent name. This is ultimately what I'm looking to do, but I need a solid method to go about doing this. Keep in mind that I currently have 16 tables in my "core" query and many of those are not needed unless the filters call for it. If anyone has any ideas on how to simplify this process I'm selcome to suggestions. We're using SQL 2000, but are looking to upgrade to SQL 2005, if that makes any difference. I know that the way I do table joins is compliant with SQL 2005 and I'm certainly open to suggestions that will make it forward compatible. This app is using .NET 2.0 and written in VB.NET. Thanks for any help!
If I have a query string that is to be stored in a database, for example
Code:
SELECT prod_id, prod_name, prod_desc FROM products WHERE prod_id = 'variable'
how can I put a variable identifier into this string so that when I need to run the query I call it from the database and simply insert the relevant variable in the correct place.
Is there an appropriate way of doing this in MS SQL Server?
