Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Getting error trying to run stored proceedure

Posted on 2012-04-04
7
Medium Priority
?
269 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: 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Screencast - Getting to Know the Pipeline
Suggested Courses

885 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