操作数类型冲突:日期与文本不兼容

Question

>我正在尝试创建一个写入数据库的调查问卷。一切都设置好了，但我只是不断得到"操作数类型冲突：日期与文本不兼容"。我有另一个网络表单，它允许用户只插入时间和日期，并且数据类型在此数据表单中匹配相同（即 time（7） 和日期），所以我有点困惑为什么它没有写，因为我输入了正确的数据以下是表单的完整代码：

    Dim EventID As Integer
    Dim Con As SqlConnection
    Dim cmd As SqlCommand
    Dim cmdstring As String = "SELECT EventID FROM [Event Table] WHERE EventName = @EventName"
    Con = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|YellowDoor.mdf;Integrated Security=True;User Instance=True")
    cmd = New SqlCommand(cmdstring, Con)
    cmd.Parameters.Add("@EventName", Data.SqlDbType.VarChar).Value = DropDownList1.SelectedValue
    Con.Open()
    EventID = (cmd.ExecuteScalar())
    Con.Close()
    Dim recordsAffected As String
    Dim cmdstring2 As String = "INSERT [Question Table](EventID, DateOfMeeting, BrideName, BrideNumber, GroomName, GroomNumber, InvoiceAddress, BGAddress, EmailAddress, DateOfWedding, WeddingLocation, WeddingReception, MarqueeCompany, NumberOfGuests, WeddingTime, TravelToReception, ReceptionTime, LineUp, Speeches, SpeechesT, Beverages, SpeechTime, GlassHire, Grace, GraceName, CutCake, CakeDetails, Cake, MirrorPlatter, Sword, FoodTime, TablePlan, Easel,LinenNapkins, TableNumbers, Tables, HeatingAndLighting, Skip, CoatRail, Toilets, ChaffingDishes, GardenFurniture, MenuPrinting, PASystem, BoneChina, Virtu, TallStem, Candelabras, Flowers, Red, White, Still, Sparkling, GlassHireMeal, Corkage, EveningGuests, EveningTime, Cheese, Finger, Rolls, Other, EveningFoodT) Values(@EventID, @DateOfMeeting, @BrideName, @BrideNumber, @GroomName, @GroomNumber, @InvoiceAddress, @BGAddress, @EmailAddress, @DateOfWedding, @WeddingLocation, @WeddingReception, @MarqueeCompany, @NumberOfGuests, @WeddingTime, @TravelToReception, @ReceptionTime, @LineUp, @Speeches, @SpeechesT, @Beverages, @SpeechTime, @GlassHire, @Grace, @GraceName, @CutCake, @CakeDetails, @Cake, @MirrorPlatter, @Sword, @FoodTime, @TablePlan, @Easel, @LinenNapkins, @TableNumbers, @Tables, @HeatingAndLighting, @Skip, @CoatRail, @Toilets, @ChaffingDishes, @GardenFurniture, @MenuPrinting, @PASystem, @BoneChina, @Virtu, @TallStem, @Candelabras, @Flowers, @Red, @White, @Still, @Sparkling, @GlassHireMeal, @Corkage, @EveningGuests, @EveningTime, @Cheese, @Finger, @Rolls, @Other, @EveningFoodT)"
    Con = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|YellowDoor.mdf;Integrated Security=True;User Instance=True")
    cmd = New SqlCommand(cmdstring2, Con)
    cmd.Parameters.Add("@EventID", Data.SqlDbType.Int).Value = EventID
    cmd.Parameters.Add("@DateOfMeeting", SqlDbType.Date).Value = txtMeetingDate.Text
    cmd.Parameters.Add("@BrideName", SqlDbType.Text).Value = txtBride.Text
    cmd.Parameters.Add("@BrideNumber", SqlDbType.NChar).Value = txtBrideN.Text
    cmd.Parameters.Add("@GroomName", SqlDbType.Text).Value = txtGroom.Text
    cmd.Parameters.Add("@GroomNumber", SqlDbType.NChar).Value = txtGroomN.Text
    cmd.Parameters.Add("@InvoiceAddress", SqlDbType.Text).Value = txtInvoice.Text
    cmd.Parameters.Add("@BGAddress", SqlDbType.Text).Value = txtBAddress.Text
    cmd.Parameters.Add("@EmailAddress", SqlDbType.Text).Value = txtEmail.Text
    cmd.Parameters.Add("@DateOfWedding", SqlDbType.Date).Value = txtDate.Text
    cmd.Parameters.Add("@WeddingLocation", SqlDbType.Text).Value = txtLocationS.Text
    cmd.Parameters.Add("@WeddingReception", SqlDbType.Text).Value = txtLocationR.Text
    cmd.Parameters.Add("@MarqueeCompany", SqlDbType.Text).Value = txtMarquee.Text
    cmd.Parameters.Add("@NumberOfGuests", SqlDbType.Text).Value = txtNumberOG.Text
    cmd.Parameters.Add("@WeddingTime", SqlDbType.Time).Value = txtWeddingService.Text
    cmd.Parameters.Add("@TravelToReception", SqlDbType.Time).Value = txtTravelRecep.Text
    cmd.Parameters.Add("@ReceptionTime", SqlDbType.Time).Value = txtReceptionTime.Text
    cmd.Parameters.Add("@LineUp", SqlDbType.NVarChar).Value = IIf(RadYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@Speeches", SqlDbType.NVarChar).Value = IIf(RadYes0.Checked, "Yes", "No")
    cmd.Parameters.Add("@SpeechesT", SqlDbType.NVarChar).Value = IIf(RadBefore.Checked, "Before", "After")
    cmd.Parameters.Add("@Beverages", SqlDbType.Text).Value = txtBeverage.Text
    cmd.Parameters.Add("@SpeechTime", SqlDbType.Text).Value = txtToastT.Text
    cmd.Parameters.Add("@GlassHire", SqlDbType.NVarChar).Value = IIf(RadYes1.Checked, "Yes", "No")
    cmd.Parameters.Add("@Grace", SqlDbType.NVarChar).Value = IIf(RadYes2.Checked, "Yes", "No")
    cmd.Parameters.Add("@GraceName", SqlDbType.Text).Value = txtGrace.Text
    cmd.Parameters.Add("@CutCake", SqlDbType.Text).Value = IIf(RadCakeYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@CakeDetails", SqlDbType.Text).Value = txtCakeDetails.Text
    cmd.Parameters.Add("@Cake", SqlDbType.Text).Value = IIf(RadYes3.Checked, "Yes", "No")
    cmd.Parameters.Add("@MirrorPlatter", SqlDbType.Text).Value = IIf(RadYes4.Checked, "Yes", "No")
    cmd.Parameters.Add("@Sword", SqlDbType.Text).Value = IIf(RadYes5.Checked, "Yes", "No")
    cmd.Parameters.Add("@FoodTime", SqlDbType.Time).Value = txtFoodTime.Text
    cmd.Parameters.Add("@TablePlan", SqlDbType.Text).Value = IIf(RadYesTbl.Checked, "Yes", "No")
    cmd.Parameters.Add("@Easel", SqlDbType.Text).Value = IIf(RadYesEasel.Checked, "Yes", "No")
    cmd.Parameters.Add("@LinenNapkins", SqlDbType.Text).Value = IIf(RadYesLinen.Checked, "Yes", "No")
    cmd.Parameters.Add("@TableNumbers", SqlDbType.Text).Value = IIf(RadYesTable.Checked, "Yes", "No")
    cmd.Parameters.Add("@Tables", SqlDbType.Text).Value = IIf(RadYesTC.Checked, "Yes", "No")
    cmd.Parameters.Add("@HeatingAndLighting", SqlDbType.Text).Value = IIf(RadYesHeat.Checked, "Yes", "No")
    cmd.Parameters.Add("@Skip", SqlDbType.Text).Value = IIf(RadYesSkip.Checked, "Yes", "No")
    cmd.Parameters.Add("@CoatRail", SqlDbType.Text).Value = IIf(RadYesCoat.Checked, "Yes", "No")
    cmd.Parameters.Add("@Toilets", SqlDbType.Text).Value = IIf(RadYesToilet.Checked, "Yes", "No")
    cmd.Parameters.Add("@ChaffingDishes", SqlDbType.Text).Value = IIf(RadYesChaf.Checked, "Yes", "No")
    cmd.Parameters.Add("@GardenFurniture", SqlDbType.Text).Value = IIf(RadYesGarden.Checked, "Yes", "No")
    cmd.Parameters.Add("@MenuPrinting", SqlDbType.Text).Value = IIf(RadYesMenu.Checked, "Yes", "No")
    cmd.Parameters.Add("@PASystem", SqlDbType.Text).Value = IIf(RadYesPA.Checked, "Yes", "No")
    cmd.Parameters.Add("@BoneChina", SqlDbType.Text).Value = IIf(RadBYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@Virtu", SqlDbType.Text).Value = IIf(RadVYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@TallStem", SqlDbType.Text).Value = IIf(RadGYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@Candelabras", SqlDbType.Text).Value = IIf(RadCanYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@Flowers", SqlDbType.Text).Value = IIf(RadFlowerYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@Red", SqlDbType.Text).Value = IIf(RadRedYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@White", SqlDbType.Text).Value = IIf(RadWhiteYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@Still", SqlDbType.Text).Value = IIf(RadStillYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@Sparkling", SqlDbType.Text).Value = IIf(RadSparkYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@GlassHireMeal", SqlDbType.Text).Value = IIf(RadYesGlass.Checked, "Yes", "No")
    cmd.Parameters.Add("@Corkage", SqlDbType.Text).Value = IIf(RadYesCork.Checked, "Yes", "No")
    cmd.Parameters.Add("@EveningGuests", SqlDbType.Text).Value = IIf(RadYesEvening.Checked, "Yes", "No")
    cmd.Parameters.Add("@EveningTime", SqlDbType.Time).Value = txtArrival.Text
    cmd.Parameters.Add("@Cheese", SqlDbType.Text).Value = IIf(RadCheeseYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@Finger", SqlDbType.Text).Value = IIf(RadFingerYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@Rolls", SqlDbType.Text).Value = IIf(RadCiabattaYes.Checked, "Yes", "No")
    cmd.Parameters.Add("@Other", SqlDbType.Text).Value = txtEveningFS.Text
    cmd.Parameters.Add("@EveningFoodT", SqlDbType.Time).Value = txtEveningFood.Text
    Con.Open()
    recordsAffected = cmd.ExecuteNonQuery
    Con.Close()

Answer 1

dim rDate as DateTime
if (DateTime.TryParse(txtDate.Text, out rDate)) then
    cmd.Parameters.Add("@DateOfWedding", SqlDbType.Date).Value = rDate
else
    cmd.Parameters.Add("@DateOfWedding", SqlDbType.Date).Value = DBNull.Value

您可以检查文本框中的字符串是否有效，然后将解析的日期放入参数中，否则将 null（或某些默认值）传递到参数中。