# Error converting data type nvarchar to int.

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)
{
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;

myConnection.Open();

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

myConnection.Close();
}

}

procedure.txt
###### Who is Participating?

Commented:
>>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

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

0

Commented:
Sorry, I meant:
SELECT @@SCOPE_IDENTITY() as SurveyID

0

Commented:
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

0

Commented:
SELECT CAST(SCOPE_IDENTITY() AS INT)  as SurveyID

What type is SurveyID
0

Author Commented:
still the same error :(
0

Author Commented:
the SurveyID as int
0

Commented:
Were you able to verify that the row is created in the tbl_Survey table?
0

Author Commented:
nope.. it doesn't create a row on the table :(.. I'm very lost
0

Commented:
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 Commented:
I tested in the procedure and it worked. Not sure why the cs is not.
0

Author Commented:
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

Commented:
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

Commented:
>>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 Commented:
not sure what else I can do :(
0

Commented:
>>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

Commented:
acperkins the procedure was already posted as attachment in the original post
0

Author Commented:
see my procedure attached.
procedure.txt
0

Commented:
>>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

Commented:
>>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 Commented:
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

Commented:
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 Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.