setting parameter value in run time

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

SQL Server Development (2000)

setting parameter value in run time

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-26 : 08:34:20

baskar writes "i have a problem.
i developed a project in vb with ms access as database.
Now i'm plannning to move to sql server...
But i have problem.
How to i write a sql query in sql server..

select * from Emp where empname=?
the above query working well in access...
but its not working in sql server...
please help me soon..

thanks and regards,
Baskar"

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-26 : 08:47:14

quote:

baskar writes "i have a problem.
i developed a project in vb with ms access as database.
Now i'm plannning to move to sql server...
But i have problem.
How to i write a sql query in sql server..

select * from Emp where empname=?
the above query working well in access...
but its not working in sql server...
please help me soon..

thanks and regards,
Baskar"

You would need to use a variable which you could pass to the SQL. Perhaps the best solution would be a stored procedure..

CREATE Procedure GetEmployeeDetails @empname varchar(50) as
select * from Emp where empname= @empname

You then call the procedure with GetEmployeeDetails 'john smith'

for employee John Smith. You can probably find out how to pass parameters to procedures in VB from lots of places more knowlegable about it than me.

-------
Moo.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-26 : 09:15:18

You'll need to use ADO and Command objects to call your sp's.

Here is a small example of an call to an sp.

Dim objConn As ADODB.Connection
Dim objComm As ADODB.Command
Dim objRs As ADODB.Recordset
Dim strConnectString As String

Set objConn = New ADODB.Connection
Set objComm = New ADODB.Command
Set objRs = New ADODB.Recordset

strConnectString = "Your connect string goes here"
objConn.Open strConnectString

objComm.CommandType = adCmdStoredProc
objComm.ActiveConnection = objConn
objComm.CommandText = "GetEmployeeDetails"

objCommand.Parameters.Append objCommand.CreateParameter ("@empname",adWChar,adParamInput,50,"john smith")

objRs.Open objComm, , adOpenStatic, adLockOptimistic

'Process your recordset here

objConn.Close

Set objRs = Nothing
Set objComm = Nothing
Set objConn = Nothing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources