ASP.NET到SQL Server 2005，UPDATE smalldatetime程序不起作用？

As the topic suggests. I'm making a small program where you can browse and handle a bunch of data. The problem is that while inserting new rows into the SQL table works fine, using the same methods to update causes a problem with the smalldatetime variables.

正如专题所示。我正在制作一个小程序,您可以浏览和处理大量数据。问题是,在SQL表中插入新行时工作正常,使用相同的方法进行更新会导致smalldatetime变量出现问题。

I noticed using profiler that the exec command puts double quotes on the dates, but I can't seem to get rid of it. Plus the same double quote thing happens when inserting, and that works just fine. The command from the procedure looks like this:

我注意到使用分析器,exec命令在日期上放置双引号,但我似乎无法摆脱它。加上插入时会发生相同的双引号,这样就可以了。该过程的命令如下所示:

exec spUpdateinfo 
@id=default,
@job=N'Loadsoftext',
@address=N'Loadsoftext',
@startdate=''2009-02-01 00:00:00:000'',
@enddate=''2009-05-15 00:00:00:000'',
@other=N'Loadsoftext'

And what I get is this error:

我得到的是这个错误:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '2009'.

What I'm doing is using a date in the asp.net codebehind and passing that as a parameter as a normal date, assuming it's converted to smalldatetime in the SQL procedure. The methods and variables and strings etc used can be exactly the same as when inserting, but for some reason, UPDATING causes a problem. I've seen some PreserveSingleQuotes() function being used while googling around but that doesn't seem to exist in vb or SQL Server 2005.

我正在做的是在asp.net代码隐藏中使用日期并将其作为参数作为正常日期传递,假设它在SQL过程中转换为smalldatetime。使用的方法,变量和字符串等可以与插入时完全相同,但由于某种原因,UPDATING会导致问题。我在google搜索时看到了一些PreserveSingleQuotes()函数,但在vb或SQL Server 2005中似乎不存在。

Any idea what's causing this and how to get the update working normally?

知道是什么导致了这个以及如何使更新正常工作?

Edit: Oh and the date is originally Finnish format being dd.MM.yyyy. But as said, all this works just fine and gets converted automatically while using INSERT.

编辑:哦,日期最初是芬兰格式dd.MM.yyyy。但正如所说,所有这些工作正常,并在使用INSERT时自动转换。

Edit 2: Here's the whole code.

编辑2:这是整个代码。

Asp.Net (vb)

        Dim fdate As Date
        Dim ldate As Date

        fdate = CDate(BegindateTextBox.Text.Trim)
        ldate = CDate(EnddateTextBox.Text.Trim)
        Dim ID As New SqlParameter("@id", Request.QueryString("job_id"))
        Dim Job As New SqlParameter("@job", JobTextBox.Text)
        Dim Address As New SqlParameter("@address", AddressTextBox.Text)
        Dim Begindate As New SqlParameter("@startdate", fdate)
        Dim Enddate As New SqlParameter("@enddate", ldate)
        Dim Otherinfo As New SqlParameter("@other", OtherinfoTextBox.Text)

        Begindate.DbType = DbType.Date
        Enddate.DbType = DbType.Date

        myCommand = New SqlCommand("spUpdateinfo")
        myCommand.CommandType = CommandType.StoredProcedure
        myCommand.Connection = conn
        myCommand.Parameters.Add(ID)
        myCommand.Parameters.Add(Job)
        myCommand.Parameters.Add(Address)
        myCommand.Parameters.Add(Begindate)
        myCommand.Parameters.Add(Enddate)
        myCommand.Parameters.Add(Otherinfo)

        myCommand.ExecuteNonQuery()

SQL procedure

    UPDATE jobInfo
    SET Job = @Job,
        Address= @Address,
        Begindate = CAST(@Begindate AS smalldatetime),
        Enddate = CAST(@Enddate AS smalldatetime),
        Otherinfo = @Otherinfo
    WHERE Job_id = @id

Took a while as I had to edit the sensitives out. Anyway, thx for help and any ideas what's going on? You can see the casts etc in there as I've tried to fiddle with whatever I could to fix it, but it still isn't working.

花了一段时间,因为我必须编辑敏感信息。无论如何,thx寻求帮助和任何想法发生了什么?你可以在那里看到演员阵容等,因为我试图摆弄任何我可以解决它的东西,但它仍然无法正常工作。

Edit 3: Thanks for the helps, off for the day so I'll continue looking at this tomorrow.

编辑3:感谢帮助,关闭一天,所以我明天会继续关注这个。

4 个解决方案

#1

Try This:

exec spUpdateinfo 
@id=default,
@job=N'Loadsoftext',
@address=N'Loadsoftext',
@startdate='2009-02-01 00:00:00:000',
@enddate='2009-05-15 00:00:00:000',
@other=N'Loadsoftext'

You should only need one apostrophe when creating a datetime string

创建日期时间字符串时,您只需要一个撇号

EDIT:

Asp.Net (vb)

    Dim fdate As Date
    Dim ldate As Date

    fdate = CDate(BegindateTextBox.Text.Trim) 
    ldate = CDate(EnddateTextBox.Text.Trim) 
    //Trim might be screwing it up not sure

    Dim ID As New SqlParameter("@id", Request.QueryString("job_id"))
    Dim Job As New SqlParameter("@job", JobTextBox.Text)
    Dim Address As New SqlParameter("@address", AddressTextBox.Text)
    Dim Begindate As New SqlParameter("@startdate", fdate)
    Dim Enddate As New SqlParameter("@enddate", ldate)
    Dim Otherinfo As New SqlParameter("@other", OtherinfoTextBox.Text)

    Begindate.DbType = DbType.Date // Try removing this
    Enddate.DbType = DbType.Date // Try removing this

    myCommand = New SqlCommand("spUpdateinfo")
    myCommand.CommandType = CommandType.StoredProcedure
    myCommand.Connection = conn
    myCommand.Parameters.Add(ID)
    myCommand.Parameters.Add(Job)
    myCommand.Parameters.Add(Address)
    myCommand.Parameters.Add(Begindate)
    myCommand.Parameters.Add(Enddate)
    myCommand.Parameters.Add(Otherinfo)

    myCommand.ExecuteNonQuery()

SQL procedure

UPDATE jobInfo
SET Job = @Job,
    Address= @Address,
    Begindate = CAST(@Begindate AS smalldatetime) //Try removing the cast,
    Enddate = CAST(@Enddate AS smalldatetime) //Try removing the cast,
    Otherinfo = @Otherinfo
WHERE Job_id = @id

This is strange because what you are doing is incredibly similar to what I regularly do and I don't have the problems you have. The only thing I can suggest is to remove the pieces that I have highlighted and see if it works. I am suggesting this because in the code I normally use, they are not required.

这很奇怪,因为你所做的与我经常做的非常相似,而且我没有你遇到的问题。我唯一可以建议的是删除我突出显示的部分,看看它是否有效。我建议这是因为在我通常使用的代码中,它们不是必需的。

#2

How are you calling your stored procedure from ASP.NET? Are you using an SqlCommand object and adding the parameters to the command through the Parameters collection?

你是如何从ASP.NET调用存储过程的?您是否正在使用SqlCommand对象并通过Parameters集合将参数添加到命令中?

Typically, you will not use a Text command, but set the type to StoredProcedure, too. The following example calls a stored procedure named "tblAccount_Save" with 5 parameters:

通常,您不会使用Text命令,而是将类型设置为StoredProcedure。以下示例使用5个参数调用名为“tblAccount_Save”的存储过程:

    Dim conn As New SqlConnection("server=myserver;integrated security=sspi;initial catalog=mydb;")
    Dim sql As String = "[dbo].[tblAccount_Save]"
    Dim comm As System.Data.SqlClient.SqlCommand = New SqlCommand(sql, conn)
    comm.CommandType = System.Data.CommandType.StoredProcedure
    comm.Parameters.AddWithValue("@ID", id)
    comm.Parameters.AddWithValue("@AccountNumber", number)
    comm.Parameters.AddWithValue("@ClassID", class)
    comm.Parameters.AddWithValue("@LastName", lastName)
    comm.Parameters.AddWithValue("@FirstName", firstName)
    comm.ExecuteNonQuery()

Please provide more information about how you are calling the stored procedure from codebehind.

请提供有关如何从代码隐藏调用存储过程的更多信息。

Thanks.

#3

I think its the double quotes thing. The profiler may look like it has it because it is showing it within one main sp_executesql statement and its escaping the single quotes. However, the statement you have as it is, it should be single quotes and not double quotes.

我认为它是双引号的东西。分析器可能看起来像它有它,因为它在一个主sp_executesql语句中显示它并且它转义单引号。但是,您拥有的声明是,它应该是单引号而不是双引号。

#4

Edit 4: Ok, got it working. Thanks for help and sorry for the trouble, since the flaw was never in dates to begin with. The @id=default should've been a hint. I had a typo in the part where I linked to the edit page, I set the 'job_id' as 'jb_id' and so the actual number never got returned. I can't believe I missed this and spent HOURS on this...

编辑4:好的,搞定了。感谢您的帮助,并为此事感到抱歉,因为这个漏洞从未在日期开始。 @ id = default应该是一个提示。我在链接到编辑页面的部分中输入了一个拼写错误,我将'job_id'设置为'jb_id',因此实际的数字永远不会被返回。我简直不敢相信我错过了这个并花了我的时间......

Oh well, as said, thanks for helps. :D

哦,好吧,如上所述,谢谢你的帮助。 :d