I am trying to create an asp page that will update an Access 2000 database. I need to update records if the user exists and create a new record if the user does not exist. Most of the variables are pulled from a separate "post" form.
I am using 2 pieces of info to find duplicates, as employee numbers can be assigned to multiple employees. Right now I have the page check for a duplicate employee id number then check for a duplicate last name. Unfortunately it is running each check separately, so if the last name is duplicated anywhere, it is sending a duplicated value.
here is the chunk of code in question... all RF_variables are request.form variables
cnt="SELECT COUNT(emp_id) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE emp_id='" & RF_emp_id & "'"
set again=conn.Execute(cnt)
dup=again("Xnum")
if dup>=1 then
cnt="SELECT COUNT(lname) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE lname='" & RF_lname & "'"
set again=conn.Execute(cnt)
dupl=again("Xnum")
if dupl=1 then
upd="UPDATE " & RF_course & " SET "
upd=upd & "section" & RF_section & "='" & RF_score & "'"
upd=upd & " WHERE emp_id='" & RF_emp_id & "'"
upd=upd & " AND lname='" & RF_lname & "'"
on error resume next
conn.Execute upd
else
ins="INSERT INTO " & RF_course
ins=ins & " (lname,fname,emp_id,cname,"
ins=ins & "section" & RF_section & ")"
ins=ins & " VALUES "
ins=ins & "('" & RF_lname & "',"
ins=ins & "'" & RF_fname & "',"
ins=ins & "'" & RF_emp_id & "',"
ins=ins & "'" & RF_cname & "',"
ins=ins & "'" & RF_score & "')"
on error resume next
conn.Execute ins
end if
else
ins="INSERT INTO " & RF_course
ins=ins & " (lname,fname,emp_id,cname,"
ins=ins & "section" & RF_section & ")"
ins=ins & " VALUES "
ins=ins & "('" & RF_lname & "',"
ins=ins & "'" & RF_fname & "',"
ins=ins & "'" & RF_emp_id & "',"
ins=ins & "'" & RF_cname & "',"
ins=ins & "'" & RF_score & "')"
on error resume next
conn.Execute ins
end if
Hopefully this is understandable.
If anyone can offer any help I would greatly appreciate it.
My SQL is very basic. How do I create a query that will accept a parameter, an integer, and based on the integer, locate all the matches in a db? SELECT COUNT(*) AS Expr1, tblArticle.ArticleIDFROM tblArticle INNER JOIN tblArticleCategory ON tblArticle.ArticleCatID = tblArticleCategory.ACategoryIDGROUP BY tblArticle.ArticleID This isn't setting up the query to request a parameter.What am I doing wrong here? I"m trying to get the total number of articles for a particular category ID.
I've been able to get this select query to work, but I'm not sure how to modify it to turn it into a DELETE query: Â Â USE QSCTestENG select p.[testid], COUNT(c.[testid]) FROM [dbo].[tblTestHeader] p left outer join [dbo].[tblTestMeasurements] c ON p.[testid]=c.[testid] where p.[model] = 'XPPowerCLC125US12' group by p.[testid] having COUNT(c.[testid]) <>48;
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0 IF (@_Stat_Count >0) BEGIN SELECT @Status = status_cd from status-table where status_id = 10 END
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.
I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.
if n1<10 and n2<10 then i=i + 1 if n4-n3=1 then i=i + 1 if more than 2 consecutive numbers then i=i + 1
So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.
declare @test1 int set @test1=0 select top 10 n1,n2,n3,n4,n5,n6, case when ( n1=2 and n2>5 ) then @test1+1 else @test1 end as t2 from allNumbers
In the database, there is Date, Store#, Item#, and %Total Sales. In some cases, the same item# for the same date may be given more than one value for '% of Total Sales'. (For some reason this is a valid business scenario that happens rarely, but it happens.)
In that situation only, the requirement is to sum the two values together into one line. So if Item# 123 has a line with a value of .05%, and another line with a value of .08%, I need to sum those two values into one line for Item #123 that has a %Total of .13%. ONLY when an item has more than one percentage assigned, those percentages should be summed. Otherwise, if an item# has only one percentage value assigned, we just want to see that value.
Basically, I would like to implement logic that would work like this:
SELECT Date, Store#, Item#, CASE WHEN Count(%Total Sales) >1 THEN Sum(%Total Sales) ELSE %Total Sales END
FROM (some tables and joins) GROUP BY Date, Store#, Item#
However, I'm not sure how to craft it so that I don't get a syntax error (this query produces errors).
Hi~, I have 3 questions about memory based bulk copy.
1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)? For example, how much insert row at below sample?(the max value of nCount) for(i=0 ; i<nCount ; i++) { pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData)); }
2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)
3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ? BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);
------------------------------------------------------- My solution is like this. Is it correct?
// CoCreateInstance(...); // Data source // Create session
I have x amount of tests for a person on a particular day. There are criteria to invalidate a test. I can filter all the bad tests away, but now I need to only accept 2 valid tests per day, even if there are more tests. The tests are filtered according to person ID and then put in order by date. I need to accept the first 2 valid test based on the criteria and reject the others. Any suggestions
I have a fact table with a create time dimension and an expiration time dimension. I'd like to have a calculated member that would compare the (count for create time) / (count with that expiration time). I already have these counts as measures.
I would be able to put the create time dimension in the "row fields" area, and see the ratio (calculated above) over the different create time periods.
Can someone point me in the right direction on how I would create that kind of calculated member? What would the MDX look like?
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.
Update CurrRank = ??? from tblAppKitOffers where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D') ORDER BY tblAppKitOffers.OfferAmount Desc
We have customer accounts that we measure usage. We want to run a report for all customers whose current usage is 0 and a count of how many months it has been zero consecutively. Here is an example.
I have the table below and want to show the prop_code if the rent_review_date count is less than 1 in 12 months. This means to show only propcode if there has not been any rent update since the first rent_review_date
DECLARE @table TABLE ( Prop_Code INT ,Current_Rent INT ,Revised_Rent INT ,Rent_Review_Date varchar(10) ,Rent_Review_Time DATEtime)
I have conducted a thorough search in the forums and cannot quite find my answer. I have a date field called open_date. If the open_date is more than 30 days old, I need to count it. I have started with the following code:
SELECT 'Older_Than_30Days' = CASE WHEN open.date >= 30 THEN '1' ELSE '0" END
I need to create a query that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd, bnb_newcases_salesd etc)
Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)
MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is null
BNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC')
Ok... I know this is something that I've seen and probably done before....
I have a value that will show up multiple times in a table. I want to order the table by that value and then count up from 1 to however many items that it shows up as.
The example below shows the results I want. Value1 and Value2 are in the table, LineNumber is what I need to generate.
I'm hoping someone can help with with a task I've been given. I need to write a trigger which will act effectively as a method of automatically distributing of incoming call ticket records. See DDL below for creation of the Assignment table, which holds information on the call ticket workload.
SELECT COUNT(CallID) AS [Total Calls], AssignmentGroup, Assignee FROM #Assignment GROUP BY AssignmentGroup, Assignee ORDER BY COUNT(CallID) DESC , AssignmentGroup, Assignee
What I need to do is write a trigger for on INSERT to automatically update the Assignee column with the name of the person who currently has the least active calls. For example, using the data above, the next PC Support call will go to Mickey Mouse, and the next two Service Desk calls will go to Jim Smith.
So, the logic for the trigger would be
UPDATE #Assignment SET Assignee = (SELECT Assignee FROM #Assignment WHERE COUNT(CallID) = MIN(COUNT(CallID))
But that's only the logic, and obviously it doesn't work with the syntax being nothing like correct.
Does any one have an idea or pointers as to how I should go about this?
i have this function it return 0 but the sql statement in the sql query return the right number?how is that i want to get the number of records any other idea or fix? Public Function UserAlbumPhotoQuota(ByVal userID As Integer) As BooleanDim Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString) Dim strSQL As StringDim dr As SqlDataReader strSQL = "SELECT *, (select count(*) from userAlbumPic where userID=" & userID & ") as rec_count from userAlbumPic "Dim cmd As New SqlCommand()cmd = New SqlCommand(strSQL, Conn) Conn.Open() dr = cmd.ExecuteReader() dr.Read()userQuota = dr("rec_count").ToString Conn.Close() End Function
Hello All, I'm wondering if you guys can help me with a problem to count every record in a table; however, I must match this table with another table to get the category names. I have tried this SQL statement in the SQL Express and it works very great. SELECT aspnet_Category.CategoryName, COUNT(*) AS Expr1FROM aspnet_Category INNER JOINaspnet_resources ON aspnet_Category.ApplicationID = aspnet_resources.ApplicationId AND aspnet_Category.CategoryID = aspnet_resources.CategoryIDGROUP BY aspnet_Category.CategoryNameORDER BY aspnet_Category.CategoryName However, when I tried to put this into my code, it gives me a error. <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, True)> _Function GetDataByCount_CategoryID() As CategoryDataTableGetDataByCount_CategoryID = Adapter.GetDataByCount_CategoryIDEnd FunctionFailed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. Can you please help me to fix this error? Thank you, Vic.
I need to export records to a text file and simply index them. i.e. in the pipe delimited file I need a column with the ordinal value of each row. Example:
1|"Jane Doe"|"23 Western ave"|... 2|"Jamie Delom"|"5 East Street|... 3|"Nat Girshon"|"5678 Main Street|... . .
Would anyone be able to tell me how I could build this within the SQL query without creating a physical table and using IDENTITY functions?
Hi i am just an beginner with sql and i am quite sure this will be a simple question i am looking for a methode to count the number of records how can i do this?
im trying to display a category name in one column and then the number of books in that category in another column.
SELECT CATEGORY, COUNT(DISTINCT CATEGORY) "Category Total" FROM BOOKS; Gives me an error saying something about category not being a single group function. What am I doing wrong?
Because of a limitation on a piece of software I'm using I need to take a large varchar field and force a carriage return/linebreak in the returned sql. Allowing for a line size of approximately 50 characters, I thought the approach would be to first find the 'spaces' in the data, so as to not split the line on a real word. achieve.
--===== Simulate a passed parameter DECLARE @Parameter VARCHAR(8000) SET @Parameter = (select a_notes from dbo.notestuff as notes where a_id = '1')
I have the following: Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapter Dim dF As Data.DataTable = ResourceAdapter.GetDataByDistinct Dim numRows = dF.Rows.Count Dim dS As Data.DataTable = ResourceAdapter.CountQuery Dim sumRows = dS.Rows.Count DataList1.DataSource = dF DataList1.DataBind() LblCount.Text = numRows LblSum.Text = sumRows numRows is the number of records in a particular category. CountQuery is a method that returns the total number of records in all the categories. I don't know how to get the result of that query, the code line in bold isn't right. How do I get this number?Diane
Hello, I am writing a piece of code in ASP.NET and I'd like to get the # of records on a table and used this code: Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='G:Aco ProntoBSCBSC_v1.mdb'"Dim Con As New OleDbConnection(ConnString) Dim Cmd As New OleDbCommand("SELECT COUNT(*) AS Expr1 FROM Metricas", Con)Dim reader As OleDbDataReader Con.Open() reader = Cmd.ExecuteReader()Dim NumMetr As Integer = Val(reader("Expr1")) reader.Close() Con.Close()
I am getting an error that that's no data in the table. Any suggestions?
Hello, I am having problems with this query below: 1 SELECT Table1.Email AS Email, 2 Table2.UserName AS Username, 3 Table3.Members_Paid AS Paid, 4 (SELECT DISTINCT COUNT(*) 5 FROM Table3 AS e JOIN Table3 AS m 6 ON e.Members_Sponsor = m.Members_ID 7 WHERE (e.Members_Sponsor = m.Members_ID)) AS TotalRecords 8 FROM Table1 INNER JOIN 9 Table2 ON Table1.UserId = Table2.UserId INNER JOIN 10 Table3 ON Table2.UserId = Table3.UserID 11 WHERE (Table3.Members_Sponsor = @UserId)Basicly what I am trying to do is get all members that belong to a certain manager along with those members count total of members they have below them.The code above is giving me the count of the first member only, not different counts for each member.Hope you understand what I am trying to say and do here. Hope someone can help me out cause this hase been driving me crazy for a few days now.
