|
I have a VB.Net windows app that retrieves data from 3 different tables. The SQL command uses a 3 table join. The results from the query is filled into a datatable and later populated into a combobox.
The SQL uses the values from a date field inside a text field, for example, 01-15-2005. So, each time I submit the subroutine it executes the SQL command and VB.Net code to retrieve the required data. The date field changes according to the user’s needs.
There’s no consistency when the error pops up.
The error message I’m getting when it executes, da.Fill(dataTable), “There’s no row at position 0”,. The information I’m on the stack track is as follow: Calling SQLDisconnect --- OleDB UnInitialize not called!!!
Connection Object destroyed
If I copy the sql into a sql query tool it does retrieve the required data according to the date field.
Here’s a sample of the subroutine:
cmdQueryINVOICE = _
"SELECT PAYITEM.TRANSACT_NUMBER, " _
& "PAYITEM.ACCOUNT_NUMBER, " _
& "PAYITEM.PAY_DATE, " _
& "LSEINVH.INVOICE_NUMBER " _
& "FROM LSEINVH, PAYITEM, TRANSHDR " _
& "WHERE " _
& " LSEINVH.INVOICE_NUMBER > '" & 75000 _
& "' AND LSEINVH.STATUS <> '" & sStatus _
& "' AND LSEINVH.INVOICE_NUMBER = TRANSHDR.INVOICE_NUMBER " _
& " AND PAYITEM.PAY_DATE > '" & InvoiceDates.StartDate _
& "' AND PAYITEM.PAY_DATE < '" & InvoiceDates.EndDate _
& "' AND PAYITEM.COMPANY = '" & sCompany _
& "' AND PAYITEM.TRANSACT_NUMBER = TRANSHDR.TRANS_NUMBER order by PAYITEM.PAY_DATE desc "
Dim sqlQuery As OleDbCommand = connDf.CreateCommand
Dim dtInvDate As New DataTable()
Dim iInvCnt, x As Integer
Dim sUnitQuery As String
Dim daInv As New OleDbDataAdapter()
Try
daInv = New OleDbDataAdapter(cmdQueryINVOICE, connDf)
daInv.Fill(dtInvDate)
Dim LoopRow As Data.DataRow
Dim test As String = dtInvDate.Rows.Item(0).ToString
For Each LoopRow In dtInvDate.Rows
cboInvoice.Items.Add(LoopRow.Item("INVOICE_NUMBER").ToString + " " + LoopRow.Item("PAY_DATE"))
Next
Catch e As Exception
Dim errMsg As String = e.Message
Finally
connDf.Close()
End Try
Thanks, for any help!
|
|
|
|
|
|
Thanks for your suggestion. I decided to retrieve the entire results from the query and fill the dataset, so I don't experience the problem.
Thanks again for the recommendation of sql injection I'll look into to it, but this is a windows application I thought sql was only for web application?
|
|
|
|
|
Hi there,
I'm trying to access a CSV file using jet from my C++ app using ADO. Here is my connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<my db="" directory="">;Extended Properties=text;"
I have also tried:
"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=<my db="" directory="">;Extensions=asc,csv,tab,txt;"
(where <my db="" directory=""> is the path and name of the directory where the csv file is stored)
The query is, eg, "select * from MyTable" where MyTable.txt exists in the directory given in the control string.
The error message I get is: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.
This seems odd to me because it seems like the name of the file it is trying to open is empty. But I know it's going to MyTable.txt because if I rename MyTable.txt I get the error message:
"The Microsoft Jet database engine could not find the object 'MyTable.txt'. Make sure the object exists and that you spell its name and the path name correctly."
More information:
-I am logged in as Administrator and have full access rights to MyTable.txt.
-This is the very first query I do
-The error occurs in the call to RecordSet::Open, ie when I query the file, not when I open the connection
-If I change the connection string and nothing else, I can connect and query an MSDE DB without any problems, so I think my ADO is OK.
Does anyone know what's going on?
|
|
|
|
|
connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + csDir +
";Extended Properties=\"text;HDR=Yes;FMT=Delimited;\";"
where csDir is the full path (excluding the file name) to the file
HDR = YES if first line is column names, FMT=Delimited to indicate comman delimited,
note the escaped embeded quotes.
Query: "select * from [myfile.txt]" - USE THE FULL FILE NAME ENCLOSED IN BRACKETS
Hope this helps...
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Thank you Rob, I *think* that helped... I now get a different error message "No value given for one or more required parameters (1)" which sound more like it could be a problem with my query than actually accessing the file, is this right? Do you know what this error means? The query I am using is:
SELECT [Language] FROM [myFile.txt];
And the first line of the text file is:
"Student","Language"
Also, it's rather unfortunate for me that getting the query to work requires having the .txt on the end of the file name. My app needs to work with either an MSDE or a CSV db and I was hoping I could just reuse the queries. Is there any smarter way to do this than using a macro or calling a function inside every query, like this or something?
<br />
CString from(CString table) {<br />
#ifdef MSDE<br />
return _T("FROM ") + table;<br />
#else CSV<br />
return _T("FROM ") + table + _T(".txt");<br />
}<br />
|
|
|
|
|
Sadly, Microsoft messed this up, SQL Access, Excel, and CSV all follow different rules in some of the details (Excel wants a $ appended to the worksheet name for the 'Table' name ), so conditional code seems the only answer.
Not sure about the error message, sounds like it is interpreting [Language] as a parameter name. Does your connection string include the HDR=1 part to indicate that the first row is column names?
What happens if you "Select *" instead of specifying the fields?
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Select * works.
If I change the name of the Language column to UnlikelyToBeAKeyword and then select on that, with or without square brackets, it still gives the same error.
I have HDR=Yes but HDR=1 gives the same result.
Actually it seems I can put whatever rubbish I want in the Extended Properties string and it just gives that "No value give for one or more required parameters" error. I don't think that string is working properly.
Here my connection string is again in case I've done something daft:
connection = _T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myTextDB\\;Extended Properties=\"text;HDR=1;FMT=Delimited;\";");
Can you see anything wrong with it?
|
|
|
|
|
if select * works, then the connection string is likely ok.
Possibly something in a 'data' row? an '@' or '?'character perhaps?
try adding IMEX=1 to the extended properties section to force all column datatypes to become strings (IMportEXport flag). Normally the provider scans the first 8 rows to determine 'numeric' columns, and could get tricked. Forcing all string datatypes gets around the 'too smart' provider. This is usually needed only for excel files, but may apply to csv as well...
One last possibility: for CSV fies, the provider normally expects an extension of .csv, or at least anything other than .txt. The reason for this is that it looks for an identically named .txt file for 'schema' definitions to use. Perhaps the filename is causing some confusion, select * works because the code takes a different path...
Looking back at code I have used in the past, your connection string looks fine (I have used 1 and YES interchangably as well).
The only thing I see different is that I always used .csv for the extension, and when writing, deleted any .txt files to avoid having the schema file overriding user changes on subsequent reads. If it exists, colum definitions in the .csv file must match those defined in the .txt file exactly or read and write fails...
At any rate it sounds like a bug.
Wish I could help more, but I am a bit mistified too.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
You don't think the fact that I can add "foo" into the extended properties is a bad sign?
I do have question marks in some of my strings. Is this a problem? MSDE didn't seem to care.
A file with .csv extension behaves the same as .txt. IMEX=1 makes no difference either.
|
|
|
|
|
wilsonian wrote:
You don't think the fact that I can add "foo" into the extended properties is a bad sign?
At least one could hope it was just gracefully ignoring things it did not understand in the connection string. 
wilsonian wrote:
do have question marks in some of my strings. Is this a problem? MSDE didn't seem to care.
I would hope that ? marks in data would be ok, in field names or elsewhere in the query they could be mistaken for parameter placeholders.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Ok, I've worked it out. Sorry, I don't blame you for not guessing since I caompletely failed to mention the pertinent detail: My files are in Unicode format. I've found the CharacterSet=UNICODE extended property and it works fine now.
Thanks for all your help, I'd probably still be stuck on that first error message without it.
|
|
|
|
|
I have the query below which contains 2 functions which return tables, fn_SamplesUserProducts & fn_SamplesUserOffices. The query itself I want to set the rowcount to say 10. However, my functions will return more than 10 rows and obviously they don't due to the set RowCount. I tried to set the RowCount in the function but an error occurs when attempting to compile: Invalid use of 'UNKNOWN TOKEN' within a function. Is there a work-around?
The query is the first part of a complex cross-tab w/o reporting services installed.
SELECT s.SampleID,
convert(CHAR(10), s.PullDate, 111) + ' ' + IsNull(S.TripNumber, rtrim(convert(varchar(25), 'N/A' + rtrim(convert(varchar(8), S.SampleID))))) as ColumnName,
s.PullDate,
s.TripNumber,
s.Quantity,
convert(CHAR(10), s.PullDate, 112) + IsNull(S.TripNumber, rtrim(convert(varchar(25), 'N/A'))) + rtrim(convert(varchar(8), S.SampleID))
FROM tblSamplesSample s,
tblSamplesSampleTerminalPortWellLocation stp,
fn_SamplesUserProducts(@UID) fnp,
fn_SamplesUserOffices(@UID) fno
WHERE S.SampleID *= stp.SampleID
AND (S.Deleted = 0 or S.Deleted IS NULL)
AND (stp.Deleted = 0 or stp.Deleted IS NULL)
AND fnp.ProductID = s.ProductID
AND fno.OfficeID = s.OfficeID
AND ((@DateRangeBegin IS Null AND @DateRangeEnd IS Null)
OR PullDate BETWEEN @DateRangeBegin AND @DateRangeEnd
)
AND (@TerminalPortWellLocationID IS Null
OR stp.TerminalPortWellLocationID = @TerminalPortWellLocationID
)
AND (@CustomerCompanyID IS NULL
OR s.CustomerCompanyID = @CustomerCompanyID
)
AND (@ProductID IS NULL
OR S.ProductID = @ProductID
)
AND (@CustomerProductReportID IS NULL
OR s.CustomerProductReportID = @CustomerProductReportID
)
AND (@DomesticInternational IS NULL
OR s.DomesticInternational = @DomesticInternational
)
AND S.StatusID = 1 --Finalized
GROUP BY S.SampleID, S.TripNumber, S.PullDate, s.Quantity
HAVING Count(stp.TerminalPortWellLocationID) = 1
ORDER BY s.PullDate DESC, TripNumber ASC, s.SampleID
Thanks,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)e and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
I declared the following table variables and used them in FROM clause as a work-around. The table variables are populated prior to the SET RowCount command.
However, is there a way around doing it this way?
DECLARE @SamplesUserProducts TABLE(ProductID int)
DECLARE @SamplesUserOffices TABLE(OfficeID int)
INSERT INTO @SamplesUserProducts SELECT ProductID FROM fn_SamplesUserProducts(592)
INSERT INTO @SamplesUserOffices SELECT OfficeID FROM fn_SamplesUserOffices(592)
Thanks,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
dear all,
i have the following problem, i would like to insert some data to many tables and there are some triggers on that tables so does anybody know how can i stop the triggers while am inserting that data and how can i delay that triggers till the transaction commited
i have tried the next code but it did not work:
<br />
create database test<br />
go<br />
use test<br />
go<br />
create table tab <br />
(<br />
id int identity ( 1,1 ) ,<br />
name nvarchar ( 100 ) <br />
)<br />
go <br />
create trigger trg on tab after insert <br />
as<br />
(<br />
select <br />
tab.name <br />
from <br />
tab , inserted<br />
where tab.id = inserted.id<br />
)<br />
go<br />
<br />
begin transaction <br />
set transaction isolation level read uncommitted<br />
insert into tab ( name ) values ( 'New Name' ) <br />
select 'Transaction Done...'<br />
commit transaction <br />
i would like to see the execution as the following :
Transaction Done...
New Name
Thanks for your help
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
ALTER TABLE tab DISABLE TRIGGER trg
Then when you are done
ALTER TABLE tab ENABLE TRIGGER trg
This will enable and disable the triggers.
If you really need to, you can try to put a WAITFOR or a WHILE loop in your trigger if you need it to delay, but then it will run with the delay everytime.
I would recommend running the select query that is in your trigger on it's own after the transaction is complete, THEN create the trigger.
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
When I use the command line arguments
REINSTALL=All
and
REINSTALLMODE=omus
to cause the MSDE 2000A setup to reinstall an existing instance, it still fails with the error message:
"The instance name specified is invalid."
I know it is already running just fine, it was a brand new, clean install of the instance not a moment earlier with this result:
"Microsoft SQL Server Desktop Engine -- Installation operation completed successfully."
What gives?
|
|
|
|
|
While using MemoryStream in the context of SQLXML3.0
sxcmd.commandText="Name of stored proc on database"
<br />
sxcmd.RootTag = "FullName"<br />
Line 36: Dim MemSt As MemoryStream<br />
Line 37: MemSt = sxcmd.ExecuteStream()---->Problem on this line<br />
Line 38: Response.Write(MemSt.Length)<br />
Line 39: Dim strRdr As New StreamReader(MemSt)<br />
System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x80040E21.
|
|
|
|
|
If you know your SQL Syntax is good, then you should check permissions. You might consider trying to run it with SA priviledges on the database to see if it works. If so, you know where your problem is.
Also if you are using XML AUTO in your query try XML RAW and see if it goes away.
Otherwise I'm not sure. But that's where I'd start trying to troubleshoot.
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
It is not a permissions problem.
|
|
|
|
|
Hello,
I need to do something like this:
SELECT ID_user, Name FROM Users WHERE (Permissions & 0x1000) > 0 I want to get all the records that have a specific bit set in a field. What is the operator to do it? Or do I have to get all the records and filter them by code?
Thanks,
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Your code should work as written.
Note: You may have to put some brackets around the field names because of key work conflicts.
SELECT ID_user, [Name] FROM Users WHERE ([Permissions] & 0x1000) > 0
|
|
|
|
|
I'm using a OleDb Connection and Command in C# to retrieve data from directories of .csv files. The .csv files (each month a new one) contain a months worth of data written from PLC's in my workplace.
The Problem:
All the files Schema's are the same for the files in each directory, but the file names are all different. Hence I can't put a 'known' filename in my schema.ini file that I'd like to use when retrieving the data.
Question:
Is there a flag or command I can use or put in my Schema.ini file, so that it will be used on any file read from the same directory? (ie a generic schema.ini file?).
Thanks Guys.
MongooseNX
|
|
|
|
|
Hi, folks.
[UPDATE]
I managed to figure it out on my own. First problem was using insert. I should have been using update. Second was figuring out how to assign a single value from the select statement. Here's what finally worked:
update data_75Q set elapsed_seconds = es<br />
from (select idnum, <br />
cast(left(elapsed_time, patindex('%:%', elapsed_time)-1) as int)*60 + <br />
cast(right(elapsed_time, 2) as int) as es<br />
from data_75Q) as t1<br />
where data_75Q.idnum = t1.idnum
Anyway, I had no answers from CodeProject, but just posting the question was enough to get me to think harder about the problem. For that, I thank all of you!
[/UPDATE]
I have a MS SQL database that has a problem. One of the columns is supposed to be the elapsed time for a given process and has a varchar datatype. The process inserts the elapsed time value as a string, 'mm:ss', which means I can't perform time-related math on it. For instance, if I return a query into a spreadsheet and try to calculate statistics, it won't work.
So, I want to convert the string 'mm:ss' entries into time values. There does not seem to be a time datatype, so I want to convert the entries into an int datatype (which I can then call seconds).
I can write a query that selects the strings and converts them into int seconds:
select top 5 elapsed_time, cast<br />
(left(elapsed_time, patindex('%:%', elapsed_time)-1) as int)*60 + <br />
cast(right(elapsed_time, 2) as int) as secs<br />
from data_75Q
returns:
elapsed_time secs <br />
-------------------------------------------------- ----------- <br />
10:30 630<br />
02:27 147<br />
03:44 224<br />
13:05 785<br />
13:52 832<br />
<br />
(5 row(s) affected)
This seems to work ok.
However, I can't figure out how to get the converted values back into the table. I added another column, elapsed_seconds as int, but I can't seem to figure out the proper syntax to use this in an INSERT statement. Here's what I've tried:
insert into data_75Q (elapsed_seconds) <br />
values <br />
select <br />
cast(left(elapsed_time, patindex('%:%', elapsed_time)-1) as int)*60 + <br />
cast(right(elapsed_time, 2) as int)<br />
from data_75Q
Although I've used the same select statement, Query Analyzer reports "Incorrect syntax near the keyword 'select'".
Thanks in advance for any help.
'til next we type...
HAVE FUN!! -- Jesse
|
|
|
|
|
hello
how can i deal with multivalued fields (field type = adVariant)
i need to get data from an ado recordset field with type adVariant and
convert it to string or array of strings (it is data from the ActiveDirectory
and not from sql sever !!!- the provider is 'ADSDSOObject')
i am using c++ code and working with ado on win xp
*i have found a sample how to do it in vb but can't find a way implement it in c++!!!
thank's
stanley
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
