Error when running sp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error when running sp

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-11-03 : 09:53:28

I am trying to run the following sp and am getting an error message 'Syntax error converting datetime from character string.'

CREATE PROCEDURE spRB_BuildBookingDates

@strDateReq datetime
AS

INSERT INTO tblRB_BookingDates(
BD_BookingDateRef,
BD_DateRequired,
BD_RoomRef)

SELECT
@strDateReq +'"*"'+ RM_RoomRef,
@strDateReq,
RM_RoomRef
from tblRB_Rooms
GO

Here's how I call it

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If CheckUserLevel() = False Then Exit Sub

Dim MySQL As String = "spRB_BuildBookingDates"
Dim myConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim Cmd As New SqlCommand(MySQL, myConn)
Cmd.CommandType = CommandType.StoredProcedure

Dim Newdate As Date

Dim x As Integer

Newdate = "02/11/2005"
For x = 1 To 90

Newdate = DateAdd("d", 1, Newdate)

Cmd.Parameters.Add(New SqlParameter("@strDateReq", CDate(Newdate)))

myConn.Open()
Cmd.ExecuteNonQuery()
myConn.Close()

Next
End Sub

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-03 : 09:59:14

try passing it as a string 20051102

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-11-03 : 10:01:07

How do I code that then in my asp.net ?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-11-03 : 11:04:14

For x = 1 To 90

Newdate = DateAdd("d", 1, Newdate)

Cmd.Parameters.Add(New SqlParameter("@strDateReq", CDate(Newdate)))

myConn.Open()
Cmd.ExecuteNonQuery()
myConn.Close()

Next

Oh
my
GOD

You make NINETY different trips to the DB from your web page? You OPEN and CLOSE the connection NINETY times?????

Looking at the code, it should work. What is your BD_BookingDateRef column declared as? Try this (or something similar)


SELECT 
CAST(CAST(@strDateReq as varchar(20))+'"*"'+ RM_RoomRef as Varchar(100)),
@strDateReq,
RM_RoomRef
from tblRB_Rooms

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-11-03 : 11:08:19

BD_BookingDateRef is nvarchar. Can I do it without making 90 trips ? If so how ? I want to write a record on tblRB_BookingDates for each date for each Roomref in tblRB_Rooms.

Thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources