Solved

Parametrized SQL Statement

Posted on 2004-08-31
2
184 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 20 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

773 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