Posts by cgyrob

Forgot the group by

Select Student,sum(case When Credit > 2 and Score > 10 Then 1 else 0 end) as NumPasses    
From MyTable
Group by Student

Try This

1. Select Student,sum(case When Credit > 2 and Score > 10 Then 1 else 0 end) as NumPasses    
2. From MyTable

I know it has been awhile but I solved this awhile ago. The problem was with the datagridview control data accessor on one of my pages. I had to change to the odp.net accessor and the cursor problem went away. The only draw back was that I had to build all the methods for the functionality from the control that I wanted manually. No built in sorting or paging. The fun of mixing Microsoft and Oracle products.

Connection String

<add name="DWSTAGE" connectionString="Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = ProdServer)&#xA;            (PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = Servicename)));Password=password;User ID=user"/>

This method is the main accessor of the DB.

void PopulateDataTable()
        {
            string query = buildSQL();
            TableGridView.Columns.Clear();
            Table = new DataTable();

            string TableName = Session["TableSelected"].ToString();
            string Entity = Session["entity"].ToString();

            OracleConnection con = null;
            OracleCommand cmd = null;
            OracleDataAdapter da = null;
                        

            try
            {
                con = new OracleConnection(ConfigurationManager.ConnectionStrings["DWSTAGE"].ConnectionString);
                con.Open();
                cmd = new OracleCommand(query, con);
                cmd.CommandType = CommandType.Text;
                da = new OracleDataAdapter(cmd);                

                da.Fill(Table);

                if (Table.Rows.Count > 0)
                {
                    DataView dv = Table.DefaultView;

                    if (this.ViewState["SortExpression"] != null)
                    {
                        dv.Sort = string.Format("{0} {1}", ViewState["SortExpression"].ToString(), this.ViewState["SortOrder"].ToString());
                    }

                    TableGridView.DataSource = Table;
                    TableGridView.DataBind();
                }

                if (Session["Search"].Equals(1))
                {
                    if (Table.Rows.Count < 1)
                    {
                        Session["Search"] = 0;
                        Literal1.Text = "<Script lang=JScript> alert('No results found with your search criteria.');</Script>";
                        txtSearch.Text = "";
                        txtSearch.Focus();
                        PopulateDataTable();
                    }
                }
            }
            catch (Exception ex)
            {
                msg_lbl.Text = ex.Message;//(ex.ToString()).Substring(40,200);
                MsgPanel.Visible = true;
                return;
            }
            finally
            {
                cmd.Dispose();
                da.Dispose();
                con.Close();
                con.Dispose();
            }

        }

I have an app where I keep getting this error.

This is a c# .net 3.5 app running on a windows 2003 server with IIS 6.0 connecting to an Oracle 10g db.

This app had no problems during testing where it was connecting to an Oracle 9i DB but once I moved to the production 10g db I started getting this error.

All the information I have found regarding this issue just says to make sure that you either close or dispose all your commands and increase your allowed cursors in Oracle.

I have set the allowed cursors in oracle to 1000 and encapsulated all my connections in a try catch finally block where I dispose all the connections, commands, and datareaders but I continue to get this error.

Both servers have .net 3.5 sp1 and both are using the same odp.net provider version so I am confused on why I don't see this error in test.

If anyone has any thoughts or ideas on this issue I would like to hear them

Thanks.

Depending on how big the table is you could create a mapping table on your end to map to the correct rows in the external database.

What you are asking doesn't make sense, this is a relational database. If there is no relation in Table 1 with the Data you want in Table 2 then why would you try to access it using Table 1. What happens when someone deletes row 4 in Table2.

Try This

SELECT Projects.BIMinspector, Sum(NZ(Projects.R9,0)) AS SumOfR9
FROM ProjectsGROUP BY Projects.BIMinspectorHAVING (((Projects.BIMinspector)=[forms]![Form Report]![Elist]));

Just an update. I bubbled up the events to the page but still was having the same issue.

Because of time constraints I have let go of trying to sync these tables bilaterally and will have them insert the id and update the desc on index change. It is an internal app and they know the id's so it isn't a major issue.

Maybe if I find the time I will figure the bilateral sync at a later date, shouldn't be as difficult as I was making it.

Thanks for all the help.

Thanks for the response.

The cost on using functions on a where clause come when using the function on the column because it causes a table scan even on an indexed field. When the function is used on the opposite side of the column it has no effect on the index seek and limited to no query cost.

Why are you creating constants when you can just use the date functions in the where clause?

SELECT sum(Salesamount)
   FROM Table
   WHERE salesdate BETWEEN   DATEADD(yy,DATEDIFF(yy,0,getdate()),0) --'First Day of Year'
   AND DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))) --'Last Day of Year'

Just replace where I had @userInput with getdate() function

SELECT sum(Salesamount)FROM TableWHERE YEAR(salesdate) = YEAR(getdate())

You can do the same with the date functions example.

There are alot of ways to do this here are a couple examples.

Using Year() function

Select sum(Salesamount)
From Table
where Year(salesdate) = Year(@userInput)

using Date functions

Select sum(Salesamount)
From Table
where salesdate between DATEADD(yy,DATEDIFF(yy,0,@userInput),0) --'First Day of Year' 
and DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@userInput)+1,0))) --'Last Day of Year'

@sknake - a little update.

After looking further into my problem I believe this has to do with Itemplate is controlling the events for all the controls that are held in it; this is why the events fire in the same order everytime.

I read that I need to bubble up the event to the page so they will be handled by the page and not by the template control. I have never used this technique but I will give it try and let you know how it worked.

The exception is caused because the test data in the table does not match all the dropdownlist, I don't have any issues with the full dataset.

To re-create

The problem is only with the drop down lists I am trying to keep in sync, all the other lists work fine.

When you open the app in edit mode, first change the value in the drop down list cpm_line_name, this will work correctly the first time. Now try changing this value again, it will not work for any subsequent updates as the event handler for cpm_line_id fires first changing the value back to the original before firing its own event. I can change the line_id value updating the name field correctly as many times as I want. The problem only occurs on the name field that I am trying to keep in sync.

If you step through you will see that the event handler for the line id fires first every time.

I was perplexed as to why the line_id event fired first every time so I changed my select to pull the line_name first in the row and then the line_name event fired first every time and I could not update the line_id only the line_name.

For some reason the firing of the events seem to be linked to the order of the fields in the row created in the template and not the sequence in which thy were triggered.

Never heard any …

I tried one more thing before sending out this copy. I tried to create different event handlers if the field was an ID field or a DESC field but the page still has the same functionality which is really making me scratch my head.

Now Instead of firing the same event handler twice with the ID field firing first it is firing the ID event handler first even if I change the DESC dropdown. I can change the ID field anytime but can only change the DESC if I try it first.

I was able to pull this page out of my project and modify it enough to work using some lists and one table. I provided a script to create and load one table. I also changed the program to use Sqlserver as I know there are more people with access to sql server then oracle.

If anyone has any ideas it would be appreciated because I am totally confused why it is functioning like this.

You don't directly use triggers in a C# app. If you have a trigger on update for the Student table it will be triggered when you send an update statement from your code. If you want the db to do jobs before an update put this code in a stored procedure as this is not what triggers are designed to do.

@DdoubleD

Thanks for the reply. i added the removal on the eventhandler on Friday because i found a blog with someone who had a similar problem and adding the removal of the eventhandler helped him. It had no affect on my code at all which is why I finally decided to ask around.

As for the purging I just want to remove any reference to the event once it is processed so it does not run again if I make subsequent changes to any drop downs within the same sequence. I should be able to change a dropdown over and over with only firing the current event only.

@Sknake
I will look at modifying my code on Monday to run without my AD and DB connections.

Thanks for the replies.

I am creating a gridview dynamically using Itemplate but I have run into an issue where the event handlers I create for my drop down boxes in edititem template are accumulating every time it fires.

To be more specific. When I enter edit mode and select a new item in the dropdownlist the event handler fires twice (this I know is because the event handler is updating a second dropdownlist), but if I change another dropdownlist during the same edit session or change the same dropdownlist again the new event fires then the original two fire again. Then if I make a third change I get four events and it keeps incrementing.

I am removing the orignal event handler before adding a new event handler so there shouldn;t be multiple handlers for each instance.

else if (ControlType == "DropDown")
                    {
                        var dropVals = new Dictionary<string, string>();
                        DropDownList field_dropbox = new DropDownList();
                        
                        field_dropbox.ID = FieldName;
                        field_dropbox.SelectedIndexChanged -= new EventHandler(field_dropbox_SelectedIndexChanged);
                        field_dropbox.SelectedIndexChanged += new EventHandler(field_dropbox_SelectedIndexChanged);
                        field_dropbox.AutoPostBack = true;

My event handler updates another dropdown so I know why the event should fire twice but it doesn't explain why they keep firing.

protected void field_dropbox_SelectedIndexChanged(Object sender, EventArgs e)
        {
            string syncDrop = "";
            string list = "";
            var _sync = new Dictionary<string,string>();
            
            _sync = GlobalVars._DropSync;
            list = ((DropDownList)sender).ID.ToString();

            if (_sync.ContainsKey(list))
            {
                syncDrop = _sync[list].ToString();
                
                GridViewRow gvr = (GridViewRow)(((Control)sender).NamingContainer);

                // Get the reference of the first DropDownlist that will generate this SelectedIndexChanged event
                DropDownList dropdownlist1 = (DropDownList)gvr.FindControl(list);

                // Get the reference …

This question should have been posted in the VB forum.

Here is a page that explains the basics of connecting to sqlserver from VB.

http://www.vbexplorer.com/VBExplorer/vb_feature/june2000/Database_Beginner_ADO_DAO.asp

If statements can not be used within a select statement. Not quite sure what you are trying to achieve here but you could sum a case statement to increment a value.

Select  abc.value as 'Name',
sum(case when (History.OLDSTR == '' and History.NEWSTR == value) then 1 else 0 end) [Add]
From History INNER JOIN MetaObjAttrRelations
		ON History.MetaObjAttrRelations_Idn = METAOBJATTRRELATIONS.METAOBJATTRRELATIONS_IDN

Why don't you insert the records into the new table with something similar to this. It will create one row per carId and fill in the appropriate property values.

Insert into newTable(CarId, Size, Name, Color)
Select a.CarId,
(Select Propertyvalue from oldTable b where b.PropertyName = 'Size' and b.CarId = a.CarId),
(Select Propertyvalue from oldTable b where b.PropertyName = 'Name' and b.CarId = a.CarId),
(Select Propertyvalue from oldTable b where b.PropertyName = 'Color' and b.CarId = a.CarId)
From oldTable a
Group by a.CarId

output would be

CarId	Size	Name	Color
1	XL	Porsche	Red
2	XS	Smart	NULL
3	NULL	Audi	NULL

@arjun0 - If you want your questions answered you need to start a new thread.

You can't use the alias in the boolean test plus you should use a having clause to test the count(*) and not the where clause.

Select title, count(*) As Cnt 
From poss_titles 
Group By title
Having count(*) > 1 
Order By count(*) desc

as for only showing fields that don't exist in another table you can use exists condition.

Select title, count(*) As Cnt 
From poss_titles pt
Where not exists (select title from other_table ot where ot.title = pt.title)
Group By title
Having count(*) > 1 
Order By count(*) desc

You can use this to get current date at midnight.

Select to_char (trunc(SYSDATE), 'MM-DD-YYYY HH:MI:SS') from dual;

Just google Oracle Date functions if you want something more specific.

The indexes are maintained so they line up in the drop down lists.

I was thinking about using the selectedindexchanged event handler, now that I set autopostback to true it is firing right away and not waiting for submit.

The problem with handling it this way though is since I am creating the template in a seperate class not in code behind I am having problems figuring out how to access the actuall gridview as it is out of focus and their is no row access in eventargs.

I tried accessing through control(sender) but the gridview cells are null so I am not sure if there is a way to pull the row in the eventhandler when it is not in the code behind.

Im currently working on a gridview built using Itemplate at runtime.

When in edit mode I use dropdownlists for some of the control fields which are populated from views in my db. The problem I am finding is I usually have a memberID field and a MemberName field and need them to be insync. If the user selects a different ID I need the dropdown with the IDname to move to the corresponding option and vice versa.

Is there a way to do this in a dynamically created gridview. I have seen a few examples using ajax in a gridview built using just the control with a sqldatasource but nothing when the grid is built at runtime.

try assigning to a variable then select into

sname1 stu.sname%TYPE;

sname1:= :new.sname

SELECT :NEW.sno, UPPER('sname1'), :NEW.class
INTO sno1, sname1, class1 FROM dual;

Really not sure why upper() is not working for you.

Maybe trying using the upper() function on the insert.

INSERT INTO stu VALUES(sno1,upper(sname1),class1);

Try using select into

Select :NEW.sno, UPPER(:NEW.sname), :NEW.class
into sno1, sname1, class1 from dual;

Try this

Select a,b,c,d,e,f from 
(
 (SELECT a, b, c, d, e, f, DATE 
  FROM table1 WHERE a=10 AND b=1) 
  UNION 
 (SELECT a, b, c, d, e, f, DATE 
  FROM table1 WHERE a=11 AND b=1) 
)
  ORDER BY DATE DESC

You need to encapsulate the whole union to order them after all the records are retrieved.

Hi,

I am working with the gridview in .net 3.5 and have run into something I can not explain. I am testing my delete methods when I realized that the row was actually being deleted before my event handler even finished processing. Am I dreaming or just missing something.

I setup a base gridview with event handlers for select, insert, update, and delete.

<asp:GridView ID="TableGridView" 
              OnRowEditing ="TableGridView_RowEditing" 
              OnRowCancelingEdit="TableGridView_RowCancelingEdit" 
              OnRowUpdating="TableGridView_RowUpdating"
              OnRowDeleting="TableGridView_RowDeleting"
              OnPageIndexChanging="TableGridView_PageIndexChanging"
              runat="server" AutoGenerateColumns="False" CellPadding="4" 
              ForeColor="#333333" GridLines="None" AllowPaging="True" 
              AllowSorting="True" HorizontalAlign="Center">
            <PagerSettings Mode="NumericFirstLast" 
                FirstPageImageUrl="~/Images/n_skip_first.gif" FirstPageText="First" 
                LastPageImageUrl="~/Images/n_skip_last.gif" LastPageText="Last" 
                NextPageImageUrl="~/Images/n_skip_next.gif" NextPageText="Next" 
                PreviousPageImageUrl="~/Images/n_skip_previous.gif" 
                PreviousPageText="Previous" PageButtonCount="5" Position="TopAndBottom" />
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#999999" />
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        </asp:GridView>

I then dynamically load the grid with buttons and data using ITemplate.

Here is my InstantiateIn Method.

public void InstantiateIn(System.Web.UI.Control Container)
        {
            switch (ItemType)
            {
                case ListItemType.Header:
                    Literal header_ltrl = new Literal();
                    header_ltrl.Text = "<b>" + FieldName + "</b>";
                    Container.Controls.Add(header_ltrl);
                    break;
                case ListItemType.Item:
                    switch (ControlType)
                    {
                        case "Command":
                            ImageButton edit_button = new ImageButton();
                            edit_button.ID = "edit_button";
                            edit_button.ImageUrl = "~/images/edit.gif";
                            edit_button.CommandName = "Edit";
                            edit_button.Click += new ImageClickEventHandler(edit_button_Click);
                            edit_button.ToolTip = "Edit";
                            Container.Controls.Add(edit_button);

                            ImageButton delete_button = new ImageButton();
                            delete_button.ID = "delete_button";
                            delete_button.ImageUrl = "~/images/delete.gif";
                            delete_button.CommandName = "Delete";
                            delete_button.OnClientClick = "return confirm('Confirm you want to delete the record?')";
                            delete_button.ToolTip = "Delete";
                            Container.Controls.Add(delete_button);

                            ImageButton insert_button = new ImageButton();
                            insert_button.ID = "insert_button";
                            insert_button.ImageUrl = "~/images/insert.bmp";
                            insert_button.CommandName = "Edit";
                            insert_button.ToolTip = "Insert";
                            insert_button.Click += new ImageClickEventHandler(insert_button_Click);
                            Container.Controls.Add(insert_button);
                            break;

                        default:
                            Label …

Found my own solution, thanks.

I am just wondering if there is a way to stop my paging images from moving around the gridview footer when using the builtin gridview paging control.

I beleive you need to use a full outer join with a coelesce on the 3 keys to get your desired result.

SELECT coalesce(a.Recordingid,b.recordingid), coalesce(a.Connection,b.Connection), coalesce(a.Sequenceno,b.Sequenceno), b.Code, b.Cause
FROM dbo.Table_1 a 
Full OUTER JOIN dbo.Table_2 b on a.recordingid = b.recordingid AND a.connection = b.connection AND a.Sequenceno = b.Sequenceno

Use double ampersands && in your if statement.

When you have a higher reputation you get more greeen squares in your header file (I guess it is supposed to signify how competent the responders are) as well I think it might have a reflection on your own altering power (I really haven't figured out how the reputation altering power is assessed but it would make sense).

I beleive it is more important to most that the thread is marked as solved as new members have very limited reputation altering power. Geeks love to help they just need some sort of external gratification for it.

Hiope that helps.

By only doing subqueries you basically created a cartesian join. It displayed a record for every possible permutation of the query.

There is a simple explantion of cartesian (cross joins) here.

http://www.dba-oracle.com/t_garmany_9_sql_cross_join.htm

As for the community. It is expected when a question you ask is solved you set the thread to solved.

Reputation points can be given as well by clicking on the link below the user name. I beleive your reputation altering power is determined by your own reputation and number of threads participated in as well as solved, possibly the reason it is encouraged to mark threads solved when complete as well as keeping the site clean.

You can see what you reputation altering power is by looking in your account by clicking on your own link.

Have a good one.

What rownum = 1 is doing is returning only the top row of employee information for each job_id. without the rownum =1 the query will return all matches which is why you get the error message.

BabyDBA's solution should return the same results and is actually a much better way to go as there is only 1 scan of the table and not 3 plus it is alot cleaner and easier to read.

So to answer your question it is possible to get the distinct value of one field while displaying multiple fields.

I know it is possible as I have done similar queries in the past using subqueries. I provided something I put together quickly, not a very good example but it does accomplish what you were inquiring about.

select distinct a.JOB_ID, 
(Select EMPLOYEE_ID from EMPLOYEE b where b.JOB_ID = a.JOB_ID and rownum = 1) , 
(Select LAST_NAME from EMPLOYEE c where c.JOB_ID = a.JOB_ID and rownum = 1)
from EMPLOYEEE

I'm not quite sure what you are trying to achieve.

If you just want the job_id then grab it seperately, if you want to see all the employees associated which each job_id you have to return all rows for each job_id, unless you have exact duplicate rows which would not make sense in your db design.

What you are asking to achieve doesn't seem to make any sense without context of your ultimate goal.

Try something like this.

SELECT DISTINCT emp.Job_ID, a.Employee_ID, a.Last_Name
FROM Employees emp
Join (Select job_id,employee_id,Lastname 
        From Employees)a on emp.Job_id = a.Job_id
ORDER BY a.Last_Name

I have been getting the same error as well on an app I am working on.

I have not been able to find much information on the actual problem but my connection is still working and I am receiving data back from my db but I am not sure if I will have problems once I run through IIS instead of locally.

The only thing I can think of is asp 3.5 connection strings have problems with versions 9i and below. I have not tried with our 10g servers so it is only a guess and like I said I have been searching for a few days for info on this error without any luck.

Did you try using wild cards.

Select * from Table1
Where Name LIKE ('%' + @Name + '%')

I want to store codes in a database i.e title code, status code, etc. I want to do this without affecting the performance of the database. I want u guys to tell me the most advisable way of doing this. I want to retrieve the actual code descriptions using these codes in the fastest time possible. I'm wondering if i should store them in multiple tables or in one. Ans how do i go about doing that.

Thank you

@debasisdas The user is looking to setup a lookup table for his status codes not store all his data in one table.

What are you going to use to read the file. If you are going to program something to read the file you can simply clean the data in your code and insert into the desired table once you have cleansed the data.

When your doing the select into #temp statement you are creating an actual temp table that is living in virtual memory and is only visible within the session it is created and is immediately dropped once this session is closed.

You can either perform the whole process within one sp or use a base procedure that calls the other procedures, you just have to remember that the table session must be maintained or else it is dropped.

There is no For or foreach loop for sqlserver unless you are talking about using the foreach container in SSIS, which I did mention would be the best way to go about bulk inserts.

Like I told you in my earlier post, 1 table is always faster.

You should also use an index if you want to increase scanning speeds as index scans are faster than table scans.

np...it happens to everyone once in awhile.