Solved

Getting error trying to run stored proceedure

Posted on 2012-04-04
7
261 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
  • 5
7 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 37808224
0
 
LVL 21

Expert Comment

by:oleggold
ID: 37808226
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now