Query With SHAPE Command In Query Analyzer ???? Please Help!
Hi,
I am writing a complicated SQL that deals with SHAPE command.
I am using MSSQL 2000. Ok.
The problem is that Query Analyzer dont recognizes SHAPE sintaxe (gives me a "[Microsoft][ODBC SQL Server Driver] Sintaxe error or access violation" message), then is a pain-in-the-*** to keep testing the query directly in VB.
Can anyone tell me if there is some SQL environment where I can test queries with SHAPE command, or if there is any way to trick Query Analyzer to force it to understand this command?
Thanks!
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Executing Query Analyzer Query File
Is it possible to execute a Query Analyzer Query File from within VB6? If so, can you show me how? Can the Query Analyzer Query File be converted into a SP?
Thanks in advance!!
Multiple Table Query/reporting Question...SHAPE Command...
I will try to make this question as clear as possible!
I have a big database which I have built to keep track of employee work. Monthly they enter in how much time they spent in a variety of categories (each category is in a seperate table: ie- ContractHours, FieldWork, etc)
A MainRecord table keeps track of names and entry numbers. In each category their hours are linked to the main table by Entry #.
So the table structure looks like this with fictious data:
MAIN TABLE
Entry# - 45
Name- Lisa
Month - May
Year - 2002
CONTRACT HOURS TABLE
Entry# - 45
Interviewing- 5 (represents 5 hours spent on this activity)
Surveying- 16
Researching- 10
FIELD WORK TABLE
Entry# - 45
Working - 15
Sitting- 30
SettingUp - 20
( this is very much sample data, obviously! In "real" life I have hundreds and hundreds of fields possibly associated with each entry # but they are spread out into the category tables!). OK, so using this fake data, this is what I am trying to do!
At the end of each month, when all employees have entered their work units, I need to compile and report how much time has been spent overall in each category. Therefore, I first of all query out all entries from the MAIN TABLE that are for the Month/Year I am reporting. I put those entries into a new temporary table using a make table query so that I have something I can work with and not worry about destroying my main records.
With the entries matching month/year (ie- I wanted to report May 2002...I would be interested in getting all records that pertain to entry# 45 ..and any other entries that existed). In "real" life I will have up to 13 entry numbers at a time I will be reporting for.
How can I take the list of entries and query for all records?
Any ideas on the best way to handle a huge query like this?
Is there a way to query it "on the fly"?
Would it be better to use VB code instead of the Access query builder in this case? and use a For Each loop...?
I have explored ActiveX, pivot tables, data access pages.....I'm relatively inexperienced and am not sure what approach to take!
Really i'm open to any suggestions! Thanks for taking the time!
Edited by - LisaRae007 on 10/16/2002 2:28:50 PM
Help Me About SQL Query Analyzer
open a server (ServerA) from SQL Query Analyzer(SQA) and type these code:
Code:
declare @rmtsrvname nvarchar(30)
set @rmtsrvname='ServerB'
if (not exists(select * from master..sysservers where srvname=@rmtsrvname) )
Execute sp_addlinkedserver @rmtsrvname
exec sp_addlinkedsrvlogin @rmtsrvname,false , NULL, 'sa','aaa'
exec ('select * from ' + @rmtsrvname +'.tableName.dbo.t_datalog')
if ServerA and ServerB in the same domain, it is ok
otherwise ,fall,return 7411 error
I do not know why, ask for help.
thanks in advance
SQL In Query Analyzer
Hello all,
I am starting to use Query Analyzer to pull data from an SQL server DB. I am trying to figure out if there is a way to filter on a defined field. I know how to do this in other SQL applications (Access, etc.). Here is code that some in my group use:
‘DEFINED_FIELD =
CASE
WHENRPT.LINE_DESCRIPTION = 'This' AND RPT.GROUP_DESCRIPTION = ‘That’ THEN
‘X’
WHENRPT.LINE_DESCRIPTION = 'Mine' AND RPT.GROUP_DESCRIPTION = ‘Yours’ THEN
‘Y’
END,
From there, other people write code to filter on either RPT.LINE_DESCRIPTION or RPT.GROUP_DESCRIPTION or both.
This seems like extra work and code when I should be able to filter on 'X' or 'Y' like I could if I used an IIF function in SQL for Access.
Thanks much
SQL Query Analyzer
Hi All,
I have a table that has several fields that contain <NULLS>. I need to replace all <NULLS> fields with data such as "INDIANA". Is there a way using the SQL Query Analyzer to accomplish this? I'm familiar with using the Analyzer but don't know if there is code that would execute a such a change across the entire table.
Cinnamon
Xml In Sql Query Analyzer
I read in a SELECT clause in query analyser
SELECT a, b, c FROM tblA INTO XML FOR AUTO
I get xml string
Now I need to UPDATE a field in tblB with this xml string. Could someone please tell me how do we do that.
Then I need read that data in VB. That will be next though
thanks
Query Analyzer
Hey, SQL2000 let's you connect to a remote SQL DB BUT in the Analyzer you can only connect to DB on the network! CAN I connect to a remote DB the same way as through the Enterprise Manager?
Thanks
SQL Works In Query Analyzer Not In ADO
The SQL statement below works fine when I use the MS SQL 2000 Query Analyzer but when I try it in the VB IDE using the Execute method of a Connection object in ADO 2.5 I get a Null value inserted into my table instead of the @@Identity. Any clue why that is?
Insert into TestTbl (Hello) Values (@@Identity)
The example above is not a simplified version of something more complicated that I am trying to do, but more of a proof of concept before I even attemp anything more complicated.
Thanks in advance
Query Analyzer Oddity
I have a stored procedure:
Code:
CREATE PROCEDURE sp_TEST_CrystalReport
AS
BEGIN
SELECTCTS_Invites.Study,
InviteSource,
Specialty,
COUNT(Specialty) AS Invites,
Accesses = SUM(CASE Accessed WHEN NULL THEN 0 ELSE 1 END),
Completions = SUM(CASE Completed WHEN NULL THEN 0 ELSE 1 END),
CAST(CAST(CAST(CAST(SUM(CASE Accessed WHEN NULL THEN 0 ELSE 1 END)
AS DECIMAL(9,2)) / CAST(COUNT(Specialty) AS DECIMAL(9,2))
AS DECIMAL(9,2)) * 100 AS INTEGER) AS VARCHAR(10)) +
'%' AS AccessRate,
CAST(CAST(CAST(CAST(SUM(CASE Completed WHEN NULL THEN 0 ELSE 1 END)
AS DECIMAL(9,2)) / CAST(COUNT(Specialty) AS DECIMAL(9,2))
AS DECIMAL(9,2)) * 100 AS INTEGER) AS VARCHAR(10))
+ '%' AS ResponseRate,
MAX(Accessed) AS LastAccess,
MAX(Completed) AS LastCompletion,
Fielded,
AvgTime
FROMCTS_Invites
INNER JOIN CTS_StudyStatus
ON CTS_Invites.Study = CTS_StudyStatus.Study
INNER JOIN VIEW_AllAverageTimes
ON CTS_StudyStatus.Study = VIEW_AllAverageTimes.Study
GROUP BYCTS_Invites.Study,
InviteSource,
Specialty,
Fielded,
AvgTime
ORDER BYCTS_Invites.Study,
InviteSource,
Specialty
END
GO
When I run the SP from Query Analyzer, I get the expected results:
Quote:
Originally Posted by Query Analyzer
sp_TEST_CrystalReport
ACTS5/05AACNA39252164%54% (etc)
ACTS5/05AACNOTO2411646%25% (etc)
ACTS5/05AMMSA35649914%3% (etc)
However, when I copy-n-paste the SELECT statement of the SP into QA, the results change (note the numbers and percents):
Quote:
Originally Posted by Query Analyzer
ACTS5/05AACNA393939100%100% (etc)
ACTS5/05AACNOTO242424100%100% (etc)
ACTS5/05AMMSA356356356100%100% (etc)
Why on earth would the results change in QA when calling the SP above directly versus running the SP's underlying SQL directly in QA?
Query Analyzer, Vb And Reporting
Hello once again i have a doubt related to the reporting style which sql query analyzer does..
I have a sql statement "Select awbno, shipper, weight from awb where dtofiss='01/01/01' and ib_ob=i" . When i run this particular sql statement in a query analyzer the result is displayed in a text pane with fields nicely formatted.. ( space maintained in between them )
I was wondering if we could achieve the same kind of reporting from visual basic through code .. Any help is this regard would be appreciated.. Thanks
For those who havent seen a query analyzer.. Here how the text file [report] should look.. The report should be a text file only.
Awbno Shipper Weight
--------- ---------- ---------
12345 Samsung 0.100
78910 Nokia 0.200
98765 Motorola 0.500
Sql Query Analyzer No Time-outs?
Whenever i execute a query on Analyzer, whether short o long large query, it gave an output, though it takes sometimes a long time. but in my own code, it tends to stop and cause an error of 'connection timed out' .... i already set my Connect Timeout high.
Anyone can help on what parameters on a connection string is missing or should be changed .thanks so much...
Query Analyzer Date Formatting
I hope someone can give me some insight on how to deal with a date formatting problem using Query Analyzer. I am pulling a date field that has a time stamp (yyyy-mm-dd hh:mm:ss). I need to use some sort of date format function that will drop the time so that when I paste into Excel, it's just m/dd/yyyy. Normal SQL Server/Access functions seem to not apply in Query Analyzer. One thing to note is that I am pulling from the database and inserting results into a local table. So my final report is actually pulling from my local table.
Please Help!
*** Please disregard. I figured it out!!!
Used: CONVERT(NVARCHAR(10),myDate,101)
Quick Question Using Query Analyzer
A column in one of my tables has a bunch of data in it. I need to set all that data to NULL. I am using the query analyzer to do this. I need a little help with the sql.
I have so far:
SELECT propertynum FROM icipcap
SET propertynum = NULL
I thought this would be easy, but its giving me hell. Could some tell me what I am doing wrong.
Error:
Incorrect syntax near "="
Thanks
SP With Parameters Running In Query Analyzer But Not In VB
SP with Parameters running in Query Analyzer but not in VB
Hi,
I have created a following Stored Procedure to retrieve all the records from the database.
If no paramter value is passed to this SP, it will return all the values, else it will return
the value based on the parameter passed to it.
CREATE PROCEDURE
sprSelect_Database
@strDatabaseName varchar(50)=''
AS
if @strDatabaseName<>''
Select DatabaseName from tblDatabaseMaster
Where DatabaseName=@strDatabaseName
Order By DatabaseName Asc
else
Select DatabaseName from tblDatabaseMaster
Order By DatabaseName Asc
GO
To my surprise, this SP is running from SQL Query Analyzer, on both passing and not passing of
database name, but when I open a recordset in VB, it is returning nothing in both the cases.
The code of the recordset goes like this:
Dim objConnection as ADODB.Connection
Dim rsGetData as ADODB.Recordset
Dim strSQL as String
Set objConnection = New ADODB.Connection
objConnection.Open "Supplied a Connection string here"
strSQL = "sprSelect_Databases"
rsGetdata.Open strSQL, objConnection, adOpenStatic, adLockReadOnly, 4
Please not that sinple SP's where I am not using any parameters are returnig data.
Please Help.
SP With Parameters Running In Query Analyzer But Not In VB
SP with Parameters running in Query Analyzer but not in VB
Hi,
I have created a following Stored Procedure to retrieve all the records from the database.
If no paramter value is passed to this SP, it will return all the values, else it will return
the value based on the parameter passed to it.
CREATE PROCEDURE
sprSelect_Database
@strDatabaseName varchar(50)=''
AS
if @strDatabaseName<>''
Select DatabaseName from tblDatabaseMaster
Where DatabaseName=@strDatabaseName
Order By DatabaseName Asc
else
Select DatabaseName from tblDatabaseMaster
Order By DatabaseName Asc
GO
To my surprise, this SP is running from SQL Query Analyzer, on both passing and not passing of
database name, but when I open a recordset in VB, it is returning nothing in both the cases.
The code of the recordset goes like this:
Dim objConnection as ADODB.Connection
Dim rsGetData as ADODB.Recordset
Dim strSQL as String
Set objConnection = New ADODB.Connection
objConnection.Open "Supplied a Connection string here"
strSQL = "sprSelect_Databases"
rsGetdata.Open strSQL, objConnection, adOpenStatic, adLockReadOnly, 4
Please not that sinple SP's where I am not using any parameters are returnig data.
Please Help.
SQL Statement In VB6 And Running In Query Analyzer
SQL server 2000, VB 6
SQL statement: select SwitchID, TrafficDate from RawData whereTrafficDate = '25-apr-2006 00:00:00'
When I run the query in query analyzer of SQL server 2000, it is OK.
When I run it through VB6 code, it returns an error "Data type mismatchin criteria expression".
What makes the difference?
The value '25-apr-2006 00:00:00' is returned from a function:
************************************************** *************
Function fdt(dtDateTime As Variant)
If IsNull(dtDateTime) Then
fdt = "null"
Else
fdt = "'" & Format(dtDateTime, "dd-mmm-yyyy hh:mm:ss") & "'"
End If
End Function
************************************************** **************
The same error happens whether I define fdt as variant or leave it open.
Many thanks!
Jonathan
SP With Parameters Running In Query Analyzer But Not In VB
SP with Parameters running in Query Analyzer but not in VB
Hi,
I have created a following Stored Procedure to retrieve all the records from the database.
If no paramter value is passed to this SP, it will return all the values, else it will return
the value based on the parameter passed to it.
CREATE PROCEDURE
sprSelect_Database
@strDatabaseName varchar(50)=''
AS
if @strDatabaseName<>''
Select DatabaseName from tblDatabaseMaster
Where DatabaseName=@strDatabaseName
Order By DatabaseName Asc
else
Select DatabaseName from tblDatabaseMaster
Order By DatabaseName Asc
GO
To my surprise, this SP is running from SQL Query Analyzer, on both passing and not passing of
database name, but when I open a recordset in VB, it is returning nothing in both the cases.
The code of the recordset goes like this:
Dim objConnection as ADODB.Connection
Dim rsGetData as ADODB.Recordset
Dim strSQL as String
Set objConnection = New ADODB.Connection
objConnection.Open "Supplied a Connection string here"
strSQL = "sprSelect_Databases"
rsGetdata.Open strSQL, objConnection, adOpenStatic, adLockReadOnly, 4
Please not that sinple SP's where I am not using any parameters are returnig data.
Please Help.
Error In SQL ADO, Works Fine In Query Analyzer
I am having an issue using a function in my SQL statement, but it works 100% fine in Query Analyzer.
I am running SQL Server, using ADO in VB6.
What I am trying to accomplish is to encrypt credit card numbers that I insert into the database. I have a function that will do this for me, and when run in query analyzer, it runs perfectly (I copied & pasted the exact SQL from a debug.print)
When I attempt to run it through code in VB, I get the error:
Run-time error '-2147217900 (80040e14)':
The name 'fn_sqEncrypt22' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Any help is appreciated
VB Stored Proc Error, But Not In Query Analyzer
Why would a stored procedure error when called from VB using ADO but when the command text is entered into query analyzer, it would work correctly?
VB returns an error about an unclosed quotation mark, that I can’t find anywhere in the statement. So I copied the command text and ran in query analyzer and it worked fine with no errors…HELP?
Sending Text To Microsoft SQL Query Analyzer
Hello,
Is there a way to send a string to SQL Query Analyzer to be executed? I would like to make an application that by one click would send test to query analyzer and run it from there.
Thanks for your help!
Edited by - Shandy on 6/1/2004 11:22:21 PM
Stored Procedure Fails But Query Analyzer Works?
Code:
SELECT TOP 100 PERCENT Tb2.MemNo, Tb3.LastContributionDate, Tb3.TimesContributed, Tb3.ContributionsTotal,
Tb2.Principal, Tb2.Agency, Tb2.Address1,MemberType, dbo.[tblLOOKUP-County].County FROM dbo.tblProspectMember Tb2 LEFT OUTER JOIN
(SELECT DISTINCT
(Tb1.Memno) AS NewMemno, MAX(Tb1.ContributionDate) AS LastContributionDate, COUNT(ContributionAmt) AS TimesContributed,
SUM(ContributionAmt) AS ContributionsTotal
FROM TblPacContributions Tb1
WHERE ContributionAmt > 0 AND Year(tb1.ContributionDate) != '1991'
GROUP BY Tb1.Memno) Tb3 ON Tb3.NewMemno = Tb2.MemNo
INNER JOIN dbo.[tblLOOKUP-County] ON Tb2.COC = dbo.[tblLOOKUP-County].ID
WHERE (Tb2.MemberType IN ('C', 'E', 'F', 'G', 'L', 'N') AND ASN = '31')
ORDER BY Tb2.MemNo
For SOme Reason When I drop this into a stored procedure it says it was successfull but didnt return any results yet when I put it in Query analyzer it works 100%
in My Stored Procedure I use:
Code:
If @Contributed = '2'
If @MyAsn = 06
SELECT TOP 100 PERCENT Tb2.MemNo, Tb3.LastContributionDate, Tb3.TimesContributed, Tb3.ContributionsTotal,
Tb2.Principal, Tb2.Agency, Tb2.Address1,MemberType, dbo.[tblLOOKUP-County].County FROM dbo.tblProspectMember Tb2 LEFT OUTER JOIN
(SELECT DISTINCT
(Tb1.Memno) AS NewMemno, MAX(Tb1.ContributionDate) AS LastContributionDate, COUNT(ContributionAmt) AS TimesContributed,
SUM(ContributionAmt) AS ContributionsTotal
FROM TblPacContributions Tb1
WHERE ContributionAmt > 0 AND Year(tb1.ContributionDate) != @MyYear
GROUP BY Tb1.Memno) Tb3 ON Tb3.NewMemno = Tb2.MemNo
INNER JOIN dbo.[tblLOOKUP-County] ON Tb2.COC = dbo.[tblLOOKUP-County].ID
WHERE (Tb2.MemberType IN ('C', 'E', 'F', 'G', 'L', 'N') AND ASN = @MyAsn)
ORDER BY Tb2.MemNo
anybody have any clua as to this one..baffled me for a cpl hours now..
ADO Empty Recordset But Query Analyzer Returns Records
I have code that was developed in VB6 (SP5) that ran without problems on my development SQL Server. However on the production SQL Server 2000, I am finding that a recordset that I attempt to open returns 0 records.
I open an "Order" recordset and then attempt to access related "Items" recordset. I find the order record in all cases, but cannot find the related item records with the new recordset.
The SQL query yields the number of expected records with SQL Query Analyzer, but returns an empty recordset within my VB code. I grab the exact SQL statement used in VB/ADO code to perform this check. Previously, the code on the production server was working whereas now it never returns the ITEMS records I would expect.
I have reviewed the connection object and it is returning NO ADO Errors. I cannot determine why I am getting no records now but was not before. The code still works flawlessly on my development server. The same exe is running on both systems. The database is identical between the systems. Both machines are running the same version of SQL (2000) and the same OS (Win2K Server).
Any ideas? Could there be some "locks" that are preventing access to the tables? I can review any of the SQL settings but I am unsure where to look.
Thanks
Connect To SQL Server Via Enterprise Manager/Query Analyzer
Ok all,
I finally got all my databases set up and what-not on my home server. Now what I want to do is connect (register) the database on my laptop and work PC using the Enterprise Manager and/or Query Analyzer. How do I go about doing this?
Shape Query Possibly Again
Greetings,
I am trying to use the Shape, and none of the examples seem to cover my exact use case. My situation is I have 3 tables, OrdHdr, OrdHdrExtra, and OrdDetail. Most of what I want is in the first two. All have the common key called OrderID. Now, what I want to be able to do is do a joined query on the first two tables, then use the Shape Append to add the one or more rows in the 3rd table that correspond to the particular row in the first part of the query.
My main query is SELECT * from Ord Hdr OH, OrdHrdExtra OE
WHERE OH.OrderID = OE.OrderID
This gets me all the header info. Now, in the OrdDetail table will be anywhere from one to many records corresponding to each row returned in the above query.
The query I tried is as follows:
SHAPE {SELECT * FROM OrdHdr OH, OrdHdrExtra OE Where OH.OrderID = OE.OrderID} APPEND ({SELECT * FROM OrdDetail WHERE OrderID = ?} AS ODField RELATE OrderHeader.OrderID To Parameter 0)
The idea is to embed another record set into the current record set for easy retrieval while I'm cycling through my parent record set.
The error is the "Reference to column OrderID is ambiguous".
Is it ambiguous because I'm doing a joined query in the parent? Is there an easy way around this?
Any advise is appreciated.
Dang! This problem is so easy to solve using JDBC/SQLJ and Oracle.
-OttawaVB
P.S. If anyone can show me how to configure the SQL Query analyzer in SqlServer 2000, that would also be really helpful.
"Query Analyzer"
Is there a way where I can read Ms-Access tables from "Query Analyzer".
Appreciate your time
Mohammed.
Command Query
Hi Everyone,
Does anyone know how to retreive a recordset field from an Access database query using a stored procedure? I am using the following code, but I cannot get anything to show up inside of the textbox. Any help would be appreciated. Thank you.
Code:
Dim cmdMember As Command
Dim sngTotalAmount As Single
Set cmdMember = New Command
cmdMember.ActiveConnection = conn
cmdMember.CommandType = adCmdStoredProc
cmdMember.CommandText = "Amounts"
recData.Open cmdMember.Execute(, Array(CLng(txtID.Text)))
Do While Not recData.EOF
sngTotalAmount = sngTotalAmount + recData.Fields("Amount").Value
recData.MoveNext
Loop
Text2.Text = sngTotalAmount
Changing Query In Command
hi,,
i have created a command under my dateEnvironment connection. the command has a sql query. please tell me how to change that sql query at runtime or how to use parameters with that query.
thanx.
Dataenvironment Command Sql Query
Hi All,
I am running datareport using dataenvironment ...the comman object of dataenvironment uses following query and its working...
(SELECT invoice.invoiceID, invoice.date, invoice.customername invoice.total, invoice_lineitem.lineitem, invoice_lineitem.qty invoice_lineitem.price_each, invoice_lineitem.price_total FRO invoice INNER JOIN invoice_lineitem ON invoice.invoiceID =3 invoice_lineitem.invoiceID )
I see datareport running perfectly above query show al Invoices.... Now i want to see particular invoice by InvoiceI .So I add where clause ...its working too..
(SELECT invoice.invoiceID, invoice.date, invoice.customername invoice.total, invoice_lineitem.lineitem, invoice_lineitem.qty invoice_lineitem.price_each, invoice_lineitem.price_total FRO invoice INNER JOIN invoice_lineitem ON invoice.invoiceID =3 invoice_lineitem.invoiceID WHERE invoice.invoiceID like '5050')
Invoice form has txtInvID text box which holds InvoiceID. Use click print button and with click of this button Invoice ge stored in database plus datareport1 appears.
Now my problem is ........
How to pass Invoice ID ( entered in txtInvID text box of Invoic form ) to dataenvironment1's command object where we store sq query?
I created Variable like Dim InvID as string.... also Public InvI as string....etc...and tried to add to sql query.....Select ** ..where invoice.invoiceID like ' " & InvID & "' ....and .....' & txtInvID.Text & " '........' " & Invoice.txtInvID.Text & '................= 2E..but its not working..............
Please Advice
Regards
Santosh
[query]CDMA AT COMMAND
Hi
This is smita .
i have problem with CDMA AT COMMANDS
i have successfully implemented the AT commands in GSM for SMS sending
BUt in CDMA commands are !SSMS is not working..
Please if anybody has idea tell me soon..
---ooo--- Internet Confidentiality Statement ---ooo---
The information contained in this communication is confidential and is
intended only for the use of the recipient named above, and may be legally
privileged and exempt from disclosure under applicable law. If the reader
of this message is not the intended recipient, you are hereby notified that
any dissemination, distribution or copying of this communication is
strictly prohibited. If you have received this communication in error,
please resend it to the sender and delete the original message and any copy
of it from your computer system. Opinions, conclusions and other
information in this message that do not relate to our official business
should be understood as neither given nor endorsed by this company.
Is This Possible In VB? (add Command Buttons From Query)
Hi.
Can anyone tell me if it is possible to populate (and add) buttons to a form depending on the number of values returned from an sql query?
What i want to do is query my Db and the values returned for a region are pulled up and a button added onto the user form.
Does anyone know of a way to do this?
I'd appreciate any helpful advice.
At the moment i'm using a sleectable flexgrid basically. With large cells, but it look s like crap. The buttons need to be big as it will be used in a touch screen based interface.
Thanks
Brian
Access Command To Check Query
Is there an internal access command to verify that a query's search does not come up with anything at all?
I want a message box to come up with a warning that the query did not come up with any information!
Thanks for the help,
Stephen
Command Button Click Query
Hi experts,
I am writing a simple calculator program and have it more or less completed. I would like to know what is the simplest way to allow a command button to be clicked, without any of the code behind it being executed. I know I could disable the buttons but i would like to visually see the buttons click without them doing anything. (ie following an error message when an attempt is made to divide by zero.) I know I could do this with some if... then... code but if there is a simpler way please let me know.
Regarding The Usage Of A Select Query With Command
Hi,
Please tell me how to get the recordcount if i use a sql statement
steps done are :
1) Opened a connection
2) Opened a recordset
3) Opened a command object
4) The number of records which can be seen by using the recordcount method of the recordset object is not working since it is giving only -1 if there are more >= 1 records also.
THE STATEMENT I used for setting the recordset to the command object's execute statement is as follows :
set rs = cmd.execut(,,adCmdText)
Above this as usual we have to
1) set the command object
2) give the activeconnection which is already opened before this
3) set the commandtext property to the sql query
4) then the exection is done as explained above
PLEASE DO LOOK INTO THIS ASAP AND REVERT BACK ASAP.
THANKS IN ADVANCE,
Eswar Pappu.
Need Help Msacess 2003 Sql Command For Query...
Im a comsci student and have a inventory system thesis... using vb6 and MSaccess 2003:
i have a table for finishproducts as tbl1 and raw materials as tblItem...
and my linker tbl is named tblLinker...
i need to link 2 tables using 1 table and here is my code i linked to tables:
Quote:
SELECT tblItem.ITEMNUMBER, ((tblItem.QUANTITYONHAND)-(tblLinker.Qty)) AS Difference
FROM tblLinker INNER JOIN tblItem ON tblLinker.ItemNumber = tblItem.ITEMNUMBER;
well as it states above is only 2 tables... i need to link tbl1 and tblItem too that if I minus 1 quantity on tbl1 (which is the finished products) the raw materials in tblItem (I defined them as ItemNumber) quantity should also be subtracted... however what it does above is only the tblItem and the the tblLinker...
note: the tblLinker (I defined what specific itemnumber should be used on each finishedproduct in tbl1)
pls help anyone i dont know how to link the tables properly T_T
if you dont understand what i posted above i could post the fields or if necessary an example database of access to u can see it... pls database administrators or anyone help... >_<
Query Using A Do While Loop Command - Resolved
Hiya
I have a combo box with a list of dat and I have a text box.
I need to add in functionality to enable a user to input a letter (or more than one letter) into the text box using a do while loop to do a search in the combo box to find an index position greater than the text entered.
Can anyone assist me with this please?
Thanks
Taz
Command Button To Generate SQL Query
I am very aware that this question has been asked before however I could not find a response that fit my discription. I am conecting to a MYSQL DB on a linux machine using ODBC. My Database is called dispatch, and the table I am trying to query is drivers. The sql command I want to query is
Select Drivers.TruckNum,Drivers.LastName,Drivers.FirstName From Drivers Where
(Drivers.Loaded <> 'Yes') Order By Drivers.LastName
Can some one please help? I need something basic
I tried using the Visual Data Manager, I created a Query but when I tried to save it it said I could'nt, more specifically:
Operation is not supported for this type of object Number:3251
Please help??
Executing And Running A Sql Statement To Create A Query And Save Query In DataBase
I cannot seem to find the syntax needed however to execute the sql or create a query based on the SQL string that I created that can be saved. Can you please take a look at the block of code below and let me know what you think?
Sub Collect_HealthPlan()
Dim Healthplan_Count As Integer 'counts the number of items in Healthplan listbox
Dim CompanySize_Count As Integer 'counts the number of items in CompanySize listbox
Dim CurrentRow As Integer 'Stores index value of current row
Dim ctrlHPlstbox As Control 'Control variable for list box
Dim ctrlNBchkbox As Control 'Control variable for checkbox
Dim ctrlNBEAchkbox As Control 'Control variable for checkbox
Dim ctrlCompSizelstbox As Control 'Control variable for list box
Dim i As Integer
Dim j As Integer
Dim HealthPlan_Selected As Integer
'Dim ConnectDB As New ADODB.Connection
'Dim rsData As ADODB.Recordset
'Dim ADOCommand As ADODB.Command
'Set ConnectDB = Nothing
'Set ConnectDB = New ADODB.Connection
'the block of code below assigns the basic SQL upon which the form will be used to build a query
SQLString = "SELECT [DBP 04 Pipeline Total].[CountOfCompany Name], [DBP 04 Pipeline Total].[Company Name], [DBP 04 Pipeline Total].[Effective Year], [DBP 04 Pipeline Total].[Health Plan], [DBP 04 Pipeline Total].[CountOfDead No Finalist], [DBP 04 Pipeline Total].[Dead No Finalist], [DBP 04 Pipeline Total].[CountOfDead Finalist1], [DBP 04 Pipeline Total].[Dead Finalist], [DBP 04 Pipeline Total].CountOfDTQ, [DBP 04 Pipeline Total].DTQ, [DBP 04 Pipeline Total].CountOfSold, [DBP 04 Pipeline Total].Sold, [DBP 04 Pipeline Total].[Opportunity Type], [DBP 04 Pipeline Total].[Quoted MBRS], [DBP 04 Pipeline Total].[Quoted Subs], [DBP 04 Pipeline Total].[Company Size]"
SQLString = SQLString & " " & "FROM [DBP 04 Pipeline Total] GROUP BY [DBP 04 Pipeline Total].[CountOfCompany Name], [DBP 04 Pipeline Total].[Company Name], [DBP 04 Pipeline Total].[Effective Year], [DBP 04 Pipeline Total].[Health Plan], [DBP 04 Pipeline Total].[CountOfDead No Finalist], [DBP 04 Pipeline Total].[Dead No Finalist], [DBP 04 Pipeline Total].[CountOfDead Finalist1], [DBP 04 Pipeline Total].[Dead Finalist], [DBP 04 Pipeline Total].CountOfDTQ, [DBP 04 Pipeline Total].DTQ, [DBP 04 Pipeline Total].CountOfSold, [DBP 04 Pipeline Total].Sold, [DBP 04 Pipeline Total].[Opportunity Type], [DBP 04 Pipeline Total].[Quoted MBRS], [DBP 04 Pipeline Total].[Quoted Subs], [DBP 04 Pipeline Total].[Company Size]"
'Set dbsNorthwind = OpenDatabase("Strategic Marketing Dashboard.mdb")
'The following block of code initializes the control variables
Set ctrlHPlstbox = lstHealthPlan
Set ctrlNBchkbox = chkNB
Set ctrlNBEAchkbox = chkNBEA
Set ctrlCompSizelstbox = lstCompanySize
'The following block of code assigns the count values to variables for the list boxes (looping purposes)
Healthplan_Count = ctrlHPlstbox.ListCount
CompanySize_Count = ctrlCompSizelstbox.ListCount
i = 1
'The following block of code loops through the Healthplna list box to gather the names of all of the selected health plans
For CurrentRow = 0 To Healthplan_Count - 1
If ctrlHPlstbox.Selected(CurrentRow) Then
HealthPlan(i) = ctrlHPlstbox.ItemData(CurrentRow)
'MsgBox "current item is " & HealthPlan(i), vbOKOnly, "test"
HealthPlan_Selected = i
i = i + 1
End If
Next CurrentRow
i = 1
'the following block of code takes the values for all of the healthplans selected and begins building the query
'from the basic SQL Statement that was assigned to the SQLString variable. The SQL Statement in Querybuilder
'is updated dynamically based on the selections on the form
For i = 1 To HealthPlan_Selected
QueryBuilder(i) = SQLString
QueryBuilder(i) = QueryBuilder(i) & " HAVING ((([DBP 04 Pipeline Total].[Health Plan])=" & HealthPlan(i) & ")"
If ctrlNBchkbox.Value = True Then
j = 1
QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Opportunity Type])=" & "New Business" & ")"
For CurrentRow = 0 To CompanySize_Count - 1
If ctrlCompSizelstbox.Selected(CurrentRow) Then
CompanySize(j) = ctrlCompSizelstbox.ItemData(CurrentRow)
QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Company Size])=" & CompanySize(i) & "));"
j = j + 1
End If
Next CurrentRow
End If
If ctrlNBEAchkbox.Value = True Then
j = 1
QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Opportunity Type])=" & "NBEA" & ")"
For CurrentRow = 0 To CompanySize_Count - 1
If ctrlCompSizelstbox.Selected(CurrentRow) Then
CompanySize(j) = ctrlCompSizelstbox.ItemData(CurrentRow)
QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Company Size])=" & CompanySize(i) & "));"
j = j + 1
End If
Next CurrentRow
End If
Next i
End Sub
Let me know if you have a better way to do this..Thanks for all your help!!!
Regards,
Eric
Edited by - bahamaej on 3/3/2005 4:50:53 AM
Crystal Reports & Dynamic Query (even Tables And Fields Changes In The Query At Run Time)
Hi there!
I need to link the crystal report to a query that is generated dynamically. I will be knowing about which table to be linked and which fields to be retrieved at run time only. Fields are refered using an alias in all the query output to get a common name for all. I cant use a dataset or TFX file solution as mentioned earlier as i am not aware of the tables and fiels till runtime. Else i have to hardcode all the possible combinations in different tft file and link .
i tried with a temporarytable but that too doesnt seems to work.
Can anyone guide me of any simpler methode of doing this?
Bipin
Access - Max Length Of Query With GROUP BY Command
Hello guys,
Situation:
- Win XP SP1
- Office 2003
I've made a query to filter some records from a table called "tblFacturen":
"SELECT tblFacturen.FactuurNr, tblFacturen.Periode, tblFacturen.KlantID, [Klantnaam] & " " & [Rechtsvorm] AS Naam, Sum(tblFacturen.Duur) AS SomVanDuur, tblFacturen.Dienstnr, tblFacturen.Opmerking, tblKlanten.Straat, tblKlanten.Postcode, tblKlanten.Woonplaats, tblKlanten.BTWcode, tblKlanten.BTWnummer, tblFacturen.OmschrijvingIC, tblFacturen.BTW, tblFacturen.Factuurdatum
FROM tblFacturen INNER JOIN tblKlanten ON tblFacturen.KlantID = tblKlanten.KlantID
GROUP BY tblFacturen.FactuurNr, tblFacturen.Periode, tblFacturen.KlantID, [Klantnaam] & " " & [Rechtsvorm], tblFacturen.Dienstnr, tblFacturen.Opmerking, tblKlanten.Straat, tblKlanten.Postcode, tblKlanten.Woonplaats, tblKlanten.BTWcode, tblKlanten.BTWnummer, tblFacturen.OmschrijvingIC, tblFacturen.BTW, tblFacturen.Factuurdatum
HAVING (((tblFacturen.FactuurNr)=2004262) AND ((tblFacturen.Dienstnr)=0))
ORDER BY tblFacturen.FactuurNr, tblFacturen.Dienstnr;"
Now this is the problem:
The query works fine except from one thing:
The field "tblFacturen.Opmerking" in the result of the query only allows to have 255 characters.
In the table "tblFacturen" on the other hand the field "Opmerking" is declared as a "memo" so it can contain much more than 255 chars.
Now the weird thing is that when I drop the "GROUP BY" line the result of the query does allow more than 255 chars ???
Does anyone have any idea how come, and how I can make a query including the GROUP BY command wich results in a field with more than 255 chars???
Thans a lot
(Query)Providing Run Time Parameter For DOS Command From VB
Hi friends
I want to execute series of dos command. I have done batch file but the problem is for one command, it requires some parameters, which I need to be passed form VB program itself without any human intervention.
Please help me through..................
Thanks And Regards
Abhijit
(Query)Providing Run Time Parameter For DOS Command From VB
Hi friends
I want to execute series of dos command. I have done batch file but the problem is for one command, it requires some parameters, which I need to be passed form VB program itself without any human intervention.
Please help me through..................
Thanks And Regards
Abhijit
Update A Query Table From A Command Button
i need that a spreadsheet from the active control menu in front page shows a query from my access database. I can do this already but I need it to refresh everytime a person enters the page. I believe I need a button with a module in it that would refresh the database once pressed. I just don't know how to go about it. Any input?
|
TRACKER
