|
The WHERE clause isn't used to specify which columns you want to retrieve, but to limit the amount of records returned. It's a filter 
What you want to do can only be done using dynamic SQL. You'll have to know what columns you want to retrieve, or use a wildcard (*) to retrieve them all.
I are Troll 
|
|
|
|
|
I had a hard time trying to decide whether I should post this in the database side of things or the vb.net.
This is coming from a vb.net 3.5 application connecting to a SQL Server 2008 database. We currently have this application on 50 different SQL Servers though all of these other ones are SQL Server 2000/2005.
This section of a code comes from a routine where we are validating that the database exists and makes sure that it is up to date. By this point we have already validated that the connection string (strADOConnString) is valid and that we can connect to the server. When we run this section of code where we check for the database name in the server the only difference between this and our normal connection string is that we are looking at the "master" database instead of our database. This section of code blows up with the "Failed to connect to server" error message.
I've validated that the user does have the proper permissions on this server and this is further verified by our ErrHandler routine which writes the error to the database using the strADOConnString. I've tested this locally using SQL Server 2008 and have had no issues. They have tested this using the SA account leading me to suspect that it is not a simple permissions issue. I've made sure that they can logon to SQL Server Management Studio with this user with no pop ups and I've made sure that they can connect to the server through an ODBC with this particular SQL user.
What else could be causing this error?
Dim strADOConnStringMaster As String = strADOConnString
strADOConnStringMaster = strADOConnStringMaster.ToString.Replace("Initial Catalog=" & objSystem.DatabaseName & ";", "Initial Catalog=master;")
Try
Dim connection As New SqlConnection(strADOConnStringMaster)
server = New Server(New ServerConnection(connection))
bDbExists = server.Databases.Contains(objSystem.DatabaseName)
connection.Close()
Catch ex As Exception
strStep = ".STEP01"
Call ErrHandler(_moduleName & strStep, _className, String.Format("Server defined in database configuration does not exist. Message:{1}.", lDBVersion.ToString, Err.Description), Not bCmdLine)
If Not bCmdLine Then
MsgBox("Unable to locate server defined in database configuration.", MsgBoxStyle.Critical)
End If
Exit Sub
End Try
Some people sail through life on a bed of roses like a knife slicing through butter.
|
|
|
|
|
Hi,
1.
IMO this is the right forum to ask.
2.
I'm no DB specialist at all, however I'll give you my ideas anyway.
3.
I suspect the line
strADOConnStringMaster = strADOConnStringMaster.ToString.Replace("Initial Catalog=" & objSystem.DatabaseName & ";", "Initial Catalog=master;")
for a couple of reasons:
a. I fail to see why ToString is used there
b. if the search fails, strADOConnStringMaster remains unchanged and nothing gets signaled. This could be caused by a difference in white space, in casing, in any minor detail.
So I'd suggest you clean up and code more defensively, if the replace has to happen, make sure it does, e.g. by storing the result in a different variable.
4.
I'm not familiar with the Err object (I use C# most of the time), however I doubt Err.ToString() is as informative as Exception.ToString() is, so I suggest you show ex.ToString() in a MessageBox.Show when an exception gets caught.
Hope this helps.
|
|
|
|
|
Without changing your connection string, you should be able execute this SQL statement:
SELECT name
FROM master..sysdatabases
You could then determine whether or not your database is installed on that server.
This of course will only work if the user you are connecting with has the proper permissions to access the master tables.
Give it a shot. 
|
|
|
|
|
Hi
Database: SQL Server 2005
select 589/5 produces 117
How can I make it return 117.8?
I am doing the division operation on 2 int columns and want the output in decimals. Do I have to make the columns themselves decimal?
Thanks
Shreekar
|
|
|
|
|
OK - I have tried the below:
declare @d decimal(10,2)
select @d = 859 * 1/5
select @d
This returns 171.00
and
declare @d decimal(10,2)
select @d = 859.00 * 1/5.00
select @d
This returns 171.80
This tells me that the 2 numbers participating in the operation have to be decimals by definition.
Is there any way to work around this as I do not want to change the column types to decimals as I do not expect them to hold fraction data any time.
It is only the result that may have fraction data.
Any ideas?
Shreekar
|
|
|
|
|
Cast[^] it;
SELECT CAST(859 AS DECIMAL) / CAST(5 AS DECIMAL)
I are Troll
|
|
|
|
|
Just missed your post. Realised the same solution as you suggested.
Thanks for the confirmation!
Shreekar
|
|
|
|
|
You're welcome. Thinking out loud helps, even on a messageboard
I are Troll
|
|
|
|
|
Figured it out myself, I cast the operands themselves to decimals and the product was decimal.
Sorry about that - turned out to be loud thinking than a genuine question.
Shreekar
|
|
|
|
|
It's like buses. You stand around waiting for an answer, and then three come along all at the same time.
|
|
|
|
|
declare @n integer
declare @d decimal(10,2)
select @n = 859
select @d = CAST(@n AS decimal(10,2)) / 5
select @d
Returns 171.80
|
|
|
|
|
in most programming languages, when all numbers are integer, division will truncate. You can promote a (sub)expression to floating-point in several ways: by inserting a floating constant, by having at least one floating variable, by casting, by converting.
As a simple experiment, I would prefix 1.0* to whatever expression you are having.
Warning: this rule also applies to subexpressions, so 2/5 will be zero, whereas 1.0*2/5 would not!
|
|
|
|
|
Multiplying by 1.0 is definitely easier on the eyes than multiple nested cast statements and produces the same output - at least in my case because there is only one operation.
So thanks for that.
Earlier, I had read posts alluding to this technique but I had missed the point - now I get it.
Shreekar
|
|
|
|
|
you're welcome.
|
|
|
|
|
One word of warning with this technique, the following give different results:
859 / 5 * 1.0
1.0 * 859 / 5
If you go down this route, you will need to understand how SQL Server applies its rules for implicit datatype conversion.
|
|
|
|
|
I have a legacy MS Access "application" that uses MS Flex Grid. I've had to create some new forms - copies of existing forms (basically identical except against different tables - same structure different names) but the new forms throw errors on anything to do with the flexgrid. The errors are generally of the form "The expression <<egmouse move="">>you entered as the event property setting produced the following error: There was an error loading an ActiveX control on one of your forms or reports". It only happens on the new copies of forms that contain flex grid. Forms that already existed with flex grid on them still work.
I've unregistered and re-registered flex grid. I've checked through the registry to see if anything is amiss - nothing obvious there. When I look in the Toolbox in Access there are two Microsoft FlexGrid Control V6.0 SP6 shown ... I can't work out why - there aren't two shown in references.
I've tried it on 2 different PCs. I'm using MS Access 2003.
Can anybody help here please? Thanks in advance.
|
|
|
|
|
Don't know whether it applies to MS-Access, but in VB6 you'd have to declare it "WithEvents", somewhat similar to below;
Private WithEvents myWobblyGrid As MSFlexGrid
I are Troll
|
|
|
|
|
Thanks for your reply ... I don't think that is the issue because the original form (that I copied for the new form) doesn't have a problem. MS Access is just evil ...
|
|
|
|
|
I found the issue - the FlexGrid version on my PC(s) was incorrect. I installed VB6 and patched to SP6 then downloaded the Microsoft Visual Basic 6.0 Service Pack 6 Cumulative Update (see http://support.microsoft.com/kb/957924[^]) Only then did I get the latest version (from March 2009) of FlexGrid.
|
|
|
|
|
Having a strange problem on a legacy system.
In one of the records if the user tries to update it they get the following error
"This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
Options are Save Record (greyed out), Copy to Clipboard, and Drop Changes."
We've tried the follow
1. updating the record manual in SQL Server Management Studio - This works
2. Updating the record in Access, through the link table and we get the above error
if we drop the linked table and rebuild we get same error if we update the table in Access or on the form.
any other suggestions?
Simon
As barmey as a sack of badgers
|
|
|
|
|
Declare @idOrder as nvarchar(10)
set @idOrder = '144'
Declare @sInvoice as nvarchar(50)
set @sInvoice = 'UK_B_IN000051'
Declare @DateRange as nvarchar(100)
set @DateRange = '28Jul2010'
Declare @Country as nvarchar(10)
set @Country = substring(@sInvoice, 1, 2)
Declare @TableName as nvarchar(500)
set @TableName = 'splashdb..Comersus_' + @Country + '_Orders_' + @DateRange
what i want is: select * from @TableName, but sql server returns me error. If someone in here can shed me a light would be really appreciated, or there is an alternative way to achieve the same goal?
|
|
|
|
|
You have to construct a query by combining required variables and pass that to EXEC[^] TSQL function.
Best wishes,
Navaneeth
|
|
|
|
|
Thank very much, i knew the one that you mentioned, but this way is not what i want, coz it is not feasible for me since i have a very long select statement.
|
|
|
|
|
It look to me that your database is a total mess. Why don't you have separate columns for region and date in one and the same table.
Then you could make the query as simple as
Select * from Comersus where sInvoice = 'B_IN000051' and Country = 'UK' and Date = '28Jul2010'
where Date naturally shouldn't be a string but an actual Date
"When did ignorance become a point of view" - Dilbert
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
