?
Solved

Parametrized SQL Statement

Posted on 2004-08-31
2
Medium Priority
?
190 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
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

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!

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Integration Management Part 2

839 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