Click here to Skip to main content
15,907,392 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
My code
C#
Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
       Dim conn As New SqlConnection(connectionString)
       Dim cmd As New SqlCommand(query, conn)
       cmd.CommandType = CommandType.StoredProcedure
       cmd.Parameters.AddWithValue("@fName", txtFirstName.Text)
       cmd.Parameters.AddWithValue("@Lname", txtLName.Text)
       cmd.Parameters.AddWithValue("@Sex", CBSex.SelectedItem.ToString)
       cmd.Parameters.AddWithValue("@DOB", Convert.ToDateTime(DTDOB.Text))
       cmd.Parameters.AddWithValue("@Age", txtAge.Text)
       cmd.Parameters.AddWithValue("@Address", txtAddress.Text)
       cmd.Parameters.AddWithValue("@phno", txtPhNo.Text)
       cmd.Parameters.AddWithValue("@Email", txtEmail.Text)
       cmd.Parameters.AddWithValue("@IdNo", txtIDNo.Text)
       cmd.Parameters.AddWithValue("@contactPerson", txtContactPerson.Text)
       cmd.Parameters.AddWithValue("@Relation", txtRelation.Text)
       cmd.Parameters.AddWithValue("@claimid", SqlDbType.VarChar)
       cmd.Parameters("@claimid").Direction = ParameterDirection.Output
       cmd.Parameters.AddWithValue("@cpPhno", txtCPPhno.Text)
       cmd.Parameters.AddWithValue("@Photo", profilepicture)
       cmd.Parameters.AddWithValue("@ID", idorpassport)
       Dim a As Integer
       conn.Open()
       a = cmd.ExecuteNonQuery()
       If a > 0 Then
           MessageBox.Show("Data Added successfully and your clientID is :" & cmd.Parameters("@claimid").Value.ToString())
       Else
           MessageBox.Show("Please try Again")
       End If

My storedprocedure
SQL
@fName varchar(50),
@Lname Varchar(50),
@Sex  varchar(2),
@DOB date,
@Age int,
@Address varchar(500),
@phno varchar(15),
@Email Varchar(100),
@IdNo varchar(30),
@contactPerson varchar(50),
@Relation Varchar(50),
@claimid varchar(30)output,
@cpPhno varchar(15),
@Photo image,
@ID image
AS
BEGIN
if @Sex='Male' 
begin
set @Sex='M'
end
if @Sex='Female' 
begin
set @Sex='F'
end

	insert into Client(FirstName,LastName,Sex,DOB,Age,[Address],PhNo,email,[IDCard/passNumber],ContactPerson,Relation,TelephoneNOContact,Created_date,photo,
	[pass/idcard]) values(@fName,@Lname,@Sex,@DOB,@Age,@Address,@phno,@Email,@IdNo,@contactPerson,@Relation,@cpPhno,GETDATE(),@Photo,@ID)
	set @claimid='CT-00000'+convert(varchar,SCOPE_IDENTITY())
	update Client set  ClientID=@claimid where ClientCode=SCOPE_IDENTITY()
	return @claimid
END


What I have tried:

I am getting the error cannot convert varchar to type int please solve this
Posted
Updated 18-Oct-16 3:49am
Comments
F-ES Sitecore 18-Oct-16 8:44am    
How do you expect to convert "CT-000001" into an int? If your code only cares about the new ID and not the "CT-" bit then return just the SCOPE_IDENTITY, not the @claimed text.

1 solution

The return value for a stored procedure (the part after the Return keyword) must always be an int.

You are trying to return a varchar containing a value which cannot be converted to an int. Hence the error.

Since you're not using the return value of the procedure, just remove the return @claimid line. The value will still be returned to the caller using the output parameter.
 
Share this answer
 
Comments
RajkumarGnanaraj 19-Oct-16 3:02am    
@Richard Deeming i want varchar as output i removed return @claimid line .but i am getting same error
RajkumarGnanaraj 19-Oct-16 3:10am    
Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
Dim conn As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(query, conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@fName", txtFirstName.Text)
cmd.Parameters.AddWithValue("@Lname", txtLName.Text)
cmd.Parameters.AddWithValue("@Sex", CBSex.SelectedItem.ToString)
cmd.Parameters.AddWithValue("@DOB", Convert.ToDateTime(DTDOB.Text))
cmd.Parameters.AddWithValue("@Age", txtAge.Text)
cmd.Parameters.AddWithValue("@Address", txtAddress.Text)
cmd.Parameters.AddWithValue("@phno", txtPhNo.Text)
cmd.Parameters.AddWithValue("@Email", txtEmail.Text)
cmd.Parameters.AddWithValue("@IdNo", txtIDNo.Text)
cmd.Parameters.AddWithValue("@contactPerson", txtContactPerson.Text)
cmd.Parameters.AddWithValue("@Relation", txtRelation.Text)
cmd.Parameters.AddWithValue("@claimid", SqlDbType.VarChar)
cmd.Parameters("@claimid").Direction = ParameterDirection.Output
cmd.Parameters.AddWithValue("@cpPhno", txtCPPhno.Text)
cmd.Parameters.AddWithValue("@Photo", profilepicture)
cmd.Parameters.AddWithValue("@ID", idorpassport)
Dim a As Integer
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
'If Not String.IsNullOrEmpty(cmd.Parameters("@claimid").Value.ToString) Then
If a > 0 Then
MessageBox.Show("Data Added successfully and your clientID is :" & cmd.Parameters("@claimid").Value.ToString)
Else
MessageBox.Show("Please try Again")
End If

BEGIN
if @Sex='Male'
begin
set @Sex='M'
end
if @Sex='Female'
begin
set @Sex='F'
end

insert into Client(FirstName,LastName,Sex,DOB,Age,[Address],PhNo,email,[IDCard/passNumber],ContactPerson,Relation,TelephoneNOContact,Created_date,photo,
[pass/idcard]) values(@fName,@Lname,@Sex,@DOB,@Age,@Address,@phno,@Email,@IdNo,@contactPerson,@Relation,@cpPhno,GETDATE(),@Photo,@ID)

select @claimid=ClientID from dbo.Client where clientcode=SCOPE_IDENTITY()
End

I changed like this
Richard Deeming 19-Oct-16 8:10am    
That means you're trying to insert a varchar into an int column.

Check the data types of the columns you're inserting into, and make sure the stored procedure parameter types match.

Then check that the parameters you're creating in your VB.NET code match the stored procedure parameter types.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900