?
Solved

Parametrized SQL Statement

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month14 days, 6 hours left to enroll

771 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