How to use ADO command object to get a record set via stored procedure?

  • Thread starter Thread starter Cylix
  • Start date Start date
C

Cylix

As the title,
I have a simple stored procedure just input a index and return a row,
how can I get the return record by in my VB.Net application using ADODB
Object 2.5?
 
here a simple ADO.Net example

Public Function GetSomeData(ByVal Id As Decimal) As DataTable
GetSomeData = New DataTable
Using con As New SqlConnection(My.Settings.MyConnectionString)
Using cmd As New SqlCommand("Myproc", con)
cmd.CommandType = CommandType.StoredProcedure
Dim par As SqlParameter = cmd.Parameters.Add("@ParA1",
SqlDbType.Decimal)
par.Value = Id
Dim da As New SqlDataAdapter(cmd)
da.Fill(GetSomeData)
End Using
End Using
End Function

regards

Michel Posseth [MCP]
 
Thanks M. Posseth.

I going to try in ADO.NET now,
one more simple question, can you tell me the connection string in
ADO.NET

example data here:

DB: sqlserver2000
location: 10.0.0.123
UID: USERNAME
PID: password
Database name: MYDB

Please advise.
 
The connection string would be :

For SQL Server Authentication, use this syntax specify a user name and
password, where asterisks represent a valid user name and password.


"Persist Security Info=False;User ID=*****;Password=*****;Initial
Catalog=AdventureWorks;Server=MySqlServer"


Use this syntax to connect using an IP address, where the network library is
Win32 Winsock TCP/IP and 1433 is the port being used (the default).

Network Library=dbmssocn;Data Source=000.000.000.000,1433;


SQL Server allows you to use the following network libraries when
establishing a connection.

dbnmpntw
Win32 Named Pipes

dbmssocn
Win32 Winsock TCP/IP

dbmsspxn
Win32 SPX/IPX

dbmsvinn
Win32 Banyan Vines

dbmsrpcn
Win32 Multi-Protocol (Windows RPC)

for copy paste examples see

http://www.connectionstrings.com/


regards

Michel Posseth [MCP]
 
Thanks Posseth ,

I have another problem during fill the datatable,
it returns system error in the error message,

Is it problem in my stored procedure?

My SP is really simply, just select a record(1 row) in a table by an
index, eg

CREATE PROCEDURE dbo.std(@uid char(12)) AS
Select studentName from student where studentID=@sid
GO
 
Back
Top