Database

Error:The cursor does not include the table being modified or the table is not updatable through the cursor

27-Oct-08 10:00

If I understood the problem correctly, in the first proc, instead of declaring @CashID as a variable, pass it as a parameter to procedure. After that a single INSERT INTO ... SELECT ... FROM ... WHERE ... will be sufficient.

Refer to INSERT (Transact-SQL)[^] and CREATE PROCEDURE (Transact-SQL)[^]

The need to optimize rises from a bad design.

My articles[^]

V K 226-Oct-08 19:19

V K 2

26-Oct-08 19:19

Hi,
CRecordSet::Update( ) is throwing the following exception.

Error:The cursor does not include the table being modified or the table is not updatable through the cursor

I donot know what could be the solution.

I am currently using SQL-Server 2005 ,VC++6.0 . Any help would be appreciated.

Thanks

Cross-post (3/3)

Wendelius27-Oct-08 9:21

Problem : Retrive Data with ref table

27-Oct-08 9:21

Don't cross-post. It's considered rude.

First one found here http://www.codeproject.com/script/Forums/View.aspx?fid=1647&msg=2773210[^]

CP IGNORE THIS USER

leckey27-Oct-08 10:00

leckey

27-Oct-08 10:00

This user is a habitual cross-poster. This message is in THREE forums. Mad | :mad:

Blog link to be reinstated at a later date.

Ahmed R El Bohoty26-Oct-08 1:14

26-Oct-08 1:14

hi ,
i make simple method that retrieve data from tables this is easy but my problem happen when i retrieve data from table that have relationship with other table, i make class to set value into properties when i read and bind it in Gridview.

i make for each table class that contains to properties and if i make all table in one class it work so i want to find solution to it with do this.

Tables

Create Table Catgerories (
CatgId int PRIMARY KEY,
Catgname nvarchar(50)
)

Create Table StudentInfo(
stId int PRIMARY KEY,
Stname nvarchar(50),
CatgId int  REFERENCES Catgerories (CatgId)
)

create PROCEDURE dbo.StoredProcedure2 
AS
	SELECT     StudentInfo.*, Catgerories.Catgname
FROM         Catgerories INNER JOIN
                      StudentInfo ON Catgerories.CatgId = StudentInfo.CatgId
	RETURN

Code:

public class CatgBL
{
    int cid = 0;

    public int Cid
    {
        get { return cid; }
        set { cid = value; }
    }
    string catgName;

    public string CatgName
    {
        get { return catgName; }
        set { catgName = value; }
    }
}

public class MyStudent
{
    int stid;

    public int Stid
    {
        get { return stid; }
        set { stid = value; }
    }
    string sname;

    public string Sname
    {
        get { return sname; }
        set { sname = value; }
    }
    int catgid;

    public int Catgid
    {
        get { return catgid; }
        set { catgid = value; }
    }
}

SqlConnection myCon = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator\My Documents\TestDb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
    public ArrayList RetriveStdentInfo()
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "dbo.StoredProcedure2";
        cmd.CommandType = CommandType.StoredProcedure;
        //cmd.Parameters=ParameterDirection.ReturnValue;
        myCon.Open();
        IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        ArrayList sequence = new ArrayList();
        ArrayList seqCat = new ArrayList();
        ArrayList seqStudent = new ArrayList();
        while (reader.Read())
        {
            MyStudent std = new MyStudent();
            CatgBL cat = new CatgBL();
            std.Stid = reader.GetInt32(0);
            std.Sname = reader.GetString(reader.GetOrdinal("stname"));
            std.Catgid = reader.GetInt32(reader.GetOrdinal("Catgid"));
            cat.CatgName = reader.GetString(reader.GetOrdinal("CatgName"));
            seqCat.Add(cat);
            seqStudent.Add(std);
            sequence.AddRange(seqStudent);
            sequence.AddRange(seqCat);
        }
        return sequence;
    }

Wendelius26-Oct-08 1:34

26-Oct-08 1:34

I'm not sure if I understood you correctly, but I think you have two choices.

1. when looping through rows, create a new object for category only when the primary key changes. Because you have joined the data when fetching, you will have the same category multiple times.

2. fetch categories and students separately.

The need to optimize rises from a bad design.

My articles[^]

Ahmed R El Bohoty26-Oct-08 1:53

26-Oct-08 1:53

Thanks for you,may be i understand some things but i have some lack in ur soltion , i do this changes in code but the problem is still

public ArrayList RetriveStdentInfo()
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "dbo.StoredProcedure2";
        //cmd.Parameters=ParameterDirection.ReturnValue;
        cmd.CommandType = CommandType.StoredProcedure;
        myCon.Open();
        IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        ArrayList sequence = new ArrayList();
        ArrayList seqCat = new ArrayList();
        ArrayList seqStudent = new ArrayList();
        while (reader.Read())
        {
           // MyStudent std = new MyStudent();
            CatgBL cat = new CatgBL();
            //std.Stid = reader.GetInt32(0);
            //std.Sname = reader.GetString(reader.GetOrdinal("stname"));
            //std.Catgid = reader.GetInt32(reader.GetOrdinal("Catgid"));
            cat.CatgName = reader.GetString(reader.GetOrdinal("CatgName"));
            seqCat.Add(cat);
            //seqStudent.Add(std);
            //sequence.AddRange(seqStudent);
            seqCat.Add(cat);
        }
        while (reader.Read())
        {
            MyStudent std = new MyStudent();
            std.Stid = reader.GetInt32(0);
            std.Sname = reader.GetString(reader.GetOrdinal("stname"));
            std.Catgid = reader.GetInt32(reader.GetOrdinal("Catgid"));
            seqStudent.Add(std);          
        }
        sequence.AddRange(seqCat);
        sequence.AddRange(seqStudent);
         return sequence;
    }

Wendelius26-Oct-08 2:06

26-Oct-08 2:06

That's not quite what I meant. Something like this:

int prevCatId = 0;
while (reader.Read())        
{            
   MyStudent std = new MyStudent();            
   std.Stid = reader.GetInt32(0);            
   std.Sname = reader.GetString(reader.GetOrdinal("stname"));            
   std.Catgid = reader.GetInt32(reader.GetOrdinal("Catgid"));            
   if (prevCatID != reader.GetInt32(reader.GetOrdinal("Catgid"))) 
   {
      CatgBL cat = new CatgBL();            
      cat.CatgName = reader.GetString(reader.GetOrdinal("CatgName"));            
      seqCat.Add(cat);            
      prevCatID = reader.GetInt32(reader.GetOrdinal("Catgid"));
   }
   seqStudent.Add(std);            
   sequence.AddRange(seqStudent);            
   sequence.AddRange(seqCat);        
}

The need to optimize rises from a bad design.

My articles[^]

Ahmed R El Bohoty26-Oct-08 4:21

26-Oct-08 4:21

Thanks again but the problem is still running

Object does not match target type

when i make debugging code the result is right but the problem appear in binding

StudentDAL std = new StudentDAL();
     DataGrid1.DataSource = std.RetriveStdentInfo();
      DataGrid1.DataBind();

Wendelius26-Oct-08 5:28

26-Oct-08 5:28

I'm not sure if DataGrid can handle two dimensional arrays. Since you return an arraylist which contains arraylists, it can be problematic.

I also noticed that you add the same elements several times. That can also cause problems. Move the following lines outside the read loop:

sequence.AddRange(seqStudent);
sequence.AddRange(seqCat);

The need to optimize rises from a bad design.

My articles[^]

Ahmed R El Bohoty26-Oct-08 8:15

26-Oct-08 8:15

Yes, u can bind 2 dimensional array to DataGrid and i found article in codeproject that talk about it. from opinion when i make 2 arraylist and store all in one arraylist to make centralize store so all data will be in one arraylist but i can not reach to the methodology to do this because its very important to do this code.

http://www.codeproject.com/KB/database/BindArrayGrid.aspx?fid=31160&df=90&mpp=25&noise=3&sort=Position&view=Quick&fr=26

Wendelius26-Oct-08 8:23

26-Oct-08 8:23

The situation in the article is a little bit different. When you setup an array of entities, all entities have properties, so now you have used two dimensions (for example if student is a row and name property is a column).

What kind of layout are you trying to achieve in DataGrid? Something like the following?

PersonID Name   Age
1        John   32    
2        Kendra 20
3        Steve  29
...

The need to optimize rises from a bad design.

My articles[^]

Ahmed R El Bohoty26-Oct-08 8:25

26-Oct-08 8:25

Yes

Wendelius26-Oct-08 8:33