Solved

Error converting data type nvarchar to int.

Posted on 2009-05-18
23
1,661 Views
Last Modified: 2012-05-07
Hello,
 I have a page to insert data in a table Survey... I keep getting this error:
 Error converting data type nvarchar to int.
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 int.

Source Error:

Line 216:        
Line 217:            //int SurveyID = Convert.ToInt32(cmd.ExecuteScalar());
Line 218:            cmd.ExecuteNonQuery();
Line 219:        
Line 220:            myConnection.Close();
see attached the procedure
See below my cs code
protected void Save_Evaluation_CLick(object sender, EventArgs e)

    {

        RadTabStrip1.Tabs[0].Enabled = true;

        RadTabStrip1.Tabs[1].Enabled = true;

        RadTabStrip1.Tabs[2].Enabled = true;

        RadTabStrip1.Tabs[3].Enabled = true;

        RadTabStrip1.Tabs[4].Enabled = true;

        //RadTabStrip1.Tabs[5].Enabled = true;

        RadMultiPage1.SelectedIndex = 0;

        RadTabStrip1.SelectedIndex = 0;

        PageView1.Focus();

        Console.Write("You have succesfully entered the participant information");
 
 
 

        if (!IsEmpty(Request.QueryString["r"]))

        {

            // create the insert command

            //SqlCommand cmd;

            SqlCommand cmd = new SqlCommand("sp_SurveyInsertInfo", myConnection);

            cmd.CommandType = CommandType.StoredProcedure;
 

            cmd.Parameters.Add(new SqlParameter("@ParticipantID", txtParticipantID.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@SchoolID", ddlSchoolName.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@EvaluationVisitID", txtEvalVisitID.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@Gender", txtGender.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@SchoolStatus", txtSchoolStatus.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@Age", txtAge.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@Race_Ethnicity", txtRace.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@PrimaryLanguage", txtPrimLanguage.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@HealthCoverage", rdbHealthCoverage.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@ChildHealthCoverage", rdbChildHealthCoverage.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@Pregnant", rdlPregnant.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@Months_Weeks_Pregnant", txtMonthsPregnant.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@TimesPregnant", txtPregnantTimes.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@PregnantSincePPT", rdbPregnantSincePPT.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@Live_with", ddlLiveWith.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@OtherLive_with", txtOtherLivewith.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@Parenting", ddlParenting.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@GoodParent", ddlGoodParent.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@Contraception", rblAvoidPreg.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@OtherContraception", txtOtherPregAvoid.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@FuturePlanning", ddlFuturePlanning.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@DecisionMaker", ddlDecisionMaker.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@GraduationImp", ddlGraduationImp.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@College_Training", ddlCollege_Training.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@College_Training_Kinds", ddlCollege_Training_Kinds.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@ParentSupport", ddlParentSupport.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@Father_Babe_Rlt", ddlFather_Babe_Rlt.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@Satisfaction_NHF", ddlSatisfaction_NHF.SelectedValue));

            cmd.Parameters.Add(new SqlParameter("@Skills_Services", ddlSkills_Services.SelectedValue));

        

                    

        myConnection.Open();

        

            //int SurveyID = Convert.ToInt32(cmd.ExecuteScalar());

            cmd.ExecuteNonQuery();

        

            myConnection.Close();

    }
 
 
 

    }

Open in new window

procedure.txt
0
Comment
Question by:Romacali
  • 9
  • 7
  • 5
  • +1
23 Comments
 
LVL 9

Expert Comment

by:cdaly33
ID: 24417411
At the end of your procedure instead of the select statement you have there now try:
SELECT SCOPE_IDENTITY() as SurveyID

Open in new window

0
 
LVL 9

Expert Comment

by:cdaly33
ID: 24417413
Sorry, I meant:
SELECT @@SCOPE_IDENTITY() as SurveyID

Open in new window

0
 
LVL 9

Expert Comment

by:cdaly33
ID: 24417423
I'm have trouble tonight because I messed up again.  I meant:

Also have you made sure that it's inserting the rows correctly?
SELECT @@IDENTITY() as SurveyID

Open in new window

0
 
LVL 12

Expert Comment

by:williamcampbell
ID: 24417449
SELECT CAST(SCOPE_IDENTITY() AS INT)  as SurveyID

In your procedure Maybe?

What type is SurveyID
0
 

Author Comment

by:Romacali
ID: 24417459
still the same error :(
0
 

Author Comment

by:Romacali
ID: 24417466
the SurveyID as int
0
 
LVL 9

Expert Comment

by:cdaly33
ID: 24417472
Were you able to verify that the row is created in the tbl_Survey table?
0
 

Author Comment

by:Romacali
ID: 24417492
nope.. it doesn't create a row on the table :(.. I'm very lost
0
 
LVL 9

Expert Comment

by:cdaly33
ID: 24417533
Ok, so let's take a couple steps back.  If the row isn't making it in the table then we need to start there.  The first step would be to verify that you're providing all of the information correctly to the stored procedure.  You need to manually run the stored proc in query analyzer and see if/where its erroring.  From there you'll be able to see what data is missing or not of the correct type which is causing the error.
0
 

Author Comment

by:Romacali
ID: 24417811
I tested in the procedure and it worked. Not sure why the cs is not.
0
 

Author Comment

by:Romacali
ID: 24417821
Error converting data type nvarchar to int.
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 int.

Source Error:

Line 215:        myConnection.Open();
Line 216:        
Line 217:       int SurveyID = Convert.ToInt32(cmd.ExecuteScalar());
Line 218:            cmd.ExecuteNonQuery();
Line 219:        


Source File: c:\WWWroot\nhfca\beta\PPT\SurveyData\evaluationTEST.aspx.cs    Line: 217
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 9

Expert Comment

by:cdaly33
ID: 24417868
When you say the procedure worked do you mean that the row was inserted in to the table and the identity was correctly returned?  If so then there must be something in the cs code that isn't assigning the variables the same when you run it from query analyzer.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24417880
>>I tested in the procedure and it worked. Not sure why the cs is not.<<
That is because the errror is not coming from your stored procedure.  Here is the reason as posted by cdaly33 #24417533 (no points please):
"The first step would be to verify that you're providing all of the information correctly to the stored procedure."

You are supplying the wrong value to the parameter.  For example, @ParticipantID is integer and txtParticipantID.Text is non-integer.

Unfortunately, until you post your stored procedure sp_SurveyInsertInfo all we can do is make wild guesses as to the exact problem.
0
 

Author Comment

by:Romacali
ID: 24417974
not sure what else I can do :(
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24418000
>>not sure what else I can do<<
Perhaps you overlooked my comment:
"Unfortunately, until you post your stored procedure sp_SurveyInsertInfo all we can do is make wild guesses as to the exact problem."

So perhaps you can post your Stored Procedure.
0
 
LVL 9

Expert Comment

by:cdaly33
ID: 24418024
acperkins the procedure was already posted as attachment in the original post
0
 

Author Comment

by:Romacali
ID: 24418029
see my procedure attached.
procedure.txt
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24418076
>>acperkins the procedure was already posted as attachment in the original post<<
You are absolutely right.  What baffles me then is why nobody pointed out that the author is missing a parameter.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24418086
>>see my procedure attached.<<

Have you verified that the values for the following are in fact integers?
txtParticipantID.Text                    
txtEvalVisitID.Text                      
txtGender.Text                          
txtAge.Text                              
txtRace.Text                            
txtPrimLanguage.Text                    
txtMonthsPregnant.Text                  
txtPregnantTimes.Text                                        
                           
0
 

Author Comment

by:Romacali
ID: 24423202
in the database it is is int for all
for the ParticipantID it is coming from the page  from the other page as in the postback as:
PostBackUrl='<%#"evaluation.aspx?r="+Eval("ParticipantID")+"&"+"e="+Eval("EvaluationVisitID")  %>'
?r=20&e=39

these:
txtGender.Text                          
txtAge.Text                              
txtRace.Text                            
txtPrimLanguage.Text                    
txtMonthsPregnant.Text                  
txtPregnantTimes.Text      
they are numeric
0
 
LVL 12

Expert Comment

by:williamcampbell
ID: 24423336
quote "in the database it is is int for all"

      @OtherLive_with as nvarchar(50),
      @OtherContraception as nvarchar(50),

These are two non int fields
0
 

Author Comment

by:Romacali
ID: 24423542
on the database the nvarchar of 50 are:
PrimaryLanguage
OtherLive_with
OtherContraception

the problem is they all except NULL but SuveyID
 maybe the problem is that some fields are empty ? when I submit?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24424731
>>in the database it is is int for all<<
You are missing my point. I know they are integers in the database.  I am asking if you have verified that the values passed to the stored procedure are in fact integers.

Finally have you added the missing parameter?
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now