ASP.NET to SQL Server 2005, UPDATE smalldatetime procedure doesn't work?

Question

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.

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 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.

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.

Edit 2: Here's the whole code.

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.

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

Answer 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.

Answer 2

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.

Answer 3

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?

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:

    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.

Answer 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...

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