Dynamic sp and dates CLOSED - 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)

Dynamic sp and dates CLOSED

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-12 : 05:47:00

Thanks for help building this sp. I have added this line
BD_DateRequired = ' + @strDateRequired + 'and

I am now getting an error 'Syntax error converting datetime from character string'

My calling code is

Private Sub FillAvailabilityGrid()

If Me.Session("RoomOnly") = "Yes" Then
Me.Session("DateRequired") = Today.ToShortDateString
End If

Dim MySQL As String = "spRB_GetAvailableRooms1"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strRoomList", Me.Session("RoomRefLogin")))
Cmd.Parameters.Add(New SqlParameter("@strDateRequired", CDate(Me.Session("DateRequiredLogin"))))
SqlDataAdapter1.SelectCommand = Cmd
Me.DsAvailableRooms1.Clear()
SqlDataAdapter1.Fill(DsAvailableRooms1, "vweAvailableRooms")

End Sub

CREATE PROC [spRB_GetAvailableRooms1]
(
@strRoomList varchar(500),
@strDateRequired datetime
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(600)

SET @SQL =
'SELECT * from vweAvailableRooms
WHERE BD_DateRequired = ' + @strDateRequired + 'and
BD_RoomRef IN (''' + Replace(@strRoomList,',',''',''') + ''')'

EXEC(@SQL)
END
GO

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-12 : 05:58:43

Try this...

SET @SQL =
'SELECT * from vweAvailableRooms
WHERE BD_DateRequired = convert(datetime,''' + @strDateRequired + ''') and
BD_RoomRef IN (''' + Replace(@strRoomList,',',''',''') + ''')'

Surendra

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-12 : 06:05:37

Same error I'm afraid........

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-12 : 06:18:10

What is the error?
Can you paste the error message here.

Surendra

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-12 : 06:32:20

Here it is !!

Syntax error converting datetime from character string.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.

Source Error:

Line 425: SqlDataAdapter1.SelectCommand = Cmd
Line 426: Me.DsAvailableRooms1.Clear()
Line 427: SqlDataAdapter1.Fill(DsAvailableRooms1, "vweAvailableRooms")
Line 428:
Line 429: End Sub

Source File: c:\inetpub\wwwroot\RoomBookings\frmAvailableRooms.aspx.vb Line: 427

Stack Trace:

[SqlException: Syntax error converting datetime from character string.]

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-12 : 06:38:24

I've looked at the content of Session("DateRequiredLogin") and it is US format (MM/DD/YYYY). My sql table has the date stored in British format (DD/MM/YYYY)- could this be the reason ???

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-12 : 06:39:09

Don't conver this parameter just pass as it is ...
Cmd.Parameters.Add(New SqlParameter("@strDateRequired", Me.Session("DateRequiredLogin")))

Surendra

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-12 : 06:44:33

Still the same error.....

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-12 : 06:54:52

Try this
SET @SQL =
'SELECT * from vweAvailableRooms
WHERE BD_DateRequired = ''' + rtrim(Convert(char(23),@strDateRequired)) + ''') and
BD_RoomRef IN (''' + Replace(@strRoomList,',',''',''') + ''')'

Surendra

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-12 : 07:01:15

New error !

Line 2: Incorrect syntax near ')'.

I tried removing the ) before the and at the end of the second line and it reverted to the old error message.....

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-12 : 07:10:22

quote:
Originally posted by Pinto

New error !

Line 2: Incorrect syntax near ')'.

I tried removing the ) before the and at the end of the second line and it reverted to the old error message.....

I think that is an additional bracket. Just remove it and just play around.... it is more easy for you to find out. Don’t give up.

Surendra

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-12 : 10:01:43

I am trying this now - still no good...same error.

CREATE PROC [spRB_GetAvailableRooms1]
(
@strRoomList varchar(500),
@strDateRequired datetime)
AS

BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(600)

SET @SQL =
'SELECT * from vweAvailableRooms
WHERE BD_DateRequired ='+ CONVERT(DATETIME, @strDateRequired , 102) +' and
BD_RoomRef IN (''' + Replace(@strRoomList,',',''',''') + ''')'

EXEC(@SQL)
END
GO

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-12 : 10:28:34

More than likely, the error is in the call to the stored procedure, not inside the stored procedure.

Try passing the date to the procedure in this format: YYYYMMDD

Edit:
I noticed that you introducted an error in the date format of your dynamic SQL string. The date string ahould be enclosed in single quotes, and format 112 is a better choice because it is a universal format independent of server settings.. You should add a print statement for debugging and get the procedure working in Query Analyzer before you try calling it from you application. Paste the output of the print statement inro a new QA window and run that to see if it works.

SET @SQL = 
'
SELECT
	*
from
	vweAvailableRooms
WHERE 
	'''+ CONVERT(DATETIME, @strDateRequired , 112) +'''	and
	BD_RoomRef IN (''' + Replace(@strRoomList,',',''',''') + ''')
'

print @SQL

CODO ERGO SUM

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-12 : 11:01:45

Thank you for your help. I have tried to run it in Anakyser and this is th error I get

SELECT * from vweAvailableRooms
WHERE BD_DateRequired = '''+ CONVERT(DATETIME, @strDateRequired , 112) + ''' and
BD_RoomRef IN (''' + Replace(@strRoomList,',',''',''') + ''')

@strDateRequired= '16/12/2005',
@strRoomList = 'NW1A1'

Erro is

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@strDateRequired'.

If I add Print @sql where will this print as I can see nothing ?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-12 : 11:21:19

This is an obvious error, since you do not have a select statement before the assignment:

@strDateRequired= '16/12/2005',
@strRoomList = 'NW1A1'

I see you also ignored my advise about using format YYYYMMDD for the date.

The PRINT output will show in the Query analyzer results pane. Have you ever used Query Analyzer before? You don't seem to be familiar with it.

CODO ERGO SUM

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-12 : 11:30:17

I am self taught I'm afraid as is obvious ! I looked up Format but was unsure of how to do this. Sorry...

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-12 : 11:35:06

Going off on a tangent, here is my original sp which worked fine except that I wanted to change the RoomRef part to use IN and pass an array as I'm trying to do, but failing. I thought I could only use IN with a dynamic array.

CREATE Procedure [spRB_GetAvailableRooms]

@strDateRequired datetime,
@strRoomRef nvarchar(10),
@strListOption nvarchar(12),
@strLocation nvarchar(100)

as

if @strListOption ='No'

BEGIN
Select *
from vweAvailableRooms where
BD_RoomRef like '%'+@strRoomRef+'%' and
RM_Location like '%' +@strLocation +'%' and
BD_DateRequired =@strDateRequired and
RM_NotInUse= 0
ORDER BY BD_RoomRef, BD_DateRequired asc
END

if @strListOption ='ThisMonth'

BEGIN
Select *
from vweAvailableRooms where
BD_RoomRef like '%'+@strRoomRef+'%' and
RM_Location like '%' +@strLocation +'%' and
Month(BD_DateRequired)= Month(@strDateRequired) and
RM_NotInUse=0
ORDER BY BD_RoomRef, BD_DateRequired asc
END
GO

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 06:22:40

New problem discussed in this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59019

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources