• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

VB.net - stored procudure execution error

I have a stored procedure as followed:

@id varchar(30),
@SQL varchar(max)
as
set @SQL = 'select id, name from employee where id = ' + @id + ''
exec(@sql)

Open in new window


The above stored procedure works fine if I run it inside of SQL.  However, I created a vb.net windows application that will allow the user to input in the ID via an interface.  I'm running into an issue with parameter @SQL.

Dim cmd as sqlcommand = New sqlcommand("EXEC EMP_INFO @ID, @SQL", con)
con.open()

Dim param1 as sqlparameter = cmd.parameters.add("@ID, sqldbtype.varchar, 30)
param1.Value = txtID.text

Dim param2 as sqlparameter = cmd.parameters.add("@SQL, sqldbtype.varchar, 8000)
param2.Value = "NULL"

Open in new window


Am I doing something wrong with my @SQL parameter?  The error I'm getting is "Object reference not set to an instance of an object.".  
0
holemania
Asked:
holemania
3 Solutions
 
Swapnil PipariaArchitectCommented:
Hi holemania,

Change SP to add null by default for SQL parameter

@id varchar(30),
@SQL varchar(max) = Null
as
set @SQL = 'select id, name from employee where id = ' + @id + ''
exec(@sql)

and in code if you want to pass @SQL as null then just avoid passing it, it will take null by default from the SP.


Dim cmd as sqlcommand = New sqlcommand("EMP_INFO", con)
con.open()

Dim param1 as sqlparameter = cmd.parameters.add("@ID", sqldbtype.varchar, 30)
param1.Value = txtID.text


Thanks,
netswap
0
 
Paul JacksonCommented:
looks like you are missing ending double quote round the parameter identifier but i suggest you try it this way :

cmd.parameters.add(New SqlParameter("@ID", sqldbtype.varchar, 30).Value = txtID.text
cmd.parameters.add(New SqlParameter("@SQL", sqldbtype.varchar, 8000).Value = DBNull.Value
0
 
käµfm³d 👽Commented:
A couple of things I see:

1

Dim cmd as sqlcommand = New sqlcommand("EXEC EMP_INFO @ID, @SQL", con)

Open in new window


Don't include the term "EXEC" or the parameters. You call a stored procedure by just submitting the name,  adding parameters to the Parameters collection of the Command object, and modifying the command type to be a stored procedure:

Dim cmd as sqlcommand = New sqlcommand("EMP_INFO", con)

cmd.CommandType = System.Data.CommandType.StoredProcedure

Dim param1 as sqlparameter = cmd.parameters.add("@ID, sqldbtype.varchar, 30)
param1.Value = txtID.text
...

Open in new window


2

Don't try to pass NULL as a string. Instead, use DBNull.Value when submitting NULL to the DB:

Dim param2 as sqlparameter = cmd.parameters.add("@SQL, sqldbtype.varchar, 8000)
param2.Value = DBNull.Value

Open in new window


3

Object reference not set to an instance of an object.

The only thing I see in the code you posted that *could* be the reason for this is the call:
con.open()

Open in new window


Did you initialize con before trying to call methods of it?

E.G.
Dim con As New SqlConnection()

Open in new window

0
 
käµfm³d 👽Commented:
P.S.

IIRC, you need to make sure the SP parameter placeholders (the @ strings) have the same name as the parameters in the SP. I don't believe case matters. It looks like you're doing this, but I just wanted to clarify  : )
0
 
holemaniaAuthor Commented:
Thanks guys.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now