Member Avatar for Don_leon

I am doing a Smart device project The problam is I can not pass parameters to the stored procedure to insert into the database from the PDA emulator for the following sqlDbtypes it gives me the following error

A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.SqlClient.dll

Deicimal
Money
Int
Numeric

these sqlDBtypes work fine when I pass the parameters to the same stored procedure with a windows application( not smart device application). the database gets uupdated correctly.

This is my Code block

public void opendb()
        {
            String connStr = "Data Source=192.168.1.1,1433;Initial Catalog=Resmanagement;User ID=sa;Password=thadiya;";
            myConn1 = new SqlConnection(connStr);

            myConn1.Open();
        }

        public void closedb()
        {
            if (myConn1 != null)
            {
                myConn1.Close();
            }

        }

        private void button1_Click(object sender, EventArgs e)
        {
            dblPrice = 1000;

            try
            {
                opendb();

                SqlCommand command1 = new SqlCommand("Test2", myConn1);
                command1.CommandType = CommandType.StoredProcedure;

                command1.Parameters.Add("@Price", SqlDbType.Decimal,9, "Price").Value =Convert.ToDecimal(dblPrice);

                command1.ExecuteNonQuery();
                command1.Dispose();

                closedb();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }


        }
Edited by mike_2000_17 because: Fixed formatting
  • 1 Contributor
  • 1 Reply
  • 129 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Don_leon

Dani AI

Generated

The Compact Framework's SqlClient behaves differently from the full desktop framework: passing a length/source-column overload (the Add overload with size and a source column) or giving a numeric parameter a "size" can trigger an InvalidOperationException on a device/emulator even though the same code works on desktop. The safest approach is to create explicit SqlParameter objects for numeric types, and for decimal/numeric set Precision and Scale rather than using the size argument.

Example patterns (avoid the Add(name,type,size,source) overload for numeric types):

var p = new SqlParameter("@Price", SqlDbType.Decimal);
p.Precision = 9;   // total digits
p.Scale = 2;       // digits after decimal
p.Value = 1000m;
command.Parameters.Add(p);
command.Parameters.Add(new SqlParameter("@Price", SqlDbType.Money) { Value = 1000m });

Practical checklist and troubleshooting:

  • Use explicit SqlParameter for Decimal/Numeric and set Precision/Scale. See SqlParameter.Precision and SqlParameter.Scale (docs).
  • Do not pass a sourceColumn/size for fixed numeric types; that overload is intended for DataAdapter mapping. Consider AddWithValue or the explicit constructor for simple cases (AddWithValue docs).
  • Confirm the stored-proc parameter type/signature matches exactly (name, SqlDbType and precision/scale). SQL Server will coerce types but mismatches can surface differently on CF.
  • Capture the full exception (ex.ToString()) and, if possible, run SQL Profiler to see what parameters arrive on the server.

For the immediate change is to stop using the size+source overload for @Price and instead add a SqlParameter with Precision/Scale (or use SqlDbType.Money without size). That pattern resolves the InvalidOperationException in most Compact Framework cases.

Member Avatar for Don_leon

I am doing a Smart device project The problam is I can not pass parameters to the stored procedure to insert into the database from the PDA emulator for the following sqlDbtypes it gives me the following error

A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.SqlClient.dll

Deicimal
Money
Int
Numeric

these sqlDBtypes work fine when I pass the parameters to the same stored procedure with a windows application( not smart device application). the database gets uupdated correctly.

This is my Code block

public void opendb()
        {
            String connStr = "Data Source=192.168.1.1,1433;Initial Catalog=Resmanagement;User ID=sa;Password=thadiya;";
            myConn1 = new SqlConnection(connStr);

            myConn1.Open();
        }

        public void closedb()
        {
            if (myConn1 != null)
            {
                myConn1.Close();
            }

        }

        private void button1_Click(object sender, EventArgs e)
        {
            dblPrice = 1000;

            try
            {
                opendb();

                SqlCommand command1 = new SqlCommand("Test2", myConn1);
                command1.CommandType = CommandType.StoredProcedure;

                command1.Parameters.Add("@Price", SqlDbType.Decimal,9, "Price").Value =Convert.ToDecimal(dblPrice);

                command1.ExecuteNonQuery();
                command1.Dispose();

                closedb();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }


        }

end quote.

new corrections

command1.Parameters.Add("@Price", SqlDbTypey.Money,8, "Price").Value=Convert.ToDecimal(dblPrice);

table Defenition(Table name test)---------Price   DataType(money)
Edited by mike_2000_17 because: Fixed formatting
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.