Error converting data type nvarchar to numeric sql

I am trying to take an average of a column in my database. The column is AMOUNT and it is stored as NVARCHAR(300),null. When I try to convert it to a numeric value I get the following error: Msg

You would think that your code would work. However, SQL Server does not guarantee that the WHERE clause filters the database before the conversion for the SELECT takes place. In my opinion this is a bug. In Microsoft’s opinion, this is an optimization feature.

Hence, your WHERE is not guaranteed to work. Even using a CTE doesn’t fix the problem.

The best solution is TRY_CONVERT() available in SQL Server 2012+:

SELECT AVG(TRY_CONVERT(DECIMAL(18,2), Reimbursement)) AS Amount
FROM Database
WHERE ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL;

In earlier versions, you can use CASE. The CASE does guarantee the sequential ordering of the clauses, so:

SELECT AVG(CASE WHEN ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL
                THEN CONVERT(DECIMAL(18,2), Reimbursement))
           END)
FROM Database;

Because AVG() ignores NULL values, the WHERE is not necessary, but you can include it if you like.

Finally, you could simplify your code by using a computed column:

alter database add Reimbursement_Value as
    (CASE WHEN ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL
          THEN CONVERT(DECIMAL(18,2), Reimbursement))
     END);

Then you could write the code as:

select avg(Reimbursement_Value)
from database
where Reimbursement_Value is not null;

Источник
  • Remove From My Forums

  • Question

  • I am getting the same error when i execute the below code,

    DECLARE @GrossClaimAmountEuro NVARCHAR(1000)
    SELECT @GrossClaimAmountEuro = SUBSTRING(RTRIM(LTRIM(TempColumn)),0,LEN(RTRIM(LTRIM(TempColumn))) — LEN(‘Total Disputed Invoice Value’))
    FROM fn_CCT_ConvertTextToRows(‘Customer Name               : SPORT DANMARK A/S
             Sold To #                   : 117799
             Total Claim Value           : 170,000.00
             Total Disputed Invoice Value: 170,000.00
             Total Claim Units           : 13
             Currency                    : EUR’)
    WHERE RTRIM(LTRIM(TempColumn)) like ‘%Total Disputed Invoice Value%’
     IF ISNUMERIC(@GrossClaimAmountEuro) = 0
      SET @GrossClaimAmountEuro = ‘0’

     SET @GrossClaimAmountEuro = REPLACE(@GrossClaimAmountEuro,’,’,»)
    SELECT @GrossClaimAmountEuro
    SELECT CONVERT(DECIMAL(15,4),LTRIM(RTRIM(‘170000.00          ‘)))
    SELECT CONVERT(DECIMAL(15,4),LTRIM(RTRIM(@GrossClaimAmountEuro)))

    The variable @GrossClaimAmountEuro  holds holds a value ‘170000.00          ‘. When i hardcode the value and execute, the code works fine, if i give the variable name in the code, i am getting a error as,
    «Msg 8114, Level 16, State 5, Line 18
    Error converting data type nvarchar to numeric.»

    I dont know..  what i am doing wrong..
    Please help

Answers

  • I got the problem solved, the variable was having some juck characters. I replaced it using ASCII code. Thank you all for the support.

    • Marked as answer by

      Wednesday, July 29, 2009 8:52 AM

Источник

Содержание

  1. Sql server error converting nvarchar to numeric
  2. Answered by:
  3. Question
  4. Getting Error: «Error converting data type nvarchar to numeric» in SQL
  5. 4 Answers 4
  6. Sql server error converting nvarchar to numeric
  7. Answered by:
  8. Question
  9. Answers
  10. Sql server error converting nvarchar to numeric
  11. Answered by:
  12. Question
  13. Answers
  14. Sql server error converting nvarchar to numeric
  15. Answered by:
  16. Question

Sql server error converting nvarchar to numeric

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I am getting the same error when i execute the below code,

DECLARE @GrossClaimAmountEuro NVARCHAR(1000)
SELECT @GrossClaimAmountEuro = SUBSTRING(RTRIM(LTRIM(TempColumn)),0,LEN(RTRIM(LTRIM(TempColumn))) — LEN(‘Total Disputed Invoice Value’))
FROM fn_CCT_ConvertTextToRows(‘Customer Name : SPORT DANMARK A/S
Sold To # : 117799
Total Claim Value : 170,000.00
Total Disputed Invoice Value: 170,000.00
Total Claim Units : 13
Currency : EUR’)
WHERE RTRIM(LTRIM(TempColumn)) like ‘%Total Disputed Invoice Value%’
IF ISNUMERIC(@GrossClaimAmountEuro) = 0
SET @GrossClaimAmountEuro = ‘0’

SET @GrossClaimAmountEuro = REPLACE(@GrossClaimAmountEuro,’,’,»)
SELECT @GrossClaimAmountEuro
SELECT CONVERT(DECIMAL(15,4),LTRIM(RTRIM(‘170000.00 ‘)))
SELECT CONVERT(DECIMAL(15,4),LTRIM(RTRIM(@GrossClaimAmountEuro)))

The variable @GrossClaimAmountEuro holds holds a value ‘170000.00 ‘. When i hardcode the value and execute, the code works fine, if i give the variable name in the code, i am getting a error as,
«Msg 8114, Level 16, State 5, Line 18
Error converting data type nvarchar to numeric.»

I dont know.. what i am doing wrong..
Please help

Источник

Getting Error: «Error converting data type nvarchar to numeric» in SQL

I am passing 4 Parameters to an asp.net Webservice. This is my Code so far:

If i invoke that Webmethod with the 4 Parameters, the Method is working fine an i get a LIST of the RaumName’s and RaumID’s. But if i put only one Parameter iam getting an Error:

The ID’s in the Database are stored as Numeric and i passing string. I think that is the problem. But i dont know how to fix this.

I also want that my Query works also with only two or three entered Parameters.

Thank in advance!

4 Answers 4

Usually it’s not a problem to pass a string to a parameter that’s numeric, as long as the SQL Server is able to convert the content of the string to a numeric value itself. If that doesn’t work, you get this error.

For example: Passing «Hello» to a parameter that’s numeric, you get an error. Passing «1234» you don’t. Please note that an empty string or a string containing whitespace can not be converted to a numeric value!

However, it should be said that it is not good style to do that. You should make sure that the types you use in your application match the types in the database to avoid problems. Maybe some further detail on why you need to have string types in your application could help.

EDIT 1
To make a parameter optional for the query, the way to go would be the following:

  1. Change your SQL statement to allow optional parameters like WHERE RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID) .
  2. Do not add the @Raumklasse_ID parameter if it should be optional or add the value DBNull.Value

You should really consider changing your string properties to nullable types like int? .

EDIT 2
This is how your code could look implementing the changes I suggested in Edit 1:

Источник

Sql server error converting nvarchar to numeric

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I’m running a program that throws this error when it executes a stored procedure. The stored procedure runs without error when I run it from SSMS. If I take the queries out of the stored procedure I can produce the error.

I have isolated the error to this particular query in the stored procedure.

Unfortunately I have tried to insert the query as code, but that doesn’t appear to work. So here it is.

declare @ActivityMonth [int]

declare @ActivityYear [int]

declare @SurveyID int

declare @BranchID varchar ( 50 )

set @ActivityMonth = 5

set @ActivityYear = 2013

set @SurveyID = 1869201

set @BranchID = ’19’

select QuestionID , avg ( cast ( [Value] as decimal )) as responseAVG

Response r WITH ( NOLOCK )

Respondent rt WITH ( NOLOCK ) on r . SurveyID = rt . SurveyID and r . Tieback = rt . Tieback

Contact c WITH ( NOLOCK ) on r . SurveyID = c . SurveyID and r . Tieback = c . Tieback

Question q WITH ( NOLOCK ) on r . SurveyID = q . SurveyID and r . QuestionID = q . ID

Respondent rd WITH ( NOLOCK ) on r . SurveyID = rd . SurveyID and r . Tieback = rd . Tieback

r . surveyid = @SurveyID and q . Style in ( ‘SelectOne’ , ‘SelectAll’ )

and datepart ( month , [Transaction Date] ) = @ActivityMonth

and datepart ( year , [Transaction Date] ) = @ActivityYear

and c . [Tag Field 1] = @BranchID

and isnumeric ( value ) = 1

and rd . SubmitFinal = 1

Answers

You have bad data in your table. That is the root problem. Then depending on the query plan, SQL Server attempts to convert the data to numeric or not. As long as you have a condition that filters out the bad data, you are not guaranteed to get the error. But just because you don’t get an error does not mean that the query is safe.

Keep in mind that SQL is a declarative language. You say what you want. Then the optimizer figures out the best way to determine the result. When you have bad data like this, the model breaks down a bit, because the optimizer permits itself to evaluate data before it is needed. Logically, from how SQL is defined, the condition on isnumeric in the WHERE clause should be enough (as long as isnumeric does not give full positives). But practically, you nede the CASE expression I showed you.

Источник

Sql server error converting nvarchar to numeric

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I’m running a program that throws this error when it executes a stored procedure. The stored procedure runs without error when I run it from SSMS. If I take the queries out of the stored procedure I can produce the error.

I have isolated the error to this particular query in the stored procedure.

Unfortunately I have tried to insert the query as code, but that doesn’t appear to work. So here it is.

declare @ActivityMonth [int]

declare @ActivityYear [int]

declare @SurveyID int

declare @BranchID varchar ( 50 )

set @ActivityMonth = 5

set @ActivityYear = 2013

set @SurveyID = 1869201

set @BranchID = ’19’

select QuestionID , avg ( cast ( [Value] as decimal )) as responseAVG

Response r WITH ( NOLOCK )

Respondent rt WITH ( NOLOCK ) on r . SurveyID = rt . SurveyID and r . Tieback = rt . Tieback

Contact c WITH ( NOLOCK ) on r . SurveyID = c . SurveyID and r . Tieback = c . Tieback

Question q WITH ( NOLOCK ) on r . SurveyID = q . SurveyID and r . QuestionID = q . ID

Respondent rd WITH ( NOLOCK ) on r . SurveyID = rd . SurveyID and r . Tieback = rd . Tieback

r . surveyid = @SurveyID and q . Style in ( ‘SelectOne’ , ‘SelectAll’ )

and datepart ( month , [Transaction Date] ) = @ActivityMonth

and datepart ( year , [Transaction Date] ) = @ActivityYear

and c . [Tag Field 1] = @BranchID

and isnumeric ( value ) = 1

and rd . SubmitFinal = 1

Answers

You have bad data in your table. That is the root problem. Then depending on the query plan, SQL Server attempts to convert the data to numeric or not. As long as you have a condition that filters out the bad data, you are not guaranteed to get the error. But just because you don’t get an error does not mean that the query is safe.

Keep in mind that SQL is a declarative language. You say what you want. Then the optimizer figures out the best way to determine the result. When you have bad data like this, the model breaks down a bit, because the optimizer permits itself to evaluate data before it is needed. Logically, from how SQL is defined, the condition on isnumeric in the WHERE clause should be enough (as long as isnumeric does not give full positives). But practically, you nede the CASE expression I showed you.

Источник

Sql server error converting nvarchar to numeric

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I am getting the same error when i execute the below code,

DECLARE @GrossClaimAmountEuro NVARCHAR(1000)
SELECT @GrossClaimAmountEuro = SUBSTRING(RTRIM(LTRIM(TempColumn)),0,LEN(RTRIM(LTRIM(TempColumn))) — LEN(‘Total Disputed Invoice Value’))
FROM fn_CCT_ConvertTextToRows(‘Customer Name : SPORT DANMARK A/S
Sold To # : 117799
Total Claim Value : 170,000.00
Total Disputed Invoice Value: 170,000.00
Total Claim Units : 13
Currency : EUR’)
WHERE RTRIM(LTRIM(TempColumn)) like ‘%Total Disputed Invoice Value%’
IF ISNUMERIC(@GrossClaimAmountEuro) = 0
SET @GrossClaimAmountEuro = ‘0’

SET @GrossClaimAmountEuro = REPLACE(@GrossClaimAmountEuro,’,’,»)
SELECT @GrossClaimAmountEuro
SELECT CONVERT(DECIMAL(15,4),LTRIM(RTRIM(‘170000.00 ‘)))
SELECT CONVERT(DECIMAL(15,4),LTRIM(RTRIM(@GrossClaimAmountEuro)))

The variable @GrossClaimAmountEuro holds holds a value ‘170000.00 ‘. When i hardcode the value and execute, the code works fine, if i give the variable name in the code, i am getting a error as,
«Msg 8114, Level 16, State 5, Line 18
Error converting data type nvarchar to numeric.»

I dont know.. what i am doing wrong..
Please help

Источник

Источник

Experts,

I have a sql query with an openrowset function,

I am trying to convert the data type of my resultset,

Below is the query:

Select
convert
(nvarchar(15),"[Dim Eng Routine ID].[Routine Hierarchy].[PLANT ID].[MEMBER_CAPTION]") as PlantId 
 
,convert(nvarchar(15),"[Dim Eng Routine ID].[Routine Hierarchy].[ROUTINE ID].[MEMBER_CAPTION]") as Routine_ID 
 
 
,convert(decimal(18,2), convert(nvarchar(100),"[Measures].[a1]")) as Mea_Sum_Squared 
 
,coalesce(convert(decimal(18,2), convert(nvarchar(100),"[Measures].[EstSigma2 Day]")),0) 
 
--, Case when 
 
-- convert(nvarchar(100),"[Measures].[EstSigma2 Day]") is null then 0.00 
 
-- else convert(decimal(18,9), convert(nvarchar(100),"[Measures].[EstSigma2 Day]")) 
 
-- end as [EstSigma2 Day] 
 
 
--,CASE ISNUMERIC(convert(decimal(18,2), convert(nvarchar(100),"[Measures].[EstSigma2 Day]"))) 
 
--When 1 then 0 
 
--Else convert(decimal(18,2), convert(nvarchar(100),"[Measures].[EstSigma2 Day]")) 
 
--End 
 
from (SELECT * 
FROM
OPENROWSET('MSOLAP', 'DATASOURCE=<data source name>;Initial Catalog=DPV1;' 
,
 
'With member [Measures].[a1] as [Measures].[Meas Sum SQUARED]
Select
{[Measures].[a1],
[Measures].[EstSigma2 Day]} on 0,
non empty({
[Dim Eng Routine ID].[Routine Hierarchy].[PLANT ID].members,
[Dim Eng Routine ID].[Routine Hierarchy].[ROUTINE ID].members
})
dimension properties MEMBER_CAPTION, MEMBER_KEY ON 1
from [DPV_1]
Where [Dim Shift].[Week Hierarchy].[YEAR KEY].&[2010]'
)
 
union
all
 
SELECT
*
 
FROM
OPENROWSET('MSOLAP', 'DATASOURCE=<data source name>;Initial Catalog=DPV2;' 
,
 
'With member [Measures].[a1] as [Measures].[Meas Sum SQUARED]
Select
{[Measures].[a1],
[Measures].[EstSigma2 Day]} on 0,
non empty({
[Dim Eng Routine ID].[Routine Hierarchy].[PLANT ID].members,
[Dim Eng Routine ID].[Routine Hierarchy].[ROUTINE ID].members
})
dimension properties MEMBER_CAPTION, MEMBER_KEY ON 1
from [DPV_1]
Where [Dim Shift].[Week Hierarchy].[YEAR KEY].&[2010]'
))a

When I execute this query I get the

Error converting data type nvarchar to numeric.

How can I achieve this?

Thanks,

Ron

  • Edited by

    Friday, May 13, 2011 7:12 PM

Источник

You can’t easily control the order in which SQL Server will evaluate the contents of a column that does not use the correct data type (or has mismatched precision). If you try to cast a column that is nvarchar to numeric, even if you have filters that should eliminate all non-numeric values from consideration, SQL Server can still try those first (see Erland’s complaint about this on UserVoice). There are cases where you can nest CTEs until the cows come home, but SQL Server will still push or pull that evaluation to a place where you didn’t expect it.

As long as aggregates and fulltext functions aren’t involved (see here and here), you can force this evaluation before conversion by using a CASE expression. Here is a simplified version of your query with no CTEs:

SELECT cfv.issue,
  priority_num = CONVERT(NUMERIC(something, something), 
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END)
FROM
  proddb1.customfieldvalue AS cfv
  INNER JOIN proddb1.customfield AS cf
  ON cfv.CUSTOMFIELD = cf.id
  INNER JOIN proddb1.customfieldoption AS cfo
  ON cfv.CUSTOMFIELD = cfo.CUSTOMFIELD
  AND CONVERT(NUMERIC(something, something), 
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END) = cfo.id
  WHERE cf.cfname = 'Issue Priority';

You will need to change something, something to the proper precision/scale (or use one of the int types if you don’t need decimal places). You should never declare varying types without specifying the length — this blog post is about varchar, but it really applies to all of the types.

If you are on SQL Server 2012 (it’s always useful to include information like what version of SQL Server you’re using), you can simplify this:

CONVERT(NUMERIC(something, something), 
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END)

to this:

TRY_CONVERT(NUMERIC(something, something), cfo.customvalue)

And it will actually be more reliable (since ISNUMERIC can return 1 and still fail at conversion time for specific types). I blogged about this way back in 2002.

Источник

Hi Everyone,

I’m running into an issue in ODI when trying to load dates (MM/DD/YYYY) to an Essbase ASO cube (Target) with Microsoft SQL Server (Source) with a staging area. I have tried everything to the best of my knowledge to integrate the date data to Essbase. The source datatype is in varchar and even changed the datatype in Essbase to date when I reverse engineered the account dimensions. I have even tried to cast and convert the target and still getting the same error below. If anyone had run into this issue, please shed some light.

ODI-1228: Task SrcSet0 (Loading) fails on the target MICROSOFT_SQL_SERVER connection SRB-SQL3_SQL3.
Caused By: weblogic.jdbc.sqlserverbase.ddc: [FMWGEN][SQLServer JDBC Driver][SQLServer]Error converting data type nvarchar to numeric.
at weblogic.jdbc.sqlserver.ddj.k(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddde.executeBatch(Unknown Source)
at oracle.odi.runtime.agent.execution.sql.BatchSQLCommand.execute(BatchSQLCommand.java:44)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:87)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)

Thank you all in advance for your help!

Hardy

Источник

i m making a registration form… where i want the should go in data table according to choice selected by user…
but when i click the «submit» button. it shows me an error like this…

Server Error in '/project@water_billing_system' Application.

Error converting data type nvarchar to numeric.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.

Source Error: 


Line 521:            cmd.Parameters.Add(prm_ac_code_password);
Line 522:
Line 523:            cmd.ExecuteNonQuery();
Line 524:            conn.Close();
Line 525:        }

Source File: f:water billing systemproject@water_billing_systemonline_redistration.aspx.cs    Line: 523

i m using stored procedure for this…
and the store procedure is like below.

Quote:

@registration_datetime datetime,
@ac_code_id varchar(8),
@ac_password varchar(max),
@owner_surname varchar(30),
@owner_first_name varchar(30),
@owner_middle_name varchar(30),
@owner_DOB date,
@add_of_c_industry varchar(MAX),
@handler1_name varchar(50),
@handler1_mobile numeric(14,0),
@handler1_landline numeric(18,0),
@handler1_email varchar(50),
@handler2_name varchar(50),
@handler2_mobile numeric(14,0),
@handler2_landline numeric(18,0),
@handler2_email varchar(50),
@handler3_name varchar(50),
@handler3_mobile numeric(14,0),
@handler3_landline numeric(18,0),
@handler3_email varchar(50),
@connection_type_id int,
@connection_size_id int,
@connection_plan_id int,
@connection_start_wish date

all the data type is same in sqltable as above…

and this is my «aspx.cs» code…

protected void btn_submit_Click(object sender, EventArgs e)
    {
        string owner_birthdate,wish_start_from,ac_code_id;
        owner_birthdate = ddl_owner_dob_mm.Text + '/' + ddl_owner_dob_dd.Text + '/' + ddl_owner_dob_yyyy.Text;
        wish_start_from=ddl_conn_wish_mm.Text+'/'+ddl_conn_wish_dd.Text+'/'+ddl_conn_wish_yyyy.Text;

        if (rbl_conn_for.SelectedValue == "1")
        {
            string hp="HP";

            conn.Open();
            SqlCommand cmd = new SqlCommand("insert_into_reg_housing_plot",conn);
            cmd.CommandType = CommandType.StoredProcedure;


            SqlParameter prm_datetime = new SqlParameter(lbl_date_time.Text, "@registration_datetime");
            cmd.Parameters.Add(prm_datetime);

            SqlParameter prm_owner_surname = new SqlParameter("@owner_surname", txt_owner_surname.Text);
            cmd.Parameters.Add(prm_owner_surname);

            SqlParameter prm_owner_first_name = new SqlParameter("@owner_first_name", txt_owner_first_name.Text);
            cmd.Parameters.Add(prm_owner_first_name);

            SqlParameter prm_owner_middle_name = new SqlParameter("@owner_middle_name", txt_owner_middle_name.Text);
            cmd.Parameters.Add(prm_owner_middle_name);

            SqlParameter prm_owner_DOB = new SqlParameter("@owner_DOB", owner_birthdate);
            cmd.Parameters.Add(prm_owner_DOB);

            SqlParameter prm_add_housing_plot = new SqlParameter("@add_of_housing_plot", txt_address_housing_plot.Text);
            cmd.Parameters.Add(prm_add_housing_plot);

            SqlParameter prm_handler1_name = new SqlParameter("@handler1_name", txt_handler1_name.Text);
            cmd.Parameters.Add(prm_handler1_name);

            SqlParameter prm_handler1_mobile = new SqlParameter("@handler1_mobile", txt_handler1_mobile.Text);
            cmd.Parameters.Add(prm_handler1_mobile);

            SqlParameter prm_handler1_landline = new SqlParameter("@handler1_landline", txt_handler1_landline.Text);
            cmd.Parameters.Add(prm_handler1_landline);

            SqlParameter prm_handler1_email = new SqlParameter("@handler1_email", txt_handler1_email.Text);
            cmd.Parameters.Add(prm_handler1_email);

            SqlParameter prm_handler2_name = new SqlParameter("@handler2_name", txt_handler2_name.Text);
            cmd.Parameters.Add(prm_handler2_name);

            SqlParameter prm_handler2_mobile = new SqlParameter("@handler2_mobile", txt_handler2_mobile.Text);
            cmd.Parameters.Add(prm_handler2_mobile);

            SqlParameter prm_handler2_landline = new SqlParameter("@handler2_landline", txt_handler2_landline.Text);
            cmd.Parameters.Add(prm_handler2_landline);

            SqlParameter prm_handler2_email = new SqlParameter("@handler2_email", txt_handler2_email.Text);
            cmd.Parameters.Add(prm_handler2_email);

            SqlParameter prm_handler3_name = new SqlParameter("@handler3_name", txt_handler3_name.Text);
            cmd.Parameters.Add(prm_handler3_name);

            SqlParameter prm_handler3_mobile = new SqlParameter("@handler3_mobile", txt_handler3_mobile.Text);
            cmd.Parameters.Add(prm_handler3_mobile);

            SqlParameter prm_handler3_landline = new SqlParameter("@handler3_landline", txt_handler3_landline.Text);
            cmd.Parameters.Add(prm_handler3_landline);

            SqlParameter prm_handler3_email = new SqlParameter("@handler3_email", txt_handler3_email.Text);
            cmd.Parameters.Add(prm_handler3_email);

            if (rbl_connection_type.SelectedValue == "3")
            {
                SqlParameter prm_yearly_conn_type = new SqlParameter("@connection_type_id",rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_yearly_conn_type);

                SqlParameter prm_fix_yearly_size = new SqlParameter("@connection_size_id",ddl_conn_size_selection_for_fixed_yearly_plan.SelectedValue);
                cmd.Parameters.Add(prm_fix_yearly_size);

                SqlParameter prm_fix_yearly_plan = new SqlParameter("@connection_plan_id", ddl_plan_selection_for_fixed_yearly_conn.SelectedValue);
                cmd.Parameters.Add(prm_fix_yearly_plan);
            }
            else if (rbl_connection_type.SelectedValue == "1")
            {
                SqlParameter prm_monthly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_monthly_conn_type);

                SqlParameter prm_fix_monthly_size = new SqlParameter("@connection_size_id", ddl_conn_size_selection_for_fixed_plant.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_size);

                SqlParameter prm_fix_monthly_plan = new SqlParameter("@connection_plan_id", ddl_plan_selection_for_fixed_conn.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_plan);
            }
            else if (rbl_connection_type.SelectedValue == "2")
            {
                SqlParameter prm_monthly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_monthly_conn_type);

                SqlParameter prm_fix_monthly_plan = new SqlParameter("@connection_plan_id", ddl_conn_size_for_meter_plan.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_plan);
            }

            SqlParameter prm_start_wish = new SqlParameter("@connection_start_wish",wish_start_from);
            cmd.Parameters.Add(prm_start_wish);


            SqlCommand ac = new SqlCommand("select max(ac_code_int) from registration_for_housing_plot",conn);
            object a;
            a=ac.ExecuteScalar();
            if (a.ToString() == "")
        {
            a="1001";
             ac_code_id= hp + a;
        }
        else
        {
            Int64 v;
            v = (Int64.Parse(a.ToString())) + 1;
            ac_code_id = hp + v.ToString();
            
           
        }
        
        SqlParameter prm_ac_code_id = new SqlParameter("@ac_code_id", ac_code_id);
        cmd.Parameters.Add(prm_ac_code_id);

        ac.ExecuteNonQuery();

        SqlParameter prm_ac_code_password = new SqlParameter("@ac_password",txt_confirm_password.Text);
        cmd.Parameters.Add(prm_ac_code_password);

        cmd.ExecuteNonQuery();
            conn.Close();
        }
        else if (rbl_conn_for.SelectedValue == "2")
        {
            string ci = "CI";

            conn.Open();
            SqlCommand cmd = new SqlCommand("insert_into_reg_c_industry", conn);
            cmd.CommandType = CommandType.StoredProcedure;


            SqlParameter prm_datetime = new SqlParameter("@registration_datetime", lbl_date_time.Text);
            cmd.Parameters.Add(prm_datetime);

            SqlParameter prm_owner_surname = new SqlParameter("@owner_surname", txt_owner_surname.Text);
            cmd.Parameters.Add(prm_owner_surname);

            SqlParameter prm_owner_first_name = new SqlParameter("@owner_first_name", txt_owner_first_name.Text);
            cmd.Parameters.Add(prm_owner_first_name);

            SqlParameter prm_owner_middle_name = new SqlParameter("@owner_middle_name", txt_owner_middle_name.Text);
            cmd.Parameters.Add(prm_owner_middle_name);

            SqlParameter prm_owner_DOB = new SqlParameter("@owner_DOB", owner_birthdate);
            cmd.Parameters.Add(prm_owner_DOB);

            SqlParameter prm_add_c_industry = new SqlParameter("@add_of_c_industry", txt_address_commercial_industry.Text);
            cmd.Parameters.Add(prm_add_c_industry);

            SqlParameter prm_handler1_name = new SqlParameter("@handler1_name", txt_handler1_name.Text);
            cmd.Parameters.Add(prm_handler1_name);

            SqlParameter prm_handler1_mobile = new SqlParameter("@handler1_mobile", txt_handler1_mobile.Text);
            cmd.Parameters.Add(prm_handler1_mobile);

            SqlParameter prm_handler1_landline = new SqlParameter("@handler1_landline", txt_handler1_landline.Text);
            cmd.Parameters.Add(prm_handler1_landline);

            SqlParameter prm_handler1_email = new SqlParameter("@handler1_email", txt_handler1_email.Text);
            cmd.Parameters.Add(prm_handler1_email);

            SqlParameter prm_handler2_name = new SqlParameter("@handler2_name", txt_handler2_name.Text);
            cmd.Parameters.Add(prm_handler2_name);

            SqlParameter prm_handler2_mobile = new SqlParameter("@handler2_mobile", txt_handler2_mobile.Text);
            cmd.Parameters.Add(prm_handler2_mobile);

            SqlParameter prm_handler2_landline = new SqlParameter("@handler2_landline", txt_handler2_landline.Text);
            cmd.Parameters.Add(prm_handler2_landline);

            SqlParameter prm_handler2_email = new SqlParameter("@handler2_email", txt_handler2_email.Text);
            cmd.Parameters.Add(prm_handler2_email);

            SqlParameter prm_handler3_name = new SqlParameter("@handler3_name", txt_handler3_name.Text);
            cmd.Parameters.Add(prm_handler3_name);

            SqlParameter prm_handler3_mobile = new SqlParameter("@handler3_mobile", txt_handler3_mobile.Text);
            cmd.Parameters.Add(prm_handler3_mobile);

            SqlParameter prm_handler3_landline = new SqlParameter("@handler3_landline", txt_handler3_landline.Text);
            cmd.Parameters.Add(prm_handler3_landline);

            SqlParameter prm_handler3_email = new SqlParameter("@handler3_email", txt_handler3_email.Text);
            cmd.Parameters.Add(prm_handler3_email);

            if (rbl_connection_type.SelectedValue == "3")
            {
                SqlParameter prm_yearly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_yearly_conn_type);

                SqlParameter prm_fix_yearly_size = new SqlParameter("@connection_size_id", ddl_conn_size_selection_for_fixed_yearly_plan.SelectedValue);
                cmd.Parameters.Add(prm_fix_yearly_size);

                SqlParameter prm_fix_yearly_plan = new SqlParameter("@connection_plan_id", ddl_plan_selection_for_fixed_yearly_conn.SelectedValue);
                cmd.Parameters.Add(prm_fix_yearly_plan);
            }
            else if (rbl_connection_type.SelectedValue == "1")
            {
                SqlParameter prm_monthly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_monthly_conn_type);

                SqlParameter prm_fix_monthly_size = new SqlParameter("@connection_size_id", ddl_conn_size_selection_for_fixed_plant.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_size);

                SqlParameter prm_fix_monthly_plan = new SqlParameter("@connection_plan_id", ddl_plan_selection_for_fixed_conn.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_plan);
            }
            else if (rbl_connection_type.SelectedValue == "2")
            {
                SqlParameter prm_monthly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_monthly_conn_type);

                SqlParameter prm_fix_monthly_plan = new SqlParameter("@connection_plan_id", ddl_conn_size_for_meter_plan.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_plan);
            }

            SqlParameter prm_start_wish = new SqlParameter("@connection_start_wish", wish_start_from);
            cmd.Parameters.Add(prm_start_wish);


            SqlCommand ac = new SqlCommand("select max(ac_code_int) from registration_for_comercial_industry",conn);
            object a;
            a = ac.ExecuteScalar();
            if (a.ToString() == "")
            {
                a = "1001";
                ac_code_id = ci + a;
            }
            else
            {
                Int64 v;
                v = (Int64.Parse(a.ToString())) + 1;
                ac_code_id = ci + v.ToString();


            }
            
            SqlParameter prm_ac_code_id = new SqlParameter("@ac_code_id", ac_code_id);
            cmd.Parameters.Add(prm_ac_code_id);

            ac.ExecuteNonQuery();

            SqlParameter prm_ac_code_password = new SqlParameter("@ac_password", txt_confirm_password.Text);
            cmd.Parameters.Add(prm_ac_code_password);

            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }

plzz help me solve this…
thanks a lot in advance… :)

Источник

Понравилась статья? Поделить с друзьями:
  • Error control flow control
  • Error continuous value supplied to discrete scale
  • Error continue statement not within a loop
  • Error context php
  • Error content0 witcher 3