adodb

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

newbe to vs.net so please be gentle.
First question is what is the best method to access sql2K database, ADODB or
system.data.SqlClient.
Next
Using ADODB I can pass parameters to SQL stored procedures but have a
problem returning parameters for example:
..parameters(1).value="someparam1"
..parameters(2).value="someparam2"
..parameters(3).value="someparam3"
..execute
somevar = .parameters(4).value (this bieng the return param)
The return value is always null unless I first call another property of the
parameter e.g
szName = .parameters(4).name
somevar = .parameters(4).value (this will now succed)

Thanks in advance if anyone can advice on this.
 
Hi,

1. Use SQLClient

2. To access Output or Return values from sp's, add the parameter to the
parameters collection of the Command object. Then, specify the parameter
direction. You will then be able to read the value. Take a look at the
following code example:

(You can also call the ExecuteNonQuery method instead of opening a
SQLDataReader)

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

Dim sampleCMD As SqlCommand = New SqlCommand("SampleProc", nwindConn)
sampleCMD.CommandType = CommandType.StoredProcedure

Dim sampParm As SqlParameter = sampleCMD.Parameters.Add("RETURN_VALUE",
SqlDbType.Int)
sampParm.Direction = ParameterDirection.ReturnValue

sampParm = sampleCMD.Parameters.Add("@InputParm", SqlDbType.NVarChar, 12)
sampParm.Value = "Sample Value"

sampParm = sampleCMD.Parameters.Add("@OutputParm", SqlDbType.NVarChar, 28)
sampParm.Direction = ParameterDirection.Output

nwindConn.Open()

Dim sampReader As SqlDataReader = sampleCMD.ExecuteReader()

Console.WriteLine("{0}, {1}", sampReader.GetName(0), sampReader.GetName(1))

Do While sampReader.Read()
Console.WriteLine("{0}, {1}", sampReader.GetInt32(0),
sampReader.GetString(1))
Loop

sampReader.Close()
nwindConn.Close()

Console.WriteLine(" @OutputParm: {0}",
sampleCMD.Parameters("@OutputParm").Value)
Console.WriteLine("RETURN_VALUE: {0}",
sampleCMD.Parameters("RETURN_VALUE").Value)

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

Let me know if you need any further information.

-Prateek


newbe to vs.net so please be gentle.
First question is what is the best method to access sql2K database, ADODB or
system.data.SqlClient.
Next
Using ADODB I can pass parameters to SQL stored procedures but have a
problem returning parameters for example:
..parameters(1).value="someparam1"
..parameters(2).value="someparam2"
..parameters(3).value="someparam3"
..execute
somevar = .parameters(4).value (this bieng the return param)
The return value is always null unless I first call another property of the
parameter e.g
szName = .parameters(4).name
somevar = .parameters(4).value (this will now succed)

Thanks in advance if anyone can advice on this.
 
See my article on handling parameters in ADO.NET
http://www.betav.com/msdn_magazine.htm.
hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top