TableAdapters With Multiple Queries
Jul 18, 2007
Hello everyone...
I have created a report that pulls data from 5 different tables. I have created a tableadapter for this. I have a sql stored procedure with left joins that I am trying to use but keep getting key violations and not null value violations when I try to use it. I have tried setting the NULLValue to NOT throw exception but that has not helped. I have also tried to writing a query for each table. I can add each query to my tableadapter but I can use them all at the same time for some reason. Can this be done? When I go to my report and try to add fields to it from the tableadapter, I only see the results of one query. I am only trying to SELECT. I am not doing any updates or deletes to the tables on the SQL Server.
Any advice would be greatly appreciated.
View 4 Replies
ADVERTISEMENT
May 23, 2008
I moved from using data adapters to tableadapters. This is my sql query that is included in my data adapter
sql = "SELECT f.Date, CPT, CPTModifier, CPTModifier2, Description, Fee, Tax, Balance, [SPatient Number]
FROM Financial f
INNER JOIN Demographics d ON d.[Patient Number]=f.[SPatient Number] WHERE (f.[SPatient Number]= (" & intPID & "))"
conn.ConnectionString = My.Settings.EbtblsConnectionString
da = New SqlDataAdapter(sql, conn)
intPID is an input box that a user types a number in and compares it to SPatient Number on the Financial table. The query has been working fine.
However, when I go into designer view and hit the table adapter's "add query" property, in vs2008. I put that query in, and it gives me an error saying that intPID is not a valid column. I am aware it's not a valid column..because it isn't a column, it's a variable in the windows form. How do I put a variable in the sql string for table adapters?
View 2 Replies
View Related
May 14, 2007
Hi,
I have problem vith implementing Transaction with TableAdapters. My code is:
try
{
using (bookTableAdapter adapter = new bookTableAdapter())
{
transaction = TableAdapterHelper.BeginTransaction(adapter);
ID=(Guid) adapter.bookInsertNew(bookOrgId, bookISBN, bookName, Convert.ToInt32(bookYear), publisherId, languageId, orgLanguageId, bookTranslation, Convert.ToInt32(bookColors), Convert.ToInt32(bookPages), bookFormat, Convert.ToDouble(bookWidth), Convert.ToDouble(bookHeight), Convert.ToDouble(bookWeight), Convert.ToDouble(bookThickness), Convert.ToInt32(bookPrize), bookResumeText, bookResumeHtml, bookPicture_s, bookPicture_m, bookPicture_l, Convert.ToInt32(bookStatusId));
}
using (bookAuthorTableAdapter adapter1 = new bookAuthorTableAdapter())
{
TableAdapterHelper.SetTransaction(Adapter1, transaction);
for (int i = 0; i <= leng1; i++)
{
adapter1.Insert(author[i],ID,1);
}
}
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}finally
{
transaction.Dispose();
}
First Insert is ok, but trow exception on second try to insert in database.
Exceprion is :
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.The statement has been terminated.Source Error:
Line 1839: }
Line 1840: try {
Line 1841: int returnValue = this.Adapter.InsertCommand.ExecuteNonQuery();
Line 1842: return returnValue;
Line 1843: }
I also set Connect Timeout=200 in Web.config but nothing change :(
Please help :( :( :(
View 1 Replies
View Related
Feb 26, 2008
So I just created a very nice dataset class with 30+ tables and relationships for a very large project. Each table has 3-8 associated queries besides the usual Fill/Get and then there will be many more in the BLL that utilize this DAL. I then created a BaseDAL class and other DAL classes that inherit from the BaseDAL(has basic sql query access if needed). What I want to know is if the way I am doing this is correct and if this is a good idea for a database of this size. I've never used TableAdapters before and I don't know if I should be using them at all? I don't know how they handle connections and if one user will have more than one connection open because of using them or will they all share an open connection?
Can someone please direct me. Thank you! Also, if you notice in the top of the UserDAL code I have listed below, I declare and initialize the usersTableAdapter and usersDeptTableAdapter. Should the call to create the object be moved into each function?Public Class Users : Inherits BaseDAL
Private usersDS As OEE_USERSTableAdapter = New OEE_USERSTableAdapter()Private usersDeptDS As OEE_USER_DEPTSTableAdapter = New OEE_USER_DEPTSTableAdapter()Private retcode As Integer = 0
#Region "USER"
' Returns employee ID if user is in system, otherwise returns 0
Public Function ValidateUser(ByVal username As String) As String
'Dim usersDS As OEE_USERSTableAdapter = New OEE_USERSTableAdapter()Dim dtUser As New OEE_USERSDataTable
dtUser = usersDS.GetUser(username)
If dtUser.Rows.Count = 1 ThenReturn dtUser.Rows(0)("EMPLID").ToString
Else
Return "0"
End IfEnd Function
'Add User
Public Function InsertUser(ByVal emplid As String, ByVal username As String) As String
Dim insertedEmplid As String = ""
emplid = usersDS.AddUser(emplid, username)Return insertedEmplid
End Function
Public Function FlagAsInactive(ByVal emplid As String) As Boolean
retcode = usersDS.FlagAsInactive(emplid)Return retcode <> 0
End Function
'Sets inactive flag to null for user (Reactivates them)
Public Function ActivateUser(ByVal emplid As String) As Boolean
retcode = usersDS.ActivateUser(emplid)
Return True
End Function
'Returns list of all usersPublic Function GetUsers() As DataTable
Return usersDS.GetUsers
End Function
#End Region
View 2 Replies
View Related
Dec 11, 2006
Hello. I have been using Table Adapters in VS2005 for the last several
projects and love them. However, when I am connecting to a 2005 SQL
Server database, I have problems connecting. That is, I cannot create
new stored procedures with the TableAdapter Config Wizard. For
instance, I try to create a new query. I use the wizard to create my
SQL statement and continue through until then end of the wizard, when
it asks me to "finish". At this point, I get an error -- This only happens when trying to connect to a 2005 table. Any ideas?
View 1 Replies
View Related
Nov 15, 2007
I've been working with TableAdapters, DAL, and BLL for a few months
now. At this point, most of my new queries are too customized with
multiple tables to fit into any of my existing TableAdapter schema's. For
instance, if I want to execute an aggregate function with group by's
and counts that includes three separate tables, how would I go about
doing this with a strongly typed DAL using TableAdapters. My
workaround to this point is to just create a whole new TableAdapter for
that one query.Is there anyway to extend the DAL in a strongly
typed dataset to create these "read-only" aggregate queries, or should
I continue to create new TableAdapters for each one of these queries?
What's the proper way to handle such customized SQL statements that
don't fit any of my existing TableAdapters?
View 3 Replies
View Related
Nov 15, 2007
Hi
I have two queries, one which takes 1 second to execute and the other which takes less that a second to execute.
I need to join them up similar to the following but when I do it take forever for them to execute.
select *
from table
where column not in (select column from table2)
It is quite complex and I have a couple of views included in the queries but it executes quickly on my dev database and when I run it on the live one it takes forever. Granted there is alot more data on the live database but the individual queries only take a second to run.
Does anyone know why there would be such a time increase whenever I join the queries and run them on my live database?
Any help would be appreciated.
Thanks,
Frankie
View 4 Replies
View Related
Aug 11, 2004
*************************************************************
<code> tags added by moderator. Please use <code> and
</code> tags to bracket comments to make code readable.
*************************************************************
I am a beginner to this whole .net thing so my code is probablly scary, but anyway, I am looking for a way to insert records into a database and then get the last ID. I am sure there is a much better way to do this, but here is the code that I have that ins't quite working:
'SQL Insert data'
Dim Conn As SqlConnection
Dim Rdr As SqlDataReader
Dim strConn As String = AppSettings("doConnect")
Dim strSQL As String = "INSERT INTO USERS (username, password, accountNum) VALUES ('" + Email.Text.Trim() + "', '" + password.Text.Trim() + "', '" + Session("accountNum").Trim() + "')"
Conn = New SqlConnection(strConn)
Dim Cmd As New SqlCommand(strSQL, Conn)
Conn.Open()
Cmd.ExecuteNonQuery()
'SQL Get Last ID
Dim ConnGet As SqlConnection
Dim RdrGet As SqlDataReader
Dim strConnGet As String = AppSettings("doConnect")
Dim strSQLGet As String = "select MAX(id) as maxid from USERS"
ConnGet = New SqlConnection(strConn)
Dim CmdGet As New SqlCommand(strSQL, Conn)
ConnGet.Open()
If CmdGet.ExecuteScalar() <> 0 Then
RdrGet = CmdGet.ExecuteReader()
While RdrGet.Read()
Session("theID") = RdrGet("name")
End While
Else
Session("theID") = 0
End If
View 1 Replies
View Related
Jul 23, 2001
Does anyone know how to count multiple columns in a SQL Query from One table and return numbers I can use the following but don't know how to join the two statements to one.
SELECT COUNT(*) AS [NUMBER OF SERVERS]
FROM DBO.OS
WHERE (OSTYPE LIKE '%SER%')
The other statement is
SELECT COUNT(*) AS [NUMBER OF WORKSTATIONS]
FROM DBO.OS
WHERE (CAPTION LIKE '%9%') OR (CAPTION LIKE '%PRO%') OR (CAPTION LIKE '%ME%')
If any one has any idea's please reply or email me directly. I would greatly appreciate it.
View 1 Replies
View Related
Jul 23, 2005
I'm writing an ASP page for a project and it requires multiple queries.However, I'm trying to combine multiple SELECT statements, but can'tfigure out a way that actually works.Basically, here are the SELECT statements I want to combine:SELECT * FROM schoolrequest WHERE SLid=10SELECT SLlastName FROM academicofficeWHERE schoolrequest.SLsendToId=academicoffice.AOidSELECT SLlastName FROM academicofficeWHERE schoolrequest.SLbillToId=academicoffice.AOidSELECT SLlastName FROM academicofficeWHERE schoolrequest.SLrequestorId=academicoffice.AOidSELECT * FROM diaryentry WHERE diaryentry.DEkeyValue=10AND diaryentry.DEdeletedDate IS NULLThe academicoffice table just contains basic contact info, but needs tobe used 3 times in the query to get specific contact info for 3different contacts(SLsendToId, SLbillToId, SLrequestorId)The diaryentry table contains info entered in by students. TheDEkeyValue is actually the primary id of the schoolrequest table(SLid).Any ideas?Thanks,M
View 4 Replies
View Related
Dec 8, 2007
hello
Could you please help me with this problem?
I have a stored procedure like this:
ALTER PROCEDURE dbo.UniqueChannelName
(
@UserName nvarchar(50),
@ChannelName nvarchar(50)
)
AS
return 5;
Then inside of my dataset, I added a new query(dataset1.QueriesTableAdapter) to handle above mentioned stored procedure. Properties window is showing that return type of this adapter is of type int32 as we expected to be.
now I want to use it inside of my code:
DataSet1TableAdapters.QueriesTableAdapter b = new DataSet1TableAdapters.QueriesTableAdapter();
int i;
i=Convert.ToInt32( b.UniqueChannelName("Ahmad", "test"));
as you may guess, the return type of b.UniqueChannelName("Ahmad", "test") is object and needs to be type-casted before assigning it's value to i; but even after explicit type casting, the value of i is always set to 0, not 5.
could you please show me the way?
many thanks in advance
View 19 Replies
View Related
May 25, 2006
The test sub below operates on a SQL Server Table with an xml-type field ("xml"). The purpose of the sub is to learn about storing and retrieving a whole xml document as a single field in a SQL Server table row.When the code saves to the xml field, it somehow automagically strips the xml.document.declaration (<?xml...>). So when it reads the xml field back and tries to create an xmldocument from it, it halts at the xmldocument.load.I order to get the save/retrieve from the xmlfield to work, I add the <?xml declaration to the string when I read it back in from the xml field (this is in the code below).At that point the quickwatch on the string I'm attempting to load into the xmldocument is this:-----------------------------------------------------<?xml version="1.0" encoding="utf-16" ?><Control type="TypeA"><Value1><SubVal1A>Units</SubVal1A><SubVal1Btype="TypeA">Type</SubVal1B></Value1><Value2><SubVal2A>Over</SubVal2A><SubVal2B>Load</SubVal2B></Value2></Control>-----------------------------------------------------The original xml document string is this:-----------------------------------------------------<?xml version="1.0" encoding="utf-16" ?><Control type="TypeA"> <Value1> <SubVal1A>Units</SubVal1A> <SubVal1B type="TypeA">Type</SubVal1B> </Value1> <Value2> <SubVal2A>Over</SubVal2A> <SubVal2B>Load</SubVal2B> </Value2></Control>-----------------------------------------------------which seems to have all the same characters as the quickwatch result above, but clearly is formatted differently because of the indenting.THE FIRST QUESTION: Is there a simpler way to do this whole thing using more appropriate methods that don't require adding the xml.document.declaration back in after reading the .xml field, or don't require using the memorystream to convert the .xml field in order to load it back to the XML document.THE SECOND QUESTION: Why does the original document open in the browser with "utf-16", but when I write the second document back to disk with "utf-16" it won't open...I have to change it to "utf-8" to open the second document in the browser.Here's the test sub'============================================ Public Sub XMLDSTest() '=========================================== Dim ColumnType As String = "XML" '=========================================== '----------Set up dataset, datatable, xmldocument Dim wrkDS As New DSet1() Dim wrkTable As New DSet1.Table1DataTable Dim wrkAdapter As New DSet1TableAdapters.Table1TableAdapter Dim wrkXDoc As New XmlDocument wrkXDoc.Load(SitePath & "App_XML" & "XMLFile.xml") Dim str1 = wrkXDoc.OuterXml Dim wrkRow As DSet1.Table1Row wrkRow = wrkTable.NewRow '=======WRITE to SQL Server============== '------ build new row With wrkRow Dim wrkG As Guid = System.Guid.NewGuid TestKey = wrkG.ToString .RecordKey = TestKey .xml = wrkXDoc.OuterXml '<<< maps to SQL Server xml-type field End With '----- add row to table and update to disk wrkTable.Rows.Add(wrkRow) wrkAdapter.Update(wrkTable) wrkTable.AcceptChanges() '----- clear table wrkTable.Clear() '=======READ From SQL Server ============== '----refill table, read row, wrkAdapter.FillBy(wrkTable, TestKey) Dim wrkRow2 As DSet1.Table1Row = _ wrkTable.Select("RecordKey = '" & TestKey & "'")(0) '===== WRITE TO New .xml FILE =========================== Dim wrkS1 As New StringBuilder Select Case ColumnType Case "XML" '---if xml build xml declaration: '---add this to xml from sql table => <?xml version="1.0" encoding="utf-16" ?> wrkS1.Append("<?xml version=" & Chr(34) & "1.0" & Chr(34)) wrkS1.Append(" encoding=" & Chr(34) & "utf-16" & Chr(34) & " ?>") wrkS1.Append(wrkRow2.xml) End Select Dim wrkBytes As Byte() = (New UnicodeEncoding).GetBytes(wrkS1.ToString) Dim wrkXDoc2 As New XmlDocument Dim wrkStream As New MemoryStream(wrkBytes) wrkXDoc2.Load(wrkStream) '=========================================== '---- this just shows that the file actually was touched Dim wrkN2 As XmlNode = wrkXDoc2.CreateNode(XmlNodeType.Text, "ss", "TestNode2") wrkN2 = wrkXDoc2.SelectSingleNode("//Value1/SubVal1B") wrkN2.Attributes("type").Value = "This was from the xml field" '---------------- '------ update the encoding....otherwise the file won't open in the browser with utf-16 Dim wrkN1 As XmlNode = wrkXDoc2.CreateNode(XmlNodeType.Element, "ss", "TestNode") wrkN1 = wrkXDoc2.FirstChild wrkN1.InnerText = Replace(wrkN1.InnerText, "utf-16", "utf-8") '------------Now write the file back as an .xml file Dim wrkFilePath As String = SitePath & "App_XML" & "XMLFile2.xml" Dim wrkXW As XmlWriter = XmlWriter.Create(wrkFilePath) wrkXDoc2.WriteContentTo(wrkXW) wrkXW.Close() End Sub===============================
View 8 Replies
View Related
Mar 3, 2008
I get a wo_ID and want to query company data. I have the following tables, work_orders (has wo_ID, wo_name and install_id), install_ids (has install_id, comp_id) and customers (comp_id, company_name). The query process goes like this: I get a wo_ID and I need to find the install_id that corresponds with that wo_ID in that same work_orders table. Then take that install_id and find out the comp_id that corresponds with that install_id from the install_ids table. Finally take that customer_id and link it to comp_id in the customers table to find out company data. I need to join it multiple times to get my final data. I have a simple one that works right now with one join and using the install_id, but I don't know how to expand it with multiple joins. Thanks.
View 4 Replies
View Related
Apr 14, 2008
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below./* will remove for aspx page use */USE Database/* these params are on the page in drop down boxes*/DECLARE @ProductID int;DECLARE @BuildID int;DECLARE @StatusID int;/* static params for this sample */SET @ProductID = -1;SET @BuildID = -2SET @StatusID = -3/*the query that will build the datagrid. currently this runs and produces three different result sets.How do I combine these statements so they produce a single set of results? */IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
View 12 Replies
View Related
Dec 25, 2014
If you have a dozen or so queries related to the same thing such as an exception report do you wrangle with the query to make it a one stop shop or do you do something different? Is it possible to drop them into a SP and have them all execute at the same time?
I am running queries for against business rules and wanted to do it the right way. I don't have a solid mentor at work, two guys who are experienced and off a lot but not in the world of reports.
View 8 Replies
View Related
Apr 14, 2008
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below.
/* will remove for aspx page use */
USE Database
/* these params are on the page in drop down boxes*/
DECLARE @ProductID int;
DECLARE @BuildID int;
DECLARE @StatusID int;
/* static params for this sample */
SET @ProductID = -1;
SET @BuildID = -2
SET @StatusID = -3
/*
the query that will build the datagrid. currently this runs and produces three different result sets.
How do I combine these statements so they produce a single set of results?
*/
IF (@ProductID = -1) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))
IF (@BuildID = -2) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))
IF (@StatusID = -3) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
View 15 Replies
View Related
Mar 30, 2008
Hi,
I have the below query that has multiple select statements from the same query. Is it possible to run this more efficiently with a SUM/ CASE statement so that its just one SELECT ?
I just used a SUM(CASE WHEN ....... statement on another query, but I am having trouble figuring out how to integrate it on this one, and if its even possible or worth it.
Thanks very much for any help!!
mike123
CREATE PROCEDURE [dbo].[select_mailbox_Count]
(
@userID int
)
AS SET NOCOUNT ON
SELECT count(*) as totalInbox,
(SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 0 and deletedbyRecipient =0 ) as totalInbox_UnRead,
(SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 2 and deletedbyRecipient =0 ) as totalInbox_UnReplied,
(SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID AND deletedByRecipient = 0 AND deletedBySender = 0 ) as totalOutbox,
(SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID or messageTo =@userID AND deletedByRecipient = 0 and deletedBySender = 0 ) as totalTrash
FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND deletedByRecipient = 0
View 13 Replies
View Related
Dec 2, 2005
I'm cleaning data which involves updating ~12 million rows with threedifferent models, progressively. First clean values using the modelwith finest granularity, then the remainder with the next model,finally what's left using the last model. The first model sets ~1/2 ofthe rows, the second ~1/4, the third ~1/5, and the remaining 5% don'tget updated.It's something like this:UPDATE t SET value=value*m.AdjustmentFactor, updateFlag='updated'FROM Table t JOIN Models m ON ....WHERE m.ModelID='first model' AND t.updateFlag IS NULLStart with 'first model' then 'second model' etc.I'm wondering what happens if I submit all three queries together, oras three separate submissions, waiting for the one before to complete.If I do them all as one group, the query planner might plan for thesecond and third updates based on the initial distribution of values.However, the first update removes half of the rows from consideration,so it seems to me a new plan should be prepared for the second query,based on the distribution at that time. If I highlight the queries inQuery Analyzer and execute, are all three plans created at thebeginning? Does putting GO between them (which I do) make anydifference?This is SQL Server 2000.Thanks,Jim
View 2 Replies
View Related
Dec 31, 2007
I've written a SQL script to go in and pull student grades for honor roll. The first subquery in the script eliminates any students that have any D's, and the second sub query is supposed to give me a only students that have no more than 1 C of any type as the Honor Roll criteria is that a student has to have a GPA between 3.0 and 3.49 and no more than 1 C.
If I run each individual sub query by itself they work and pull the students that they are supposed to. If I run the main query along with the first sub query to eliminate students with D's everything works. If I run the 2nd sub query by itself it will pull the students that meet the criteria. If I run the main query along with both sub queries or the main along with the 2nd sub query it doesn't work.
Here is what the query looks like that I'm trying to run.
Thanks in advance for any help.
select distinct i.personid, i.lastname + ',' + ' ' + i.firstname as student, p.studentnumber, e.grade,
t.name as Term, gs.score, c.name as course, sec.teacherdisplay
from gradingscore gs
JOIN [identity] i on i.personid = gs.personid
JOIN person p ON p.personid = i.personid
JOIN enrollment e on e.personid = p.personid
JOIN term t on t.termid = gs.termid
JOIN v_TermGPA tg on tg.personid = gs.personid and tg.calendarid = gs.calendarid
JOIN v_GpaTermCalc tc ON tc.personid = gs.personid and tc.calendarid = gs.calendarid
JOIN section sec on sec.sectionid = gs.sectionid
JOIN course c on c.courseid = sec.courseid
where gs.calendarid = 20 and t.name = '2nd 6wk' and tc.gpa between 3.0 and 3.49 and
e.enddate is null
--Sub Query to eleminate students with any D's in scores
and p.personid not in
(select distinct gs1.personid from gradingscore gs1
join Term t1 ON t1.termid = gs1.termid
join v_TermGPA tg1 ON tg1.personid = gs1.personid
where gs1.calendarid = 20 AND t1.name ='2nd 6wk'and
tc.gpa >=3.0 AND tc.gpa <= 3.49
and gs1.score IN('D+','D', 'D-')
--Sub Query to eliminate students with more than 1 C of any type in scores.
and gs.personid not in
(select distinct gs2.personid from gradingscore gs2
join Term t2 ON t2.termid = gs2.termid
join v_TermGPA tg2 ON tg2.personid = gs2.personid
where gs2.calendarid = 20 AND t2.name ='2nd 6wk'and tc.gpa >=3.0 AND tc.gpa <= 3.49
and gs2.score IN('C+','C', 'C-')
group by gs2.personid
HAVING count(gs2.score)<=1))
order by i.[student]
View 6 Replies
View Related
Feb 26, 2008
Hi,
I have some questions about making muliple T-SQL queries againt an SQL-server. I retrieve all rows from an SQL-server table called ActivityGroup and add the result to a Radio Button List. Suppose, I also wish to add the total sum of every "ActivityGroup" value to the Radio Button List. I guess this ought to be done using a SELECT COUNT statement and then retrieved using the Executescalar method?
The question is do I need to copy the code for the SELECT ('*) statement and make the neccessary changes for the SELECT COUNT statement and Executescalar method or is there a more simple way? Is it possible to use two command statements in the same code block?
The total sum is going to be calculated using the values from both an SQL-server and an Access database. The OleDb sample below is what I refer to as a code block. I appreciate any help!
string connectionString = ConfigurationManager.ConnectionStrings["ServetteConnectionString"].ConnectionString;
string sQuery = "SELECT * FROM Aktivitetsgrupper where aktivitetsid = " + Request["AktivitetsId"];
OleDbConnection oOleDbConnection = new OleDbConnection(connectionString);
oOleDbConnection.Open();
OleDbCommand command = new OleDbCommand(sQuery, oOleDbConnection);
OleDbDataReader reader = command.ExecuteReader();
int test = 12;
while (reader.Read())
{
rblAktivitetsgrupper.Items.Add(new ListItem(String.Format("{0}, platser", reader["aktivitetsGruppNamn"]), reader["aktivitetsGruppID"].ToString()));
}
View 4 Replies
View Related
May 27, 2008
In Sql 2005, I can't get this to work. I am getting an error that says SQL does not support subquery. SQL has a problem with my subquery. It works fine with one subquery, but adding multiple gives me an an error.
INSERT INTO STU_SUMMARY
(SUMMARY_PERIOD,
CHILD_COUNT, NO_DATA_ERRORS, PROG_CD_DESCREP, TOT_CURR_SPEDSTU)
VALUES (GETDATE(),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
)
View 1 Replies
View Related
Dec 15, 2006
Hi,
I'm having problems manipulating the results from multiple count(*) queries.
I have a table 'RECOMMENDATIONS' holding a field called 'SCORE'. This field holds a number from 1 to 10 and has no null or zero figures.
I currently have the following three queries:
1) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)
2) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
3) SELECT COUNT(*) FROM RECOMMENDATIONS
I now need to combine these three queries so that i can divide and multiply the resulst: (query 2/query 1)*query 3
My initial idea was:
SELECT (COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)))* (SELECT COUNT(*) FROM RECOMMENDATIONS) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
... but this gives a result of "0". I then stripped out the multiplication section to see if the divide was working:
SELECT COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
... and again the result was "0"
Please could someone help me!!
Ian
View 2 Replies
View Related
Jan 4, 2015
I was messing around with stored procedures and I was wondering if creating a SP that populates a single table for reporting is a good idea?
I have ~10 queries that I have to currently run manually and was hoping to drop them into a physical table and then leverage that single table to pull into excel.
Some of my queries use virtual tables or CTE's, this is to get the aggregate set correctly.
Essentially I am working out of a data warehouse and would like to eventually get all my queries in one SP or something similar and then call that query for a insert.
Speaking of which could you create a SP that has several selects than with that output drops the records into a single table by using an insert into query so the data from the all the queries would line up into the right columns?
View 1 Replies
View Related
Oct 17, 2013
I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.
I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?
select
clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type = 'CC'
group by clientnumber;
OUTPUT currently looks like this:
1234 2 0 0
1234 0 1 0
1234 0 0 4
Is it possible to result in this, along with a flag with the criteria above?:
1234 2 1 4 Y
View 3 Replies
View Related
Jul 10, 2007
Hi everyone,
I am reading about the Rollback transaction but I'm not sure if it's the feature I need.
My application is going to update a few tables, but my programmer said that it will be done using more than 1 transaction.
For example, if I want to create a new employer with all the detailed informations, tables will be filled when the user complete the insertion of a part of data. If he decides to abort the operation, i'd like to delete all the values inserted before with the other queries. Is it possible to create a "savepoint" and roll back all transactions processed from this savepoint?
thank you!
View 6 Replies
View Related
Jul 23, 2005
HiI am trying to write two Select * statements to the same text fileusing bcp (from a stored procedure).But cannot find a way of appending to a file using bcp.Does anyone know if this is possible or is there another way of writingmultiple queries to a file from a stored procedure?ThanksCaro
View 2 Replies
View Related
Sep 15, 2005
Greetings,I have what seems a simple problem....I want to save the output from the following queries in 1 file in'SQL Query Analyser', but I can only save each 'grid' separately.I have about 30 queries in total. 'Messages' tells me the number ofrows affected but I need the data.So can I save all the grids together at the same time in 1 file ?ThanksMikePRINT 'HEAT 3510'SELECT f.persfirstname AS First_Name,f.perslastname AS Last_Name,a.rsrchqnumber AS Personal_nr,b.asgtassignmentid AS Contract_nr,ISNULL(c.tishcode,'') AS TS_Id,ISNULL(d.tpitpayrollcode,'6900') AS Pay_Code,d.tpitdaydate AS Payroll_Date,c.tishactualenddate AS TS_end_dateFROM resources aINNER join assignments b ON b.asgtrsrcguid = a.rsrcguidINNER join timesheets c ON c.tishasgtguid = b.asgtguidINNER join users e ON e.userguid = a.rsrcuserguidINNER join persons f ON e.userpersguid = f.persguidLEFT OUTER join timesheetpayrollitems d ON d.tpittishguid =c.tishguidWHERE a.rsrchqnumber = 80000147and b.asgtassignmentid = 0000001234and c.tishactualenddate > '20050614'and (d.tpitdaydate > '20050614'or d.tpitdaydate IS null)order by d.tpitdaydateSELECT f.persfirstname AS First_Name,f.perslastname AS Last_Name,a.rsrchqnumber AS Personal_nr,b.asgtassignmentid AS Contract_nr,ISNULL(c.tishcode,'') AS TS_Id,c.tishactualenddate AS TS_end_date,g.thisdaydate AS TSH_dateFROM resources aINNER join assignments b ON b.asgtrsrcguid = a.rsrcguidINNER join timesheets c ON c.tishasgtguid = b.asgtguidINNER join users e ON e.userguid = a.rsrcuserguidINNER join persons f ON e.userpersguid = f.persguidINNER join timesheethistory g ON g.thistishguid = c.tishguidWHERE a.rsrchqnumber = 80000147and b.asgtassignmentid = 0000001234and c.tishactualenddate > '20050619'order by g.thisdaydatePRINT 'HEAT 3213'
View 3 Replies
View Related
Dec 7, 2007
I am currently using enterprise manager to run multiple queries on asingle table in a DB. I refresh these queries every few minutes. Dueto the huge number of them I was looking for a better way (or should Ijust say "a way") to manage/save these queries so I can recall themeasier/faster for monitoring purposes. Suggestions?TIA.
View 4 Replies
View Related
Jul 20, 2005
I have the following fieldname in an Access_Table:Field 1 = Cust_ID (Primary key)Field 2 = DateField 3 = DescriptionField 4 = Inv_NoField 5 = AmountMy SQL Input syntax are:"Select distinct Cust_ID,Date,Description,Inv_No,Amount fromAccess_Table WHERE Date <Now()-30 And Date >Now()-60 AND Date =Main.Date"It failed to generate the result I want,anywayMy "Required" Output is:Cust_ID Date Description Inv_No >30DYS >60DYS8000 21/05/2004 PC RAM 2008000 26/06/2004 Modem 180---------------------------------------------------------------Total: 180 200---------------------------------------------------------------8001 22/04/2004 Cable 508001 23/05/2004 HD 210---------------------------------------------------------------Total: 260---------------------------------------------------------------Grand Total: 180 460can anyone help? ThanksFrom:Cady Steldyn*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jan 26, 2007
Hi there,
I am new to ssis.I need to execute multiple queries to update number of tables in sqlserver.Pleasesuggest me the right way to do this.
Thanks in Advance.
Regards
Vinod
View 4 Replies
View Related
Oct 4, 2007
Hey guys,
In my DB i have hundreds on queries setup for all different reporting purposes..
We have just changed they way our system handles costing and are moving from a average cost function to a standard cost function.. This in turns, means that i need to replace any reports where i use the average cost field and replace it with the standard cost field.
Is it possible, that i can do a full search and replace over all my queries, looking for a specific field name and replacing it with something else.
I am dreading the idea of opening each individual queries and checking if it exists..
If anyone knows of software or someway to write a SP to accomadate this, please let me know. I would be most grateful.
Thanks
Scotty
View 3 Replies
View Related
Feb 13, 2008
I have a situation where I need to do some reporting for simular asset records but the data is listed on different database from the same server.
I was curious as to the way to write the queries that I will need.
Lets say I have 3 databases and I am looking for a asset of some type and the ID is a serial number.
The serial will be on the main table from each database. Is there a way I can set a relation from a view between these records?
I.E.:
1: Database A -DSNA_tblMaiin - Serialnumber
2: Database B -DSNB_tblMaiin - Serialnumber
3: Database C -DSNC_tblMaiin - Serialnumber
I believe that this is a dot notation question.
Thanks in advance,
Gene
View 8 Replies
View Related
Jul 22, 2004
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)
SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
View 5 Replies
View Related
