Replace save database query variable with value using C# and VB.Net in ASP.Net

Last Reply 4 months ago By alya14

Posted 4 months ago

hi ,

my scenario like below

the PLN is static value I am reading before call query

I stored the query like this
 
SELECT NAME FROM CONTACTS WHERE CODE='" + PLN + "'"
 
and I set to PLN variable in login screen, so if login users=a than PLN=ALY
 
after login I call to string query from DATABASE and watching by sql profiler, the sql profiler show to me
 
SELECT NAME FROM CONTACTS WHERE CODE='" + PLN + "'" 
 
instead of
 
SELECT NAME FROM CONTACTS WHERE CODE='ALY'

How can I do ?

Posted 4 months ago Modified on 4 months ago

Hi alya14,

Refer below sample.

HTML

<asp:Button Text="Save" runat="server" OnClick="Save" />
<br />
<asp:Label ID="lblQuery" runat="server" />

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.Data.SqlClient

Code

C#

protected void Save(object sender, EventArgs e)
{
    var query = "SELECT Query FROM StoreQuery";
    GetName(query);
    lblQuery.Text = GetName(query).Replace("\"", "").Replace("+", "").Replace("PLN", "aly");
}

private string GetName(string query)
{
    string name = "";
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            con.Open();
            name = cmd.ExecuteScalar().ToString();
            con.Close();
        }
    }
    return name;
}

VB.Net

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    Dim query = "SELECT Query FROM StoreQuery"
    GetName(query)
    lblQuery.Text = GetName(query).Replace("""", "").Replace("+", "").Replace("PLN", "aly")
End Sub

Private Function GetName(ByVal query As String) As String
    Dim name As String = ""
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            con.Open()
            name = cmd.ExecuteScalar().ToString()
            con.Close()
        End Using
    End Using
    Return name
End Function

Screenshot


Posted 4 months ago

I solved this issue using parameters like bellow

I stored like this WHERE CODE=@P1

and call

cmd.parameters.addwithvalue("@P1",PLN)

Thanks for your time.