[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Parametrized SQL Statement

Posted on 2004-08-31
2
Medium Priority
?
189 Views
Last Modified: 2010-04-23
In the following code
on the following line

Private cmdGrd As SqlCommand = New SqlCommand(sqlGrd, sConnectionString)
I am getting the System.InvalidCastException error

-----------------------------------------------


Imports System.Data
Imports System.Data.SqlClient

Public Class ParameterizedQuery
    Inherits System.Windows.Forms.Form

    Private sConnectionString = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")

    Private sqlGrd As String = "SELECT * from ao_assets WHERE plant_cd = @PlantCd"
    Private cmdGrd As SqlCommand = New SqlCommand(sqlGrd, sConnectionString)
    Private daGrd As New SqlDataAdapter(cmdGrd)
    Private dsGrd As New DataSet

    Private sqlUpdate As String = "UPDATE ao_assetx_cost set asset_cost = 50 WHERE " & _
                      "  asset_name = @name"

    Private conOutage As New SqlConnection(sConnectionString)
    Private cmdUpdate As New SqlCommand(sqlUpdate, conOutage)

    Private sqlInsert As String = "INSERT INTO ao_assetx_cost(asset_cost_id, asset_name, " & _
                               "  asset_cost ) VALUES (@assCostId, @assName, @assCost ) "

    Private cmdInsert As New SqlCommand(sqlInsert, conOutage)

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    Friend WithEvents btnUpdate As System.Windows.Forms.Button
    Friend WithEvents btnInsert As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.Button1 = New System.Windows.Forms.Button
        Me.TextBox1 = New System.Windows.Forms.TextBox
        Me.btnUpdate = New System.Windows.Forms.Button
        Me.btnInsert = New System.Windows.Forms.Button
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(48, 64)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(560, 160)
        Me.DataGrid1.TabIndex = 0
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(288, 24)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 1
        Me.Button1.Text = "Button1"
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(104, 24)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.Size = New System.Drawing.Size(168, 20)
        Me.TextBox1.TabIndex = 2
        Me.TextBox1.Text = "TextBox1"
        '
        'btnUpdate
        '
        Me.btnUpdate.Location = New System.Drawing.Point(376, 24)
        Me.btnUpdate.Name = "btnUpdate"
        Me.btnUpdate.TabIndex = 3
        Me.btnUpdate.Text = "Update"
        '
        'btnInsert
        '
        Me.btnInsert.Location = New System.Drawing.Point(464, 24)
        Me.btnInsert.Name = "btnInsert"
        Me.btnInsert.TabIndex = 4
        Me.btnInsert.Text = "Insert"
        '
        'ParameterizedQuery
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(752, 266)
        Me.Controls.Add(Me.btnInsert)
        Me.Controls.Add(Me.btnUpdate)
        Me.Controls.Add(Me.TextBox1)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.DataGrid1)
        Me.Name = "ParameterizedQuery"
        Me.Text = "ParameterizedQuery"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Try
            conOutage.Open()
            cmdGrd.Parameters.Add("@PlantCd", "ALF")
            dsGrd.Clear()
            daGrd.Fill(dsGrd)

        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try

        conOutage.Close()
       
        DataGrid1.SuspendLayout()
        DataGrid1.DataSource = dsGrd
        DataGrid1.DataMember = dsGrd.Tables(0).ToString
        DataGrid1.ResumeLayout()
    End Sub

    Private Sub ParameterizedQuery_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
       
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Try
            conOutage.Open()
            cmdUpdate.Parameters.Add("@name", "ALF-01")

            cmdUpdate.ExecuteNonQuery()
            MsgBox("Updated")
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try

        conOutage.Close()
    End Sub

    Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        Try
            conOutage.Open()

            cmdInsert.Parameters.Add("@assCostId", "8")
            cmdInsert.Parameters.Add("@assName", "Joyce")
            cmdInsert.Parameters.Add("@assCost", "10")
            cmdInsert.ExecuteNonQuery()

            MsgBox("Inserted")
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try

        conOutage.Close()
    End Sub
End Class
0
Comment
Question by:jra2002
[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
2 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 80 total points
ID: 11942234
Private sConnectionString = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")

Private connGrd As SqlConnection = New SqlConnection(sConnectionString)
Private sqlGrd As String = "SELECT * from ao_assets WHERE plant_cd = @PlantCd"
Private cmdGrd As SqlCommand = New SqlCommand(sqlGrd, connGrd)

Bob


0
 

Author Comment

by:jra2002
ID: 11942340
Dear Bob,
Thanks. It works great. Could you please let me know what I am trying to do with paramertized query - SELECT, INSERT, Update is a write approch. Or if you have any suggestions please let me know.

Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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