[RESOLVED] Problem with query and updata?

[ioigoume]

Goodevening everybody,
i am developing an application with two tables. Those tables have parent - child control. What i want to do is press the button of add new row and a new row is added to the parent and child. So far so good. The problem is when i wish to fill the gaps that the autoicrement property leaves after deletion of some row.

The code i use is:

 // The connection string
                string ConnString = Settings.Default.axisConnectionString;

                string Query = " INSERT INTO dbo.owner" +
                               " (ID, NAME, SURNAME)" +
                               " VALUES (" + inside_row_index.ToString().Trim() + ",' ',' ')";


                // Create my connection
                SqlConnection insertconn = new SqlConnection(ConnString);
                // Create my command
                SqlCommand insertcommand = insertconn.CreateCommand();
                insertcommand.CommandText = Query;
                

                try
                {
                    // Connect to database
                    insertconn.Open();
                    // Create the transaction
                    mytran = insertconn.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
                    // Execute query
                    insertcommand.ExecuteNonQuery();


                    // Before creating the child save the parent matrix
                    // and refresh the grid
                    axisDataSet.ownerDataTable newParentRecord = (axisDataSet.ownerDataTable)axisDataSet.owner.GetChanges();
                    if (newParentRecord != null)
                    {
                        // Update the grid
                        Validate();
                        ownerTableAdapter.Update(axisDataSet);
                        axisDataSet.AcceptChanges();
                        ownerDataGridView.Refresh();

                        // Child add row
                        // Return the obtained id
                        //new_child_row["ID"] = inside_row_index;
                        //axisDataSet.xyz.Rows.Add(new_child_row);
                        mytran.Commit();
                        insertconn.Close();
                    }
                    else
                    {
                        throw new Exception("Could not save or create the new row.");
                    }
                }
                catch (SqlException a)
                {
                    MessageBox.Show(a.Message, "Exciting.", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    mytran.Rollback();
                    return;
                }
                catch (Exception a)
                {
                    MessageBox.Show(a.Message, "Exciting.", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    mytran.Rollback();
                    return;
                }

I tried to use the sql statement set_identity on and off but error occured. After deleting that it seemed that it worked but when i try to use the new row to create a new child row an error concening the foreign key arises. This indicates that the row is not inserted into database.
So i updated before creating new row for the child. Still the same. Then i called the GetChanges and saw that no changes are happening?

Do you have any ideas because i seem to have run out!!!

This is the code i use to create the new rows

 DataRow new_row = axisDataSet.owner.NewRow();
 DataRow new_child_row = axisDataSet.xyz.NewRow();

Any idea is welcome

ioigoume

[GaryMazzone]

You do not fill the gaaps. Auto lnriment means just that the ssystem takes care of that number.

[ioigoume]

Thanx for the reply,

I do feel the gaps. I have implemented a functions that searches my table and finds if there is an inconsistency, then the function returns the id value to use so that i can feel the gap!!!The problem is not whether i create something to fill the gaps the problem is why the code i use creates a line but i can not save it?

ioigoume

[GaryMazzone]

Arte you using SQL Server? If so you need to set Set Identity Insert On if you expect to insert your own number into an Identity (auto incrementing) field.

[ioigoume]

Hi,
in the original post i am writing that i do use the set identity isert on but it does not work. Since my last postage i tried to use the simple create row and insert through the dataset interface. It works but it also increases the identity, even i choose my own. So nothing again!

ioigoume

[GaryMazzone]

The point of an autoincremet field is that you don't I repeat with emphisis DO NOT resue the numbers. The gaps mean nothing and are not an issue.

[abhijit]

Hi,
in the original post i am writing that i do use the set identity isert on but it does not work. Since my last postage i tried to use the simple create row and insert through the dataset interface. It works but it also increases the identity, even i choose my own. So nothing again!

ioigoume

What do you mean by "it does not work"?

Is it failing to insert a record?
Is it failing to update the identity column?

[ioigoume]

Hi everybody,
it is failing to update. Here is the code of the query

string Query = " SET IDENTITY_INSERT dbo.owner ON;" +
                               " INSERT INTO owner" +
                               " (ID, NAME, SURNAME)" +
                               " VALUES (@ID,@NAME,@SURNAME);";
                               " SET IDENTITY_INSERT dbo.owner OFF;";

When i try to update it says that for the array owner the identity is not set. Then i check my datatable and i see that i have enabled identity. This part i can not understand. Then if i delete the set identity statements it can not update because it is not causing an Changed status.

Here is the code of the add new row in parent and child array

private void bindingNavigatorAddNewItem_Click(object sender, EventArgs e)
        {
            int inside_row_index = 0;
            int max_row_id = 0;
            string[] metadata = null;
            SqlDataReader newreader = null;

            DataRow parent_row = null;
            DataRow new_row = axisDataSet.owner.NewRow();
            DataRow new_child_row = axisDataSet.xyz.NewRow();

            // Find if there is an empty id num to insert the new row
            inside_row_index = TableGapFill(axisDataSet, axisDataSet.owner);
            // Finde the the max id of the table
            parent_row = axisDataSet.owner.Rows[axisDataSet.owner.Rows.Count-1];
            max_row_id = (int)parent_row["ID"];


            // Add row with auto increment if everything is ok
            if (inside_row_index == 0 || inside_row_index >= max_row_id)
            {
                // Parent add row
                axisDataSet.owner.Rows.Add(new_row);

                //Child add row
                new_child_row["ID"] = new_row["ID"];
                axisDataSet.xyz.Rows.Add(new_child_row);
            }
            else // if a gap is occured the use select insert statement
            {
                // The connection string
                string ConnString = Settings.Default.axisConnectionString;
                string Query_col = "SELECT * FROM owner";

                string Query = //" SET IDENTITY_INSERT dbo.owner ON;" +
                               " INSERT INTO owner" +
                               " (ID, NAME, SURNAME)" +
                               " VALUES (@ID,@NAME,@SURNAME);";
                               //" SET IDENTITY_INSERT dbo.owner OFF;";
                                        

                // Create my connection
                SqlConnection insertconn = new SqlConnection(ConnString);
                // Create my command
                SqlCommand insertcommand = insertconn.CreateCommand();


                // Retrieve the names of the columns to create the scalars
                using(TransactionScope myscope = new TransactionScope())
                {
                    insertcommand.CommandText = Query_col;
                     try
                    {
                        insertconn.Open();
                        newreader = insertcommand.ExecuteReader();
                        metadata = new string[axisDataSet.owner.Columns.Count];
                        for (int i = 0; i < (axisDataSet.owner.Columns.Count); i++)
                        {
                            metadata[i] = "@" + newreader.GetName(i);
                        }
                        insertconn.Close();
                    }
                    catch (SqlException a)
                    {
                        MessageBox.Show(a.Message,"Exciting.",MessageBoxButtons.OK,MessageBoxIcon.Error);
                        return;
                    }
                    myscope.Complete();
                }

                // New query
                using (TransactionScope mytran = new TransactionScope())
                {
                    
                    insertcommand.CommandText = Query_new;
                    
                    SqlParameter[] data = new SqlParameter[metadata.Length];
                    data[0] = new SqlParameter("@ID", SqlDbType.Int);
                    data[0].Direction = ParameterDirection.Output;
                    data[0].Value = inside_row_index;
                    insertcommand.Parameters.Add(data[0]);


                    // Array of parameters
                    int i = 0;
                    foreach (string mystr in metadata)
                    {
                        if (mystr != "@ID")
                        {
                            
                            data[i] = new SqlParameter(mystr, SqlDbType.NChar,20);
                            data[i].Direction = ParameterDirection.InputOutput;
                            data[i].Value = "";
                            insertcommand.Parameters.Add(data[i]);
                        }
                        i++;
                    }
                    
                    try
                    {
                        insertconn.Open();
                        insertcommand.ExecuteNonQuery();
                        // Parent table, always update after new row
                        ownerBindingSource.EndEdit();
                        ownerTableAdapter.Update(axisDataSet.owner);
                        axisDataSet.AcceptChanges();
                        ownerDataGridView.Refresh();
                    }
                    catch (SqlException a)
                    {
                        MessageBox.Show(a.Message,"Exciting.",MessageBoxButtons.OK,MessageBoxIcon.Error);
                        return;
                    }

                    //Child add row
                    // Return the obtained id
                    new_child_row["ID"] = inside_row_index;
                    axisDataSet.xyz.Rows.Add(new_child_row);

                    insertconn.Close();
                    mytran.Complete();
                }
            }
        }

I hope someone can understand something more. I thought that when doing a query events arise but it seems i was wrong!

ioigoume

[abhijit]

How does it behave in the SQL front-end? If you fire a query through SQL Query Analyzer what is the result?

[ioigoume]

Goodmorning,
I do not fuly understand the front-end and query analyzer expressions. But if i make anoither query over the base everything works ok.

ioigoume

[abhijit]

Goodmorning,
I do not fuly understand the front-end and query analyzer expressions. But if i make anoither query over the base everything works ok.

ioigoume

Have you got the SQL Server Management Studio installed?



How does it work when you try to insert a record in your table?

Here's the script I used to create the table.

/****** Object:  Table [dbo].[IdTest]    Script Date: 06/01/2011 08:59:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[IdTest](
	[Id] [int] IDENTITY(1000,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Surname] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_IdTest] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

[ioigoume]

Goodevening,
sorry for not responding but i was out of town. Well i do not have the sql development studio. I use the visual studio 2008. There i create 2 tables using the insert data into project. This is using the sql exress. Perhaps the possibilities of this library are limited. I do not know. I am new, i am reading a lot but it seems that some pieces are missing

ioigoume

[abhijit]

SQL Express is not limited. It has all the features you need for your purposes. I am sure there's a SQL MAnagement studio tool included with SQL Express.

[ioigoume]

Ok,
i will look around that for a couple of days, because this tool is something new for me and i will come back with result and reply

ioigoume

[abhijit]

Ok,
i will look around that for a couple of days, because this tool is something new for me and i will come back with result and reply

ioigoume

Do you have any other tool than visual studio to access your database? There's a host of free tools that you can use. I believe there's a freeware version of Toad for SQL Server.

[ioigoume]

Goodevening,
i found the solution and i think it is a work around rather than what i was looking for

// Get the parents column ID and disable auto increment
                DataColumn parcol = nexusdatabaseDataSet.PersonalInfoTable.IDColumn;
                parcol.AutoIncrement = false;

                // Create a new row
                DataRow new_row = nexusdatabaseDataSet.PersonalInfoTable.NewRow();
                // Parent set the ID of the new row
                new_row["ID"] = inside_row_index;
                // Add the new row to the table
                nexusdatabaseDataSet.PersonalInfoTable.Rows.Add(new_row);
                // Create the the new child row
                new_child_row["ID_PERSONALINFO"] = new_row["ID"];
                //new_child_row["SUBJECT"] = new_row["SUBJECT"];
                nexusdatabaseDataSet.MeasurementDataTable.Rows.Add(new_child_row);

                // Enable the auto increment
                parcol.AutoIncrement = true;
                parcol.AutoIncrementStep = 1;
                parcol.AutoIncrementSeed = max_row_id;

I disable the auto increment property, then do my work and i re-enabling it giving as initial value the one i needs.

ioigoume