Solved

Parametrized SQL Statement

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now