INSERT INTO.. A Stored procedure!

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

General SQL Server Forums

New to SQL Server Programming

INSERT INTO.. A Stored procedure!

Author

Topic

magnetica
Starting Member

36 Posts

Posted - 2006-08-30 : 19:48:56

I have created a stored procedure which simply inserts two records into a table. Here is my stored procedure:-
//BEGIN
ALTER PROCEDURE [dbo].[pendingcol]
@cuser varchar(100)
AS
Declare @sqls nvarchar(1000)

SELECT @sqls = 'INSERT INTO' + @cuser + '(UserName, Pending) VALUES ("recordone", "recordtwo")'

EXECUTE sp_executesql @sqls

RETURN
//END

This is the code i am using to call my stored procedure using VB.NET:-
//BEGIN
'variables
Dim user As String
user = Profile.UserName

'connection settings
Dim cs As String
cs = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\friends.mdf;Integrated Security=True;User Instance=True"
Dim scn As New SqlConnection(cs)

'parameters
Dim cmd As New SqlCommand("pendingcol", scn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@cuser", SqlDbType.VarChar, 1000)
cmd.Parameters("@cuser").Value = user

'execute
scn.Open()

cmd.ExecuteNonQuery()

scn.Close()
//END

Now when i execute this code i get an error point to cmd.ExecuteNonQuery() that says
" The name "recordone" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. "

As far as i can see theres nothing wrong with the VB code, im guessing that the problem lies somewhere in my stored proc!

Can anyone please enlighten me on where i may be going wrong?
Cheers

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-30 : 20:02:56

There are several problem with your dynamic sql in the stored procedure.


SELECT @sqls = 
	'INSERT INTO' + -- >> No spaces before or after tablename
	@cuser + 
	'(UserName, Pending) VALUES ("recordone", "recordtwo")'
-- Values should be in single quotes, not double quotes.



-- Change to

SELECT @sqls = 
	'INSERT INTO ' +
	@cuser + 
	' (UserName, Pending) VALUES (''recordon'',''recordtwo'')'

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-30 : 20:04:12

Are you trying to insert two values in your table named recordone and recordtwo? Or is there a different meaning to recordone and recordtwo?

Tara Kizer

magnetica
Starting Member

36 Posts

Posted - 2006-08-30 : 20:15:05

Michael Valentine Jones

quote:
SELECT @sqls =
'INSERT INTO' + -- >> No spaces before or after tablename
@cuser +
'(UserName, Pending) VALUES ("recordone", "recordtwo")'
-- Values should be in single quotes, not double quotes.

Yea i realised that after i posted but still get the same error..

tkizer

quote:
Are you trying to insert two values in your table named recordone and recordtwo? Or is there a different meaning to recordone and recordtwo?

recordone and recordtwo are simply test data i am trying to insert into the table.. Is this not good? Because the error said something about recordone not being permitted. Also that column names are not permitted.
Although recordone and recordtwo are not the names of column's in my table..

The error is shown in the first post!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-30 : 20:25:11

The best way to troubleshoot dynamic SQL is to print out your statement. Debug the code in Query Analyzer like this:

DECLARE @SQL nvarchar(4000), @cuser nvarchar(256)

SET @cuser = 'SomeTable'

SELECT @SQL = 'INSERT INTO ' + @cuser + '(UserName, Pending) VALUES ("recordone", "recordtwo")'

PRINT @SQL

You can then see where the problem is.

Here is what you want:
SELECT @SQL = 'INSERT INTO ' + @cuser + '(UserName, Pending) VALUES (''recordone'', ''recordtwo'')'

Notice that I am using single quotes around the data and not double quotes. To use double quotes, you'd have to use the QUOTED_IDENTIFIER option.

Tara Kizer

magnetica
Starting Member

36 Posts

Posted - 2006-08-30 : 20:51:47

quote:
Here is what you want:
SELECT @SQL = 'INSERT INTO ' + @cuser + '(UserName, Pending) VALUES (''recordone'', ''recordtwo'')'

This is what i had before!

magnetica
Starting Member

36 Posts

Posted - 2006-08-30 : 20:57:21

Also i changed my command to this which looks fine.. (i think)

quote:
SELECT @sqls =
'INSERT INTO ' +
@cuser +
' (UserName, Pending) VALUES (''recordon'',''recordtwo'')'

But when i execute this i get an error that says:-

ERROR
" String or binary data would be truncated.
The statement has been terminated. "

Any ideas why? Doesnt this mean that it will be deleting??

magnetica
Starting Member

36 Posts

Posted - 2006-08-30 : 21:02:06

Oh yea and by the way there is already data in one of the fields i am trying to INSERT into..
does this matter?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-30 : 21:36:07

quote:
Originally posted by magnetica
...when i execute this i get an error that says:-

ERROR
" String or binary data would be truncated.
The statement has been terminated. "

Any ideas why? Doesnt this mean that it will be deleting??...

It means that one of the columns you are trying to insert data into is not long enough to hold the string you are trying to insert.

CODO ERGO SUM

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-31 : 03:04:59

String or binary data would be truncated. The statement has been terminated.

This means as MVJ says that the datatype is too narrow for your records. Go to Enterprise Manager, open up the table that you try to insert into and increase the length of each column to 50 or something.

Oh yea and by the way there is already data in one of the fields i am trying to INSERT into..

This doesn't matter, it will only create a new record (a new line with data). If you want to update any existing data yyou have in your table you need to use the UPDATE-statement:

UPDATE mytablename SET UserName = 'recordthree', Pending = 'recordfour' WHERE myIDcolumn = [something]

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-31 : 10:35:20

'INSERT INTO' + @cuser + '(UserName, Pending) VALUES ("recordone", "recordtwo")'

From reading just this, it looks like you have one table per user. i shudder at seeing the actual db design.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources