Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Getting error trying to run stored proceedure

Posted on 2012-04-04
7
Medium Priority
?
268 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 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