locked
FAQ: How do I make a parameterized query in the database with VB.NET?

    Question

  • How do I make a parameterized query in the database with VB.NET?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Saturday, April 11, 2009 6:18 PM

Answers

  • Code sample: Parameterized query in SQL Server database

    Thread: http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/bcf9011f-43e5-454b-8c03-4f2a6be250ac/


    Imports System.Data.SqlClient
    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, _
                            ByVal e As System.EventArgs) Handles Button1.Click
            Dim con As SqlConnection = New SqlConnection( _
                            "Data Source=.;Integrated Security=True;AttachDbFilename=D:\myDB.mdf")
            con.Open()
            Dim cmdText As String = _
                            "INSERT INTO Customer(UserName, [Password]) VALUES (@UserName,@Password)"
            Dim cmd As SqlCommand = New SqlCommand(cmdText, con)
            With cmd.Parameters
                .Add(New SqlParameter("@UserName", txtUserName.Text))
                .Add(New SqlParameter("@Password", txtPassword.Text))
            End With
            cmd.ExecuteNonQuery()
            con.Close()
            con = Nothing
        End Sub
    End Class
    
    

     

    Code sample: Parameterized query in MS Access database

    Thread: http://social.msdn.microsoft.com/forums/en/vbgeneral/thread/6ba7ec3d-fe34-44c7-8cdf-28984080fa17/

     

    Imports System.Data.OleDb
    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, _
                            ByVal e As System.EventArgs) Handles Button1.Click
            Dim con As OleDbConnection = New OleDbConnection( _
                            "Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
            con.Open()
            Dim cmdText As String = "INSERT INTO Customer(UserName, [Password]) VALUES (?,?)"
            Dim cmd As OleDbCommand = New OleDbCommand(cmdText, con)
            cmd.CommandType = CommandType.Text ‘ The default is CommandType.Text
            With cmd.Parameters
                .Add("@p1", OleDbType.VarChar).Value = txtUserName.Text
                .Add("@p2", OleDbType.VarChar).Value = txtPassword.Text
            End With
            cmd.ExecuteNonQuery()
            con.Close()
            con = Nothing
        End Sub
    End Class
    
    

     

    Note:

    http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx

     

    The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

    SELECT * FROM Customers WHERE CustomerID = ?

    Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

     

    KB: How to call a Parameterized Stored Procedure by Using ADO.NET

    http://support.microsoft.com/kb/310070

    For more FAQ about Visual Basic .NET General, please see Visual Basic .NET General FAQ


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Saturday, April 11, 2009 6:21 PM