如何解决错误必须声明标量变量

Question

我在vb.net中遇到一个错误。它显示必须声明标量变量"FieldDeg"；。我只能使用insertSql将数据插入数据库，但不能使用insertSql 2，因为它一直在FieldDeg中显示错误，有人能帮我处理这些代码吗。非常感谢。这是我的密码。

Imports System.Data.SqlClient
Public Class CV
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmd2 As SqlCommand
Dim Genders() As String = {"Male", "Female"}

Private Sub CV_Load(sender As Object, e As EventArgs) Handles MyBase.Load
cboGender.Items.AddRange(Genders)
cboGender.DropDownStyle = ComboBoxStyle.DropDown
cboGender.SelectedIndex = 0
OpenFileDialog1.Filter = "Image Files (*.bmp;*.jpg;*.jpeg;*.png)|*.BMP;*.JPG;*.JPEG;*.PNG"
txtField1.Enabled = False
txtMajor1.Enabled = False
txtInstitute1.Enabled = False
txtGrade1.Enabled = False
mskGradDate1.Enabled = False
txtField2.Enabled = False
txtMajor2.Enabled = False
txtInstitute2.Enabled = False
txtGrade2.Enabled = False
mskGradDate2.Enabled = False
End Sub
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Dim insertSql As String
Dim insertSql2 As String
conn = New SqlConnection("Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=|DataDirectory|Assignment.mdf;Integrated Security=True;Connect Timeout=30")
insertSql = "Insert into [Personal Information] ([Full Name], [Address], [Phone Number], [Mobile Number], [Email], [Age], [Date of Birth], [Nationality], [Gender], [Marital Status], [IC Or Passport], [Permanent Residence]) Values (@Fullname, @Address, @Phone, @Mobile, @Email, @Age, @DOB, @Nationality, @Gender, @Marital, @IC, @Residence)"
insertSql2 = "Insert into [Education Background] ([Field of Study Deg], [Major Deg], [Institute Or University Deg], [Grade Deg],[Graduation Date Deg], [Field of Study Dip], [Major Dip], [Institute Or University Dip], [Grade Dip], [Graduation Date Dip]) Values (@FieldDeg, @MajorDeg, @InstituteDeg, @GradeDeg, @GradDateDeg, @FieldDip, @MajorDip, @InstituteDip, @GradeDip, @GradDateDip)"
If (txtFullname.Text = "" Or txtAddress.Text = "" Or mskPhone.Text = "" Or mskMobile.Text = "" Or txtEmail.Text = "" Or mskAge.Text = "" Or mskAge.Text = "" Or mskDOB.Text = "" Or txtNationality.Text = "" Or txtMarital.Text = "" Or txtIC.Text = "" Or txtPR.Text = "") Then
MessageBox.Show("Please enter all information for Curriculum Vitae.", "Data Field Cannot Be Empty", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
'open the database connection
conn.Open()
'create the command object
cmd = New SqlCommand(insertSql, conn)
cmd.Parameters.AddWithValue("@Fullname", txtFullname.Text)
cmd.Parameters.AddWithValue("@Address", txtAddress.Text)
cmd.Parameters.AddWithValue("@Phone", mskPhone.Text)
cmd.Parameters.AddWithValue("@Mobile", mskMobile.Text)
cmd.Parameters.AddWithValue("@Email", txtEmail.Text)
cmd.Parameters.AddWithValue("@Age", mskAge.Text)
cmd.Parameters.AddWithValue("@DOB", mskDOB.Text)
cmd.Parameters.AddWithValue("@Nationality", txtNationality.Text)
cmd.Parameters.AddWithValue("@Gender", cboGender.Text)
cmd.Parameters.AddWithValue("@Marital", txtMarital.Text)
cmd.Parameters.AddWithValue("@IC", txtIC.Text)
cmd.Parameters.AddWithValue("@Residence", txtPR.Text)
cmd2 = New SqlCommand(insertSql2, conn)
cmd.Parameters.AddWithValue("@FieldDeg", txtField1.Text)
cmd.Parameters.AddWithValue("@MajorDeg", txtMajor1.Text)
cmd.Parameters.AddWithValue("@InstituteDeg", txtInstitute1.Text)
cmd.Parameters.AddWithValue("@GradeDeg", txtGrade1.Text)
cmd.Parameters.AddWithValue("@GradDateDeg", mskGradDate1.Text)
cmd.Parameters.AddWithValue("@FieldDip", txtField2.Text)
cmd.Parameters.AddWithValue("@MajorDip", txtMajor2.Text)
cmd.Parameters.AddWithValue("@InstituteDip", txtInstitute2.Text)
cmd.Parameters.AddWithValue("@GradeDip", txtGrade2.Text)
cmd.Parameters.AddWithValue("@GradDateDip", mskGradDate2.Text)
'executes the INSERT SQL statement
Dim status As Integer = cmd.ExecuteNonQuery()
Dim status2 As Integer = cmd2.ExecuteNonQuery()
If status > 0 And status2 > 0 Then
MessageBox.Show("Curriculum Vitae has been recorded successfully", "CV Recorded", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Curriculum Vitae cannot be recorded. Please check the cv details.", "CV Record Failed", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
conn.Close()  'close the connection
End If
End Sub
Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
OpenFileDialog1.ShowDialog()
Pic.ImageLocation = OpenFileDialog1.FileName
End Sub
Private Sub chkDegree_CheckedChanged(sender As Object, e As EventArgs) Handles chkDegree.CheckedChanged
If chkDegree.Checked Then
txtField1.Enabled = True
txtMajor1.Enabled = True
txtInstitute1.Enabled = True
txtGrade1.Enabled = True
mskGradDate1.Enabled = True
Else
txtField1.Enabled = False
txtMajor1.Enabled = False
txtInstitute1.Enabled = False
txtGrade1.Enabled = False
mskGradDate1.Enabled = False
End If
End Sub
Private Sub chkDiploma_CheckedChanged(sender As Object, e As EventArgs) Handles chkDiploma.CheckedChanged
If chkDiploma.Checked Then
txtField2.Enabled = True
txtMajor2.Enabled = True
txtInstitute2.Enabled = True
txtGrade2.Enabled = True
mskGradDate2.Enabled = True
Else
txtField2.Enabled = False
txtMajor2.Enabled = False
txtInstitute2.Enabled = False
txtGrade2.Enabled = False
mskGradDate2.Enabled = False
End If
End Sub
End Class

Answer 1

我认为行：cmd.Parameters.AddWithValue("@FieldDeg", txtField1.Text)应该引用cmd2。您将参数添加到错误的命令中。

Answer 2

顺便说一句，你是"检查"；子程序可以简化如下：

Private Sub chkDiploma_CheckedChanged(sender As Object, e As EventArgs) Handles chkDiploma.CheckedChanged
txtField2.Enabled = chkDiploma.Checked
txtMajor2.Enabled = chkDiploma.Checked
txtInstitute2.Enabled = chkDiploma.Checked
txtGrade2.Enabled = chkDiploma.Checked
mskGradDate2.Enabled = chkDiploma.Checked
End Sub

Answer 3

我将验证代码移到了一个单独的方法中。我在那里发表了关于额外验证的评论。我用了OrElse而不是Or。这会使If语句短路，一旦发现True条件就停止检查。

将连接和命令保持在使用它们的方法的本地。这些物体需要关闭和处理。Using...End即使出现错误，使用块也会为您处理此问题。

声明Using块外的2个状态变量。

使用参数值得称赞！对于Sql Server，请使用.Add方法作为参数。看见http://www.dbdelta.com/addwithvalue-is-evil/和https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/和另一个：https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications这是另一个https://andrevdm.blogspot.com/2010/12/parameterised-queriesdont-use.html

我不得不猜测你的字段的数据类型和大小。请检查数据库中的实际值。

在.Execute...之前的最后一刻打开连接

我在Using块之外声明了状态变量，这样我们就可以在处理连接后使用它们。我们不希望在显示MessageBox时打开连接。用户本可以去吃午饭的。

我仍然担心教育背景中的数据与个人信息无关。通常，教育背景中会有一个外键字段引用个人信息中的主键。如果是这种情况，则需要将Select Scope_Identity附加到第一个命令，调用.ExecuteScalar而不是nonquery，并在第二个查询中使用结果值作为参数。

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
If Not ValidateInput() Then
Exit Sub
End If
Dim Status As Integer
Dim Status2 As Integer
Using conn As New SqlConnection("Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=|DataDirectory|Assignment.mdf;Integrated Security=True;Connect Timeout=30"),
cmd As New SqlCommand("Insert into [Personal Information] ([Full Name], [Address], [Phone Number], [Mobile Number], [Email], [Age], [Date of Birth], [Nationality], [Gender], [Marital Status], [IC Or Passport], [Permanent Residence]) Values (@Fullname, @Address, @Phone, @Mobile, @Email, @Age, @DOB, @Nationality, @Gender, @Marital, @IC, @Residence)", conn),
cmd2 As New SqlCommand("Insert into [Education Background] ([Field of Study Deg], [Major Deg], [Institute Or University Deg], [Grade Deg],[Graduation Date Deg], [Field of Study Dip], [Major Dip], [Institute Or University Dip], [Grade Dip], [Graduation Date Dip]) Values (@FieldDeg, @MajorDeg, @InstituteDeg, @GradeDeg, @GradDateDeg, @FieldDip, @MajorDip, @InstituteDip, @GradeDip, @GradDateDip)", conn)
With cmd.Parameters
.Add("@Fullname", SqlDbType.VarChar, 200).Value = txtFullname.Text
.Add("@Address", SqlDbType.VarChar, 200).Value = txtAddress.Text
.Add("@Phone", SqlDbType.VarChar, 200).Value = mskPhone.Text
.Add("@Mobile", SqlDbType.VarChar, 200).Value = mskMobile.Text
.Add("@Email", SqlDbType.VarChar, 200).Value = txtEmail.Text
.Add("@Age", SqlDbType.VarChar, 200).Value = mskAge.Text
.Add("@DOB", SqlDbType.VarChar, 200).Value = mskDOB.Text
.Add("@Nationality", SqlDbType.VarChar, 200).Value = txtNationality.Text
.Add("@Gender", SqlDbType.VarChar, 200).Value = cboGender.Text
.Add("@Marital", SqlDbType.VarChar, 200).Value = txtMarital.Text
.Add("@IC", SqlDbType.VarChar, 200).Value = txtIC.Text
.Add("@Residence", SqlDbType.VarChar, 200).Value = txtPR.Text
End With
With cmd2.Parameters
.Add("@FieldDeg", SqlDbType.VarChar, 200).Value = txtField1.Text
.Add("@MajorDeg", SqlDbType.VarChar, 200).Value = txtMajor1.Text
.Add("@InstituteDeg", SqlDbType.VarChar, 200).Value = txtInstitute1.Text
.Add("@GradeDeg", SqlDbType.VarChar, 200).Value = txtGrade1.Text
.Add("@GradDateDeg", SqlDbType.VarChar, 200).Value = mskGradDate1.Text
.Add("@FieldDip", SqlDbType.VarChar, 200).Value = txtField2.Text
.Add("@MajorDip", SqlDbType.VarChar, 200).Value = txtMajor2.Text
.Add("@InstituteDip", SqlDbType.VarChar, 200).Value = txtInstitute2.Text
.Add("@GradeDip", SqlDbType.VarChar, 200).Value = txtGrade2.Text
.Add("@GradDateDip", SqlDbType.VarChar, 200).Value = mskGradDate2.Text
End With
conn.Open()
Status = cmd.ExecuteNonQuery()
Status2 = cmd2.ExecuteNonQuery()
End Using
If Status > 0 AndAlso Status2 > 0 Then
MessageBox.Show("Curriculum Vitae has been recorded successfully", "CV Recorded", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Curriculum Vitae cannot be recorded. Please check the cv details.", "CV Record Failed", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
End Sub
Private Function ValidateInput() As Boolean
'You will want to add additional validation if any of your database fields require numbers or dates.
'Use the various .TryParse methods to validate
If txtFullname.Text = "" OrElse txtAddress.Text = "" OrElse mskPhone.Text = "" OrElse mskMobile.Text = "" OrElse txtEmail.Text = "" OrElse mskAge.Text = "" OrElse mskAge.Text = "" OrElse mskDOB.Text = "" OrElse txtNationality.Text = "" OrElse txtMarital.Text = "" OrElse txtIC.Text = "" OrElse txtPR.Text = "" Then
MessageBox.Show("Please enter all information for Curriculum Vitae.", "Data Field Cannot Be Empty", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return False
End If
Return True
End Function