|
Thanks for responding, I am not connecting to any SQL server. I am just opening a text file and populating a recordset My code is below.
After I run this I am having trouble finding a way to just get a list of unique QueueName's from the recordset??
I create a recordset via this code....
Set rs = New Recordset
rs.CursorLocation = adUseClient
rs.Fields.Append "UserName", adBSTR, 128
rs.Fields.Append "DocName", adBSTR, 128
rs.Fields.Append "QueueName", adBSTR, 128
rs.Fields.Append "Date", adDBDate
rs.Fields.Append "Time", adDBTimeStamp
rs.Fields.Append "Workstation", adBSTR, 128
rs.Fields.Append "ClientCode", adBSTR, 128
rs.Fields.Append "Subcode", adBSTR, 128
rs.Fields.Append "PaperSize", adBSTR, 128
rs.Fields.Append "Features", adBSTR, 128
rs.Fields.Append "Sizeinbytes", adBSTR, 128
rs.Fields.Append "Pagecount", adBSTR, 128
rs.Fields.Append "cost", adBSTR, 128
rs.Fields.Append "AccountBalance", adBSTR, 128
rs.Open
Then I import the txt file into the recordset using this code...
Dim F As Long, sLine As String, A(0 To 13) As String
F = FreeFile
Open "C:\temp\text.txt" For Input As F
Do While Not EOF(F)
Line Input #F, sLine
ParseToArray sLine, A()
rs.AddNew
rs.Fields(0) = A(0)
rs.Fields(1) = A(1)
rs.Fields(2) = A(2)
rs.Fields(3) = CDate(A(3))
rs.Fields(4) = CDate(A(4))
rs.Fields(5) = A(5)
rs.Fields(6) = A(6)
rs.Fields(7) = A(7)
rs.Fields(8) = A(8)
rs.Fields(9) = A(9)
rs.Fields(10) = A(10)
rs.Fields(11) = A(11)
rs.Fields(12) = A(12)
rs.Fields(13) = A(13)
rs.Update
DoEvents
Loop
Close #F
End Sub
Sub ParseToArray(sLine As String, A() As String)
Dim P As Long, LastPos As Long, i As Long
P = InStr(sLine, ",")
Do While P
A(i) = Mid$(sLine, LastPos + 1, P - LastPos - 1)
LastPos = P
i = i + 1
P = InStr(LastPos + 1, sLine, ",", vbBinaryCompare)
Loop
A(i) = Mid$(sLine, LastPos + 1)
End Sub
|
|
|
|
|
Sorry to disappointed you,I can't understand these code because I don't know VB
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
This may be disloyal, but look on the code guru site. It contains several examples of FTP code.
http://codeguru.com/internet/index.shtml
Hope this helps,
Bill
|
|
|
|
|
Please disregard the previous post, I typed it into the wrong window. It has nothing to do with this question.
Thanks for the help,
Bill
|
|
|
|
|
I have a Access database running on Win ME with VC6.0
and I created a table (by hand in Access) then I try and
use my program to create a record in that table.
the code is executed but the record is not created and
no error messages are thrown. If there is a record already
in the table (again put in by hand) then additional records
can be added without any problems.
Any ideas on what may be wrong ?
KaS
|
|
|
|
|
Can you tell me the code that you add recordset with it?
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
presenting the code:
m_rs is a recordset class
m_pRs is a pointer to the above (type _RecordsetPtr *)
m_piAdoRecordBinding is of type IADORecordBinding *
//... code omited for brevity
strcpy(m_rs.m_szSerialNumber, (LPCTSTR)SerialNumber);
//... code omited for brevity
m_piAdoRecordBinding->AddNew(&m_rs);
m_pRs->MoveLast();
KaS
|
|
|
|
|
I can't understand what is wrong there,but I have two suggestion,first
debug your code to see whats going on there,and second update your database
after you addnew ,also try this,may be this one is ok
hr = pRs->AddNew();
if( FAILED( hr ) ) throw _com_error( hr );--------->you'd better to put it in your code too
pRs->Fields->GetItem( L"fieldname1")->Value = anything1;
pRs->Fields->GetItem( L"fieldname2")->Value = anything2;
pRs->Fields->GetItem( L"fieldname3")->Value = anything3;
hr = pRs->Update();
if( FAILED( hr ) ) throw _com_error( hr );
Hope thats help you
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
One field of my database is Text(string).
Is there any SQL statement or ODBC function to find a word in text of
each recordsets and return recordsets contain that word?
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
select * from table where TextColumn like ('%search term%')
Andy Gaskell, MCSD
|
|
|
|
|
Thanks Andy ,when I used the statement you said nothing return but there is no error,then I changed '%' to '*' then it find the search term ,but there is still one problem,e.g I want to find 'M' in 'Mazdak',it doesn't find it,but if I change '*M*' to '*M or M*' then it find it(the first letter).Can you tell what's the problem?(I use Access)
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
I need convert a SqlBinary to an interger. What is the best way to do this?
Thanks.
|
|
|
|
|
We are using SQL Server 8, The column size is 1024 when
I try to insert a text that is longer than 255 it gets truncated.
This happens even when I try to insert directly into the table (using Query analyser), -usually we use ADO with VB 6-
I checked the database option to make sure the compatibility option is not set to 6.5, since that would make SQL 8 act
as SQL 6.5 which has a maximum varchar size of 255, and therefore truncates anything bigger.
Help
|
|
|
|
|
O.k I found the answer to this a while ago.
First it was not truncated. In Query analyzer the default max size of the results returned is 255.
Second our ADO module had a hard coded parameter that limited the use of any strings larger than 255.
|
|
|
|
|
The statement:
DELETE FROM License WHERE LicenseID = 4
Should surely delete all rows from the License table that have a LicenseID field equalling 4, but it is not affecting the table License in any way, despite indicating success.
Any ideas? The Row with LicenseID equal to 4 (there is only one) is still there. It is executed on a Microsoft Access database, and the data type of LicenseID is (auto)number, if any of that helps.
After 3 hours my eyes are starting to bleed.
________________
David Wulff
http://www.davidwulff.co.uk
"My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group, there was less competition there" - Gandhi
|
|
|
|
|
What is the result of doing:
SELECT * FROM License WHERE LicenseID = 4 ?
Are there any triggers on the table?
Do you get the same behaviour on other tables?
Cheers,
Simon
If we fail to anticipate the unforeseen or expect the unexpected in the universe of infinite possibilities, then we may find ourselves at the mercy of anyone or anything that can not be programmed, categorized or easily referenced.
|
|
|
|
|
simons wrote:
SELECT * FROM License WHERE LicenseID = 4
That returns, as expected, the row where the LicenseID is equal to 4. I know I must be doing something wrong, as I have managed to delete rows before. (Interestingly enough, I had this same problem before, and eventualyl found a solution, but i'll be damned if I can find where it is now).
________________
David Wulff
http://www.davidwulff.co.uk
"Love your neighbor, but don't get caught" — Murphy's Laws of Sex
|
|
|
|
|
As is said above, basically, you need to specify the columns to operate on as well (you could just delete data from certain columns, something I've never tried). Just bung a * after DELETE, as if you were doing a SELECT operation.
Also, I don't use spaces around '=' in SQL, I don't know if this is permitted or not, you might wanna check that out (presumably if you done it elsewhere it's fine).
The reason it's returning success is because you're not actually telling it to delete anything at all. Hope that helps.
--
Andrew.
|
|
|
|
|
Andrew Peace wrote:
Just bung a * after DELETE, as if you were doing a SELECT operation
That was the first thing I tried, but alas it makes no difference. The row/s are still not being deleted.
Andrew Peace wrote:
Also, I don't use spaces around '=' in SQL
I do when writing the statements in ASP/JScript, as it makes it easier to read with all the " + strVar + "'s floating around in there. It doesn't make any difference though if I remove the spaces.
Andrew Peace wrote:
The reason it's returning success is because you're not actually telling it to delete anything at all.
That is what I had been suspect all along, though I can't understand why it could not be finding the rows.
Thanks for the suggestions though.
________________
David Wulff
http://www.davidwulff.co.uk
"Love your neighbor, but don't get caught" — Murphy's Laws of Sex
|
|
|
|
|
Did you find this problem? If not, I have an idea....
In Access at least, if you set relationships up between tables and use the Enforce Referential Integrity option, delete operations will not occur unless you deleted all related rows in other tables. You can avoid this problem using the 'Cascade' options which will make the changes for you.
--
Andrew.
|
|
|
|
|
I haven't fixed it, no, but what you've just said could be the problem... I'll check tomorrow as it's far to late/early to do anything about it tonight.
Thanks again.
________________
David Wulff
http://www.davidwulff.co.uk
"DON'T MOVE! It can't see you if you don't move" - Sam Neil talking to me as John Simmons walked into the office.
|
|
|
|
|
Unfortunately this was not the problem.
Any further ideas?
________________
David Wulff
http://www.davidwulff.co.uk
"I loathe people who keep dogs. They are cowards who haven't got the guts to bite people themselves" - August Strindberg
|
|
|
|
|
Not immediately, the following springs to mind but I presume you've already checked these;
1. Is the connection string to the database correct and allowing read/write access? An example of a connection string from one of my programs is;
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbFileName
That's all you should need, the defaults for the rest of the stuff are ok unless you need to use usernames and passwords.
2. Is the command being built as you expect? E.g. make sure that the string actually does equal what you expect when the command is being executed.
3. The database isn't a read-only file (?!) Seems daft but you wouldn't believe the number of times I forget to reset attributes when copying from backup cd's.
Can't think of anything else for now but will try my best to think of something a bit more solid - I know how annoying this kind of thing can be 'cause I'm currently working on a database app too. It's doing my head in 'cause it's got to be written in VB and it's SO repetetive. Maybe a code snippet might be useful if possible?
--
Andrew.
|
|
|
|
|
Andrew Peace wrote:
1. Is the connection string to the database correct and allowing read/write access? An example of a connection string from one of my programs is;
I use that exact same line.
Andrew Peace wrote:
2. Is the command being built as you expect? E.g. make sure that the string actually does equal what you expect when the command is being executed.
I have got into the habit now of always printing any SQL statement I construct dynamically, onto the page so I can check it. I learnt to do that the hard way.
Yes, the SQL statement is correct. If I execute it from within Access itself it will delete the records!
Andrew Peace wrote:
3. The database isn't a read-only file (?!) Seems daft but you wouldn't believe the number of times I forget to reset attributes when copying from backup cd's.
I hadn't checked this before, but I have now and it is not read only. The one I uploaded to the web server was - thanks for pointing that out. At least that's one headache prevented before it happens!
----
Like I said to simons above, I had this problem before and eventually managed to solve it - but i'll be damned if I can remember what it was I did.
What's even more frustrating is that I know this is going to be some stupidly simple problem when we know what is causing it!
________________
David Wulff
http://www.davidwulff.co.uk
"I loathe people who keep dogs. They are cowards who haven't got the guts to bite people themselves" - August Strindberg
|
|
|
|
|
Okay, seeing as I can't think of anything else I'll post some of my code, you might notice something perhaps.
This comes with the disclaimer that it's in VB and I don't usually do VB ;
Private ConnectionString As String
' Sets the filename of the database to open
'
' IN
' dbFileName - the filename of the database
Public Sub SetDatabaseFilename(dbFileName As String)
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbFileName
End Sub
' Opens the database
'
' IN
' OUT - a connection to the database - close with Connection.Close
Public Function OpenDatabase() As ADODB.Connection
Dim adoconn As New ADODB.Connection
' DEBUG ONLY
SetDatabaseFilename "C:\Documents and Settings\Administrator\My Documents\Main.mdb"
' END DEBUG ONLY
adoconn.Open ConnectionString
Set OpenDatabase = adoconn
End Function
' Removes a customers's record from the database
'
' IN
' ID - ID of the customer record to be deleted
'
' OUT - success?
Public Function RemoveCustomer(ID As Integer) As Boolean
On Error GoTo Fail
Dim adoconn As New ADODB.Connection
Set adoconn = OpenDatabase
adoconn.Execute "DELETE * FROM tblClients WHERE ID=" & ID
adoconn.Close
RemoveCustomer = True
Exit Function
Fail:
RemoveCustomer = False
If IsObject(adoconn) Then
adoconn.Close
End If
End Function For some reason it's taken out some of my blank lines, but anyway, hope that's useful.
--
Andrew.
|
|
|
|
|