CODEWITHSUNDEEP
sql.net
Mash
Mash

Reputation: 159

Error converting data type nvarchar to int

I have a problem and i dont know where the problem lies.My visual studio throws an exception when i enter the details and click on the submit button on my REGISTER page.I dont know if the problem lies in my SQL query or my code.So i am posting both

This is my Sql query:

        Create proc spRegisteredUsers

        @Name nvarchar(100),
        @UserName nvarchar(100),
        @Password nvarchar(200),
        @ContactNo int,
        @Email nvarchar(200)
         as
        Begin

       Declare @Count int
       Declare @ReturnCode int

       Select @Count = COUNT(UserName)
       from tblRegisteredUsers where UserName=@UserName
       if @Count>0
       Begin
       Set @ReturnCode= -1
       End
       Else
       Begin
       Set @ReturnCode= 1
       Insert into tblRegisteredUsers values(@Name,@UserName,@Password,@ContactNo,@Email)
       End
       Select @ReturnCode as ReturnValue
        End

whereas i get an exception on the line below:

           protected void btnRegSubmit_Click(object sender, EventArgs e)
          {
         if (Page.IsValid)
          {
          string CS = ConfigurationManager.ConnectionStrings["AK"].ConnectionString;
           using (SqlConnection con = new SqlConnection(CS))
           {
            SqlCommand cmd = new SqlCommand("spRegisteredUsers", con);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter Name = new SqlParameter("@Name", txtRegName.Text);
            SqlParameter UserName = new SqlParameter("@UserName", txtRegUsername.Text);
            SqlParameter Password = new SqlParameter("@Password", txtRegPassword.Text);
            SqlParameter ContactNo = new SqlParameter("@ContactNo", txtRegContact.Text);
            SqlParameter Email = new SqlParameter("@Email", txtRegEmail.Text);

            cmd.Parameters.Add(Name);
            cmd.Parameters.Add(UserName);
            cmd.Parameters.Add(Password);
            cmd.Parameters.Add(ContactNo);
            cmd.Parameters.Add(Email);

            con.Open();
            int ReturnCode = Convert.ToInt32(cmd.ExecuteScalar().ToString());
            if (ReturnCode == -1)
            {
                lblMessage.Text = "UserName already exists.";
            }
            else
            {
                Response.Redirect("~/Login.aspx");
            }
        }
    }

}

One funny thing i saw.When i remove Name and ContactNo fields from the table,stored procedure and cs code.IT WORKS.

Upvotes: 0

Views: 21518

Answers (2)

Milen
Milen

Reputation: 8867

try this instead:

 using (SqlConnection con = new SqlConnection(CS))
           {
            SqlCommand cmd = new SqlCommand("spRegisteredUsers", con);
            cmd.CommandType = CommandType.StoredProcedure;
            //use long instead...
            long Contact = Convert.ToInt64(txtRegContact.Text); //convert to long here 

            SqlParameter Name = new SqlParameter("@Name", txtRegName.Text);
            SqlParameter UserName = new SqlParameter("@UserName", txtRegUsername.Text);
            SqlParameter Password = new SqlParameter("@Password", txtRegPassword.Text);

            SqlParameter ContactNo = new SqlParameter("@ContactNo", Contact);

            SqlParameter Email = new SqlParameter("@Email", txtRegEmail.Text);
            ......

you're passing a string and SQL Procedure expects int for ContactNo.

Upvotes: 2

Zafua
Zafua

Reputation: 51

You will probably getting error in first line. Try this:

int ReturnCode = Convert.ToInt32(cmd.ExecuteScalar().ToString());

Upvotes: 0

Related Questions