|
First let me say that a few months ago I found the answer to this question and have forgottten it (That means I know the answer does exist)
Also, let me say that I do not want alternate solutions. I only want the exact solution I previously found.
There is a method to convert a variable of type varchar into some sort of structure directly usuable in an in clause, ie
SELECT * FROM customer WHERE customerID in *something*(@variable)
This method does not use instr, does not use loops, does not use custom split functions and looks very elegant in sql code. If anyone knows I would appreciate the answer. It is driving bonkers.
I am thinking it may have something to do with the TABLE function but the TABLE solutions I see on the web are not elegant and is not the one I am looking for.
On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage
|
|
|
|
|
|
All of those solutions are fairly common, in fact I had already read that page. The one I want is very elegant. I know it exists because I have seen and used it but now I can't remember it!
On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage
|
|
|
|
|
|
Yes I have, which is weird because I would have thought the ask tom authors would know.
What I am looking for actually exists but it is so hard to find (Trust me I am not jerking any chains). I am sure the method is a hack based on ETL functions but searching reveals nothing. I just wish I could remember the page where I originally read it. In fact, since I have had so much trouble finding it again, if I do refind it I will definately make it into an article. Even though CP isn't known for oracle buffs :p
On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage
|
|
|
|
|
I have another question regarding datasets and datarows/columns.
I populated a combobox with this:
SqlCommand cmdSelect = sqlConnection1.CreateCommand();
cmdSelect.CommandText = "SELECT EmpID,EmpName FROM Employees";
SqlDataAdapter sdaEmp = new SqlDataAdapter();
sdaEmp.SelectCommand = cmdSelect;
sdaEmp.Fill(dstEmp,"Employees");
cboEmployee.DataSource = dstEmp.Tables["Employees"];
cboEmployee.DisplayMember = "EmpName";
cboEmployee.SelectedIndex = 0;
Now what I want to do when the user changes the 'selected index' of the combo box, to grab the info in the corresponding EmpId column from the datatable above.
What would be the correct porocedure to accomplish this?
Thanx in advance!
Jude
Jude
|
|
|
|
|
TheJudeDude wrote: Now what I want to do when the user changes the 'selected index' of the combo box, to grab the info in the corresponding EmpId column from the datatable above.
Use event Onselected index change
|
|
|
|
|
I am sorry I didn't make question more clear. I know the event to use, but I do not know how to access the data in the datatable that matches the same row as the selected index. IE:
EmpName EmpID (Selected Index)
John Doe 123 0
Jane Doe 2112 1
Bob Smith 9765 2
Lillte Johnny 321 3
If the user selects Bob Smith from the cobmobox, the selected index is 2. How to I retrieve the EmpId from that row?
Jude
|
|
|
|
|
Hello,
I know this is a very common connectivity error but I am really stuck here. I am currently trying to access a DB via a BindingNaviagator control via .NET. I know the connection string is 100% correct as when I do the preview data on my TableAdapter the data comes through. When I go to fire the program and access these records, I get the SqlException - Login failed for user (user) error. Like I said I know this is a pretty general error, but any advice on where to continue looking would be greatly appreciated.
Thanks In Advance
-Nate Lindley-
.NET Aficionado
|
|
|
|
|
That would suggest that the account the preview in Visual Studio is using is different to the account your application is using. This is possible if you are using a trusted connection as the account that will be used to access SQL Server will be the account of the currently running process.
I suggest you find out what account your application is using and set up the appropriate permissions on SQL Server, or change the account your application is running in.
|
|
|
|
|
I was thinking the same thing but I did the connection over and it still didnt work. I came to the conclusion that it was the DB settings not letting it connect because I actually created the DB on the server as opposed to the System Admin. So I had the SA make a new DB and it worked like a charm. I am not sure what settings were causing the remote connections to not be allowed but its all good now. Thanks for the response.
Big Pimpin, Spendin Gs
Nathan Lindley, .NET Aficionado
|
|
|
|
|
Can any body tell me how i can get the ip address of sql server 2000 in my lan to a text box in my vb.net application.
|
|
|
|
|
its someone how can tell me how can i make ODBC SQL Server and if is some link were can i learn?
Bravoone
|
|
|
|
|
Flaviu_2006 wrote: can i make ODBC SQL Server
What do you mean by that?
|
|
|
|
|
Sir,
CComPtr<Fields> pComFields;
CComPtr<Field> pComField;
m_SrcRecordSet->get_Fields(&pComFields);
pComFields->get_Item((CComVariant)FieldName,&pComField);
pComField->get_Value(&PVal);
DTEnum = pComField->Type;
What is wrong with the aove code ? I cannot retive the field value if the field type is string or number?
Why this is happen
Please help me?
Thanks
|
|
|
|
|
How do I export data from SQL to Excel using C#?
|
|
|
|
|
|
can nyone suggest how to right align data using sql query
nilesh
|
|
|
|
|
SQL Server Query Help (Trying to compare two tables and display the nonmatching records)otherwise delete data from one table after comparing it to another table 
|
|
|
|
|
A vague question deserves a vague answer:
SELECT
Table1_Id
FROM
Table1
WHERE
Table1_FK NOT IN (SELECT
Table2_Id
FROM
Table2)
|
|
|
|
|
Hello ,
Here is the SQL I am trying to run against our database.
*****************************************************
SELECT * FROM Part WHERE (Company_Abrv = 'TSBI')
AND (Catalog_PartType = 'Component')
AND (TypeName = 'Generic')
AND (Version = 'Space Holder')
AND (KeyComponentName = 'SPACER')
AND ( Part_Number IN (SELECT Part_Number FROM PartAttributes
WHERE (TypeName = 'Generic')
AND (Version = 'Space Holder')
AND (KeyComponentName = 'SPACER')
AND (AttributeName = 'Inside Diameter'
AND AttributeValue <= 18))
********************************************************
the last part of the WHERE clause AttributeValue is a Varchar type column. But as we are building the SQL query dynamically we also might get integer values at times. So our query may look like the above.
So in this case I am trying to convert the AttributeValue column as an int or decimal type when it has a numerical or a decimal value.
so I am trying to do it like 'convert(decimal,AttributeValue) <= 18' but it doesn't accept it.
could some one please help me with how do I do it?
Thanks
-Learner
|
|
|
|
|
cast(AttributeValue as decimal(20)) <= 18
|
|
|
|
|
Hi everybody,
Can anybody tell me the exact difference between
SqlCommand and SqlCommandBuilder.
Thanking you.
param
|
|
|
|
|
A Command object is used to execute scalar or non query commands to a database. You would set a command objects CommandText property to and sql statement that you want to run and then use it's ExecuteScalar or ExecuteNonQuery method to run it. Here is an example:
Dim conn As New SqlClient.SqlConnection(strMyConnectionString)
Dim cmd As New SqlClient.SqlCommand()
cmd.CommandText = "UPDATE myTable SET col1='my value'"
cmd.ExecuteNonQuery()
A CommandBuilder object is used to automatically create Update, Delete, and Insert SQL statements for you, based on a Select statament that you supply. You would declare a DataAdapter object, set it's SelectCommand.CommandText property to your Select SQL statement. Then when you declare a CommandBuilder object, you include the dataadapter in the CommandBuilder's constructor parameter and it will automatically create the other statements for you when you run a DataAdapter. Here is an example:
Dim conn As New SqlClient.SqlConnection(strMyConnectionString)
Dim cmd As New SqlClient.SqlCommand()
cmd.CommandText = "SELECT * FROM myTable"
Dim da As New SqlClient.SqlDataAdapter(cmd)
Dim cb As New SqlClient.SqlCommandBuilder(da)
da.Update(myDataTable)
If you hadn't of used a CommandBuilder, the program would have blown up on the da.Update(myDataTable) statement, because the adapter wouldn't have known how to insert new rows that had been added to the table, or delete rows that had been removed, etc.
|
|
|
|
|
An SQLCommand object can also be used to return query results. Under the covers, this is what happens in the DataAdapter - the command buider builds commandtext for the other sqlcommand instances used by the adapter, using the commandtext provided for the query or read command. SqlCommand instances can also be used to return query results as a datreader, which is like a fast forward-only cursor.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
