I'm having trouble figuring out how to create the correct select string. My SQL table is like this:tblSoldVehilces Contract Date Stock Number Vin Number Make Model Sale Type PIDI'm trying to find a SQL string that will return a table that I"m not sure is possible.What I need is to get a table like this:Make Model CountMake Model CountWhere it will for instance count all the Honda Accords and give me a total in return so that I can graph all the honda accords sold...so on and so fourth. There is consistancyin the data for model names and such.(ie an Accord is always an Accord and never an accord LX)Any help would be greatly appreciated. I'll continue to read posts to see if I can find the answer.ThanksJosh
hey everyone, everyone here has been extremely helpful, I'm extremely appreciative. i have another question if anyone has the time.I want to pull the value of one column/row into a string, i know this value to be one int or 1 word under 10 characters. I'd like to be able to use this variable as a conditional, so my if/else statements have information to work off of. I have been using the following format in by code-behind pages to do my SQL insert/update/delete - however I cannot figure out how to SELECT and get those results into a string. I'm new obviously, so dumbed-down explanation would be greatly appreciated!This is what I've used so far for working with my DB:using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls; protected void Button1_Click(object sender, EventArgs e) { SqlDataSource profilesinsert = new SqlDataSource(); profilesinsert.ConnectionString = ConfigurationManager.ConnectionStrings["ProfilesConnectionString1"].ToString(); profilesinsert.InsertCommandType = SqlDataSourceCommandType.Text; profilesinsert.InsertCommand = "INSERT INTO ProfileComments (Approved) VALUES (@Approved)"; profilesinsert.InsertParameters.Add("Approved", "yes"); profilesinsert.Insert(); } The SELECT into a string I'd like to do on page_load, so that I can test that variable upon button click, and have a different value in "Approved" depending on the 1 int or small word result from my SELECT.THANK YOU very much to anyone who offers help! love you guys :)
I'm building a select string on the fly based on criteria selected by the user. The user is given a data grid with Names and Check Boxes, where they can select multiple names and then either print those selections, or download them to excel. Everything is working fine, except when the Name has an ' in it. For example O'Kelly or St. John's. I know I can take all the 's out of the database, but I'd rather keep the data authentic. Is there a way to manipulate a select string built on the fly accounting for an embedded '? For example, I build Select * from table where Name IN ('Smith', 'Jones', 'Jordan', 'Bird', 'O'Kelly').... the ' in O'Kelly ends my string and my sql statement blows up. Any ideas? Sample Code:<code> For Each SelectedIndex In rsc.SelectedIndexes Counter=Counter + 1 dgAssociates.SelectedIndex = SelectedIndex If Counter = 1 then SelectString = "Select * from Associate_Table where AssociateID IN ('" & dgAssociates.SelectedItem.Cells(16).Text() & "'" Else SelectString &= ",'" & dgAssociates.SelectedItem.Cells(16).Text() & "'" End If Next If Counter > 0 then SelectString &= ")" End If</code>
i'm using sql server 2005 and i need the sql i can use to select rows where the string contains a substring (in access i used instr but now it tells me it's not a built-in function.
HelloWe've got a string field with some words, and I created a formso that anybody could search someting into this field.SELECT * FROM customer_table WHERE description LIKE '%query%'";But if you search '100', it returns '45,100'. Or if you get'plugged', it reurns 'unplugged'.I tried with:SELECT * FROM customer_table WHERE description LIKE '% query %'";(note the blanks after and before)But it does not work with a word in the beginning of the field.Does anybody have any experience? Thank you very much.
For example, select fieldA form tableA where fieldA = 'aaa' I got following output fieldA --------- aaa aAa AAA AAa ... if I want select only the lower case 'aaa', how can I do that?
Hi I am trying to include a string variable in a Select Statement. My problem is that when I code with the user name hard coated in the SQL Statement it works fine (see below:) cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = 'jdoe' " But when I try to use the String variable I get an error (See below): cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = " & StrUserName I know there must be something wrong with my syntax ?? Thanks Jackson
I'm trying to add a 'change password' control to my site and seem to be having some issues. I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly. Error:System.Data.SqlClient.SqlException: The column prefix 'System.Security.Principal' does not match with a table name or alias name used in the query. Here's a piece of the code that is supposed to detect the current logged in user. However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell) public void InitPage() { IPrincipal p = HttpContext.Current.User; String myid = HttpContext.Current.User.ToString(); SqlServer sqlServer = new SqlServer(Util.SqlConnectionString()); DataTable dt; SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn); DataTable UIDtable = new DataTable(); cmd1.Fill(UIDtable); User_Id.Value = UIDtable.Rows[0]["ID"].ToString(); dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value);
I'm looking for some good hints and tips for reprogrammin an old VB module I just found.
Basically what it does, is receive an input parameter (an int), does a select [name row] from Names where Name_id = [input parameter] and turns this into a string if multiplenames appear.
E.g. result set: John, Josh, Jock turns it into string "John Josh Jock".
So its piece of cake creating a stored procedure selecting data on the base of an input parameter. Select X from Y where Z = @input... the trick is, I don't know how to do arrays in TSQL as in VB.
In the VB edition I create an array, load the names into it, I do a count on how many row the select returns and then a simple for... next adding the names to the string.
Any good examples on how to do this in a sql-server stored proc?
Thanks,
Trin
P.S. This is what I have pieced together this far:
DECLARE medlemcursor CURSOR FOR SELECT [MCPS Kode] FROM DW.dbo.names(NOLOCK) WHERE number = @number
OPEN medlemcursor
FETCH NEXT FROM medlemcursor INTO @tempinstt WHILE (@@FETCH_STATUS <> -1) BEGIN SET @instrument = @instrument + @tempinstt + '-' FETCH NEXT FROM medlemcursor INTO @tempinstt END
CLOSE medlemcursor DEALLOCATE medlemcursor
SELECT @instrument GO
Just doesn't seem to work, returns NULL, even though I've checked that the cursor SELECT statement actually returns data,
Hello, i try to work a my Pocket PC project. I have a question about SQL.
How to write a sql select dataset from table with a give string date but the column is datetime? I don't know how to convert the date string to datetime.Also the time part in the datetime coulmn have some value in it, how to get over that?
and how to make sure both format is correct? because as i check the msdn, there are many different format in datetime.
For example
using date string XX-XX-XXXX to select dataset in the following table
table with following column DateTimeStamp XX-XX-XXXX yy:yy:yy:yyyy
Anyone who knows a smarter way to select a special part of a text string. A have done like this now but mabye the textstring changes. I know that the GO02 in the future will expand. KnowgoodP_GO02_AA_K_20070807_2010_L.BBB But the underscore _ sign is like a seperator and will always be there to separate the words.
I need the 20070807_2010 and fomat to 2007-08-07 20:10
hi there, i have a bit of a strange problem, and i must be doing something wrong because this works with insert and update statements in other pages. What is going on is that i am trying to do 2 select statements in the same bit of code so i dont have to open a datareader twice, but if i put the delivery charge select before the other it seems to get it, but if i put it after it does not, i just cant work it out! i have posted my code below for you all to have a look at because im stumped! //gets order id and customer id and puts them to strings and int to be used laterstring strCustID = Request.QueryString["qsnOrderCustID"].ToString(); int intCustID = Convert.ToInt32(strCustID);string strOrderID = Request.QueryString["orderID"].ToString();int intOrderID = Convert.ToInt32(strOrderID);
//This is the sql statement.string sql = "SELECT [sub_total], [VAT], [Total] FROM tbl_order WHERE order_ID = " + intOrderID + ";" + "SELECT [del_cost] FROM tbl_del WHERE order_ID = " + intOrderID;
//This creates a sql command which executes the sql statement.SqlCommand sqlCmd = new SqlCommand(sql, myConn); myConn.Open();SqlDataReader dr = sqlCmd.ExecuteReader();
//This reads the first result from the sqlReader dr.Read(); //This sets the title label text to the value of the description column. try {
//fill these labels with the first select statement lblSubTot.Text = dr["sub_total"].ToString(); lblVAT.Text = dr["VAT"].ToString();lblOrderTotal.Text = dr["Total"].ToString();lblDelCharge.Text = dr["del_cost"].ToString();
//fill this label with the second select statementlblDelCharge.Text = dr["del_cost"].ToString(); }catch (Exception except) {string strError = Convert.ToString(except); } finally { myConn.Close(); } any ideas would be great! thanks Jez
Ok so we gotSELECT this, that, others FROM some.database WHERE this=@this So in the database the others field is a string that can have up to 200 characters, but on this particular data pull I only want to pull the first 50 characters of the others field. How can I do that? Thanks.
FROMTable1 INNER JOIN Table2 ON Table1.UID = Table2.UID
WHERE(SET @Temp = Table2.Column1
--remove all 0's SET @k = patindex('%[^0 ]%', @Temp) WHILE @k> 0 BEGIN SET @Temp = replace(@Temp, substring(@Temp, @k, 1), '') SET @k= patindex('%[^0 ]%', @Temp) END SELECT @Temp ) = ''
But of course this isn't working so much. I am wondering if I have to use a cursor?
I am using a SQLDataSource with the following Select query. If the "spouse" values are not in the database, I get the HTML non-breaking-space character back, rather than an empty string.
SelectCommand="select applicant_id, (applicant_last_name + ', ' + applicant_first_name) as applicant_name, CONVERT(varchar(10), applicant_dob, 101) as applicant_dob, applicant_ssn, (spouse_last + ', ' + spouse_first) as spouse_name, CONVERT(varchar(10), spouse_dob, 101) as spouse_dob, spouse_ssn from applicant where applicant_last_name like '%'+@last_name+'%' order by applicant_last_name"
I got a really simple question here. Say I have a table with
ID, Name 1, A1 2, A2 3, A3 .... 10, A10
Now I want to combine the names into another table grouped by their ID (say 1-5, 6-10), so this new table has two names instead of 10: A1 A2 A3 A4 A5 A6 A7 A8 A9 A10
Is there a function that allows me to 'combine' the names from a select statement?
Hello, I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product. I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here: http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so: WHERE (OrderStatus IN ((select value from dbo.fn_Split(@StatusList,','))) OR @StatusList IS NULL) but now I don't know how to assign the string value to the parameter, say to '@solution_id'.my current select statement which was generated by Visual Studio 2005 looks like this: SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @solution_ID) ...but this only pulls results for the first item checked in the checkboxlist.Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.
Hi all,I have created this simple Stored procedure. But it gives me wrong result when I pass a parameter to it. But if I hard code it, it gives me the right result. The I check if the field value of 'Email' is exactly equal to the parameter '@Email'. The field 'Email' is varchar, and CID is integer. CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = @Emailreturn Instead, if I check the value directly, it gives me correct answer. The the following code works fine when I typethe Email directly in the code. CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = 'tomyseba@yahoo.com' return Can anyone tell me the reason for it. Thanking you in advance
i have a case : i'm using a select statement with SqlCommand and save the results in the SQLDataAdapterand using the data table I post the result to the gridview and show it there....my question is what should I do to append string to the SQL Command??
1. Have a query that fetches a real value (e.g. 4.3345643)
Let say the field is called TheReal.
How can I format the value in the select statement so I get a thousand separator(s) and two decimals in the resultset.
2. In the same query I have a left join as well. Table 2 retur (sometimes) <NULL>. Is it possible to force this <NULL> value to be a fixed string value instead in the select statement.
Hello, I am facing a problem in a SELECT clause which i cannot solve. In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not. For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC". when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:
SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3 FROM myTable
but this doesn't work in SQL 2005! Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.
