如何通过使用更新查询在测试日期coloumn中插入日期 [英] how to insert date in test date coloumn through use of update query

问题描述

我有四列名称是测试号（自动生成），测试日期（日期），测试周期号（号码），测试日（文本）

首先我需要生成600个日期根据我的要求，我已经做了，但是在一个单独的表中。现在我希望它们被插入到这个有上面四列的表中。



这可以使用更新查询完成，直到我知道但朋友我从类型字符串到错误转换为双重错误。



i写了这个查询： -

Dim query_update As String =UPDATE Trans_Test_Hdr_1 SET Test_Date =+ sch_ends_txtbox.Text +WHERE Test_no =+ loopcount +



--- -

其中sch_ends_txtbox是一个文本框，我用它来显示loopvalue，loopcount是循环变量。

循环从1移动到600所以我正在使用更新loopcount值。

i have four columns names are test no(auto generated),test date (date),test cycle no(number),test day (text)
first i need to generate 600 dates according to my requirement,which i have did,but in a separate table.so now i want them to be inserted in this table which has four columns as mentioned above.

this can be done using an update query till i know but friends i get an error conversion from type string to double error.

i wrote this query:-
Dim query_update As String = "UPDATE Trans_Test_Hdr_1 SET Test_Date = " + sch_ends_txtbox.Text + " WHERE Test_no = " + loopcount + ""

-----
where sch_ends_txtbox is a textbox which i have used to display the loopvalue and loopcount is variable of loop.
loop moves from 1 to 600 so i am doing update using loopcount value.

推荐答案

根据我之前的评论 - 使用参数化查询 - 他们是av注意sql注入的风险，并根据需要使用引号来处理周围的日期和varchars ...参见此链接 [ ^ ]获取更多信息



您的查询应该看起来类似于这个

As per my earlier comment - use parameterised queries - they avoid the risk of sql injection and also take care of surrounding dates and varchars with quotes as required ...see this link[^] for more information

Your query should look similar to this

Dim sql As String = "UPDATE Trans_Test_Hdr_1 SET Test_Date = @schends WHERE Test_no = @loopcount"
Dim cmd As New SqlCommand(sql)
cmd.Parameters.Add("@schends", SqlDbType.Date).Value = sch_ends_txtbox.Text
cmd.Parameters.Add("@loopcount", SqlDbType.SmallInt).Value = loopcount

如果你仍然出现错误，然后 sch_ends_txtbox.Text 的内容格式错误，您需要确保数据库识别它



类似

If you are still getting errors then the content of sch_ends_txtbox.Text is in the wrong format and you need to make sure the database recognises it

Something like

Dim convertedDate As Date
Dim convertedString As String
If Date.TryParse(sch_ends_txtbox.Text, convertedDate) Then
    convertedString = String.Format("{0:dd-MMM-yyyy}", convertedDate)
Else
    MessageBox.Show(String.Format("Unable to convert '{0}' to a date.", sch_ends_txtbox.Text))
End If

可行......你需要将一行更改为

would work ... you'll need to change one line to

cmd.Parameters.Add("@schends", SqlDbType.Date).Value = convertedString

警告：我无法正确测试，所以可能会有一些语法错误

Caveat: I haven't been able to test this properly so there may be some syntax errors

这篇关于如何通过使用更新查询在测试日期coloumn中插入日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！