invalid column in passing parameters into a stored procedure 1

[bernardsylunar]

hi everyone,



i've created a stored procedure:


CREATE PROCEDURE GetAllRecords
@Tbl nvarchar(256), @Fld nvarchar(256), @FldValue nvarchar(256)
AS
declare @tsql varchar(100)
set @tsql='SELECT * FROM ' + @Tbl + ' where ' + @Fld + ' = ' + @FldValue
exec ( @tsql )
GO


where
@Tbl is for table name
@Fld is a field of the table
@FldValue is the value of the field

and call it from my VB module with this code:


Set Cn = New ADODB.Connection
Set sRecordset = New ADODB.Recordset
Set Cmd = New ADODB.Command

With Cmd
.ActiveConnection = Cn
.CommandType = adCmdStoredProc
.CommandText = "GetAllRecords"
.Parameters.Append .CreateParameter("@Tbl", adVarChar, adParamInput, 9, "memo_info")
.Parameters.Append .CreateParameter("@Fld", adVarChar, adParamInput, 10, "email_from")
.Parameters.Append .CreateParameter("@FldValue", adVarChar, adParamInput, 7, "bslunar")
Set Rs = .Execute
End With


when i run my application, i got this error 'Invalid column name 'bslunar'

do i miss something in creating my store procedure or in calling it from my VB code?

thanks



ynad
----
It is foolish to listen to someone who will not listen to you.

 

[fredericofonseca]

as you are trying to insert a char value you need to surround it with single quotes, otherwise it is considered to be a variable.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[bernardsylunar]

thanks for the reply frederico

>as you are trying to insert a char value

correct me if i'm wrong but what i understand on you reply is i'm inserting a value to a table.

i'm not inserting any char, i just querying records from a certain table(@Tbl) "memo_info", where field(@Fld) "email_from" is equal to a value(@FldValue) "bslunar"

i hope you'll understand my explanation.

but if what you mean in your reply is different from what i understand, just let me know..

btw, thank you very much for the reply.

ynad
----
It is foolish to listen to someone who will not listen to you.

 

[fredericofonseca]

Sorry not insert, query, but same rule applies.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[gmmastros]

I think the problem is with the dynamic sql. You need extra apostrophes around the FldValue in the where clause.

CREATE PROCEDURE GetAllRecords
  @Tbl nvarchar(256), @Fld nvarchar(256), @FldValue nvarchar(256)
AS
  declare @tsql varchar(100)
  set @tsql='SELECT * FROM ' + @Tbl + ' where ' + @Fld + ' = ''' + @FldValue + ''
  exec ( @tsql )

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bernardsylunar]

george, i tried your code but it gives me an error


[COLOR== red]Server: Msg 105, Level 15, State 1, Line 1,[/color]
Unclosed quotation mark before the character string 'bslunar'.
[COLOR== red]Server: Msg 170, Level 15, State 1, Line 1[/color]
Line 1: Incorrect syntax near 'bslunar'.
Stored Procedure: memo.dbo.GetAllRecordsByEmailFrom
Return Code = 0


ynad
----
It is foolish to listen to someone who will not listen to you.

 

[bernardsylunar]

sorry for my mistake in posting my reply
----

george, i tried your code but it gives me an error


Server: Msg 105, Level 15, State 1, Line 1,
Unclosed quotation mark before the character string 'bslunar'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'bslunar'.
Stored Procedure: memo.dbo.GetAllRecordsByEmailFrom
Return Code = 0


i've tried experimenting with my single quotes on my stored procedure but i still got errors just like before


thanks again...


ynad
----
It is foolish to listen to someone who will not listen to you.

 

[gmmastros]

Sorry, my mistake. I created this SP in one of my databases, and it works. My post had an apostrophe problem.

CREATE PROCEDURE GetAllRecords
  @Tbl nvarchar(256), @Fld nvarchar(256), @FldValue nvarchar(256)
AS
  declare @tsql varchar(100)
  set @tsql='SELECT * FROM ' + @Tbl + ' where ' + @Fld + ' = ''' + @FldValue + ''''
  exec ( @tsql )

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bernardsylunar]

THANK YOU VERY VERY MUCH GEORGE

i think you deserve a star.

thanks for the help and your time.

ynad
----
It is foolish to listen to someone who will not listen to you.

 

[gmmastros]

No problem. I'm glad it works for you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom