附加信息:无法将参数值从字符串转换为Int32.vb.net

Question

Imports System.Data.SqlClient
Public Class PersonalInfo
Private Sub Guna2GradientButton1_Click(sender As Object, e As EventArgs) Handles BUTTNEX.Click
PD1.Hide()
PD2.Show()
End Sub
Private Sub Guna2GradientButton3_Click(sender As Object, e As EventArgs) Handles Guna2GradientButton3.Click
PD2.Hide()
PD1.Show()
End Sub
Private Sub NEXYAT_Click(sender As Object, e As EventArgs) Handles NEXYAT.Click
Dim conn As New SqlConnection("Data Source=DESKTOP-39ATE1Q;Initial Catalog=INFINITYMATRIMONY;Integrated Security=True")
Dim command As New SqlCommand("INSERT INTO perdetails VALUES(@CBGENDER,@DOB,@CBHEIGHT,@TXTCOUNT,@TXTSTATE,@TXTCITY,@TXTEDU,@TXTWORK,@CBINC,@CBMS,@CBMT,@CBRELI,@TXTFNAME,@TXTEMAIL,@TXTPH,TXTPASS)", conn)
command.Parameters.Add("@CBGENDER", SqlDbType.VarChar).Value = CBGENDER.Text
command.Parameters.Add("@DOB", SqlDbType.DateTime).Value = DOB.Text
command.Parameters.Add("@CBHEIGHT", SqlDbType.VarChar).Value = CBHEIGHT.Text
command.Parameters.Add("@TXTCOUNT", SqlDbType.VarChar).Value = TXTCOUNT.Text
command.Parameters.Add("@TXTSTATE", SqlDbType.VarChar).Value = TXTSTATE.Text
command.Parameters.Add("@TXTCITY", SqlDbType.VarChar).Value = TXTCITY.Text
command.Parameters.Add("@TXTEDU", SqlDbType.VarChar).Value = TXTEDU.Text
command.Parameters.Add("@TXTWORK", SqlDbType.VarChar).Value = TXTWORK.Text
command.Parameters.Add("@CBINC", SqlDbType.VarChar).Value = CBINC.Text
command.Parameters.Add("@CBMS", SqlDbType.VarChar).Value = CBMS.Text
command.Parameters.Add("@CBMT", SqlDbType.VarChar).Value = CBMT.Text
command.Parameters.Add("@CBRELI", SqlDbType.VarChar).Value = CBRELI.Text
command.Parameters.Add("@TXTFNAME", SqlDbType.VarChar).Value = TXTFNAME.Text
command.Parameters.Add("@TXTEMAIL", SqlDbType.VarChar).Value = TXTEMAIL.Text
command.Parameters.Add("@TXTPH", SqlDbType.Int).Value = TXTPH.Text
command.Parameters.Add("@TXTPASS", SqlDbType.VarChar).Value = TXTPASS.Text
conn.Open()
command.ExecuteNonQuery()
conn.Close()
Me.Hide()
End Sub
End Class

Answer 1

首先使用TryParse验证用户输入。

众所周知，DBA会在不通知开发人员的情况下更改数据库模式。在Insert中列出字段和参数可以节省跟踪类型不匹配的时间。

Using conn行末尾的逗号允许我们将命令(也需要处理(放在同一个Using块中。

请注意，@DOB和@TXTPH的Value属性使用TryParses中的第二个参数。当解析成功时，将使用转换后的值填充该参数。

Private Sub NEXYAT_Click(sender As Object, e As EventArgs) Handles NEXYAT.Click
Dim DOB As Date
Dim PH As Integer
If Not Integer.TryParse(TXTPH.Text, PH) Then
MessageBox.Show("Please enter a valid number for PH.")
Exit Sub
End If
If Not Date.TryParse(DOB.Text, DOB) Then
MessageBox.Show("Please enter a valid date for DOB")
Exit Sub
End If
Dim strSQL = "INSERT INTO perdetails (CBGENDER,DOB,CBHEIGHT,TXTCOUNT,TXTSTATE,TXTCITY,TXTEDU,TXTWORK,CBINC,CBMS,CBMT,CBRELI,TXTFNAME,TXTEMAIL,TXTPH,TXTPASS) 
VALUES(@CBGENDER,@DOB,@CBHEIGHT,@TXTCOUNT,@TXTSTATE,@TXTCITY,@TXTEDU,@TXTWORK,@CBINC,@CBMS,@CBMT,@CBRELI,@TXTFNAME,@TXTEMAIL,@TXTPH,TXTPASS)"
Using conn As New SqlConnection("Data Source=DESKTOP-39ATE1Q;Initial Catalog=INFINITYMATRIMONY;Integrated Security=True"),
insComm As New SqlCommand(strSQL, conn)
insComm.Parameters.Add("@CBGENDER", SqlDbType.VarChar).Value = CBGENDER.Text
insComm.Parameters.Add("@DOB", SqlDbType.DateTime).Value = DOB
insComm.Parameters.Add("@CBHEIGHT", SqlDbType.VarChar).Value = CBHEIGHT.Text
insComm.Parameters.Add("@TXTCOUNT", SqlDbType.VarChar).Value = TXTCOUNT.Text
insComm.Parameters.Add("@TXTSTATE", SqlDbType.VarChar).Value = TXTSTATE.Text
insComm.Parameters.Add("@TXTCITY", SqlDbType.VarChar).Value = TXTCITY.Text
insComm.Parameters.Add("@TXTEDU", SqlDbType.VarChar).Value = TXTEDU.Text
insComm.Parameters.Add("@TXTWORK", SqlDbType.VarChar).Value = TXTWORK.Text
insComm.Parameters.Add("@CBINC", SqlDbType.VarChar).Value = CBINC.Text
insComm.Parameters.Add("@CBMS", SqlDbType.VarChar).Value = CBMS.Text
insComm.Parameters.Add("@CBMT", SqlDbType.VarChar).Value = CBMT.Text
insComm.Parameters.Add("@CBRELI", SqlDbType.VarChar).Value = CBRELI.Text
insComm.Parameters.Add("@TXTFNAME", SqlDbType.VarChar).Value = TXTFNAME.Text
insComm.Parameters.Add("@TXTEMAIL", SqlDbType.VarChar).Value = TXTEMAIL.Text
insComm.Parameters.Add("@TXTPH", SqlDbType.Int).Value = PH
insComm.Parameters.Add("@TXTPASS", SqlDbType.VarChar).Value = TXTPASS.Text
conn.Open()
insComm.ExecuteNonQuery()
End Using
Me.Hide()
End Sub