Solved

Getting error trying to run stored proceedure

Posted on 2012-04-04
7
267 Views
Last Modified: 2012-04-04
The error is Object variable or With block variable not set.
I get it in this code where the --> is
    Public Sub fillMembers()
        Dim GUIDS As String = Me.ddlUnfilteredList.SelectedValue.ToString
        
        Try

            Dim connString As String = "server=SERVER;initial catalog=DB;persist security info=False;user id=USER;password=PASS"
            Dim conn As New SqlConnection(connString)
            Dim SQLCmd = Nothing

            conn.Open()
            'SQLCmd.Parameters.AddWithValue("GUIDS")
            -->SQLCmd.CommandText = "exec jpi_interface.ts_get_email_group_assoc_list '" & GUIDS & "'" ' Stored Procedure to Call
            SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type
            SQLCmd.Connection = conn 'Active Connection
            Dim da As New SqlDataAdapter
            Dim ds As New DataSet
            da.Fill(ds, "Email_Group")
            lbBelongResults.DataSource = ds.Tables("Email_Group")
            Me.lbBelongResults.DataTextField = "full_nm"
            Me.lbBelongResults.DataValueField = "associateid"
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try

Open in new window

1. am I calling a store proceedure right and 2. what is causing the error and 3. if anything is incorrect can you show me the correct way......please
Thank you in advance
R3nder
0
Comment
Question by:r3nder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
7 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 37808224
0
 
LVL 21

Expert Comment

by:oleggold
ID: 37808227
Defining the connection string in the element in a .config file.

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
    <appSettings>
    <add key="ConnectionString"
    value="data source=SCI\Erewhon;initial
    catalog=Northwind;integrated security=SSPI;persist
    security info=True;workstation id=PTK800;packet size=4096" />
    </appSettings>
     
    </configuration>
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Expert Comment

by:oleggold
ID: 37808230
"Opening the Connection

ADO.NET uses a disconnected model. This doesn't mean we don't use a connection; what it does mean is that we don't hold the connection. We still need to declare and initialize a connection object to talk to our database. Listing 2 demonstrates how to read an external connection string from an App.config file and initialize a connection object.

Listing 2: Declaring and initializing a connection object.

    Dim Connection As SqlConnection = _
    New SqlConnection( _
    ConfigurationSettings.AppSettings( _
    "ConnectionString"))
"
0
 
LVL 21

Accepted Solution

by:
oleggold earned 500 total points
ID: 37808233
"isting 5: Invoking a stored procedure and displaying the results.

    1: Imports System.Data
    2: Imports System.Data.SqlClient
    3: Imports System.Configuration
    4:
    5: Public Class Form1
    6: Inherits System.Windows.Forms.Form
    7:
    8: [ Windows Form Designer generated code ]
    9:
    10: Private Sub Form1_Load(ByVal sender As Object, _
    11: ByVal e As System.EventArgs) Handles MyBase.Load
    12:
    13: NoParameter()
    14:
    15: End Sub
    16:
    17: Private Sub NoParameter()
    18:
    19: Dim Connection As SqlConnection = _
    20: New SqlConnection( _
    21: ConfigurationSettings.AppSettings( _
    22: "ConnectionString"))
    23:
    24: Dim Command As SqlCommand = _
    25: New SqlCommand()
    26: Command.Connection = Connection
    27: Command.CommandText = "Ten Most Expensive Products"
    28: Command.CommandType = CommandType.StoredProcedure
    29:
    30: Dim Adapter As SqlDataAdapter = _
    31: New SqlDataAdapter(Command)
    32:
    33: Dim DataSet As DataSet = _
    34: New DataSet(Command.CommandText)
    35:
    36: Adapter.Fill(DataSet)
    37: DataGrid1.DataSource = DataSet.Tables(0)
    38:
    39: End Sub
    40:
    41: End Class
"
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37808259
Your SQLCmd variable is not initialized.  Try changing line #8 from:
Dim SQLCmd = Nothing
To:
Dim SQLCmd = New SqlCommand()
I hope this helps.
0
 
LVL 6

Author Closing Comment

by:r3nder
ID: 37808840
Thank you......works now
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question