Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
|
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 1st, 2007, 06:57 PM
Authorized User
 
Join Date: Feb 2007
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default Attempting to Insert Value into DB Query

So, I am trying to insert a value into an SQL query using the following code:
Dim VarLetter As Data.SqlClient.SqlParameter
        Dim MyConnection As Data.SqlClient.SqlConnection
        Dim InitialCommand As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand( _
        "select pkclient,ClientName,Active,City, State from client Where ClientName Like '@Letter%'")
        Dim MyReader As Data.SqlClient.SqlDataReader
        MyConnection = New Data.SqlClient.SqlConnection
        MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("RASConnect ionString").ConnectionString
        InitialCommand.Connection = MyConnection
        InitialCommand.CommandType = Data.CommandType.Text

        VarLetter = New Data.SqlClient.SqlParameter
        VarLetter.ParameterName = "@Letter"
        VarLetter.SqlDbType = Data.SqlDbType.VarChar
        VarLetter.Size = 1
        VarLetter.Direction = Data.ParameterDirection.Input
        VarLetter.Value = "A"
        InitialCommand.Parameters.Add(VarLetter)

        InitialCommand.Connection.Open()
        MyReader = InitialCommand.ExecuteReader
        gvClients.DataSource = MyReader
        gvClients.DataBind()
        InitialCommand.Dispose()
        MyConnection.Dispose()

The query still runs but no results are returned. This works fine when I hard code the last part of the query as Like 'A%'.

Even when I set the VarLetter.Value = "'A%'" it doesn't work

Anyone have any ideas on why this won't work?
 
Old April 1st, 2007, 08:54 PM
Authorized User
 
Join Date: Feb 2007
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, I didn't exactly find the way to make that work but I did figure out a workaround.

Instead of having the parameter in the SQL Command and then assigning a value I am building the SQL command and including my variable. Such as this:

"select pkclient,ClientName,Active,City, State from client Where ClientName Like '" + Letter + "%'"

I believe this opens me up for an SQL Injection attack but I can't find any other option.

 
Old April 2nd, 2007, 08:17 AM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

First, the problem you are having is that you put the variable name in a string literal. So the SQL was actually trying to find the literal string "@Letter%". You need to omit the single quotes around the variable name. Of course, then you can't put the % wildcard in the query, therefore...

I'd recommend sticking with the SqlCommand object instead of building the SQL dynamically. You should be able to just add a "%" to the end of the value entered to allow the wildcard.

Dim InitialCommand As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand( _
       "select pkclient,ClientName,Active,City, State from client Where ClientName Like @Letter")

VarLetter.Value = String.Format("{0}%", "A")


-Peter





Similar Threads
Thread Thread Starter Forum Replies Last Post
Create Database Error when Attempting SQL Security wirerider ASP.NET 2.0 Professional 1 March 6th, 2007 07:19 PM
I solved insert query.now see this Update Query. [email protected] VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
Error when attempting to add New Topic rudydotnet Classic ASP Databases 1 August 22nd, 2005 01:55 PM
insert into db keyvanjan Classic ASP Databases 1 May 9th, 2005 11:50 PM
can't insert in db darkhalf Classic ASP Databases 11 January 12th, 2005 06:48 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.