SP INSERT *solved* - 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)

SP INSERT *solved*

Author

Topic

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-03-04 : 10:32:03

Don't understand what I am doing wrong... I am working on this messaging system and I am creating a stored procedure as follows:
CREATE PROCEDURE message_send
@uid int,
@rid int,
@tit varchar(50),
@bod text
AS
INSERT INTO messages (message_sender, message_reciever, message_title, message_body)
VALUES(@uid, @rid, @tit, @bod)
GO

The ASP code:
SQL="message_send " & user_id & " " & message_reciever & " " & message_title & " " & message_body

It gives me this error message:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Title'.

=====================================
Why not try and do the impossible?

Edited by - Swede on 03/04/2002 12:29:20

izaltsman
A custom title

1139 Posts

Posted - 2002-03-04 : 10:41:25

In your ASP code, try using commas instead of spaces to separate stored procedure parameters...

Nazim
A custom title

1408 Posts

Posted - 2002-03-04 : 10:43:25

Hi Sweede,

you missed the comma's (the comma's seperate each parameter) and single quote (for varchar).

SQL="message_send " & user_id & ", " & message_reciever & ", '" & message_title & " '," & message_body

HTH

--------------------------------------------------------------

Nazim
A custom title

1408 Posts

Posted - 2002-03-04 : 10:45:15

Argh! got Sniped

--------------------------------------------------------------

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-03-04 : 10:45:38

I am now using it as you entered it Nazim... Still, something is wrong... I guess the actualy value inserted affects it?

Get this errormsg...
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ','.
/devarea/messages/messages_reply_send.asp, line 22

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; T312461; Q312461)

Page:
POST 232 bytes to /devarea/messages/messages_reply_send.asp

POST Data:
message_title=RE%3ATitle&message_body=1111%0D%0A%0D%0A------------------%0D%0ATitle%3A+Title%0D%0AFrom%3A+Chris+Turner%0D%0AMessage+sent%3A+2%2F15%2F2002%2C+14%3A43%0D%0AMessage%3A%0D%0ABody&message_r . . .

=====================================
Why not try and do the impossible?

Nazim
A custom title

1408 Posts

Posted - 2002-03-04 : 10:53:34

Its not a good idea using Text field. if you can convert it into varchar(max is 8000 char).

if you continue to use text field. check for WRITETEXT and UPDATETEXT in BOL . this should get you going with your text field

SQL="message_send " & user_id & "," & message_reciever & ", '" & message_title & "',' " & message_body &"'"

HTH

--------------------------------------------------------------

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-03-04 : 11:00:31

Hmmm it's still not working... I even entered all values as 1 to prove it was not the values that is gefunkt...

Hmmm... Maybe I should try the varchar variant...

=====================================
Why not try and do the impossible?

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-03-04 : 11:03:53

Nope, it's not even working with varchar.......

=====================================
Why not try and do the impossible?

izaltsman
A custom title

1139 Posts

Posted - 2002-03-04 : 11:33:27

It would be helpful if you posted the rest of your ASP code... Also, add "EXEC " to your command string:

SQL="EXEC message_send " & user_id & ", " & message_reciever & ", '" & message_title & "', '" & message_body &"'"

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-03-04 : 11:39:39

HEre is the code... Why put EXEC in front by the way?

<%
message_id = Request.Form("message_id")
message_title = Request.Form("message_title")
message_body = Request.Form("message_body")
message_reciever = Request.Form("message_reciever")
user_id = Session("user_id")
If user_id = "" Then user_id = 0
if message_title = "" Then Response.Redirect("messages_reply.asp?message_id=") & message_id & "&message_read=1&msg=title"
if message_body = "" Then Response.Redirect("messages_reply.asp?message_id=") & message_id & "&message_read=1&msg=body"
message_title = Replace(message_title, Chr(34), "|||")
message_title = Replace(message_title, Chr(39), "|??|")
message_body = Replace(message_body, Chr(34), "|||")
message_body = Replace(message_body, Chr(39), "|??|")
%>

<%
Set RS=Server.CreateObject("ADODB.recordset")
SQL="message_send " & user_id & "," & message_reciever & ", '" & message_title & "',' " & message_body &"'"
RS.Open SQL, Conn
Set RS = Nothing
Conn.Close
Set Conn = Nothing
%>

=====================================
Why not try and do the impossible?

Teroman
Posting Yak Master

115 Posts

Posted - 2002-03-04 : 12:22:56

before the RS.Open line put in

Response.Write SQL
Response.End

copy/paste this into Query analyser to see if what you are actually executing is the same as what you think you are trying to execute, most of the silly problems I have can be spotted easily by doing this.

col

PS i mean copy and paste the SQL it spits out, not that response stuff, dont want any confusion ;-)

Edited by - teroman on 03/04/2002 12:23:55

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-03-04 : 12:28:40

I should have thought of that... that would have saved me this embarrasing thing hahaha...

Yes, there was of course a missing value in there... *sighs*

Thanks guys!

=====================================
Why not try and do the impossible?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources