• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

What's wrong with my asp.net project?

Jeff7181

Jeff7181

Lifer
What's wrong with my LIKE operator?

@customer_name is input from a text box

If I enter "Jeff" in the text box, and there's a record with the tblCutomer.customer_name of Jeff7181, it won't return that as a match.

(sorry if it's difficult to read... apparently code doesn't preserve formatting... I edited it to remove aliases so it's easier to read)

Updated the topic now since the problem with LIKE isn't a problem anymore.

Here's my current issue...

It may just be a syntax problem, but I can't make the data grid use my built SQL query in the data source configuration to populate the data grid with the results of the query. Right now I'm dealing with this code for the data source...

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:copilot_joblogConnectionString2 %>"
SelectCommand="SELECT * FROM [tblCustomer]"></asp:SqlDataSource>

What I'm struggling with is the SelectCommand portion... instead of "SELECT * FROM [tblCustomer]" I want to use the built SQL string I posted above, but it needs to pull that either from a variable or the invisible label I've put the SQL string in and I don't know how to make it do that, everything I've tried has resulted in an exception or error of some sort. I can't count how many exceptions I've seen while working on this, lol.
 
I'm not a SQL expert, but don't you need to use "%" as the wild card in a like statement? Using your example, if you type in "Jeff%" I believe it should return the correct results.
 
Yes, that works... but I was hoping there would be a way for the user not to have to use % as a wildcard. Can't I make that part of the SQL query so that it always uses the wildcard on that particular variable?
 
When you build the query surround the user's string with the '%' token as Noobsa44 suggested. There's no reason for your users to have to input this token, as it is consistent across all queries.

Something like...

"WHERE tblCustomer.customer_name LIKE '%" + customer_name + "%'"
 
When I use...

WHERE tblCustomer.customer_name LIKE '%' + @customer_name + '%' it returns zero results.

If I use...

WHERE tblCustomer.customer_name LIKE "%" + @customer_name + "%" it says % is an invalid column name.
 
This statement should have worked:
WHERE tblCustomer.customer_name LIKE '%' + @customer_name + '%'

This is sql server right?

Post the code that fills in the parameter and/or the stored procedure.
 
However you build the command string, it has to end up looking like this when SQL server gets it...

WHERE tblCustomer.customer_name LIKE '%somestring%'

Something tells me you're missing the single quotes.
 
Originally posted by: Jeff7181
When I use...

WHERE tblCustomer.customer_name LIKE '%' + @customer_name + '%' it returns zero results.

If I use...

WHERE tblCustomer.customer_name LIKE "%" + @customer_name + "%" it says % is an invalid column name.
Click to expand...

WHERE tblCustomer.customer_name LIKE ''% + @customer_name + '%'''

Try that.
 
Ok... here's what I'm doing...
I have a SQL Server 2005 database... very simple, 3 tables. Relationships are all set up correctly.
I'm trying to create a simple UI using ASP.NET and VB.
Basically for this part, I'm trying to allow the user to search for a customer name. So I have a text box, a button, and a grid view. I'm configuring the datasource for the grid view so it takes input from the text box and returns matches.

Here's the entire chunk of code for the datasource and datagrid... again, sorry for the hard to read code... why doesn't the code section preserve formatting? 😕

 
Somethin else is screwed up now... I can't even get it to display anything when I use (Select * from tblCustomer)
I'll work on it and try everyone's suggestions tomorrow. I really appreciate everyone's ideas, thank you. 🙂
 
Ok, so I think I need to go about this a different way, so I'm using VB code to build the SQL query from the user inputs. Now I'm just having trouble figuring out how to configure the data source with that SQL query built from the user inputs since it won't use the variables I've declared in my VB code.
 
Originally posted by: nakedfrog
Build your SQL string, then
YourDataSource.SelectCommand = YourSQL
YourDataSource.DataBind()
Does that help?
Click to expand...

Here's what I can get to work...

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:copilot_joblogConnectionString2 %>"
SelectCommand="SELECT * FROM [tblCustomer]"></asp:SqlDataSource>

That works fine... but what I want to do is use a variable to fill in the SQL query. So I've built the query with VB and stored it in a string variable strSQL. So what I'm trying to do is...

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:copilot_joblogConnectionString2 %>"
SelectCommand=strSQL></asp:SqlDataSource>

But that doesn't work... and I don't know if it's a syntax issue, or if I need to (and how to, if I do) pass a variable from *.aspx.vb to *.aspx, or if it's something else I'm unaware of.
 
From reading asp.net forums I found that I can pass the string by storing it in an invisible lable. Now my problem is getting it to use the entire SQL statement instead of building part of it and getting field values from the label...
 
Personally, I would handle that all in the code-behind (aspx.vb) on postback. That would allow you to build the string and use the values from the label.
 
That's what I'm trying to do now. I have the string built... I just can't get it to query the database and return results (the select statement works, I've copied it from a label on the aspx page into SQL Server and it works fine).

I have a drop down list that I want the user to use to select what column they want to search and a text box for them to enter the search string. From that I built the select statement no problem. The problem is querying the database and getting the results. The drag & drop controls in VS 2008 don't seem to want to let me do that and I don't seem to be able to modify the code to get it to work.

I may just scrap what I've done so far and start over after brushing up since the last time I did this was over a year ago.
 
HAve you tried running the contents of the built sql string within query analyzer? Can you post what the sql string looks like?
 
Originally posted by: KLin
HAve you tried running the contents of the built sql string within query analyzer? Can you post what the sql string looks like?
Click to expand...

Yeah, it runs perfectly when I do that... I just can't get my .aspx page to accept any variables for the column I want to search within.

Here's the built sql string...

SELECT tblCustomer.customer_id AS [Customer ID], tblCustomer.customer_name AS [Customer], tblJob.job_id AS [Job Log Number], tblJob.order_date AS [Order Date], tblJob.due_date AS [Due Date], tblJob.completed AS [Picked Up/Delivered], tblJob.invoiced AS [Invoiced], tblJob.mailed AS [Mailed/E-mailed]
FROM tblCustomer, tblJob
WHERE (tblCustomer.customer_id = tblJob.customer_id) AND tblCustomer.customer_name LIKE '%jeff%';

That's the statement I want to use to populate the data grid with... but I don't know how to get the data grid to take that statement, either from a variable or a label. Nothing I've tried has worked unless I make separate pages to search each different column, which would be kind of ridiculous.
 
Like nakedfrog said above, I would move it out to code-behind and just get the values from the input controls and build the select statement using StringBuilder. The thing about embedded SQL is you have to be extremely careful about quotes and where they are needed. That's probably number one on the list of bonehead mistakes I make initially, followed by losing a space, mispelling a column name, etc.

Are you getting an exception or error when the thing executes?
 
I have moved it to the VB code. That SQL string is generated with VB code from user inputs and stored in a variable. Then I couldn't figure out how to pass a variable from the code portion to the .aspx page, so I put the SQL string in a label and made the label invisible. So now my built SQL code is on the .aspx page, but I can't seem to use it to display the results in a data grid.

It may just be a syntax problem, but I can't make the data grid use my built SQL query in the data source configuration to populate the data grid with the results of the query. Right now I'm dealing with this code for the data source...

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:copilot_joblogConnectionString2 %>"
SelectCommand="SELECT * FROM [tblCustomer]"></asp:SqlDataSource>

What I'm struggling with is the SelectCommand portion... instead of "SELECT * FROM [tblCustomer]" I want to use the built SQL string I posted above, but it needs to pull that either from a variable or the invisible label I've put the SQL string in and I don't know how to make it do that, everything I've tried has resulted in an exception or error of some sort. I can't count how many exceptions I've seen while working on this, lol.
 
What you need is some basic samples of using binding with a datagrid. There shouldn't be any reason to use an invisible label to store that kind of data. What you need is a string variable to hold your select statement until you send it to the command object. Then you would open your database connection and execute the command. Then finally you bind the returned data to your datagrid and close your connection.

Try starting here:

http://aspnet.4guysfromrolla.com/articles/040502-1.aspx

 
In the code-behind file, handle the "Selecting" event of the SqlDataSource. You can access the Command object in this event, and change its properties to your liking. This event is raised right before the SqlDataSource makes the DB call. Hope this helps.

Also, why don't you have this wrapped in a stored procedure instead?
 
Originally posted by: Dhaval00
In the code-behind file, handle the "Selecting" event of the SqlDataSource. You can access the Command object in this event, and change its properties to your liking. This event is raised right before the SqlDataSource makes the DB call. Hope this helps.

Also, why don't you have this wrapped in a stored procedure instead?
Click to expand...

Wouldn't I need a stored procedure for searching each column I want to be searchable from the UI? Would that be more efficient?



Thanks for the link GoatMonkey, I'll check it out, looks like that's what I need.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top