updating using a stored Procedure

[sthmpsn1]

I am trying to use stored procedures for the first time and have created the following script which is giving me an error and I don't know why. Here is my code

Sub update_click( s As Object, e As EventArgs )
Dim dbconn1 As SqlConnection = New SqlConnection("server=AM1ST_FS1;database=HRINFO;uid=sa;&quot
Dim mycommand as SqlCommand
dbconn1.open()
mycommand = New SqlCommand( "insertupdate", dbconn1 )
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Parameters.Add( New SqlParameter( "@firstName", SqlDBType.nvarchar, 50 ))
mycommand.Parameters( "@firstname" ).Value = firstName
mycommand.Parameters.Add( New SqlParameter( "@lastName", SqlDBType.nvarchar, 50 ))
mycommand.Parameters( "@lastName" ).Value = lastName
mycommand.Parameters.Add( New SqlParameter( "@address", SqlDBType.nvarchar, 50 ))
mycommand.Parameters( "@address" ).Value = address
mycommand.Parameters.Add( New SqlParameter( "@city", SqlDBType.nvarchar, 50 ))
mycommand.Parameters( "@city" ).Value = city
mycommand.Parameters.Add( New SqlParameter( "@state", SqlDBType.nvarchar, 50 ))
mycommand.Parameters( "@state" ).Value = state
mycommand.Parameters.Add( New SqlParameter( "@ssn", SqlDBType.nvarchar, 50 ))
mycommand.Parameters( "@ssn" ).Value = ssn
mycommand.Parameters.Add( New SqlParameter( "@gender", SqlDBType.nvarchar, 50 ))
mycommand.Parameters( "@gender" ).Value = gender
mycommand.Parameters.Add( New SqlParameter( "@emergancy", SqlDBType.nvarchar, 50 ))
mycommand.Parameters( "@emergancy" ).Value = emergancycontact
mycommand.Parameters.Add( New SqlParameter( "@loginID", SqlDBType.nvarchar, 50 ))
mycommand.Parameters( "@loginID" ).Value = loginID

mycommand.ExecuteNonQuery()
dbconn1.close

End Sub

HERE IS THE ERROR



Object must implement IConvertible.
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.InvalidCastException: Object must implement IConvertible.

Source Error:


Line 101:mycommand.Parameters( "@loginID" ).Value = loginID
Line 102:
Line 103:mycommand.ExecuteNonQuery()
Line 104:dbconn1.close
Line 105:


Source File: E:\Intranet\Mystuff\Mystuff\aspnet\index.aspx Line: 103

Stack Trace:


[InvalidCastException: Object must implement IConvertible.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +643
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +206
ASP.index_aspx.update_click(Object s, EventArgs e) in E:\Intranet\Mystuff\Mystuff\aspnet\index.aspx:103
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1263




--------------------------------------------------------------------------------

Any Help would be great!

 

[link9]

The little I've found out there on this error message is sort of splotchy on what the problem actually is. So I suggest you try to implement the following logic, instead of what you have up there. This is how I execute sprocs and mine work fine, so here goes:

dim param as sqlParameter

param = new sqlParameter()
param.parameterName = "@firstName"
param.direction = parameterDirection.input
param.sqldbtype = sqldbtype.nvarchar
param.size = 50
param.value = firstName
myCommand.parameters.add(param)

param = new sqlparameter()
param.parameterName = "@lastname"
param.direction = parameterDirection.input
param.sqldbtype = sqldbtype.nvarchar
param.size = 50
param.value = lastName
myCommand.parameters.add(param)

etc...

hope that helps
paul

 

[jfrost10]

When we add paramters to our sp's, we pretty much just do a one liner:

objCommand.Parameters.Add(blah blah blah).Value = variable

Thats all you really need to add a parameter to a command object, so you'd be able to reduce your code by 9 lines that way.

As for the error, what line was it actually highlighting red? was it the ExecuteNonQuery, or at the value assignment to @loginID?

Also, have you checked to make sure that the variables in the stored proc are of the same type as in the code? Are null values attempting to be passed in, and its not handling them properly?

Let us know how it goes


Jack

 

[Zarcom]

Just thought I would put my 10 cents up cause my 2 cents is free
*sorry just listening to music

Anyway I usually just use the designer to access my sprocs cause I am lazy I guess. But this is how the designer adds a parameter to a command


Me.SqlCommandCancelReservation.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Confirmation", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Output, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))


seems a little long and convoluted compared to Jacks, but everything is explicitly defined and it works. That'l do donkey, that'l do