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

10 Jul 2006 6:50 AM

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?

10 Jul 2006 9:02 AM

M. Posseth

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]

Show quoteHide quote

"Cylix" wrote:

> 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?
>
>

10 Jul 2006 9:47 AM

Cylix

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.

10 Jul 2006 11:11 AM

M. Posseth

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]

Show quoteHide quote

"Cylix" wrote:

> 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.
>
>

11 Jul 2006 1:35 AM

Cylix

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