help with syntax please.RESOLVED

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)

help with syntax please.RESOLVED

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-11-03 : 05:05:50

Can someone help me with the syntax on this please. It is the second line that is wrong and I want to insert three values into tblRB_Bookingdates. The correct format would appear as.

2FM1*03/11/2005
03/11/2005
2FM1

For each room in tblRB_Rooms I want to insert a record as above.

Dim strSQL As String = "Insert tblRB_BookingDates(BD_BookingDateRef, BD_DateRequired, BD_RoomRef) " _
& " select " & Newdate & "' + '*' + '" , & RM_RoomRef , & Newdate & "', RM_RoomRef from tblRB_Rooms"

Kristen
Test

22859 Posts

Posted - 2005-11-03 : 08:35:27

Maybe this?


Dim strSQL As String = "Insert tblRB_BookingDates(BD_BookingDateRef, BD_DateRequired, BD_RoomRef) " _
& " select '" & Newdate & "*" & RM_RoomRef & "', '" & Newdate & "', '" & RM_RoomRef & "'" from tblRB_Rooms"

Print strSQL out so you can see what it looks like, and try executing it in Query Analyser. Once you get it working in Q.A. retro-fit the changes into your VB code

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-11-03 : 09:20:01

from tblRB_Rooms" is highlighted in blue and says 'end of statement expected.

I got the following to work, BD_BookingDateref is fine and so is BD_RoomRef, but the default date 1/01/1900is being inserted in BD_DateRequired. This is Datetime in my sql table.

Dim strSQL As String = "INSERT INTO tblRB_BookingDates(BD_BookingDateRef, BD_DateRequired, BD_RoomRef) " _
& "SELECT '" & Newdate & "*' + RM_RoomRef," & Newdate & ", RM_RoomRef from tblRB_Rooms"

Kristen
Test

22859 Posts

Posted - 2005-11-03 : 12:47:18

"from tblRB_Rooms" is highlighted in blue and says 'end of statement expected."

Yeah, I got the last bit of the syntax wrong


& "'" from tblRB_Rooms"
should be
& "' from tblRB_Rooms"

"1/01/1900is being inserted in BD_DateRequired. This is Datetime in my sql table"

Probably your best bet is to present dates to SQL Server as strings in yyyymmdd format

so '19000101' would be good (note the single-quotes)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources