One table has an automatic numbering primary key named ID and is named rfi.
The other table has a field named rfinumber and is named discussion.
Both ID and rfinumber have a datatype of number.
Upon using the querybuilder with Web Matrix, I issue a select command to get all records from the discussion table that have a rfinumber field equal to the ID field in the rfi table.
Problem is that I am getting the entire discussion table when I use the following query:
"SELECT [discussion].* FROM [discussion], [rfi] WHERE ([discussion].[rfinumber] = [rfi].[ID])"
For example
DISCUSSION TABLE
rfinumber
1
1
1
2
RFI TABLE
rfi
1
2
Given the query, I should get a discussion table only listing the rfinumber = 1.
When I run the query from my database package it runs fine??
Hello to all, I have a problem with ms sql query. I hope that somebody can help me. i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships . 3387 (2345, 2388,4567,....) 4567 (8990, 7865, 3387...) i wirte a query to check if there is Relationship between two members. Query: Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567; select * from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in (select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query. Can Someone tell me where is the problem? Thanks
I got the following code to add a column in a matrix with a variance:
IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing, ( (Fields!Amount.Value) /Previous(sum(Fields!Amount.Value)) ) )
This code works fine, except that the first row of the matrix shows an #error
This happens with each matrix where I use this expression. A warning emerges:
rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.
Attempted to divide by zero.
The strange thing is that the part
Fields!year.Value=First(Fields!year.Value,"Category") should prevent an error and I expect it to show 'nothing'
An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)
As you can see, all other 2013 rows show a blank cell, except the first row.
I have a Matrix table that expands to the right when choosing an amount of months to be shown. Under this matrix I have to Charts. The two charts are situated together, that is no space between them, and to the left of the report.
Now, if I choose a lot of months, say three years the matrix diagram will be huge to the right. The problem I have is that the second diagram, the one on the right, moves to the right depending on how big the report gets, and this is not good at all. The two charts are supposed to be all the way to the left.
Link ( GroupID int , MemberID int ) Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID, then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
End Sub /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// I executed the code successfully and I got a box which asked for "Enter the query string". I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
Hello. I hope to explain myself well - I want to make a matrix with two rows. Lats say my data is this: I hava a list of months and in every month I have a number of pepole and there age. How can I show this in a matrix? It need to be in a matrix since I need the columns to expand acording to the month but I don't know how to create two diffrent rows in my matrix.
I have a report thats fully functional. I just want to add a filter so that my "Visits" field only displays the Visits per day that are less then 6. When i try to filter out the matrix or the group, it tells me the datatypes are different . Something about int32. Its in a matrix, but i have seen this happen in a table too, so i guessing thats not the problem. I just want to be able to display the information for Sales Reps with less then 6 Visits. Any help, will be greatlly appreciated.
Adding more columns in a matrix report that don€™t belong to the columns drilldown dimensions€¦
That is, for example, having the following report:
Product Family
Product
Country City Number of units sold
Then I would add some ratios, that is, Units Sold/Months (sold per month) and other that is the average for Product Family (Units Sold/Number of Product Family), for putting an example€¦ some columns should be precalculated prior to the report so do not get into it, the real problem I don€™t see how to solve is adding one or two columns for showing these calculated column that doesn€™t depend on the column groups but they do for the rows groups€¦
Any guidance on that?
The only way I am seeing by now is to set it as two different reports, and that is not what my client wants€¦
I have got a grid view which i want to query i have added a WHERE for it WHERE (([carinfo] = @carinfo) AND ([carmake] = @carmake) AND ([postcode] LIKE '%' + @postcode + '%') AND ([carprice] <= @carprice)) I have made a search page with 4 textboxes and a search button but what i cant same to get working is the code to take the infor from my text boxes and run the query on the grid view page.If i just had a query with (([carinfo] = @carinfo) i can get that to work by doing this Protected Sub SearchButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles searchButton.Click '~/Default2.aspx Response.Redirect("search.aspx?man=" + Carmake.Text) End Sub After that i just dont know what to do, my asp code for the Data Source is <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:addcar %>" SelectCommand="SELECT [AdId], [carinfo], [carmake], [cartype], [carprice], [other1], [enginesize], [fuel], [listdate], [adtitle] FROM [classifieds_ex] WHERE (([carinfo] = @carinfo) AND ([carmake] = @carmake) AND ([postcode] LIKE '%' + @postcode + '%') AND ([carprice] <= @carprice))"> <SelectParameters> <asp:QueryStringParameter Name="carinfo" QueryStringField="man" Type="String" /> <asp:QueryStringParameter Name="carmake" QueryStringField="make" Type="String" /> <asp:QueryStringParameter Name="postcode" QueryStringField="postcode" Type="String" /> <asp:QueryStringParameter Name="carprice" QueryStringField="pricerange" Type="Decimal" /> </SelectParameters> if anyone could help me with this would be great i been trying to work this out for two days now. Keep safeNick
Hi, I'm self learning asp.net and have a little experience in vb.net. What i'm trying to do is run an SQL query and write the results into variables. The SQL query's im running will only ever return 1 row (specifing primary key / uniqueID). This is becasue i dont want to output the results as a table, rather to other objects. (using VS Web Developer express 2008) Currently my code is: ------------------------------------------------------------------------------------------------------------------------------------------------------ Imports System Imports System.Data.SqlClientPartial Class details Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Dim VariablesDim job_id As Integer Dim myConnection As SqlConnection Dim var_jobid As String = "" Dim var_2 As String = "" Dim var_3 As String = ""Dim SQL_read As New Object
'Write valus to Variables job_id = Request.QueryString(job_id)SQL_read = "SELECT * FROM TABLE_NAME WHERE job_id = " & job_id 'Write Variables and Text to labelsLbl_title.Text = "Details for Job " & job_id Lbl_jobid.Text = job_id 'Try to Establish link to SQL Server '=================================== TrymyConnection = New SqlConnection("server= [my server here] ; database=ServerLog ; User Id = sa; Password= [my password here]") myConnection.Open() -------------------------------------------------------------------------------------------------------------------------------- I realise the quality of the code may be pretty abismal, due to pretty much guessing my way through. I dont know how to basically 'run' my sql query for starters, and then to output the results into variables. -IE- coloumn 1 (row1) of my results being put into var_1, column 2(row1) being put into var_2 etc etc.
I inherited a project at work in which I have to diagnose a bad query string that should be passing a value. Below are what I hope are relevant pieces of code. Your help ASAP will help insure job protection for yours truly. Thanks. Dim strSQL as String = "SP2 "'strSQL = strSQL & Request.Cookies("ODM")("User_ID_NO") & ", "strSQL = strSQL & Request.Querystring("queue_id")strSQL = strSQL & ",'" & Request.Querystring("subtype")+ "'" ''xx no subtype is being passed herePart of SP2:SELECT '<A target="new" href="../document-ds.aspx?dcn=' + CAST(A.DCN AS VARCHAR(25)) + '">' + CAST(A.DCN AS VARCHAR(25)) + '</A>' AS 'DCN', QUEUE_NAME AS 'Queue Name', DOC_SUBTYPEDESC as 'Department' , DOC_CLASSDESC as 'WorkGroup', DOC_TYPEDESC as 'Doc Type' , WKF_SUBMIT as 'Received Date', QI.QUEUE_DATE as 'Queue Date', dbo.MIN2PARTS(DATEDIFF(mi,A.WKF_SUBMIT, GETDATE())) as 'Doc Age', CASE WHEN (DBO.SLA_HOURSDIFF_DCN(A.WKF_SUBMIT, GETDATE(), A.DCN) - SLA_HOURS) > 0 THEN '<font color = red> ' + CAST(DBO.SLA_HOURSDIFF_FORMAT( DBO.SLA_HOURSDIFF_DCN (A.WKF_SUBMIT, GETDATE(),A.DCN)) AS VARCHAR(25)) + '</font>' WHEN (DBO.SLA_HOURSDIFF_DCN(A.WKF_SUBMIT, GETDATE(), A.DCN) - SLA_HOURS) < = 0 THEN '<font size = 2 color = green><b> 0 </b></font>' WHEN (DBO.SLA_HOURSDIFF_DCN(A.WKF_SUBMIT, GETDATE(), A.DCN) - SLA_HOURS) IS NULL THEN 'No SLA Configured' END AS 'Out of SLA', (DBO.SLA_HOURSDIFF(A.WKF_SUBMIT, GETDATE()) -SLA_HOURS) as 'SLA_HOURS' FROM T_WF_DOC_TYPES DT INNER JOIN T_WF_AP_TRACKING A ON A.DOC_ID = DT.DOC_ID INNER JOIN T_WF_QUEUE_INV QI ON QI.DCN = A.DCN INNER JOIN T_WF_QUEUES Q ON Q.QUEUE_ID = QI.QUEUE_ID WHERE Q.QUEUE_ID = @QUEUE_ID AND DT.DOC_SUBTYPE = @DOC_SUBTYPE
My main task is to discover where the @DOC_SUBTYPE comes from and why it's not passing the value to the stored proc or beyond. Let me know if you have any other questions since I'm unsure if I gave you the right info or not.
I have a query on a peson application that poduces a record. Within the application there is a question that allows the applicant to choose several answers: -
Applic_no Question_ID Question Answer 12345 40 Medical 2,5,12
There is a lookup table that tells me what each answer is: -
However, how do I query the lookup table if my answer is 2,5,12? I need to somehow split it or query the string array to pick out the values seperated by commas.
Hi there, im trying to create a string from a query, i got a table like this one id name -- ----------- 1 Robert DeNiro 2 Will Smith 3 Bruce Willis 4 Al Pacino
Now, i want to get this output
Robert Deniro; Will Smith; Bruce Willis; Al Pacino
I'm wondering if there is a way to acomplish this.
I have a matrix in my report that is based on an MDX query. I copy-paste the matrix and then see the two matrices in the report and all is fine.
I then insert a group into the second matrix. When I run the report the second matrix appears as I would expect, but the cells are blank in the first matrix. Sometime all the data cells are blank, but not the totals.
I tried a similair thing with a table. I added a table based on the same MDX dataset and the cells in the matrix were also then blanked out.
I made sure that the matrices were not sitting on top of each or even in the same space.
The goal here is to show the same dataset but with different groupings. I thought of doing drilldowns, drillthroughs, etc. but this is the way they want to see the data.
Hello,I would like to keep some values as session variables while the user is loged in, but i am missing some part of how to implement it.This is what I have:<script runat="server">
Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)
Dim conn As SqlConnection Dim cmd As SqlCommand Dim cmdString As String = "SELECT users.username, users.password, users.FirstName, users.LastName, users.CompanyId, Company.CompanyName, users.SecurityLvl FROM users LEFT OUTER JOIN Company ON users.CompanyId = Company.CompanyId WHERE (users.password = @Password) AND (users.username = @Username)"
I am sorry for my basic question I am sure. I have this SQL Query that attaches to 3 tables and pulls data in: Select OffNormalInfo.OffNormalID, OffNormalInfo.CompanyID, OffNormalInfo.SiteID, OffNormalInfo.CaskID, DATEPART(mm,EventDate)as Month, DATEPART(dd,EventDate)as Day, DATEPART(yyyy,EventDate)as Year, EventName, Damage, Action, UnitIDVCC, UnitIDTSC FROM OffNormalInfo, DamageInfo, CaskInfo WHERE OffNormalInfo.OffNormalID = DamageInfo.OffNormalID and CaskInfo.CaskID = OffNormalInfo.CaskID Now, the main table that has the main document that everything else rides off of is the OffNormalInfo table. The CaskInfo table will also always have data in it, but there could be an instance where the DamageInfo table doesn't have a corrisponding record with the same OffNormalID as the OffNormalInfo table. What is happening right now with this string is that nothing is being returned at all for that OffNormalInfo record if there is nothing in the DamageInfo table. There are also many times that the DamageInfo table has multiple records with the OffNormalID and so it returns the entire row multiple times with the same info from the OffNormalInfo table - this is how it is designed and is working correctly. So - the question. How do I change my SQL to get it to still return everything in the OffNormalInfo table and CaskInfo table even when there is not a corresponding record in the DamageInfo table? In this event I would like it to show the two columns (Damage and Action) with empty strings in the GridView. Any thoughts would be appreciated. Thanks! Tim
Hi, I have hit a brick wall with this. My code is as below
public void fillCustomer() { string connectionString = "server='local'; trusted_connection= true; integrated security=sspi; database='Mrbob'"; System.Data.SqlClient.SqlConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString); string queryString = "SELECT * FROM [Customer] WHERE ([CustomerID] = @CustomerID)"; System.Data.SqlClient.SqlCommand dbCommand= new System.Data.SqlClient.SqlCommand(); dbCommand.CommandText = queryString; dbCommand.Connection = dbConnection; System.Data.IDataParameter param_CustomerID = new System.Data.SqlClient.SqlParameter(); param_CustomerID.ParameterName ="@CustomerID"; param_CustomerID.Value = customerID;dbCommand.Parameters.Add("@CustomerID", SqlDbType.Int); dbCommand.Connection.Open(); System.Data.IDataReader dataReader = dbCommand.ExecuteReader(); dbCommand.Connection.Close(); while(dataReader.Read()) { customerID = dataReader.GetInt32(0); date = dataReader.GetDateTime(1); eposCode = dataReader.GetInt32(2); } dataReader.Close();
}
The error I am getting is
Prepared statement '(@CustomerID int)SELECT * FROM [Customer] WHERE ([CustomerID] = ' expects parameter @CustomerID, which was not supplied.
As you can see from my queryString the @CustomerID parameter is passed in. It seems as if the string is being truncated at 64 characters long. If I remove the paramter to pass the relevant infomration and pass in a customerID I know exists it works.
I am really stumped on this and would really appreciate any pointers
Hi! When I run a select statement, it would retrieve a product description. In some rows, it is long. Consequently, the product description was truncated. Did anybody have resulotion for this issue?
Now I should issue a sql which should extract the CustomerID where UserID in (‘U2’,’U4’) or (U2 or U4) in OtherUsers. i.e I should get the CustomerIDs C2,C4,C6,C8
I have trouble in coming out with the sql. Does anyone have idea how to achieve this?
declare @sql char(500) Select @sql = '"Insert Into T74ACCO Select * From tempdb..##Temp tp Where tp.' + @Key + ' not In (Select ' + @Key + ' From T74ACCO)"'
As I build a record set in an SP I need to add in a string containing a list derived from a second query. I need the results of the sub query to be presented as a single string in the first query, separated by a single space.
I have no idea how to do this in t-sql, and am doing it on the web server at the moment, but becase the dataset is quite large, I'm getting 20 - 40 second processing times which is far too long.
I have found reference to the xp_sprintf function but this is not supported by my host, so not a solution.
I'm no expert in t-sql, so I imagine there's a way somewhere, and would be grateful for any advice available.
Hi I wanna put string in query "where" part.For example,$sql="select VEHICLEFROM databaseWHERE MECHANIC =BRIAN";like above, "mechanic" column is filled with strings. Then how can Iwrite "where" part?Above query does not work.Thanks.
Hi,could you tell me please what are the ways of the query stringencryption in T-SQL ? I would like to have a storage procedure thatencrypts e.g. http://123.23.43.1/pagegen.asp?param1=23¶m2=124 intohttp://pagegen.asp?code=fdgfehiqrzvhe and convert it back to theorginal url.Thanks in advanceOmi
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
As I'm searching for solutions to my problem (see my most recent post before this one if you're interested), I keep coming upon "command-based expressions." I keep seeing examples such as:
= "select a,b from table1 where c=0"
I can't put that in a textbox can I? I tried it and it just spits out the string on report execution, it doesn't actually execute the command.
So what's up? Should that be able to be put right in a textbox? That would solve a world of problems for me if it could be.
I have been attempting to build a search engine that searches a database full of invoices. The user will enter in a invoice number and then the results will be returned if the exact invoice exists and if the record belongs to the user. My first attempt has involved the use of a SQL Datasource to pass a stored procedure based on what is in the search textbox and the information in the cookie about the user. I was hoping that on the btnsearch.click event that I could somehow return the stored procedure in the SQL DataSource. My next step was to make the result (if exists) part of the query string like this: Response.Redirect("~/Invoice.aspx?Invoice=?"+ SQLRETURNEDVALUE)
I'm looking for any help to a possible way to use my idea or any other ideas to get a invoice number and place it into a query string so that the result can be fetched on the next page. Thanks
I can not get this query to work can some on help me? SELECT count(JOBID) AS 'transcount', count(distinct patientid) AS 'patient count', sum(TRANSPORTATION_TCOST) AS 'tcost,sum(TRANSPORTATION_DISC_COST) as ' dtcost ',avg(TRANSPORTATION_DISC) as "avgTDisc",(sum(transportation_tcost) + sum(TRANSPORTATION_DISC_COST)) as "TGrossAMTBilled', (sum(transportation_tcost) / count(DISTINCT patientid)) AS 'PatAvgT', sum(TRANSPORTATION_DISC) AS 'avgPercentDiscT' job.JURSDICTIONFROM job LEFT JOIN payer ON payer.payerid = job.payerid LEFT JOIN states ON job.jurisdiction = states.initialsWHERE (job.transportation = '1') AND ((job.datedcreated = @startdate) AND (job.datecreated = @enddate)) AND states.region = 'GA'GROUP BY job.JURISDICTIONORDER BY PatAvgT DESC
Hi, I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers. Thanks
I have a column name URL in Table1 with data like <a href="/Folder1/view_media_news.cfm?news_media_i=1">August 2002 Factsheet</a> <a href="/Folder1/view_media_news.cfm?news_media_i=149">March 2002 Newsletter </a> i need to grab the news_media_i value by sql query Please any one can help me to get that particular value from string using substring or any other suggestion Thank you in advance
