I am trying to update a record though a stored procedure and
parameters, but I keep getting this error:
Additional information: Argument 'Prompt' cannot be converted to type
'String'.
Any ideas?
Here is the code
Public Function UpdateClient()
Dim dapClients As System.Data.SqlClient.SqlDataAdapter = New
System.Data.SqlClient.SqlDataAdapter
'Specify a connection for a data adpter that
'fills the data set used on the form.
Dim conn As System.Data.SqlClient.SqlConnection _
= New System.Data.SqlClient.SqlConnection(Utilities.GetC onnectionString())
'Specify the command and data adpter that serves
'as the source for the data set on the form
conn.Open()
Dim cmd As System.Data.SqlClient.SqlCommand = _
New System.Data.SqlClient.SqlCommand("UpdClient", conn)
cmd.CommandType = CommandType.StoredProcedure
'Declare Parameters
Dim prmClientID As SqlClient.SqlParameter = _
cmd.Parameters.Add("@ClientID", SqlDbType.Int, 4,
"ClientID")
prmClientID.Direction = ParameterDirection.Input
prmClientID.IsNullable = False
prmClientID.Value = CInt(ClientID)
Dim prmClientCode As SqlClient.SqlParameter = _
cmd.Parameters.Add("@ClientCode", SqlDbType.Char, 10,
"ClientCode")
prmClientCode.Direction = ParameterDirection.Input
prmClientCode.IsNullable = False
prmClientCode.Value = ClientCode
Dim prmClientName As SqlClient.SqlParameter = _
cmd.Parameters.Add("@ClientName", SqlDbType.Char, 50,
"ClientName")
prmClientName.Direction = ParameterDirection.Input
prmClientName.IsNullable = True
prmClientName.Value = Name
Dim prmClientDescription As SqlClient.SqlParameter = _
cmd.Parameters.Add("@ClientDescription", SqlDbType.Char,
255, "ClientDescription")
prmClientDescription.Direction = ParameterDirection.Input
prmClientDescription.IsNullable = True
prmClientDescription.Value = ClientDescription
Dim prmBillingContact As SqlClient.SqlParameter = _
cmd.Parameters.Add("@BillingContact", SqlDbType.Char, 50,
"BillingContact")
prmBillingContact.Direction = ParameterDirection.Input
prmBillingContact.IsNullable = True
prmBillingContact.Value = BillingContact
Dim prmOtherContact As SqlClient.SqlParameter = _
cmd.Parameters.Add("@OtherContact", SqlDbType.Char,
50, "OtherContact")
prmOtherContact.Direction = ParameterDirection.Input
prmOtherContact.IsNullable = True
prmOtherContact.Value = OtherContact
Dim prmOtherContactDesc As SqlClient.SqlParameter = _
cmd.Parameters.Add("@OtherContactDesc",
SqlDbType.Char, 255, "OtherContactDesc")
prmOtherContactDesc.Direction = ParameterDirection.Input
prmOtherContactDesc.IsNullable = True
prmOtherContactDesc.Value = OtherContactDesc
Dim prmStreetAddress As SqlClient.SqlParameter = _
cmd.Parameters.Add("@StreetAddress", SqlDbType.Char, 150,
"StreetAddress")
prmStreetAddress.Direction = ParameterDirection.Input
prmStreetAddress.IsNullable = True
prmStreetAddress.Value = StreetAddress
Dim prmStreetCity As SqlClient.SqlParameter = _
cmd.Parameters.Add("@StreetCity", SqlDbType.Char, 50,
"StreetCity")
prmStreetCity.Direction = ParameterDirection.Input
prmStreetCity.Value = StreetCity
Dim prmStreetState As SqlClient.SqlParameter = _
cmd.Parameters.Add("@StreetState", SqlDbType.Char, 2,
"StreetState")
prmStreetState.Direction = ParameterDirection.Input
prmStreetState.IsNullable = True
prmStreetState.Value = StreetState
Dim prmStreetZip As SqlClient.SqlParameter = _
cmd.Parameters.Add("@StreetZip", SqlDbType.Char, 10,
"StreetZip")
prmStreetZip.Direction = ParameterDirection.Input
prmStreetZip.IsNullable = True
prmStreetZip.Value = StreetZip
Dim prmMailSameAsStreet As SqlClient.SqlParameter = _
cmd.Parameters.Add("@MailSameAsStreet", SqlDbType.Bit, 1)
prmMailSameAsStreet.Direction = ParameterDirection.Input
prmMailSameAsStreet.IsNullable = True
prmMailSameAsStreet.Value = MailSameAsStreet
Dim prmMailAddress As SqlClient.SqlParameter = _
cmd.Parameters.Add("@MailAddress", SqlDbType.Char, 150,
"MailAddress")
prmMailAddress.Direction = ParameterDirection.Input
prmMailAddress.IsNullable = True
prmMailAddress.Value = MailAddress
Dim prmMailCity As SqlClient.SqlParameter = _
cmd.Parameters.Add("@MailCity", SqlDbType.Char, 50,
"MailCity")
prmMailCity.Direction = ParameterDirection.Input
prmMailCity.IsNullable = True
prmMailCity.Value = MailCity
Dim prmMailState As SqlClient.SqlParameter = _
cmd.Parameters.Add("@MailState", SqlDbType.Char, 2,
"MailState")
prmMailState.Direction = ParameterDirection.Input
prmMailState.IsNullable = True
prmMailState.Value = MailState
Dim prmMailZip As SqlClient.SqlParameter = _
cmd.Parameters.Add("@MailZip", SqlDbType.Char, 10,
"MailZip")
prmMailZip.Direction = ParameterDirection.Input
prmMailZip.IsNullable = True
prmMailZip.Value = MailZip
Dim prmPhone As SqlClient.SqlParameter = _
cmd.Parameters.Add("@Phone", SqlDbType.Char, 10, "Phone")
prmPhone.Direction = ParameterDirection.Input
prmPhone.IsNullable = True
prmPhone.Value = Phone
Dim prmExtention As SqlClient.SqlParameter = _
cmd.Parameters.Add("@Extention", SqlDbType.Char, 10,
"Extention")
prmExtention.Direction = ParameterDirection.Input
prmExtention.IsNullable = True
prmExtention.Value = Extention
Dim prmFax As SqlClient.SqlParameter = _
cmd.Parameters.Add("@Fax", SqlDbType.Char, 10, "Fax")
prmfax.Direction = ParameterDirection.Input
prmfax.IsNullable = True
prmfax.Value = Fax
Dim prmComments As SqlClient.SqlParameter = _
cmd.Parameters.Add("@Comments", SqlDbType.Char, 255,
"Comments")
prmComments.Direction = ParameterDirection.Input
prmComments.IsNullable = True
prmComments.Value = Comments
Dim prmNumberOfPhotos As SqlClient.SqlParameter = _
cmd.Parameters.Add("@NumberOfPhotos", SqlDbType.Int,
4)
prmNumberOfPhotos.Direction = ParameterDirection.Input
prmNumberOfPhotos.IsNullable = True
prmNumberOfPhotos.Value = CInt(NumberOfPhotos)
Dim prmActive As SqlClient.SqlParameter = _
cmd.Parameters.Add("@Active", SqlDbType.Bit, 1)
prmActive.Direction = ParameterDirection.Input
prmActive.IsNullable = True
prmActive.Value = Active
Dim prmConfirmationLetter As SqlClient.SqlParameter = _
cmd.Parameters.Add("@ConfirmationLetter",
SqlDbType.Bit, 1)
prmConfirmationLetter.Direction = ParameterDirection.Input
prmConfirmationLetter.IsNullable = True
prmConfirmationLetter.Value = ConfirmationLetter
Dim prmInspectLockOuts As SqlClient.SqlParameter = _
cmd.Parameters.Add("@InspectLockOuts", SqlDbType.Bit, 1)
prmInspectLockOuts.Direction = ParameterDirection.Input
prmInspectLockOuts.IsNullable = True
prmInspectLockOuts.Value = InspectLockOuts
Dim prmCreditMaterialOS As SqlClient.SqlParameter = _
cmd.Parameters.Add("@CreditMaterialOS", SqlDbType.Bit, 1)
prmCreditMaterialOS.Direction = ParameterDirection.Input
prmCreditMaterialOS.IsNullable = True
prmCreditMaterialOS.Value = CreditMaterialOS
Dim prmLastEditedUserID As SqlClient.SqlParameter = _
cmd.Parameters.Add("@LastEditedUserID", SqlDbType.Int, 4)
prmLastEditedUserID.Direction = ParameterDirection.Input
prmLastEditedUserID.IsNullable = False
prmLastEditedUserID.Value = LastEditedUserId
Dim rows As Integer = cmd.ExecuteNonQuery()
If rows = 0 Then
MsgBox("Did not work")
Else
MsgBox("Worked")
End If
conn.Close()
End Function
'''''''''''''''''''''''''''
Store Procedure
'''''''''''''''''''''''''''
CREATE procedure updClient
@ClientID int,
@ClientCode char(10),
@ClientName Char(50),
@ClientDescription Char(255),
@BillingContact char(10),
@OtherContact char(50),
@OtherContactDesc char(255),
@StreetAddress char(150),
@StreetCity char(50),
@StreetState char(2),
@StreetZip char(10),
@MailSameAsStreet bit,
@MailAddress char(150),
@MailCity char(50),
@MailState char(2),
@MailZip char(10),
@Phone char(10),
@Extention char(10),
@Fax char(10),
@Comments char(255),
@NumberOfPhotos int,
@Active bit,
@ConfirmationLetter bit,
@InspectLockOuts bit,
@CreditMaterialsOS bit,
@LastEditedUserID int
as
Update clients set ClientCode = @ClientCode,
ClientDescription =@ClientDescription,
ClientName =@ClientName,
BillingContact= @BillingContact,
OtherContact = @OtherContact,
OtherContactDesc = @OtherContactDesc,
StreetAddress = @StreetAddress,
StreetCity = @StreetCity,
StreetState = @StreetState,
StreetZip = @Streetzip,
MailSameAsStreet = @MailSameAsStreet,
MailAddress = @MailAddress,
MailCity = @MailCity,
MailState = @MailState,
MailZip = @MailZip,
Phone = @Phone,
Extention = @Extention,
Fax = @Fax,
Comments = @Comments,
NumberOFPhotos = @NumberOfPhotos,
Active = @Active ,
ConfirmationLetter = @ConfirmationLetter,
InspectLockOuts = @InspectLockOuts,
CreditMaterialOS = @CreditMaterialsOS,
LastEditedUserId = @LastEditedUserID,
LastEditedTime = getdate()
Where Clients.ClientID = @ClientID
GO
