Question Must declare scalar variable error

[rmclean]

I keep getting "Must declare scalar variable" error when I try to run this sub. Any helpful hints??


Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

Dim cmd As New SqlCommand
Dim strSQL As String
Dim intID As Integer

cmd.Parameters.AddWithValue("@FName", txtFirstName.Text)
cmd.Parameters.AddWithValue("@LName", txtLastName.Text)
cmd.Parameters.AddWithValue("@PName", txtPrefName.Text)
cmd.Parameters.AddWithValue("@Title", txtTitle.Text)

If lstJur.SelectedItem = Nothing Then
cmd.Parameters.AddWithValue("@Jur", txtOtherJur.Text)
Else
cmd.Parameters.AddWithValue("@Jur", lstJur.SelectedItem)
End If

cmd.Parameters.AddWithValue("@BsAdd", txtBusAdd.Text)
cmd.Parameters.AddWithValue("@StAdd", txtStrAdd.Text)
cmd.Parameters.AddWithValue("@MAdd", txtMailAdd.Text)
cmd.Parameters.AddWithValue("@BPh", txtBusPh.Text)
cmd.Parameters.AddWithValue("@Cell", txtCell.Text)
cmd.Parameters.AddWithValue("@ACell", txtAltCell.Text)
cmd.Parameters.AddWithValue("@HPh", txtHomePh.Text)
cmd.Parameters.AddWithValue("@Fax", txtFax.Text)
cmd.Parameters.AddWithValue("@AFax", txtAltFax.Text)
cmd.Parameters.AddWithValue("@Email", txtEmail.Text)
cmd.Parameters.AddWithValue("@Web", txtWebsite.Text)
cmd.Parameters.AddWithValue("@Ethic", cboEthic.Text)

If txtEthicDt.Text <> "" Then
If IsDate(txtEthicDt.Text) Then
cmd.Parameters.AddWithValue("@EthicDt", CDate(txtEthicDt.Text))
Else
MsgBox("Incorrect date format")
Exit Sub
End If
Else
cmd.Parameters.AddWithValue("@EthicDt", SqlTypes.SqlDateTime.Null)
End If

If chkCellConf.Checked = True Then
cmd.Parameters.AddWithValue("@ConCell", 1)
Else
cmd.Parameters.AddWithValue("@ConCell", 0)
End If

If chkAltCellConf.Checked = True Then
cmd.Parameters.AddWithValue("@ConACell", 1)
Else
cmd.Parameters.AddWithValue("@ConACell", 0)
End If

If chkHomeConf.Checked = True Then
cmd.Parameters.AddWithValue("@ConHome", 1)
Else
cmd.Parameters.AddWithValue("@ConHome", 0)
End If

Dim ocon As SqlConnection = New SqlConnection(m_sSQL_Connect)
ocon.Open()

If lblID.Text = "" Then

strSQL = "INSERT into CommitteeMembers_new (LastName, FirstName, PrefName, Title, Jurisdiction, BusAdd, StrAdd, MailAdd, BusPh, Cell, AltCell, HomePh, Fax, AltFax, Email, Website, EthicsPolicy, EthicDt, ConfCell, ConfAltCell, ConfHomePh)" & _
"VALUES (@LName, @FName, @PName, @Title, @Jur, @BAdd, @SAdd, @MAdd, @BPh, @Cell, @ACell, @HPh, @Fax, @AFax, @Email, @Web, @Ethic, @EthicDt, @ConCell, @ConACell, @ConHome)"

Else

intID = CType(lblID.Text, Int32)

strSQL = " UPDATE CommitteeMembers SET" & _
"LastName = @LastName" & _
"FirstName = @FName" & _
"PrefName = @PName" & _
"Title = @Title" & _
"Jurisdiction = @Jur" & _
"BusAdd = @BAdd" & _
"StrAdd = @SAdd" & _
"MailAdd = @MAdd" & _
"BusPh = @BPh" & _
"Cell = @Cell" & _
"AltCell = @ACell" & _
"HomePh = @HPh" & _
"Fax = @Fax" & _
"AltFax = @AFax" & _
"Email = @Email" & _
"Website = @Web" & _
"Ethic = @Ethic" & _
"EthicDt = @EthicDt" & _
"ConfCell = @ConCell" & _
"ConfAltCell = @ConACell" & _
"ConfHomePh = @ConHome" & _
"WHERE MemberID =" & intID & "'"
End If

Dim oCommand As New SqlCommand(strSQL, ocon)
oCommand.ExecuteNonQuery()

Dim ds As New DataSet
Dim da As New SqlDataAdapter
Dim cmdID As New SqlCommand
Dim strSQLID As String = "SELECT * FROM CommitteeMembers_new WHERE LastName = @LastName AND FirstName = @FirstName AND Jurisdiction = @Jur"
cmdID.Connection = ocon
cmdID.CommandText = strSQLID
da.SelectCommand = cmdID
da.Fill(ds)
lblID.Text = (ds.Tables(0).Columns("MemberID")).ToString

Dim answer As MsgBoxResult
answer = MsgBox("The contact information has been saved. Do you want to add membership details?", MsgBoxStyle.YesNo, "Membership Details")

If answer = MsgBoxResult.Yes Then
grpMembership.Enabled = True

ElseIf answer = MsgBoxResult.No Then
Dim answer2 As MsgBoxResult

answer2 = MsgBox("Do you want to exit the application?", MsgBoxStyle.YesNo, "")
If answer2 = MsgBoxResult.No Then
Me.Hide()
frmStart.Show()
ElseIf answer2 = MsgBoxResult.Yes Then
Application.Exit()
Else
Exit Sub
End If
End If

End Sub

 

[demausdauth]

Double check your parameter names. Found this one....

cmd.Parameters.AddWithValue("@BsAdd", txtBusAdd.Text)

No BsAdd....

strSQL = "INSERT into CommitteeMembers_new (LastName, FirstName, PrefName, Title, Jurisdiction, BusAdd, StrAdd, MailAdd, BusPh, Cell, AltCell, HomePh, Fax, AltFax, Email, Website, EthicsPolicy, EthicDt, ConfCell, ConfAltCell, ConfHomePh)" & _
"VALUES (@LName, @FName, @PName, @Title, @Jur, @BAdd, @SAdd, @MAdd, @BPh, @Cell, @ACell, @HPh, @Fax, @AFax, @Email, @Web, @Ethic, @EthicDt, @ConCell, @ConACell, @ConHome)"

Just so you know a Scalar Variable is defined within the query itself.

 

[cjard]

strSQL = " UPDATE CommitteeMembers SET" & _
"LastName = @LastName" & _
"FirstName = @FName" & _
"PrefName = @PName" & _
"Title = @Title" & _
"Jurisdiction = @Jur" & _
"BusAdd = @BAdd" & _
"StrAdd = @SAdd" & _
"MailAdd = @MAdd" & _
"BusPh = @BPh" & _
"Cell = @Cell" & _
"AltCell = @ACell" & _
"HomePh = @HPh" & _
"Fax = @Fax" & _
"AltFax = @AFax" & _
"Email = @Email" & _
"Website = @Web" & _
"Ethic = @Ethic" & _
"EthicDt = @EthicDt" & _
"ConfCell = @ConCell" & _
"ConfAltCell = @ConACell" & _
"ConfHomePh = @ConHome" & _
"WHERE MemberID =" & intID & "'"
End If

Oops.. We were doing so well parameterizing our queries right up to the WHERE clause in the UPDATE sql..

How about:

"WHERE MemberID = @memberid"
...
cmd.Parameters.AddWithValue("@memberid", intID)

Please use

 tags when posting code


ps; are you bored of writing these SQLs into your code yet? For pointers on how to use visual studio to write these queries for you (it doesnt get parameter names wrong, which is an instant productivity boost) and help you generate a data layer for your well-designed object oriented program, read the DW4 link in my signature.. Start with Creating a Simple Data Application, and read others like Creating a Form to Search Data, and articles on saving data