|
Hi:
I have this challenge that i am sure many of us would have faced.
I want to write a stored procedure that has to list out all company names based on whether one of the characteristic has value 1..
For eg:
create procedure (proc_name) @paramcharacteristic varchar(40) as
select * from tblcompany where @paramCharacteristic=1
Now this @paramcharacteristic will always match one of the column names in the table like Performance,Satisfaction,Profits and so on.But this does not seem to work.
When i run the stored procedure from an application i get the error
Cannot convert varchar 'Satisfaction' to integer.So i think what it does is try to compare the value of @paramcharacteristic variable and 1.But instead i want SQL to understand the statement as
select * from tblcompany where Satisfaction=1
Where do u think should i make the change.In the definition of paramcharacteristic in the stored procedure??Pls give me some suggestions.On how to deal this situation..
Thanks in advance for the help..
Regards
Pradhip.S
If a Building is Completed then why do they call it BUILDING ??
|
|
|
|
|
One option is to build up the T-SQL statement dynamically, like:
exec('select * from sysobjects where ' + @myColumnName + ' >5')
Just watch out for SQL injection problems.
Cheers,
Simon
"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg article
|
|
|
|
|
You just saved me at least 3 days work.
Cheers mate! 
|
|
|
|
|
Hi all..
I use SQL Server 2000 and i wonder how to connect to a remote SQL server (on a site not network) using query analyzer..
can I make that just the same way i do when i connect to a server on a network ?
|
|
|
|
|
You need to go into Client Network Utility and enter the connection parametes for the remote server ie.; TCP IP, IP address,server name) then go back into Query Analyzer and select the server from the list.
|
|
|
|
|
thank you a lot .. but it did not work 
can i use the name of server not the ip ?
such as www.server.com ?
|
|
|
|
|
I give in, after two days of trying to resolve a problem i need your guys help. First i have a microsoft complaint.
Why does microsoft give hundreds and hundreds of (the same) example of how to move through a bound DataTable one by one,
ie. this.BindingContext[dsPubs1, "authors"].Position += 1;
but not for my scenario below.
My Question:
I have a customer details form with numerous textboxes all bound to the same datatable. Now if a user enters an Account Number i go and retrieve the related rows information. How do i get the textBoxes to reflect this change in row.
I'm sorry i cannot give you an idea of how i have tried to get round the problem as i have tried alot of ways, with no luck. The textBoxes are bound at runtime.
Any help would save me going bold.
Thanks
Matt G
|
|
|
|
|
I am trying to createn an app in VB6.0 and Win98 to edit a simple csv (comma separated values)-file. The csv is not very big, so memory is no problem.
But I wanted to make life easy by using SQL-commands, so I decided to use ADOdc and MSHFlexGrid(e.g.)
I figured out some solutions, but noone works. If somebody could help me to get just one to work I would be glad.
(I thought using ADO woudl be RAD, but if I needed a long time to understand that db-Stuff. If I not succeed fastly, I will use a ListView and populate it "by hand" )
First Idea: (Without ADO)
MSData-Control and MSDBGrid. I can display the Data in the Grid. ok.
But when I try to edit the data I get:
"[Microsoft][ODBC Text Driver] ISAM doesn't support updating Data in a connected Tabel" (or sth like this, I translate from German)
1) How to solve this?
2) What (the hell) ist ISAM?
Second Idea: (With ADO)
ADOdc: Provider: MS OLE DB Provider for ODBC Drivers,
ADOdc: DataSource Name: Text-Files ...
ADOdc: Test connection: ok!
But also same message like above after editing:
"[Microsoft][ODBC Text Driver] ISAM doesn't support updating data in a connected Tabel" (or sth like this, I translate from German)
(MY FAVOURITE IDEA!!)
1) (Questions like above)
Another idea:
creating a db1.mdb (MS Access), creating a macro to import the my.csv, creating another macro to export it.
Idea: Start Import, then access the mdb by ADOdc-control, finally export.
1) How to get rid of fixed path in MS Access file-import?
2) The .mdb-File can be used without MS Access installed on the machine. Am I right?
3) How can I start MS-Access-macros from my VB app??
And another idea:
.udl | Provider "OLE DB for MS Directory Services" has worked with MSData Control, but just under W2K. In W98 the checkboxes for "Share Deny None" etc are greyed, and it doesn't work.
1) Whats MS Directory Services? (This "Active Directory??)
2) Does this work on WinNT? (its the SYS to run on) Are there some "Packets" to install "MS Directory Services" to get it to work?
My last idea:
Do some registy-manipulation (I havn't done yet and want to avoid this):
http://support.microsoft.com/default.aspx?scid=kb;de;245524
Friedrich
|
|
|
|
|
Hi,
I am creating an MSAccess DB using ADOX in C++/MFC.
I don't seem to be able to set the "Allow Zero Length" field to "Yes" (as seen in Access design view) from my code.
Does anyone know how to do this?
I've tried the adColNullable and adColFixed Attributes, but they seem to have no effect on an adWChar field.
Thanks....
Blade[DMS]
|
|
|
|
|
Hi
anyone care to point me at a SQL optimization 101 type FAQ etc eg dos and don't of tables/views/queries server config etc
Thanks
Shaun
|
|
|
|
|
Well, a good site on SQL Server optimization is http://www.sql-server-performance.com/[^]. Maybe it has too much information for you to read, so I recommend you to read some introduction first.
[shameless plug]
For the DO's and DON'Ts part, I can recommend you my article, SQL Server Do's and Dont's[^]
[/shameless plug]
I see dumb people
|
|
|
|
|
thanks that site is a great help - confirmed somethings I'd already picked up by trial and error and taught me 1000x as much more and I still don't feel I read more than 1% of the site
Technically speaking the dictionary would define Visual Basic users as programmers.
But here again, a very generalized, liberal definition is being employed and it's wrong
- just plain wrong - Tom Archer 5/12/02
|
|
|
|
|
i am using the following SQL statement to populate a datagrid in VB, it executes fine when i test it in access but it does not work in vb - what is the problem? thank you!
Dim strCompany As String
Dim strLikeCompany As String
strCompany = txtCompany.Text
strLikeCompany = strCompany & "*"
Dim findSQL As String
findSQL = " Select ClientCode, Company, ContactName " & _
" from Client " & _
" Where Company LIKE '" & strLikeCompany & "'"
Set RS = Conn.Execute(findSQL)
|
|
|
|
|
Are you definitely using the same strLikeCompany value when testing on Access and VB?
Cheers,
Simon
"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg article
|
|
|
|
|
Hi !
I'd want to be able to visualize data from a huge database's table using a ComboBox (like Access ComboBoxes).
In fact, I've got 50000 records in my table (from MySQL using Odbc), the data loading time into the DataTable is quite good but when I want to dropdown the ComboBox, it takes 3 or 4 seconds to paint it.
My question is : Do you know a ComboBox control that can display the data progressively from its datasource member ? or Do you have another solution ?
Please help me, it's very important to get a faster painting time for my app.
Thanks
jpeg
|
|
|
|
|
Hi..
do you use a static cursor ?
I think you will get better results if you use dynamic cursor..
let me know if this works
|
|
|
|
|
Thank you for your fast answer !
First of all, I've forgotten to say that my program is written with C# and I use DataSet/DataTable classes.
Next, I'm a newbie with developing Databases' app and I don't know if I use a static or dynamic cursor. So, can you tell me how do you use static/dynamic cursor with these classes ?
jpeg
|
|
|
|
|
Hi..
As you are using C# so you don't use ADO .. you use ADO.NET which does not support dynamic cursor because ADO.NET is based on the disconnected model (you get all the data once then you close the connection)
so for your problem I recommend you to use DataReader
read this carefully:
in MSDN:
You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Using the DataReader can increase application performance and reduce system overhead because only one row at a time is ever in memory.
|
|
|
|
|
I'll try to fill my ComboBox "dynamically" with a DataReader object but I think it won't be easy because of the stream's 'Forward Only' property. 
I can't understand that the Microsoft Framework don't provide any class/control to visualize data dynamically & automatically in a control (old MS Access versions were able to do this) : It's incredible, don't you think ?
Thank you for your help.
jpeg
|
|
|
|
|
I agree with you that .NET has some strange things..data bound controls in the VB6 way was great..
ADO was much better than ADO.NET which I did not understand yet why it's based on the disconnected model, while ADO can give you the same functionality if you need..
So I never liked ADO.NET !!
|
|
|
|
|
jpeg wrote:
it takes 3 or 4 seconds to paint it
I tried to load 50,000 elements into a combobox and it painted instantly.
What machine are you running off?
When are you doing the actual load into the combobox? i.e. which event
Cheers,
Simon
"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg article
|
|
|
|
|
My developing machine is a 1.7 Ghz Celeron. So it's a quite good machine. This is not the problem. Maybe the problem is that I use C#. The initializing code (which is located in the form's loading event-handler) is :
DataTable mytable=LoadMyTable();
myComboBox.DataSource=mytable;
myComboBox.DisplayMember="myfieldname";
All this code is fast (I see it in debugging mode) but the first painting takes a lot of time (Maybe because of the internal control initializing method). After the first painting, the dropdown takes a little time.
If you have a piece of code that permit the control to be painted instantly, I'm highly interested !!!
jpeg
|
|
|
|
|
Try and populate the combobox manually.
The delay may be in the data binding itself as painting a 50,000 element combox is very quick.
Cheers,
Simon
"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg article
|
|
|
|
|
Hi!
I want to do a simple SELECT on a table, returning the count of records containing a greater date than a certain date I write in the query expression.
Using Access, I'd simply write:
SELECT Count(1) As nMsg FROM Bacheca WHERE ([Date] > #2003/01/01 13:59:07#)
But that select statement returns an error when used in SQL Server, it says Syntax error near '13'. Can you tell me how to fix it?
And, I'd like to make that query work for both Access and SQL Server - is it possible?
Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)
|
|
|
|
|
Use ' instead of # and treat it as a string, e.g.:
SELECT Count(1) As nMsg FROM Bacheca WHERE ([Date] > '2003/01/01 13:59:07')
This won't work in Access though, AFAIK you'll need to know before hand and use the correct query for each one. Can anyone confirm or deny that?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
