nhorsley
asked on
Need syntax for where clause in sql server using C#
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?
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?
ASKER
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:
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:
I had inferred Open is the name of the string variable that holds value for IncidentStatus...
How and where have you declared Open ?
How and where have you declared Open ?
ASKER
Open is one of the string values contained in the field named IncidentStatus
ASKER
This is the entire code that contains the Select statement:
<%@ Page Language="c#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.Sql Client" %>
<script runat="server">
SqlConnection myConn = new SqlConnection ("server=(local);database= BCA;truste d_connecti on=true");
SqlCommand myCmd = new SqlCommand ( );
string id;
protected string strUser;
protected void Page_Load ( object src, EventArgs e ) {
Response.AppendHeader("Ref resh", 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.CloseConne ction );
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=@Complet ed,
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>
<%@ Page Language="c#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.Sql
<script runat="server">
SqlConnection myConn = new SqlConnection ("server=(local);database=
SqlCommand myCmd = new SqlCommand ( );
string id;
protected string strUser;
protected void Page_Load ( object src, EventArgs e ) {
Response.AppendHeader("Ref
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.CloseConne
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
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>
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("@IncidentSta tus", SqlDbType.NVarChar, 15)
myParam.Value = "Open";
myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus = @IncidentStatus";
myCmd.CommandType = CommandType.Text
// Assuming "IncidentStatus" is of type varchar(15) in the database.
// If not.. change accordingly
SqlParameter myParam = new SqlParameter("@IncidentSta
myParam.Value = "Open";
myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus = @IncidentStatus";
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"
Answer is as simple as using it for the Select statements as well.. and setting the ".Value" property to "Open"
ASKER
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.
what error does it throw ? can you post the error message??
ASKER
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("@IncidentSta tus", SqlDbType.NVarChar, 50);
Line 14: myParam.Value = "Open";
__________________________ ______
This is the code:
<%@ Page Language="c#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.Sql Client" %>
<script runat="server">
SqlConnection myConn = new SqlConnection ("server=(local);database= BCA;truste d_connecti on=true");
SqlCommand myCmd = new SqlCommand ( );
string id;
protected string strUser;
myCmd.CommandType = CommandType.Text;
SqlParameter myParam = new SqlParameter("@IncidentSta tus", SqlDbType.NVarChar, 50);
myParam.Value = "Open";
protected void Page_Load ( object src, EventArgs e ) {
Response.AppendHeader("Ref resh", 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("@IncidentSta tus", SqlDbType.NVarChar, 50);
myParam.Value = "Open";
myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus = @IncidentStatus";
myConn.Open ( );
myGrid.DataSource = myCmd.ExecuteReader ( CommandBehavior.CloseConne ction );
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=@Complet ed,
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\membera rea\update Admin.aspx Line: 12
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("@IncidentSta
Line 14: myParam.Value = "Open";
__________________________
This is the code:
<%@ Page Language="c#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.Sql
<script runat="server">
SqlConnection myConn = new SqlConnection ("server=(local);database=
SqlCommand myCmd = new SqlCommand ( );
string id;
protected string strUser;
myCmd.CommandType = CommandType.Text;
SqlParameter myParam = new SqlParameter("@IncidentSta
myParam.Value = "Open";
protected void Page_Load ( object src, EventArgs e ) {
Response.AppendHeader("Ref
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("@IncidentSta
myParam.Value = "Open";
myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus = @IncidentStatus";
myConn.Open ( );
myGrid.DataSource = myCmd.ExecuteReader ( CommandBehavior.CloseConne
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
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\membera
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did it!
As usual, you did it. Thanks again.
As usual, you did it. Thanks again.
Good.. am sorry I should have corrected that the first time around.. anyway.
Thanks.. best of luck.
Thanks.. best of luck.
// Assuming string Open;
myCmd.CommandText = "SELECT * FROM BCAIncident WHERE IncidentStatus=" + Open;