Mark Schindler
3/16/2007 4:01:00 PM
Hello
I'm updating DB from the website. I'm using a simple SP to update hours in
the DB. Hours is smallint type, I'm getting an error:
Error converting data type nvarchar to smallint
Here is the SP:
CREATE PROCEDURE DBUpdate_Hrs
(
@emp_id Varchar( 100 ),
@emp_hrs smallint,
@emp_date smalldatetime,
@project_number Varchar( 100 )
)
AS
IF EXISTS( SELECT ID
FROM Project
WHERE Project_number=@project_number )
INSERT Project_hrs (
Emp_id,
emp_hrs,
emp_date,
project_number
) VALUES (
@emp_id,
@emp_hrs,
@emp_date,
@project_number
)
ELSE
RETURN - 1
RETURN @@IDENTITY
GO
and here is the part of the code from the web which makes this happen:
If IsValid Then
conMyData = New SqlConnection(
"Server=mtsda;UID=xxxxxx;PWD=xxxxxx;Database=xxxx" )
cmdSelect = New SqlCommand( "DBUpdate_hrs", conMyData )
cmdSelect.CommandType = CommandType.StoredProcedure
parmReturnValue = cmdSelect.Parameters.Add( "RETURN_VALUE",
SqlDbType.Int )
parmReturnValue.Direction = ParameterDirection.ReturnValue
cmdSelect.Parameters.Add( "@emp_id", txtEmployee_ID.Text )
cmdSelect.Parameters.Add( "@emp_hrs", txtEmployee_Hrs.Text )
cmdSelect.Parameters.Add( "@emp_date", txtEmployee_Date.Text )
cmdSelect.Parameters.Add( "@project_number", txtProject_Number.Text )
conMyData.Open()
cmdSelect.ExecuteNonQuery()
intResult = cmdSelect.Parameters( "RETURN_VALUE" ).Value
conMyData.Close()
If intResult = - 1 Then
lblMessage.Text = "Project number doesn't exist enter valid
Project Number!"
Else
lblMessage.Text = "Your hours have been updated"
end if
End If
End Sub
</Script>
<html>
<link rel="stylesheet" type="text/css" href="../style.css">
<head><title>Update_hrs.aspx</title></head>
<body runat=server>
<form runat=server ID="Form1">
<asp:Table CssClass=Main_Table HorizontalAlign=Center BackColor=white
BorderWidth=1 Runat=server>
<asp:TableRow >
<asp:TableCell HorizontalAlign=Left VerticalAlign=Bottom Width=230>
<img src="../images/logo_only_sml.jpg" align=left name=logo_sml
/></asp:TableCell>
<asp:TableCell CssClass=menu_text Width=230>
<a href="../Default.aspx"> HOME </a>  <a href=sign_out.aspx>SIGN
OUT</a>
</asp:TableCell></asp:TableRow>
<asp:TableRow ID=spacer>
<asp:TableCell Height=50 ColumnSpan=2
Width=230></asp:TableCell></asp:TableRow>
<asp:TableRow>
<asp:TableCell HorizontalAlign=right Width=230>
Project Number: </asp:TableCell>
<asp:TableCell HorizontalAlign=left Width=230>
<asp:TextBox ID=txtProject_Number Width=120 Runat=server></asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell HorizontalAlign=right Width=230>
Employee ID:</asp:TableCell>
<asp:TableCell HorizontalAlign=left Width=230>
<asp:TextBox ID=txtEmployee_ID width= 120 Runat=server></asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell HorizontalAlign=right Width=230>
Employee Hrs: </asp:TableCell>
<asp:TableCell HorizontalAlign=left Width=230>
<asp:TextBox ID=txtEmployee_Hrs Width=40
Runat=server></asp:TextBox></asp:TableCell>
</asp:TableRow>
I hope some one can help me to resolve this issue.
Mark