Solved

Need syntax for where clause in sql server using C#

Posted on 2004-08-14
13
226 Views
Last Modified: 2008-01-09
What is proper for the following select statement:
 myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus=[The IncidentStatus value I need to filter on is the string Open] ;

 I've tried these options:

 myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus=" + "Open";
as well as others that have not worked.

What is the correct select statement?
0
Comment
Question by:nhorsley
  • 7
  • 6
13 Comments
 
LVL 8

Expert Comment

by:daffodils
ID: 11800729
Try..

// Assuming string Open;
myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus=" + Open;
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11800744
I tried it and got, Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0103: The name 'Open' does not exist in the class or namespace 'ASP.updateAdmin_aspx'

Source Error:
Line 41:    
Line 42:    
Line 43:    myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus=" + Open;
Line 44:
Line 45:    
 
0
 
LVL 8

Expert Comment

by:daffodils
ID: 11800788
I had inferred Open is the name of the string variable that holds value for IncidentStatus...

How and where have you declared Open ?
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11800820
Open is one of the string values contained in the field named IncidentStatus
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11800822
This is the entire code that contains the Select statement:

<%@ Page Language="c#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">


    SqlConnection myConn = new SqlConnection ("server=(local);database=BCA;trusted_connection=true");
    SqlCommand myCmd = new SqlCommand ( );
    string id;
      protected string strUser;
   
    protected void Page_Load ( object src, EventArgs e ) {

Response.AppendHeader("Refresh", Convert.ToString((Session.Timeout * 60)) + @"; URL=default.aspx");

       
 myCmd.Connection = myConn;
        id = Request.QueryString [ "id" ] ;
        if ( !IsPostBack ) {
           if ( id == null ) {
              bindGrid ( );
              loadPanel.Visible = true;
              editPanel.Visible = false;
              postPanel.Visible = false;
           } else {
              getMessage ( Request.QueryString [ "id" ] );
              loadPanel.Visible = false;
              editPanel.Visible = true;
              postPanel.Visible = false;
           }
        } else {
           loadPanel.Visible = false;
           editPanel.Visible = false;
           postPanel.Visible = true;
        }
    }
   
    public void bindGrid ( )
      
      {
   
   
   myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus=" + 'Open';

   
        myConn.Open ( );
        myGrid.DataSource = myCmd.ExecuteReader ( CommandBehavior.CloseConnection );
        myGrid.DataBind ( );
    }
   
    public void getMessage ( String id ) {
        SqlDataAdapter myAdapter = new SqlDataAdapter (
           "select * from BCAIncident where IncidentID=" + id, myConn );
        DataTable msgDetails = new DataTable ( );
        myAdapter.Fill ( msgDetails );
        DataRowView myRow = msgDetails.DefaultView [ 0 ] ;
   
        msgDate.Value = myRow [ "IncidentID" ].ToString ( );
        msgFrom.Value = myRow [ "IncidentAuthorID" ].ToString ( );
        msgEmail.Value = myRow [ "IncidentEmail" ].ToString ( );
        msgDesc.Value = myRow [ "IncidentDesc" ].ToString ( );
        msgShrtDesc.Value = myRow [ "IncidentShrtDesc" ].ToString ( );
        msgStatus.Value = myRow [ "IncidentStatus" ].ToString ( );
        msgAction.Value = myRow [ "IncidentAction" ].ToString ( );
        msgCompleted.Value = myRow [ "IncidentCompleted" ].ToString ( );
        msgIncName.Value = myRow [ "IncidentName" ].ToString ( );
        msgIncOwner.Value = myRow [ "IncidentOwner" ].ToString ( );
        editPanel.DataBind ( );
    }
   
    public void updateMessage ( object src, EventArgs e ) {
        if ( Page.IsValid ) {
           myCmd.CommandText = "UPDATE BCAIncident SET IncidentAuthorID=@From, IncidentEmail=@Mail, IncidentDesc=@Desc,

IncidentShrtDesc=@ShrtDesc, IncidentStatus=@Status, IncidentAction=@Action, IncidentCompleted=@Completed,

IncidentName=@IncName, IncidentOwner=@IncOwner where IncidentID=" + id;
   
           myCmd.Parameters.Add ( "@From", SqlDbType.NVarChar ).Value = msgFrom.Value;
           myCmd.Parameters.Add ( "@Mail", SqlDbType.NVarChar ).Value = msgEmail.Value;
           myCmd.Parameters.Add ( "@Desc", SqlDbType.NVarChar ).Value = msgDesc.Value;
           myCmd.Parameters.Add ( "@ShrtDesc", SqlDbType.VarChar ).Value = msgShrtDesc.Value;
           myCmd.Parameters.Add ( "@Status", SqlDbType.NVarChar ).Value = msgStatus.Value;
           myCmd.Parameters.Add ( "@Action", SqlDbType.VarChar ).Value = msgAction.Value;
           myCmd.Parameters.Add ( "@Completed", SqlDbType.VarChar ).Value = msgCompleted.Value;
           myCmd.Parameters.Add ( "@IncName", SqlDbType.NVarChar ).Value = msgIncName.Value;
           myCmd.Parameters.Add ( "@IncOwner", SqlDbType.NVarChar ).Value = msgIncOwner.Value;
           myConn.Open ( );
           myCmd.ExecuteNonQuery ( );
           myConn.Close ( );
        }
        bindPostPanel ( id );
    }
   
    public void bindPostPanel ( String id ) {
        myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentID='" + id + "'";
        myConn.Open ( );
        listDetails.DataSource = myCmd.ExecuteReader ( CommandBehavior.SingleRow );
        listDetails.DataBind ( );
        myConn.Dispose ( );
    }

</script>
0
 
LVL 8

Expert Comment

by:daffodils
ID: 11800836
Okay.. got it.. "open" is the value not the name of the string

myCmd.CommandType = CommandType.Text

// Assuming "IncidentStatus" is of type varchar(15) in the database.
// If not.. change accordingly
SqlParameter myParam = new SqlParameter("@IncidentStatus", SqlDbType.NVarChar, 15)

myParam.Value = "Open";
myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus = @IncidentStatus";
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Expert Comment

by:daffodils
ID: 11800842
Hey.. You are using SqlParamater in your Update code...
Answer is as simple as using it for the Select statements as well.. and setting the ".Value" property to "Open"
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11800918
I've tried to implement it but it's still throwing erros.  If you would supply the solution in the code I provided earlier exactly as it should be entered, we would be done.  Thank you.
0
 
LVL 8

Expert Comment

by:daffodils
ID: 11800944
what error does it throw ? can you post the error message??
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11800959
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS1519: Invalid token '=' in class, struct, or interface member declaration

Source Error:

Line 10:       protected string strUser;
Line 11:
Line 12:       myCmd.CommandType = CommandType.Text;
Line 13:          SqlParameter myParam = new SqlParameter("@IncidentStatus", SqlDbType.NVarChar, 50);
Line 14:       myParam.Value = "Open";

________________________________

This is the code:

<%@ Page Language="c#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">


    SqlConnection myConn = new SqlConnection ("server=(local);database=BCA;trusted_connection=true");
    SqlCommand myCmd = new SqlCommand ( );
    string id;
      protected string strUser;

      myCmd.CommandType = CommandType.Text;
         SqlParameter myParam = new SqlParameter("@IncidentStatus", SqlDbType.NVarChar, 50);
      myParam.Value = "Open";
   
    protected void Page_Load ( object src, EventArgs e ) {

Response.AppendHeader("Refresh", Convert.ToString((Session.Timeout * 60)) + @"; URL=default.aspx");

       
 myCmd.Connection = myConn;
        id = Request.QueryString [ "id" ] ;
        if ( !IsPostBack ) {
           if ( id == null ) {
              bindGrid ( );
              loadPanel.Visible = true;
              editPanel.Visible = false;
              postPanel.Visible = false;
           } else {
              getMessage ( Request.QueryString [ "id" ] );
              loadPanel.Visible = false;
              editPanel.Visible = true;
              postPanel.Visible = false;
           }
        } else {
           loadPanel.Visible = false;
           editPanel.Visible = false;
           postPanel.Visible = true;
        }
    }
   
    public void bindGrid ( )
      
      {
         myCmd.CommandType = CommandType.Text;
         SqlParameter myParam = new SqlParameter("@IncidentStatus", SqlDbType.NVarChar, 50);
      myParam.Value = "Open";
      myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus = @IncidentStatus";

   
        myConn.Open ( );
        myGrid.DataSource = myCmd.ExecuteReader ( CommandBehavior.CloseConnection );
        myGrid.DataBind ( );
    }
   
    public void getMessage ( String id ) {
        SqlDataAdapter myAdapter = new SqlDataAdapter (
           "select * from BCAIncident where IncidentID=" + id, myConn );
        DataTable msgDetails = new DataTable ( );
        myAdapter.Fill ( msgDetails );
        DataRowView myRow = msgDetails.DefaultView [ 0 ] ;
   
        msgDate.Value = myRow [ "IncidentID" ].ToString ( );
        msgFrom.Value = myRow [ "IncidentAuthorID" ].ToString ( );
        msgEmail.Value = myRow [ "IncidentEmail" ].ToString ( );
        msgDesc.Value = myRow [ "IncidentDesc" ].ToString ( );
        msgShrtDesc.Value = myRow [ "IncidentShrtDesc" ].ToString ( );
        msgStatus.Value = myRow [ "IncidentStatus" ].ToString ( );
        msgAction.Value = myRow [ "IncidentAction" ].ToString ( );
        msgCompleted.Value = myRow [ "IncidentCompleted" ].ToString ( );
        msgIncName.Value = myRow [ "IncidentName" ].ToString ( );
        msgIncOwner.Value = myRow [ "IncidentOwner" ].ToString ( );
        editPanel.DataBind ( );
    }
   
    public void updateMessage ( object src, EventArgs e ) {
        if ( Page.IsValid ) {
           myCmd.CommandText = "UPDATE BCAIncident SET IncidentAuthorID=@From, IncidentEmail=@Mail, IncidentDesc=@Desc,

IncidentShrtDesc=@ShrtDesc, IncidentStatus=@Status, IncidentAction=@Action, IncidentCompleted=@Completed,

IncidentName=@IncName, IncidentOwner=@IncOwner where IncidentID=" + id;
   
           myCmd.Parameters.Add ( "@From", SqlDbType.NVarChar ).Value = msgFrom.Value;
           myCmd.Parameters.Add ( "@Mail", SqlDbType.NVarChar ).Value = msgEmail.Value;
           myCmd.Parameters.Add ( "@Desc", SqlDbType.NVarChar ).Value = msgDesc.Value;
           myCmd.Parameters.Add ( "@ShrtDesc", SqlDbType.VarChar ).Value = msgShrtDesc.Value;
           myCmd.Parameters.Add ( "@Status", SqlDbType.NVarChar ).Value = msgStatus.Value;
           myCmd.Parameters.Add ( "@Action", SqlDbType.VarChar ).Value = msgAction.Value;
           myCmd.Parameters.Add ( "@Completed", SqlDbType.VarChar ).Value = msgCompleted.Value;
           myCmd.Parameters.Add ( "@IncName", SqlDbType.NVarChar ).Value = msgIncName.Value;
           myCmd.Parameters.Add ( "@IncOwner", SqlDbType.NVarChar ).Value = msgIncOwner.Value;
           myConn.Open ( );
           myCmd.ExecuteNonQuery ( );
           myConn.Close ( );
        }
        bindPostPanel ( id );
    }
   
    public void bindPostPanel ( String id ) {
        myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentID='" + id + "'";
        myConn.Open ( );
        listDetails.DataSource = myCmd.ExecuteReader ( CommandBehavior.SingleRow );
        listDetails.DataBind ( );
        myConn.Dispose ( );
    }

</script>
 

Source File: C:\Inetpub\wwwroot\memberarea\updateAdmin.aspx    Line: 12

0
 
LVL 8

Accepted Solution

by:
daffodils earned 250 total points
ID: 11800980
okay.. lets then remove the Line 12.. and I made a mistake - I hadn't added the parameter to the Command object

public void bindGrid ( )
{
     SqlParameter myParam = new SqlParameter("@IncidentStatus", SqlDbType.NVarChar, 50);
     myParam.Value = "Open";
     myCmd.Parameters.Add(myParam);
     myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus = @IncidentStatus";
 
      myConn.Open ( );
      myGrid.DataSource = myCmd.ExecuteReader ( CommandBehavior.CloseConnection );
      myGrid.DataBind ( );
}
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11801051
That did it!

As usual, you did it.  Thanks again.
0
 
LVL 8

Expert Comment

by:daffodils
ID: 11801061
Good.. am sorry I should have corrected that the first time around.. anyway.

Thanks.. best of luck.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

943 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

6 Experts available now in Live!

Get 1:1 Help Now