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.Sql Client.SqlDataA dapter = New
System.Data.Sql Client.SqlDataA dapter
'Specify a connection for a data adpter that
'fills the data set used on the form.
Dim conn As System.Data.Sql Client.SqlConne ction _
= New System.Data.Sql Client.SqlConne ction(Utilities .GetConnectionS tring())
'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.Sql Client.SqlComma nd = _
New System.Data.Sql Client.SqlComma nd("UpdClient" , conn)
cmd.CommandType = CommandType.Sto redProcedure
'Declare Parameters
Dim prmClientID As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@ClientID" , SqlDbType.Int, 4,
"ClientID")
prmClientID.Dir ection = ParameterDirect ion.Input
prmClientID.IsN ullable = False
prmClientID.Val ue = CInt(ClientID)
Dim prmClientCode As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@ClientCod e", SqlDbType.Char, 10,
"ClientCode ")
prmClientCode.D irection = ParameterDirect ion.Input
prmClientCode.I sNullable = False
prmClientCode.V alue = ClientCode
Dim prmClientName As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@ClientNam e", SqlDbType.Char, 50,
"ClientName ")
prmClientName.D irection = ParameterDirect ion.Input
prmClientName.I sNullable = True
prmClientName.V alue = Name
Dim prmClientDescri ption As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@ClientDes cription", SqlDbType.Char,
255, "ClientDescript ion")
prmClientDescri ption.Direction = ParameterDirect ion.Input
prmClientDescri ption.IsNullabl e = True
prmClientDescri ption.Value = ClientDescripti on
Dim prmBillingConta ct As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@BillingCo ntact", SqlDbType.Char, 50,
"BillingContact ")
prmBillingConta ct.Direction = ParameterDirect ion.Input
prmBillingConta ct.IsNullable = True
prmBillingConta ct.Value = BillingContact
Dim prmOtherContact As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@OtherCont act", SqlDbType.Char,
50, "OtherConta ct")
prmOtherContact .Direction = ParameterDirect ion.Input
prmOtherContact .IsNullable = True
prmOtherContact .Value = OtherContact
Dim prmOtherContact Desc As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@OtherCont actDesc",
SqlDbType.Char, 255, "OtherContactDe sc")
prmOtherContact Desc.Direction = ParameterDirect ion.Input
prmOtherContact Desc.IsNullable = True
prmOtherContact Desc.Value = OtherContactDes c
Dim prmStreetAddres s As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@StreetAdd ress", SqlDbType.Char, 150,
"StreetAddress" )
prmStreetAddres s.Direction = ParameterDirect ion.Input
prmStreetAddres s.IsNullable = True
prmStreetAddres s.Value = StreetAddress
Dim prmStreetCity As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@StreetCit y", SqlDbType.Char, 50,
"StreetCity ")
prmStreetCity.D irection = ParameterDirect ion.Input
prmStreetCity.V alue = StreetCity
Dim prmStreetState As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@StreetSta te", SqlDbType.Char, 2,
"StreetStat e")
prmStreetState. Direction = ParameterDirect ion.Input
prmStreetState. IsNullable = True
prmStreetState. Value = StreetState
Dim prmStreetZip As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@StreetZip ", SqlDbType.Char, 10,
"StreetZip" )
prmStreetZip.Di rection = ParameterDirect ion.Input
prmStreetZip.Is Nullable = True
prmStreetZip.Va lue = StreetZip
Dim prmMailSameAsSt reet As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@MailSameA sStreet", SqlDbType.Bit, 1)
prmMailSameAsSt reet.Direction = ParameterDirect ion.Input
prmMailSameAsSt reet.IsNullable = True
prmMailSameAsSt reet.Value = MailSameAsStree t
Dim prmMailAddress As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@MailAddre ss", SqlDbType.Char, 150,
"MailAddres s")
prmMailAddress. Direction = ParameterDirect ion.Input
prmMailAddress. IsNullable = True
prmMailAddress. Value = MailAddress
Dim prmMailCity As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@MailCity" , SqlDbType.Char, 50,
"MailCity")
prmMailCity.Dir ection = ParameterDirect ion.Input
prmMailCity.IsN ullable = True
prmMailCity.Val ue = MailCity
Dim prmMailState As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@MailState ", SqlDbType.Char, 2,
"MailState" )
prmMailState.Di rection = ParameterDirect ion.Input
prmMailState.Is Nullable = True
prmMailState.Va lue = MailState
Dim prmMailZip As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@MailZip", SqlDbType.Char, 10,
"MailZip")
prmMailZip.Dire ction = ParameterDirect ion.Input
prmMailZip.IsNu llable = True
prmMailZip.Valu e = MailZip
Dim prmPhone As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@Phone", SqlDbType.Char, 10, "Phone")
prmPhone.Direct ion = ParameterDirect ion.Input
prmPhone.IsNull able = True
prmPhone.Value = Phone
Dim prmExtention As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@Extention ", SqlDbType.Char, 10,
"Extention" )
prmExtention.Di rection = ParameterDirect ion.Input
prmExtention.Is Nullable = True
prmExtention.Va lue = Extention
Dim prmFax As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@Fax", SqlDbType.Char, 10, "Fax")
prmfax.Directio n = ParameterDirect ion.Input
prmfax.IsNullab le = True
prmfax.Value = Fax
Dim prmComments As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@Comments" , SqlDbType.Char, 255,
"Comments")
prmComments.Dir ection = ParameterDirect ion.Input
prmComments.IsN ullable = True
prmComments.Val ue = Comments
Dim prmNumberOfPhot os As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@NumberOfP hotos", SqlDbType.Int,
4)
prmNumberOfPhot os.Direction = ParameterDirect ion.Input
prmNumberOfPhot os.IsNullable = True
prmNumberOfPhot os.Value = CInt(NumberOfPh otos)
Dim prmActive As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@Active", SqlDbType.Bit, 1)
prmActive.Direc tion = ParameterDirect ion.Input
prmActive.IsNul lable = True
prmActive.Value = Active
Dim prmConfirmation Letter As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@Confirmat ionLetter",
SqlDbType.Bit, 1)
prmConfirmation Letter.Directio n = ParameterDirect ion.Input
prmConfirmation Letter.IsNullab le = True
prmConfirmation Letter.Value = ConfirmationLet ter
Dim prmInspectLockO uts As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@InspectLo ckOuts", SqlDbType.Bit, 1)
prmInspectLockO uts.Direction = ParameterDirect ion.Input
prmInspectLockO uts.IsNullable = True
prmInspectLockO uts.Value = InspectLockOuts
Dim prmCreditMateri alOS As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@CreditMat erialOS", SqlDbType.Bit, 1)
prmCreditMateri alOS.Direction = ParameterDirect ion.Input
prmCreditMateri alOS.IsNullable = True
prmCreditMateri alOS.Value = CreditMaterialO S
Dim prmLastEditedUs erID As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@LastEdite dUserID", SqlDbType.Int, 4)
prmLastEditedUs erID.Direction = ParameterDirect ion.Input
prmLastEditedUs erID.IsNullable = False
prmLastEditedUs erID.Value = LastEditedUserI d
Dim rows As Integer = cmd.ExecuteNonQ uery()
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),
@ClientDescript ion Char(255),
@BillingContact char(10),
@OtherContact char(50),
@OtherContactDe sc char(255),
@StreetAddress char(150),
@StreetCity char(50),
@StreetState char(2),
@StreetZip char(10),
@MailSameAsStre et 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,
@ConfirmationLe tter bit,
@InspectLockOut s bit,
@CreditMaterial sOS bit,
@LastEditedUser ID int
as
Update clients set ClientCode = @ClientCode,
ClientDescripti on =@ClientDescrip tion,
ClientName =@ClientName,
BillingContact= @BillingContact ,
OtherContact = @OtherContact,
OtherContactDes c = @OtherContactDe sc,
StreetAddress = @StreetAddress,
StreetCity = @StreetCity,
StreetState = @StreetState,
StreetZip = @Streetzip,
MailSameAsStree t = @MailSameAsStre et,
MailAddress = @MailAddress,
MailCity = @MailCity,
MailState = @MailState,
MailZip = @MailZip,
Phone = @Phone,
Extention = @Extention,
Fax = @Fax,
Comments = @Comments,
NumberOFPhotos = @NumberOfPhotos ,
Active = @Active ,
ConfirmationLet ter = @ConfirmationLe tter,
InspectLockOuts = @InspectLockOut s,
CreditMaterialO S = @CreditMaterial sOS,
LastEditedUserI d = @LastEditedUser ID,
LastEditedTime = getdate()
Where Clients.ClientI D = @ClientID
GO
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.Sql Client.SqlDataA dapter = New
System.Data.Sql Client.SqlDataA dapter
'Specify a connection for a data adpter that
'fills the data set used on the form.
Dim conn As System.Data.Sql Client.SqlConne ction _
= New System.Data.Sql Client.SqlConne ction(Utilities .GetConnectionS tring())
'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.Sql Client.SqlComma nd = _
New System.Data.Sql Client.SqlComma nd("UpdClient" , conn)
cmd.CommandType = CommandType.Sto redProcedure
'Declare Parameters
Dim prmClientID As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@ClientID" , SqlDbType.Int, 4,
"ClientID")
prmClientID.Dir ection = ParameterDirect ion.Input
prmClientID.IsN ullable = False
prmClientID.Val ue = CInt(ClientID)
Dim prmClientCode As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@ClientCod e", SqlDbType.Char, 10,
"ClientCode ")
prmClientCode.D irection = ParameterDirect ion.Input
prmClientCode.I sNullable = False
prmClientCode.V alue = ClientCode
Dim prmClientName As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@ClientNam e", SqlDbType.Char, 50,
"ClientName ")
prmClientName.D irection = ParameterDirect ion.Input
prmClientName.I sNullable = True
prmClientName.V alue = Name
Dim prmClientDescri ption As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@ClientDes cription", SqlDbType.Char,
255, "ClientDescript ion")
prmClientDescri ption.Direction = ParameterDirect ion.Input
prmClientDescri ption.IsNullabl e = True
prmClientDescri ption.Value = ClientDescripti on
Dim prmBillingConta ct As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@BillingCo ntact", SqlDbType.Char, 50,
"BillingContact ")
prmBillingConta ct.Direction = ParameterDirect ion.Input
prmBillingConta ct.IsNullable = True
prmBillingConta ct.Value = BillingContact
Dim prmOtherContact As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@OtherCont act", SqlDbType.Char,
50, "OtherConta ct")
prmOtherContact .Direction = ParameterDirect ion.Input
prmOtherContact .IsNullable = True
prmOtherContact .Value = OtherContact
Dim prmOtherContact Desc As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@OtherCont actDesc",
SqlDbType.Char, 255, "OtherContactDe sc")
prmOtherContact Desc.Direction = ParameterDirect ion.Input
prmOtherContact Desc.IsNullable = True
prmOtherContact Desc.Value = OtherContactDes c
Dim prmStreetAddres s As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@StreetAdd ress", SqlDbType.Char, 150,
"StreetAddress" )
prmStreetAddres s.Direction = ParameterDirect ion.Input
prmStreetAddres s.IsNullable = True
prmStreetAddres s.Value = StreetAddress
Dim prmStreetCity As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@StreetCit y", SqlDbType.Char, 50,
"StreetCity ")
prmStreetCity.D irection = ParameterDirect ion.Input
prmStreetCity.V alue = StreetCity
Dim prmStreetState As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@StreetSta te", SqlDbType.Char, 2,
"StreetStat e")
prmStreetState. Direction = ParameterDirect ion.Input
prmStreetState. IsNullable = True
prmStreetState. Value = StreetState
Dim prmStreetZip As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@StreetZip ", SqlDbType.Char, 10,
"StreetZip" )
prmStreetZip.Di rection = ParameterDirect ion.Input
prmStreetZip.Is Nullable = True
prmStreetZip.Va lue = StreetZip
Dim prmMailSameAsSt reet As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@MailSameA sStreet", SqlDbType.Bit, 1)
prmMailSameAsSt reet.Direction = ParameterDirect ion.Input
prmMailSameAsSt reet.IsNullable = True
prmMailSameAsSt reet.Value = MailSameAsStree t
Dim prmMailAddress As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@MailAddre ss", SqlDbType.Char, 150,
"MailAddres s")
prmMailAddress. Direction = ParameterDirect ion.Input
prmMailAddress. IsNullable = True
prmMailAddress. Value = MailAddress
Dim prmMailCity As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@MailCity" , SqlDbType.Char, 50,
"MailCity")
prmMailCity.Dir ection = ParameterDirect ion.Input
prmMailCity.IsN ullable = True
prmMailCity.Val ue = MailCity
Dim prmMailState As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@MailState ", SqlDbType.Char, 2,
"MailState" )
prmMailState.Di rection = ParameterDirect ion.Input
prmMailState.Is Nullable = True
prmMailState.Va lue = MailState
Dim prmMailZip As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@MailZip", SqlDbType.Char, 10,
"MailZip")
prmMailZip.Dire ction = ParameterDirect ion.Input
prmMailZip.IsNu llable = True
prmMailZip.Valu e = MailZip
Dim prmPhone As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@Phone", SqlDbType.Char, 10, "Phone")
prmPhone.Direct ion = ParameterDirect ion.Input
prmPhone.IsNull able = True
prmPhone.Value = Phone
Dim prmExtention As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@Extention ", SqlDbType.Char, 10,
"Extention" )
prmExtention.Di rection = ParameterDirect ion.Input
prmExtention.Is Nullable = True
prmExtention.Va lue = Extention
Dim prmFax As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@Fax", SqlDbType.Char, 10, "Fax")
prmfax.Directio n = ParameterDirect ion.Input
prmfax.IsNullab le = True
prmfax.Value = Fax
Dim prmComments As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@Comments" , SqlDbType.Char, 255,
"Comments")
prmComments.Dir ection = ParameterDirect ion.Input
prmComments.IsN ullable = True
prmComments.Val ue = Comments
Dim prmNumberOfPhot os As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@NumberOfP hotos", SqlDbType.Int,
4)
prmNumberOfPhot os.Direction = ParameterDirect ion.Input
prmNumberOfPhot os.IsNullable = True
prmNumberOfPhot os.Value = CInt(NumberOfPh otos)
Dim prmActive As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@Active", SqlDbType.Bit, 1)
prmActive.Direc tion = ParameterDirect ion.Input
prmActive.IsNul lable = True
prmActive.Value = Active
Dim prmConfirmation Letter As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@Confirmat ionLetter",
SqlDbType.Bit, 1)
prmConfirmation Letter.Directio n = ParameterDirect ion.Input
prmConfirmation Letter.IsNullab le = True
prmConfirmation Letter.Value = ConfirmationLet ter
Dim prmInspectLockO uts As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@InspectLo ckOuts", SqlDbType.Bit, 1)
prmInspectLockO uts.Direction = ParameterDirect ion.Input
prmInspectLockO uts.IsNullable = True
prmInspectLockO uts.Value = InspectLockOuts
Dim prmCreditMateri alOS As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@CreditMat erialOS", SqlDbType.Bit, 1)
prmCreditMateri alOS.Direction = ParameterDirect ion.Input
prmCreditMateri alOS.IsNullable = True
prmCreditMateri alOS.Value = CreditMaterialO S
Dim prmLastEditedUs erID As SqlClient.SqlPa rameter = _
cmd.Parameters. Add("@LastEdite dUserID", SqlDbType.Int, 4)
prmLastEditedUs erID.Direction = ParameterDirect ion.Input
prmLastEditedUs erID.IsNullable = False
prmLastEditedUs erID.Value = LastEditedUserI d
Dim rows As Integer = cmd.ExecuteNonQ uery()
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),
@ClientDescript ion Char(255),
@BillingContact char(10),
@OtherContact char(50),
@OtherContactDe sc char(255),
@StreetAddress char(150),
@StreetCity char(50),
@StreetState char(2),
@StreetZip char(10),
@MailSameAsStre et 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,
@ConfirmationLe tter bit,
@InspectLockOut s bit,
@CreditMaterial sOS bit,
@LastEditedUser ID int
as
Update clients set ClientCode = @ClientCode,
ClientDescripti on =@ClientDescrip tion,
ClientName =@ClientName,
BillingContact= @BillingContact ,
OtherContact = @OtherContact,
OtherContactDes c = @OtherContactDe sc,
StreetAddress = @StreetAddress,
StreetCity = @StreetCity,
StreetState = @StreetState,
StreetZip = @Streetzip,
MailSameAsStree t = @MailSameAsStre et,
MailAddress = @MailAddress,
MailCity = @MailCity,
MailState = @MailState,
MailZip = @MailZip,
Phone = @Phone,
Extention = @Extention,
Fax = @Fax,
Comments = @Comments,
NumberOFPhotos = @NumberOfPhotos ,
Active = @Active ,
ConfirmationLet ter = @ConfirmationLe tter,
InspectLockOuts = @InspectLockOut s,
CreditMaterialO S = @CreditMaterial sOS,
LastEditedUserI d = @LastEditedUser ID,
LastEditedTime = getdate()
Where Clients.ClientI D = @ClientID
GO
Comment