Need help with sql search please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with sql search please

Author

Topic

Rae
Starting Member

1 Post

Posted - 2009-02-15 : 20:40:42

Hello;
I'm new here. Maybe someone could help me with this?
[url]http://support.microsoft.com/kb/320916#appliesto[/url]
I got the sample above working fine in Visual Studio NET 2008 except that I had to change way of connecting. I changed that as follows:
System.Data.SqlClient.SqlConnection MyConnection = new System.Data.SqlClient.SqlConnection();

MyConnection.ConnectionString = "integrated security=SSPI;data source=.\\SQLEXPRESS;" +
"persist security info=False;initial catalog=pubs";

But in the project I want to use a search for, I want to be able to search more than just one text box/field. So I modified the sample code by putting in a second text box and button and then duplicating the code changing only the variables.

I created a second stored procedure called GetCity. All references to the the new addition to that code is under the "GetCity" part. I just basically copied and pasted and changed the variables.

Now the error I get is:

Server Error in '/' Application.
Incorrect syntax near the keyword 'select'.
Must declare the scalar variable "@CityCount".
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'select'.
Must declare the scalar variable "@CityCount".

Source Error:

Line 103: " city like @city; select @CityCount=@@ROWCOUNT";
Line 104: MyDataReader2.Close();
Line 105: MyCommand2.ExecuteNonQuery();
Line 106: }
Line 107: else

Source File: C:\My Documents\SearchExample\WebApplication1\WebApplication1\Default.aspx.cs Line: 105

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near the keyword 'select'.
Must declare the scalar variable "@CityCount".]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +317
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
WebApplication1._Default.Page_Load(Object sender, EventArgs e) in C:\My Documents\SearchExample\WebApplication1\WebApplication1\Default.aspx.cs:105
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +50
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

Did I make a mistake by trying to add the second search box to the code? Is there a different way to do a search on more than one field?

Below is my code behind page where I made the changes from the online sample:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Data.SqlClient;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

namespace WebApplication1
{

public partial class _Default : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{
// Only run this code the first time the page is loaded.
// The code inside the IF statement is skipped when you resubmit the page.
if (!IsPostBack)
{
//Create a connection to the SQL Server; modify the connection string for your environment
System.Data.SqlClient.SqlConnection MyConnection = new System.Data.SqlClient.SqlConnection();
// TODO: Modify the connection string and include any
// additional required properties for your database.

MyConnection.ConnectionString = "integrated security=SSPI;data source=.\\SQLEXPRESS;" +
"persist security info=False;initial catalog=pubs";

// Create a Command object, and then set the connection.
// The following SQL statements check whether a GetAuthorsByLastName
// stored procedure already exists.
SqlCommand MyCommand = new SqlCommand("select * from sysobjects where id = object_id(N'GetAuthorsByLastName')" +
" and OBJECTPROPERTY(id, N'IsProcedure') = 1", MyConnection);

// Set the command type that you will run.
MyCommand.CommandType = CommandType.Text;

// Open the connection.
MyCommand.Connection.Open();

// Run the SQL statement, and then get the returned rows to the DataReader.
SqlDataReader MyDataReader = MyCommand.ExecuteReader();

// If any rows are returned, the stored procedure that you are trying
// to create already exists. Therefore, try to create the stored procedure
// only if it does not exist.
if (!MyDataReader.Read())
{
MyCommand.CommandText = "create procedure GetAuthorsByLastName" +
" (@au_lname varchar(40), select * from authors where" +
" au_lname like @au_lname; select @RowCount=@@ROWCOUNT";
MyDataReader.Close();
MyCommand.ExecuteNonQuery();
}
else
{
MyDataReader.Close();
}

MyCommand.Dispose(); //Dispose of the Command object.
MyConnection.Close(); //Close the connection.

//ADDED NEW PART (1) here:
//Create a connection to the SQL Server; modify the connection string for your environment
System.Data.SqlClient.SqlConnection MyConnection2 = new System.Data.SqlClient.SqlConnection();
// TODO: Modify the connection string and include any
// additional required properties for your database.

MyConnection2.ConnectionString = "integrated security=SSPI;data source=.\\SQLEXPRESS;" +
"persist security info=False;initial catalog=pubs";

// Create a Command object, and then set the connection.
// The following SQL statements check whether a GetAuthorsByLastName
// stored procedure already exists.
SqlCommand MyCommand2 = new SqlCommand("select * from sysobjects where id = object_id(N'GetCity')" +
" and OBJECTPROPERTY(id, N'IsProcedure') = 1", MyConnection2);

// Set the command type that you will run.
MyCommand2.CommandType = CommandType.Text;

// Open the connection.
MyCommand2.Connection.Open();

// Run the SQL statement, and then get the returned rows to the DataReader.
SqlDataReader MyDataReader2 = MyCommand2.ExecuteReader();

// If any rows are returned, the stored procedure that you are trying
// to create already exists. Therefore, try to create the stored procedure
// only if it does not exist.
if (!MyDataReader2.Read())
{
MyCommand2.CommandText = "create procedure GetCity" +
" (@city varchar(20), select * from authors where" +
" city like @city; select @CityCount=@@ROWCOUNT";
MyDataReader2.Close();
MyCommand2.ExecuteNonQuery();//ERROR SHOWS AT THIS SPOT
}
else
{
MyDataReader2.Close();
}

MyCommand2.Dispose(); //Dispose of the Command object.
MyConnection2.Close(); //Close the connection.
//END OF NEW ADDITION PART (1)
}

// Add the event handler to the Button_Click event.
this.btnGetAuthors.Click += new System.EventHandler(this.btnGetAuthors_Click);
// btnGetAuthors

//ADD NEW PART (2) HERE
// Add the event handler to the Button_Click event.
this.btnGetCity.Click += new System.EventHandler(this.btnGetCity_Click);
// btnGetAuthors
//END PART (2)
}

private void btnGetAuthors_Click(object sender, System.EventArgs e)
{
//Create a connection to the SQL Server; modify the connection string for your environment.
System.Data.SqlClient.SqlConnection MyConnection = new System.Data.SqlClient.SqlConnection();

MyConnection.ConnectionString = "integrated security=SSPI;data source=.\\SQLEXPRESS;" +
"persist security info=False;initial catalog=pubs";

//Create a DataAdapter, and then provide the name of the stored procedure.
SqlDataAdapter MyDataAdapter = new SqlDataAdapter("GetAuthorsByLastName", MyConnection);

//Set the command type as StoredProcedure.
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

//Create and add a parameter to Parameters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@au_lname", SqlDbType.VarChar, 40));

//Assign the search value to the parameter.
MyDataAdapter.SelectCommand.Parameters["@au_lname"].Value = (txtLastName.Text).Trim();

//Create and add an output parameter to the Parameters collection.
MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int, 4));

//Set the direction for the parameter. This parameter returns the Rows that are returned.
MyDataAdapter.SelectCommand.Parameters["@RowCount"].Direction = ParameterDirection.Output;

//I ADDED THIS TO GET RID OF ERROR "DATASOURCE AND DATASOURCEID ARE SAME" WHICH WORKED:
GrdAuthors.DataSourceID = null;
// GrdAuthors.DataSource = dt;
GrdAuthors.DataBind();

//END OF ADDITION

//Create a new DataSet to hold the records.
DataSet DS = new DataSet();

//Fill the DataSet with the rows that are returned.
MyDataAdapter.Fill(DS, "AuthorsByLastName");

//Get the number of rows returned, and assign it to the Label control.
//lblRowCount.Text = DS.Tables(0).Rows.Count().ToString() & " Rows Found!"
lblRowCount.Text = MyDataAdapter.SelectCommand.Parameters[1].Value + " Rows Found!";

//Set the data source for the DataGrid as the DataSet that holds the rows.
GrdAuthors.DataSource = DS.Tables["AuthorsByLastName"].DefaultView;

//NOTE: If you do not call this method, the DataGrid is not displayed!
GrdAuthors.DataBind();

MyDataAdapter.Dispose(); //Dispose the DataAdapter.
MyConnection.Close(); //Close the connection.
}

// NEW SECOND BUTTON CLICK EVENT FOR CITY - NEW PART (3) STARTS HERE:
private void btnGetCity_Click(object sender, System.EventArgs e)
{
//Create a connection to the SQL Server; modify the connection string for your environment.
System.Data.SqlClient.SqlConnection MyConnection2 = new System.Data.SqlClient.SqlConnection();

MyConnection2.ConnectionString = "integrated security=SSPI;data source=.\\SQLEXPRESS;" +
"persist security info=False;initial catalog=pubs";

//Create a DataAdapter, and then provide the name of the stored procedure.
SqlDataAdapter MyDataAdapter2 = new SqlDataAdapter("GetCity", MyConnection2);

//Set the command type as StoredProcedure.
MyDataAdapter2.SelectCommand.CommandType = CommandType.StoredProcedure;

//Create and add a parameter to Parameters collection for the stored procedure.
MyDataAdapter2.SelectCommand.Parameters.Add(new SqlParameter("city", SqlDbType.VarChar, 20));

//Assign the search value to the parameter.
MyDataAdapter2.SelectCommand.Parameters["city"].Value = (txtCity.Text).Trim();

//Create and add an output parameter to the Parameters collection.
MyDataAdapter2.SelectCommand.Parameters.Add(new SqlParameter("@CityCount", SqlDbType.Int, 4));

//Set the direction for the parameter. This parameter returns the Rows that are returned.
MyDataAdapter2.SelectCommand.Parameters["@CityCount"].Direction = ParameterDirection.Output;

//I ADDED THIS TO TRY GETTING RID OF ERROR DATASOURCE AND DATASOURCEID ARE SAME:
GrdAuthors.DataSourceID = null;
// GrdAuthors.DataSource = dt;
GrdAuthors.DataBind();

//END OF ADDITION

//Create a new DataSet to hold the records.
DataSet DS2 = new DataSet();

//Fill the DataSet with the rows that are returned.
MyDataAdapter2.Fill(DS2, "GetCity");

//Get the number of rows returned, and assign it to the Label control.
//lblRowCount.Text = DS.Tables(0).Rows.Count().ToString() & " Rows Found!"
lblRowCount.Text = MyDataAdapter2.SelectCommand.Parameters[1].Value + " Rows Found!";

//Set the data source for the DataGrid as the DataSet that holds the rows.
GrdAuthors.DataSource = DS2.Tables["GetCity"].DefaultView;

//NOTE: If you do not call this method, the DataGrid is not displayed!
GrdAuthors.DataBind();

MyDataAdapter2.Dispose(); //Dispose the DataAdapter.
MyConnection2.Close(); //Close the connection.
}
// END OF BUTTON CLICK PART 3

}
}
THE ERROR IN CODE ABOVE USING THE DEBUG MODE WAS AT THE LINE:

if (!MyDataReader2.Read())
{
MyCommand2.CommandText = "create procedure GetCity" +
" (@city varchar(20), select * from authors where" +
" city like @city; select @CityCount=@@ROWCOUNT";
MyDataReader2.Close();
MyCommand2.ExecuteNonQuery();// this is where error occurs
}

(I don't get the message about the need to declare scalar variable on the 1st part for @RowCount.)
Rachel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-16 : 07:48:51

1 Why are you creating the procedure thru code?
2 You should declare the variable @citycount

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources