Click here to Skip to main content
15,891,529 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
private void getmaxScript()
        {

            string ConnectionStringIntellectServerDetails = "Data Source=" + txtServerNameIPIntellect.Text + ";Initial Catalog=" + lblSqlDatabase.Text + ";user id=" + txtUsernameIntellect.Text + ";password=" + txtPasswordIntellect.Text + "";
            SqlConnection conn = new SqlConnection(ConnectionStringIntellectServerDetails);
            conn.Open();

            SqlCommand cmmd = new SqlCommand("", conn);
            cmmd.CommandText = "CREATE TABLE tbl_Script([tblScript_Id] INTEGER CONSTRAINT PKeytblScript_Id Primary Key, [ScriptId] INTEGER)";
            if (conn.State == ConnectionState.Open)
            {
                try
                {
                    cmmd.ExecuteNonQuery();
                    MessageBox.Show("Add!");
                    var ScriptId = 0;
                    string sql = "Select Max(ScriptId) from tbl_Script";
                     SqlCommand cmd = new SqlCommand(sql, conn);
                     using (var reader = cmd.ExecuteReader())
                     {
                         if (reader["ScriptId"] == System.DBNull.Value)
                         {
                             ScriptId = 1;
                         }
                         else
                         {
                            ScriptId= ScriptId++;
                         }

                         globalScriptid = ScriptId.ToString();
                         string sqlmax = "insert into tbl_Script(ScriptId) values(" + globalScriptid+ ")";
                         SqlCommand cmd1 = new SqlCommand(sqlmax, conn);
                         cmd1.ExecuteNonQuery();
                     }
                }
                catch (SqlException expe)
                {
                    MessageBox.Show(expe.Message);
                    conn.Close();
                }      

            }
            else
            {
                MessageBox.Show("Error!");
            }
        }


above is my code i an get error ( indexoutof range Exception was unhandled) on line
if (reader["ScriptId"] == System.DBNull.Value) pls help me wt change i do than my code work proper way pls help
Posted

You don't return a column "ScriptId" from your query:
C#
string sql = "Select Max(ScriptId) from tbl_Script";
 SqlCommand cmd = new SqlCommand(sql, conn);
 using (var reader = cmd.ExecuteReader())
 {
     if (reader["ScriptId"] == System.DBNull.Value)

You return a count, which will have no name instead.

But there are a couple of other things here you need to look at:
1) You don't need a reader to return a count: any single value return can be done with an ExecuteScalar instead of ExecuteReader.
2) Your code is wide open to nasty intermittent bugs which are very hard to track down. Remember you are working with SQL which is designed as a multi-use4r system: so it is very easy for a different PC to "get in" and use the same ID value you are trying to. Don't "read the highest value and add one" - that is a recipe for your app falling over in production, but working perfectly in development. Consider using either an Identity column, or a Guid column instead.
 
Share this answer
 
replace your string sql with below


string sql = "Select Max(ScriptId) as ScriptId from tbl_Script";
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900