Plesse help - I've tried everything -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)

Plesse help - I've tried everything -RESOLVED

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-11-03 : 10:56:22

This sql works fine

Dim strSQL1 As String = "Insert tblRB_FacilitiesDates(FD_FacilitiesDateRef, FD_DateRequired, FD_Item) " _
'& " select FA_Item + '*' + '" & Newdate & "', '" & Newdate & "', FA_ItemDesc from tblRB_Facilities"

Howver if I run this which changes the first value so the dateis first I get an error all the time in that the date stored in BD_DateRequired is 01/01/1900

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

I have spent ALL day on this and got nowhere...

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-11-03 : 11:09:52

1) put that in a stored procedure, and call that from your page. From your other post, i know you are working with .NET. Use the power that it has. Stored procs are your FRIENDS.

2) read the link in my signature, and follow the directions. you will get some REAL experts to look at the problem that way.

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)

Kristen
Test

22859 Posts

Posted - 2005-11-03 : 12:59:33

"I have spent ALL day on this and got nowhere"

I reckon you are making the job look hard!

As Don said you'd be better with Sprocs, but if you have never used them that would be a bit of a step up.

My advice would be to get the SQL working first in Query Analyser and THEN do the substitution stuff for your VB codes. Its really hard to work out where quotes might be missing, what error SQL might want to give you, typos, and so on when you start from the VB end.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-04 : 01:13:56

Duplicate post?
http://sqlteam.com/forums/topic.asp?TOPIC_ID=57278

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-11-04 : 04:01:13

No, it's not a duplicate as in that query I had the variable first concatenated with the date. I did look back at that. I now want it the other way round and am getting muddled up with the syntax. I have tried Query analyser, but in that how do I increment the date ? Here's my sp I have tried.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-04 : 04:18:00

>> but in that how do I increment the date ?

Refer this and post accordingly
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-11-04 : 06:06:04

Resolved

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources