VB.Net Stored Procedures

Discussion in 'ASP .Net' started by =?Utf-8?B?SHV0dHk=?=, Jan 24, 2006.

  1. I'm trying to call a stored procedure from vb.net that's on the sql server. I
    would like to have the output on another form or a listbox where I can print
    results. I'm not getting any errors, but i'm not getting any output either.
    Any help appreciated. Thanks.

    'Stored Procedure = COMPARE, Output = comp_out. COL001 is returned 'field
    from SP.

    Dim myCommand As New SqlCommand
    Dim myParameter As SqlParameter

    Dim myConnection = New SqlConnection("Integrated Security=SSPI;Packet
    Size=4096;Data Source=TEST\MYSQL;Initial Catalog=hutty;Persist Security
    Info=False;Workstation ID=TEST")

    myCommand.Connection = myConnection
    myCommand.CommandText = "COMPARE"
    myCommand.CommandType = CommandType.StoredProcedure
    myParameter = myCommand.CreateParameter()
    myParameter.ParameterName = "@comp_out"
    myParameter.Direction = ParameterDirection.Output
    myParameter.SqlDbType = SqlDbType.VarChar
    myParameter.Size = 50
    myCommand.Parameters.Add(myParameter)



    myCommand.Connection.Open()
    myCommand.ExecuteNonQuery()

    Span1.Text =
    Convert.ToString((myCommand.Parameters("@comp_out").Value.ToString()))

    ListBox1.Items.Add(myCommand.Parameters("@comp_out").Value.ToString)

    --
    Hutty
     
    =?Utf-8?B?SHV0dHk=?=, Jan 24, 2006
    #1
    1. Advertising

  2. Have you check first that your store procedure itseld is working fine from
    server side?


    "Hutty" wrote:

    > I'm trying to call a stored procedure from vb.net that's on the sql server. I
    > would like to have the output on another form or a listbox where I can print
    > results. I'm not getting any errors, but i'm not getting any output either.
    > Any help appreciated. Thanks.
    >
    > 'Stored Procedure = COMPARE, Output = comp_out. COL001 is returned 'field
    > from SP.
    >
    > Dim myCommand As New SqlCommand
    > Dim myParameter As SqlParameter
    >
    > Dim myConnection = New SqlConnection("Integrated Security=SSPI;Packet
    > Size=4096;Data Source=TEST\MYSQL;Initial Catalog=hutty;Persist Security
    > Info=False;Workstation ID=TEST")
    >
    > myCommand.Connection = myConnection
    > myCommand.CommandText = "COMPARE"
    > myCommand.CommandType = CommandType.StoredProcedure
    > myParameter = myCommand.CreateParameter()
    > myParameter.ParameterName = "@comp_out"
    > myParameter.Direction = ParameterDirection.Output
    > myParameter.SqlDbType = SqlDbType.VarChar
    > myParameter.Size = 50
    > myCommand.Parameters.Add(myParameter)
    >
    >
    >
    > myCommand.Connection.Open()
    > myCommand.ExecuteNonQuery()
    >
    > Span1.Text =
    > Convert.ToString((myCommand.Parameters("@comp_out").Value.ToString()))
    >
    > ListBox1.Items.Add(myCommand.Parameters("@comp_out").Value.ToString)
    >
    > --
    > Hutty
     
    =?Utf-8?B?c2VyZ2UgY2FsZGVyYXJh?=, Jan 24, 2006
    #2
    1. Advertising

  3. Yes... I checked the SP through query analyzer. I did make a modification
    after this post. The parameter name "comp_out" I deleted from the SP. It
    wasn't needed. This is my SP that I'm trying to get the output in VB:

    CREATE PROCEDURE [DBO].[COMPARE]
    AS
    SELECT MIN(TableName) as TableName, COL001
    FROM

    (
    SELECT '[FILE1]' as TableName, Col001
    FROM [FILE1]
    UNION ALL
    SELECT '[FILE2]' as TableName, Col001
    FROM [FILE2]
    ) tmp
    GROUP BY COL001
    HAVING COUNT(*) = 1
    ORDER BY COL001
    GO


    --
    Hutty


    "serge calderara" wrote:

    > Have you check first that your store procedure itseld is working fine from
    > server side?
    >
    >
    > "Hutty" wrote:
    >
    > > I'm trying to call a stored procedure from vb.net that's on the sql server. I
    > > would like to have the output on another form or a listbox where I can print
    > > results. I'm not getting any errors, but i'm not getting any output either.
    > > Any help appreciated. Thanks.
    > >
    > > 'Stored Procedure = COMPARE, Output = comp_out. COL001 is returned 'field
    > > from SP.
    > >
    > > Dim myCommand As New SqlCommand
    > > Dim myParameter As SqlParameter
    > >
    > > Dim myConnection = New SqlConnection("Integrated Security=SSPI;Packet
    > > Size=4096;Data Source=TEST\MYSQL;Initial Catalog=hutty;Persist Security
    > > Info=False;Workstation ID=TEST")
    > >
    > > myCommand.Connection = myConnection
    > > myCommand.CommandText = "COMPARE"
    > > myCommand.CommandType = CommandType.StoredProcedure
    > > myParameter = myCommand.CreateParameter()
    > > myParameter.ParameterName = "@comp_out"
    > > myParameter.Direction = ParameterDirection.Output
    > > myParameter.SqlDbType = SqlDbType.VarChar
    > > myParameter.Size = 50
    > > myCommand.Parameters.Add(myParameter)
    > >
    > >
    > >
    > > myCommand.Connection.Open()
    > > myCommand.ExecuteNonQuery()
    > >
    > > Span1.Text =
    > > Convert.ToString((myCommand.Parameters("@comp_out").Value.ToString()))
    > >
    > > ListBox1.Items.Add(myCommand.Parameters("@comp_out").Value.ToString)
    > >
    > > --
    > > Hutty
     
    =?Utf-8?B?SHV0dHk=?=, Jan 24, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Taras
    Replies:
    2
    Views:
    4,814
    Rick Spiewak
    Oct 5, 2003
  2. Max
    Replies:
    5
    Views:
    382
  3. Elliot M. Rodriguez, MCSD

    Re: crystal reports and asp.net stored procedures

    Elliot M. Rodriguez, MCSD, May 21, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    478
    Elliot M. Rodriguez, MCSD
    May 21, 2004
  4. msnews.microsoft.com

    How to view #temp tables in vs.net (stored procedures)

    msnews.microsoft.com, Sep 10, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    364
    Girish bharadwaj
    Sep 11, 2004
  5. .Net Sports
    Replies:
    3
    Views:
    609
    Robbe Morris [C# MVP]
    May 12, 2005
Loading...

Share This Page