Searching An Oracle 9i CLOB Field

VB,ADO & Oracle CLob
Hi
I wrote this code with VB6,ADO2.5 to execute stored procedure and obtain a parameter in Clob type in oracle8i.

'-----------------------Start Code------------------------------------------------------------
Dim MyCLob As String
Dim Conn As ADODB.Connection
Dim cmd As ADODB.Command
Set Conn = New ADODB.Connection
Conn.Open "Data Source=x; password=y; user id=z ;Provider=OraOLEDB.Oracle"
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Conn
cmd.CommandType = adCmdStoredProc

cmd.CommandText = "{call Test1(?)}"
cmd.Properties("SPPrmsLOB") = True 'Error Occurs
cmd.Parameters.Append cmd.CreateParameter("Result", adLongVarChar, adParamOutput, 100000)
cmd.Execute

MyCLob = cmd.Parameters(0).Value
MsgBox MyCLob
Set cmd = Nothing
Conn.Close
Set Conn = Nothing
'-----------------------End Code------------------------------------------------------------

The Property SPPrmsLOB does not appear in the properties collection of the command.
Any Advise???
Thanks in advanced

Note: SPPrmsLOB is a command custom property for the provider OraOLEDB.Oracle.

---

VB6 Reading CLOB In Oracle 8.1.7
Hello,

I'm trying to read a CLOB in an Oracle 8.1.7 table with VB6. I can read small CLOB's of around 80 characters, but I cannot read larger CLOB's (around 30,000 characters). Here's my code:

Code:Dim MySession As OraSession
Dim OraDb As OraDatabase

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("blah", "blah/blah", 0&)
Dim OraDyn As OraDynaset, OraStory As String, amount_read%, chunksize%, chunk

Set OraDyn = OraDb.CreateDynaset("SELECT test_id, test_clob FROM clob_table WHERE test_id=7", ORADYN_DEFAULT)

OraStory = OraDyn.Fields(1).Value

With small CLOBs, the contents are copied into variable OraStory and everything is fne.
With large CLOBs, the code crashes and gives the error:
OIP-08194: Error fetching data

Why are large CLOBs crashing my VB code?

---

Oracle Clob Data Type
I am using VB to read an oracle database, and on the open command i get a "Data type not supported" error. The database has a field that is a CLOB data type, anyone with ideas on how to get around this?

---

Cannot SELECT Oracle CLOB Datatypes With ADODB.connection
SELECT SQL Command for Oracle CLOB Datatype Columns returns the runtime error (80004005) Datatype is not supported. Connection with ORA is achieved with Microsoft ActiveX Data Objects 2.0 Library (msado20.tlb).

For example the following sample code returns this error.

'Declare Connection
Dim cConnection as ADODB.Connection
'Declare Recorset
Dim xQuery as ADODB.Recordset
'Start Connection
Set cConnection = New ADODB.Connection
With cConnection
'Connection Settings
.CursorLocation = adUseClient
.Provider = "MSDAORA"
.CommandTimeout = 500
'Create connection with Oracle Database
.Open "Data Source=<Host string>;User Id=" _
& "<User Name>;Password=<password>;", _
"<User Name>", "<password>", adConnectAsync
End With
'Create Recordset
Set xQuery = New ADODB.Recordset
With xQuery
'Send SQL command to Oracle Database
.Open "SELECT <ANY NON CLOB FIELD> FROM " _
& "<TABLE>",cConnection
'SQL query executed succesfully.
.Open "SELECT <CLOB FIELD> FROM <TABLE>",cConnection
'Visual Basic Returns Error 80004005
End With

Could anyone help me please

---

Hi! Question About Searching And Display Data From Oracle Via VB6 Form
Hi everyone! I'm new to VB6 and I have a project using a VB6 form to search and display the data from Oracle9i database by press the Search button.

Firstly, I have a database, and a table named FoneDir. The FoneDir include:
Customer_ID; FoneNum; Customer_Name; Customer_Add;

I am writing a from that whenever I type the phone number and then press the Search button, the program will connect to FoneDir table and display back all the data form the FoneDir table. Would you please help me out!

Example:
         _______________________________________________
        |             Customer Search          
        | Phone number:      ________________  |Search|
        | Customer ID:       ________________   
        | Customer name:     ________________           
        | Customer address:  ________________           
        |_______________________________________________
the result should likes:
         _______________________________________________
        |             Customer Search          
        | Phone number:      0906804840        |Search|
        | Customer ID:       9982              
        | Customer name:     Rivera                     
        | Customer address:  Ho Chi Minh city           
        |_______________________________________________

Please help me out this one! Thank you!
(Sorry for my English)

---

Regarding Searching By Date Range In Access W/oracle Database
Larry , thank you for your hlep, however I am still not getting it to respond.... I did correct the error you point out regarding the "bang" I missed.... so this is my issue again... if you guys could look it over for me once more....
( Oh and I did not understand the piece about time/date issue, Larry spoke of ... it's only a date I need it to respond too)
First of all...I am new at this site.... and new at VB.... so plz be gentle with me...
this is the code...
  I am using access 2000, and this is a front end to an Oracle on a I think a rs6000 unit... if that makes sense...
This is to collect data for a well information in a date range... all my other searches work so for...
thank you in advance
barry

Private Sub Command34_Click()
On Error GoTo Err_Command34_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim MyDate, MyCheck
     
    stDocName = "PV_WELL_DATA4"
     
    stLinkCriteria = "([PV_WELL_DATA.JOB_DATE]>=" & Me![From] & ") AND (" & "[PV_WELL_DATA.JOB_DATE] <= " & Me![To] & ")"
    MyDate = IsDate(Me![From])
    MyCheck = IsDate(Me![To])
    'MsgBox ("result of isdate = " & MyCheck)
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command34_Click:
    Exit Sub

Err_Command34_Click:
    MsgBox Err.Description
    Resume Exit_Command34_Click
End Sub

---

Searching A Field
For the life of me, i cannot remember how to use strings or how to search for $#!7 in the fields, or even if they're tied together.

im working on a game that's kinda a mix between majormud and dnd type role playing. I wanted to store the character's special attacks/magic/whatever all in one field with "," as the seporator. but like i said, im at a loss as to how to tell vb to split each attack/whatever into a .newitem in the proper listbox.

I usually jog my memory with codes submitted on planetsourcecode.com, or going through the limited-on-info vb6 book...or my friend spike, whom is more talented than i.
but niether of them have come through for me(spike mainly cuz he's anal and is working on a major project of his)

HELP

---

Searching A Field
My database is coming along nicely. I just need some help with searching. My database contains a table called CARDs and a field called ENTRY. Sample data in field ENTRY:

iron mines chester wharton trenton NJ

or

iron mines rockaway MN

or

iron mines kinnelon trenton NJ

MY goal is to be able to click a search button and type in "iron NJ" or "mines trenton" and the correct records would display.

Right now my search works great if I type in only one word to search for. Does this make any sense? The Command7_Click() sub contains my current search code.

Here is my project code:


Code:
Option Explicit
Const NUMROWS As Integer = 10
Dim cnDB As ADODB.Connection
Dim rsDB As ADODB.Recordset
Dim BindColl As BindingCollection

Private Sub Command1_Click()

Dim U As Integer
Dim NU As Long
Dim NUMOFCARDS As Integer
Dim NEWCARDNum As String
Dim CARDSET As String
Dim CARDSETNUM As Integer
Dim strOri1 As String
Dim strOri2 As String
Dim FOLDER As String
Dim strOri As String
Dim strNum As String
Dim strNew As String
Dim Pass As Long

U = 0
NUMOFCARDS = InputBox("Enter the number of cards:")
FOLDER = InputBox("Enter the folder path:")
CARDSET = InputBox("Enter the card set (ex. al-allem, a-ak:")
CARDSETNUM = InputBox("Enter the first card number (ex. 0001, 1034:")

For U = 1 To NUMOFCARDS

rsDB.MoveLast
NU = rsDB.Fields("unique#")

strOri1 = "e:cardsgen"
strOri2 = FOLDER
strOri = CARDSET
strNum = CARDSETNUM

If Pass = 0 Then
strNum = strNum - 1
strNum = Format(Val(strNum) + 1, "0000")
Else
strNum = Format(Val(strNum) + 0, "0000")
End If

strNew = strOri1 & strOri2 & strOri & strNum & ".jpg"

NEWCARDNum = CARDSET & strNum
CARDSETNUM = CARDSETNUM + 1

rsDB.AddNew
NU = CLng(NU) + 1
rsDB.Fields("unique#") = NU
rsDB.Fields("CardNum") = NEWCARDNum
rsDB.Fields("CardPath") = strNew

strOri = strNew
Pass = Pass + 1

rsDB.Update

Next U

rsDB.MoveLast

End Sub

Private Sub Command2_Click()
Dim sSQL As String
Dim INQUIRE As String


INQUIRE = InputBox("Enter record number:")
sSQL = "SELECT * FROM cards WHERE unique# = '" & INQUIRE & "'"
Set rsDB = New ADODB.Recordset
Set BindColl = New BindingCollection

rsDB.Open sSQL, cnDB, adOpenKeyset, adLockOptimistic, adCmdText
Set BindColl.DataSource = rsDB

Set MSHFlexGrid1.DataSource = rsDB
BindColl.Add Text1, "text", "Unique#"
BindColl.Add Text4, "text", "Entry"

rsDB.MoveFirst
Image1.Picture = LoadPicture(rsDB.Fields("CardPath"))



End Sub

Private Sub Command6_Click()
On Error GoTo ErrLabel
rsDB.MoveLast
Image1.Picture = LoadPicture(rsDB.Fields("CardPath"))

Exit Sub

ErrLabel:
MsgBox Err.Description
End Sub
Private Sub Command3_Click()
On Error GoTo ErrLabel
rsDB.MoveFirst
Image1.Picture = LoadPicture(rsDB.Fields("CardPath"))

Exit Sub

ErrLabel:
MsgBox ("Beginning of List!")
rsDB.MoveFirst
Image1.Picture = LoadPicture(rsDB.Fields("CardPath"))

End Sub
Private Sub Command4_Click()
On Error GoTo ErrLabel
rsDB.MovePrevious
Image1.Picture = LoadPicture(rsDB.Fields("CardPath"))

Exit Sub

ErrLabel:
MsgBox ("Beginning of list!")
rsDB.MoveFirst
Image1.Picture = LoadPicture(rsDB.Fields("CardPath"))

End Sub
Private Sub Command5_Click()
On Error GoTo ErrLabel
rsDB.MoveNext
Image1.Picture = LoadPicture(rsDB.Fields("CardPath"))

Exit Sub

ErrLabel:
MsgBox ("End of list!")
rsDB.MovePrevious
Image1.Picture = LoadPicture(rsDB.Fields("CardPath"))
End Sub

Private Sub Command7_Click()
Dim sSQL As String
Dim INQUIRE As String

On Error GoTo ErrLabel

INQUIRE = InputBox("Enter search criteria:")
sSQL = "SELECT * FROM cards WHERE entry LIKE '%" & INQUIRE & "%'"
Set rsDB = New ADODB.Recordset
Set BindColl = New BindingCollection

rsDB.Open sSQL, cnDB, adOpenKeyset, adLockOptimistic, adCmdText
Set BindColl.DataSource = rsDB

Set MSHFlexGrid1.DataSource = rsDB
BindColl.Add Text1, "text", "Unique#"
BindColl.Add Text4, "text", "Entry"

rsDB.MoveFirst
Image1.Picture = LoadPicture(rsDB.Fields("CardPath"))


Exit Sub

ErrLabel:
MsgBox ("No such record. Please try again.")


End Sub

Private Sub Form_Load()

Set cnDB = New ADODB.Connection
Set rsDB = New ADODB.Recordset
Set BindColl = New BindingCollection

cnDB.Open "Driver=SQL Server; Server=DIGITAL; Database=gen_cards_sql;User ID=cinnamon;Password=sinnamon"
rsDB.Open "SELECT * FROM Cards", cnDB, adOpenKeyset, adLockOptimistic, adCmdText

Set BindColl.DataSource = rsDB

'Set MSHFlexGrid1.DataSource = rsDB
'If rsDB.RecordCount > 0 Then SyncFlexGrid

BindColl.Add Text1, "text", "Unique#"
BindColl.Add Text2, "text", "CardNum"
BindColl.Add Text3, "text", "CardPath"
BindColl.Add Text4, "text", "Entry"

End Sub
Private Sub MSFlexGrid1_Click()
Dim rp As Interger

rp = MSFlexGrid1.MouseRow
If rp > 0 And rp <= rsDB.RecordCount Then
rsDB.AbsolutePosition = rp
SyncFlexGrid
End If
If MSFlexGrid1.Row > 0 Then SyncFlexGrid

End Sub
Private Sub SyncFlexGrid()
Dim temp As Integer

If rsDB.EditMode = adEditAdd Then
temp = 1
Else
temp = rsDB.AbsolutePosition
End If
MSHFlexGrid1.Row = temp
MSHFlexGrid1.Col = 0
MSHFlexGrid1.RowSel = temp
MSHFlexGrid1.ColSel = rsDB.Fields.Count - 1
If temp < MSHFlexGrid1.TopRow Then
MSHFlexGrid1.TopRow = temp
ElseIf temp >= MSHFlexGrid1.TopRow + NUMROWS Then
MSHFlexGrid1.TopRow = temp + 1 - NUMROWS
End If
End Sub
Thank you.

Cinnamon

---

Searching A Certain Field
I need to search for a number IE R027, or another number IE 10208. I got the second to work, but I cant get the first one to work with basically the same code...

Private Sub cmdsearch_Click()
'Search for a Tank number
On Error Resume Next
Message = "Enter the Tank Number you want to find."
Title = "Tank Search"
Adodc1.Recordset.MoveFirst
Dim searchtext As String
searchvalue = InputBox(Message, Title)
If searchvalue = "" Then MsgBox "Please enter a Serial Number to find": Exit Sub
searchtext = searchvalue 'for numbers i have a val(searchvalue) here
Adodc1.Recordset.Find "Number = " &amp; searchtext
If Adodc1.Recordset.EOF = True Then
MsgBox "No Record Found!"
Adodc1.Recordset.MoveFirst
End If
End Sub

So basically its the same sub, one works but the other don't
Any ideas?
Shawn

---

Searching Field
I am working on SQL. How can I find a specific field is available in how many table in specific database

---

ORACLE: Field Lenght!
Hello!
for the first time, i'm using ORACLE DB with my VB Project.
I find a little problem, with CHAR field:
1) i open my recordset
2) i set datasource and datafield of my textbox
3) the value the DB ORACLE have, after my digits, is a string of the dimension of the DB Field Size!
if i write: "HELLO"
i'll find in the field:"HELLO "

not very nice
any knows hot to store value dimensioned by my digits, not by field size?

---

Update Oracle Field
Hi All:

I need to write a quick app to simply update a field in an oracle table, basically just connect to the db and do an update statement. any pointers on how to do this would be greatly appreciated!

thanks in advance!

//brett

---

How To Set Textbox To An Oracle Field
I have a textbox and I want to set my textbox to a field in my Oracle database. My table name is cama and
the field that I want my textbox set to is (p_city)
Can any one help me on this issue?
thankyou
this is what I have right now

Private Sub Form_Load()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim txt As String
Dim fld As Field

    ' Open a connection using Oracle ODBC.
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Driver={Oracle in OraHome92};" & _
        "UID=sirron;PWD=linton"
    conn.Open

    ' Open the table.
    Set rs = New ADODB.Recordset
    rs.Open "cama", conn, adOpenDynamic, adLockOptimistic, adCmdTable

---

Searching A Field In A Database
One more quick question if I may.

Not sure the best way to do this so I'm asking for a little assitance.

I have a field in my database designated for keywords. I want to use this field for up to five words that can be used to describe the record. On my form, I have a search field that the user will type what they are looking for and then click a command button to perform the search on that keyword field.

Not making much (any) progress to get it to work. Can anyone help me with this chore? or am I going at it completely all wrong?

Thanks

---

Please Help:How To Get A Sum Of Values In A Field After Searching!
Hi,

Here is the scenario. I have 5 clients.Names are Ben, Martha, Mark, Bret and Diana. They pay me daily cash for a span of a month. I have two fields which are "Name" and "Paid_Amount".When searching the name Martha, i want to display in a label the sum of all values in the field "Paid_Amount" with the corresponding name "Martha". Im using DAO recordset, an access table and a DBGrid. Have syntax for searching the name "Martha" which is (Data1.Recordsource="Select * from table1 where name like '*" + txtname.text + "*'"). txtname is the textbox i use for name input in searching. With respect to the search syntax i stated, how can i integrate a syntax to display the sum of a field after a search.

Thank you!

---

Searching A Memo Field
I have this small dataBase that contains a Memo Field.
I need to be able to search this memo field for particular words.
Being a DataBase ignorant soul. I am at a loss.
Can anyone help?
Here is an example of a SQL statement the program currently uses

set mRS = mDB.OpenRecordset("select * from codeitems where id = " & msKey)
'
'
I need it something like
set mRS = mDB.OpenRecordset("select * from codeitems where example contains the word "Bookmark").



My DataBAse for Dummies book does not cover this situation.


John G

---

Put A Date Into An Oracle Table's Field?
I had a project that was using SQL, and then had to modify it to put a date into a field, going from MS Access to Oracle. Here is what the code looked like:

strSQL = ""
strSQL = strSQL & "INSERT INTO MasterPN "
strSQL = strSQL & "(PN, Description, FSCM, "
strSQL = strSQL & "ATAPN, ESDS, SPN, "
strSQL = strSQL & "Keyword, CompVal, DimData, ModUser, ModDate) "
strSQL = strSQL & "VALUES ('" & rsTemp!PN & "', '" & strDescription & "', '" & rsTemp!Fscm & "', "
strSQL = strSQL & "'" & strATAPN & "', " & intESDS & ", '" & strSPN & "', "
strSQL = strSQL & "'" & rsTemp!Keyword & "', "
strSQL = strSQL & "'" & rsTemp!CompVal & "', '" & rsTemp!DimData & "', "
strSQL = strSQL & "'SYSTEM'" & Now()
conOracleDb.Execute strSQL, , adExecuteNoRecords

This did not place a date into the desired field. So, I deleted that piece of code and put in the following code:

strSQL = ""
strSQL = "SELECT * From MasterPN"
rsPart.Open strSQL, conOracleDb, adOpenStatic, adLockOptimistic, adCmdText
If Not (rsTemp.EOF = True And rsTemp.BOF = True) Then
rsPart.MoveLast
rsPart.AddNew
rsPart!PN = rsTemp!PN
rsPart!Description = rsTemp!Description
rsPart!Fscm = rsTemp!Fscm
rsPart!ATAPN = rsTemp!ATAPN
rsPart!ESDS = rsTemp!ESDS
rsPart!SPN = rsTemp!SPN
rsPart!Keyword = rsTemp!Keyword
rsPart!CompVal = rsTemp!CompVal
rsPart!DimData = rsTemp!DimData
rsPart!ModUser = "SYSTEM"
rsPart!ModDate = Now()
rsPart.Update
End If

And that code worked!! My question...... can anyone tell me why one way doesn't work but another way using the same Now() function does work? This one drove me nuts for the better part of a day.

Are there issues between MS Access date functions and date items in Oracle?

Any help and advice on this will be greatly appreciated. Thanks!!






Added green "resolved" checkmark - Hack

---

Problem With Oracle BLOB Field In VB
Hi,

I have a VB 6.0 application with a backend Oracle 8i Database.
A piece of code in this application is throwing me an error:

Err.Number = -2147467259
Err.Description = Data Type is not supported.

I must mention the following:
1) I am using Microsoft ActiveX Data Objects 2.6 Library

2) The DB has a table named MyPicTable with the following structure:

Code:
Create Table MyPicTable
(
Pic_ID NUMBER(10)
, Pic_Image BLOB
)

3) I am using MSDORA as connection Provider as follows:
(I must mention that I am getting a connection to the DB! That's OK! Also, using this connection string is a compulsion!!)


VB Code:
' ************************************************** *********' serverName, dbUID, dbPWD are variables defined elsewhere ' serverName will be in the format 127.0.0.1ConnectStr = "Provider=MSDAORA;Server=" & serverName & ";User ID=" & dbUID & ";Password=" & dbPWD & ";"Set myConn = New ADODB.ConnectionWith myConn.ConnectionString = ConnectStr.CursorLocation = adUseClient.OpenEnd With ' ************************************************** *********Dim myRS As New ADODB.RecordsetDim strSelectSQL As String Set myRS = New ADODB.RecordsetstrSelectSQL = "SELECT * FROM MyPicTable WHERE 1=2" ' This line of code causes the error' Err.Number = -2147467259' Err.Description = Data Type is not supported.myRS.Open strSelectSQL, myConn, adOpenForwardOnly, adLockOptimistic


Could anyone kindly throw some light as to why the application is throwing this error...

Thanks in advance,
Nilanjan.

---

ADO Default Field Values For Oracle
I'm having a problem getting default field values from an ADO recordset using Microsoft's ADO Provider.  I'm 90% certain I saw this work at some point, but I just can't figure out how to get it to work now.  Basically, what I would hope to see is that when I do a .AddNew on the ADO recordset, fields that have a default value would already be populated in the recordset.  Here's a piece of code that I am using:

  m_oConnection.ConnectionString = _
"Provider=MSDAORA.1;Password=PASSWORD;User ID=USER;Data Source=ALIAS"
  m_oConnection.CursorLocation = adUseClient
  m_oConnection.Open
  
  Set m_oRS = New Recordset
  m_oRS.ActiveConnection = m_oConnection
  m_oRS.CursorType = adOpenDynamic
  m_oRS.Source = "select * from TABLENAME"
  m_oRS.Open
  
  m_oRS.AddNew

================

At this point I would expect to see the database column defaults in the field, but do not.  Any suggestions?

Thanks in advance,

Tom
 

---

Ado Retreive Zero Value To Number Oracle's Field
Hello,

I'm working with VB 6.0, and try to query (through ADO) an ORACLE database (OLE-DB for ODBC) all works fine except Number fields. Indeed they're always retreived with a Zero value.

Anybody meet the strange error ?

---

Sorting A Field In Oracle Table?
When i am insering a record through VB6 form to oracle table, the last record i have entered is not saved as the last row, rather it is saved anywhere such as a 3rd or 4th row. I have used OLEDB MSDAORA provider and ADO 2.1. Can u solve these problem?

Another topic is how can i make indexed ( duplicates or no duplicates) a field in Oracle in design time of the table like Access? I am new in Oracle plzz Help me



 

---

Searching On A Database Date Field Using Sql
Can someone help me with what must be a simple solution.

I am passing a date contained in a string variable to a sql routine.

However, the variable is not being recognised as a date


Having passed the date to my routine it is assigned the variable name "search"

So


SqlString= "Select * from Records where date = " & search


The field "date" is of type date in the database.


Please help!

---

Searching For Substring In Field In Access
In my SQL statement, I need to search for a substring in a field.

EX:

Name-----------|Address--------------|
AM-3-50............here
AR-4-43.............home



I want to search the name column and return the names that have a 3 in the 4th character of the string. I was thinking substring, but when i try to use it, access says it doesnt know what it is. Any ideas?

Thanks

---

Searching Numbers In Text Field
I have an access app that keeps track of Job locations/street addresses. A very few of the records have street addresses with "-" in them, like : "120-122 Smith St".

I have a form where I can look up Job addresses. In order to allow the "-" in the street number, I had to change the field from number to text. So when I look for street number between 100 and 105 I get: 100, 1014, 1040, etc.

So unless there is a way to limit the search to exactly between 100 and 105 while allowing the "-", I think I need to create a second street address field for the second part of the street number, so for 120-122, 120 will be in streetNumberfield1 and 122 will be in streetnumberfield2.

Assuming its not possible to keep the fields *with* the "-" as text, and search the way I need to, I need to check the exisitng records and split them if they have '-', so I can go with the 2 street number fields.

I remember there was a way when working with text files that I could check each character in a field, determine if its a "-", then split based on that. Whats the best way to handle this?

---

Problems Searching For A Blank Field
Hi i know this is kinda Newbee but i have a problem searching for the fields in my DB that contain nothing.
I want to sort out the post that or the DB line that has not yet got an owner.

Example of db

onr    oeier        odato        onrnr
2    Wizkidno        12-12-2003    25256
3            13-12-2003    25455
4            14-12-2003    54877
5    lars        09-11-2003    88774

I would now like to apply an sql command that would retrive only the Blank fields into a datagrid.
So it would look like this

onr    oeier        odato        onrnr
3            13-12-2003    25455
4            14-12-2003    54877

I can sucsessfully search for names and retrive them but i cant make the sql search for the blank ones.

 sql = "Select * from tbldmfprovg where oeier like 'lars' " This works and i retrive the posts where lars is the owner
                     Under are som tryes but they didnt work. i only end up with a completly blank datagrid
 sql = "Select * from tbldmfprovg where oeier like '' " This does not work
 sql = "Select * from tbldmfprovg where oeier like "" " This does not work
 sql = "Select * from tbldmfprovg where oeier like Blank "This does not work

Hellp

Thanks

Gabrielli

 

Edited by - Wizkidno on 12/4/2003 2:58:00 AM

---

Problems Searching A Text Field
I am having some trouble searching a text field. This same search works fine on an integer, so I think that the string for .findfirst is incorrect I receive an error message stating "Syntax error (missing operator) in expression" I have tried so many different variations and cannot make it work.

Can anyone suggest where I am going wrong?

code below:
Private Sub txtWellName_DblClick(Cancel As Integer)
Dim SBuffer As Variant
Dim vBookMark As Variant

    SBuffer = InputBox("What is the Well Name?", "Find By Well Name")
    
        vBookMark = m_Well_Head.Bookmark
        m_Well_Head.FindFirst ("DTI Well Name = " & SBuffer)
        If m_Well_Head.NoMatch = True Then
            MsgBox ("No matching Record was found. Check your spelling")
            m_Well_Head.Bookmark = vBookMark
        Else
        DisplayCurrentRow
        End If
    
End Sub

---

Searching Table Field For String
I am trying to search a field for a particular string. For example if the search string is "Shire" and the records for the searching field contain data such as:

"Shire Council of Victoria"
"Dandenong Shire Ranges"
"Shirens Mechanics"

How do I search for that particular string and bring up all possible matches?

Any thoughts/ideas/codes welcome

cheers








A VB turned PHP geek

---

How To Ignore Null When Searching Db Field? - Thanks
How to ignore null when searching db field? - thanks

---

Is BLOB Field Element In Oracle Contain Data?
Does anyone know how to work out if a BLOB field in Oracle contains any data using VB?

---

Addition To Date/time Field In Oracle
Hi Guyz,

Q1
Lets say i have the following data in the date time field..

6/25/2004 7:02:31 AM

How do i add another 8000seconds to this data in oracle?

Q2
And how do i search, for an example, give me all the list of data with 6/25/2005, no matter what time it is.
I tried using the like operator but it doesnt seem to work..

Any ideas?

thankx in advance

---

Insert VB String Into Oracle Blob Field
I would like to insert a VB string as an Oracle Blob field. Could anyone please tell me the way to do it?
Thanks,
VS

---

How To Insert A Date Field In Oracle Table
I am using VB5 and oracle as backend I want to insert a date value in a date field of a table. what insert statement shoud i use??
i tried number of permutations.
e.g.
sqlstr = "insert into customerqry(cid,Billing_End_Date) values(1, " & Format("20/11/1999", "short date") & ")"
it is giving me errror.
please anyone help me. it is a bit urgent.

thyanx in advance

---

Searching An Access Date/Time Field?
Good Morning.
I am having trouble searching an Access(2000) DataTime Field.

The field holds both the Date and Time: But I only want to search this field by the Date.

Below is my search string it returns no records. (Does not give an error, just no records.)

searchsql = "select * from slog where wdate=" & Chr$(35) & FormatDateTime(DTPicker2.Value, vbShortDate) & Chr$(35)

---

Searching For A Specific Field In Access Table
hi
I am trying to match a user given string value with an existing value in a table , can anyone pls tell me how to do it. Also how do i get a Select statement value in to a single Str variable.

Thanx

---

Searching DBase Tables By Date Field
I am successfully using VB6 to read/update some dBase
tables.

However, I have a problem when I try to use a date field
in the select statement. For Access, you normally use
the "#" as a delimiter for the date, like this:

"Trans_Date >= #" & Format(dTransFrom, "yyyy/mm/dd") & "#"

This doesn't seem to work with the dBase data. I have
tried ever format I can think of.

I thought I had it worked out. If I use:

"Trans_Date >= {" & dTransFrom & "}"

this works fine for the start date, and it seems to filter
correctly. However, using the same structure for the end
date, i.e.

"Trans_Date <= {" & dTransTo & "}"

doesn't work.

No error, it just doesn't filter!!!

Does anybody know the correct format?



Edit: Ah, I've actually found that the "{" approach doesn't work either.

What is the correct format?????

---

Trouble Inserting Number Field With Precision In Oracle 8.1.5 DB
I am accesing OracleDb 8.1.5 version using ADO objects in VB. I am able to insert, update and delete records in Oracle DB but when the field is number datatype like(number(10,2)) , I m not able to insert into this field . I am using the following code.
Private Sub Command1_Click()
Dim cn As new ADODB.Connection
Dim rs As new ADODB.Recordset
Dim strConnection As String
Dim strSQL As String

cn.CursorLocation = adUseClient
strConnection = "DSN=TEST;UID=scott;PWD=tiger"
cn.Open strConnection
strSQL = "select * from numbertest"
rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic
rs.AddNew
rs!C1 = 101.5
rs.Update
Set rs = Nothing

End Sub

I am using the Microsoft ODBC Driver for Oracle.
It works fine with the Insert command but it fails for above code.

Can anyone help me with this problem..?

It gives the following error .
"[Microsoft][ODBC driver for Oracle][Oracle]ORA-01722: invalid number" error .
 

---

Test If Field Exists In Oracle Table Using ADODB
Does anyone know of a way to query an Oracle table to see if a field (column) exists by name?, possibly using ADO in VB6...

Cheers

Simon

---

Grouping Records By Month In Oracle PL/SQL From DATE Field
Hi,

I'm sorry if this was posted before. I searched around and didn't see anything that answers my question. So if this is a repeat, sorry.

I have an Oracle table with a DURATION column and a DATE column. The dates are stored as '1/1/0001 12:00:00 AM' format and the durations are stored as Doubles. I'm trying to write a query that returns the SUM of the duration GROUP BY month. So far, here's what I've got:

Code:SELECT DATE, SUM(DURATION) AS DUR
FROM table
WHERE DATE BETWEEN TO_DATE('1/1/2000', 'MM/DD/YYYY') AND TO_DATE('12/31/2000', 'MM/DD/YYYY')
GROUP BY TO_DATE(DATE, 'MONTH, YYYY')

This version doesn't seem to work. If anyone can help straighten this out I'd really appreciate it!


-DDennison

---

Searching For Date Field Contents In Access Table
I have an access database with a field called taskdate defined as a "ShortDate" format (mm/dd/ccyy")

I use the following code to search the database for a record:

statement = "SELECT * FROM tasktable WHERE taskdate=" & CDate(CboTaskDate.Text)

The record does exist, but I always eof on the call.

I tried putting the date in quotes but to no avail.
The MSDN library states that the CDate routine converts any date to date format
but does not refer to any shortdate issues.

When I debug the database record during a non-explicit search, I get date and an added space ? (or special character ) ?

I tried changing the date format to General Date format but still to no avail.

How can I search the short Date field ?

Txs.

BB

---

Searching For A String Within A Database Field....*solved Thx For The Help Guys*
I need to find all the records in a field that contain a certain string within them....

I think this involves using the instr() function, but I'm having difficulty setting the search criteria so that it works....

say if I wanted to find all book titles that contain "dBase" in them in the biblio.mdb file, what should the search requirement look like?

thx

---

Doubt In Fetching Time From Date Field From A Oracle Database Using VB 6.0
I'm using Oracle 9i as my database,in which I have a table with a Date field.Using VB 6.0 I want to fetch the time value from the date field in time format using a Select Query.

---

Actual Size Of The Data In A LONG RAW (oracle Blob) Field
Hi,

I'm going crazy. I want to find out the actual size of the data which is stored in a long raw oracle field. I know how to read and write data to a long-raw field with the Get- and Append-Chunk methods. this works fine as long as you the file length stored separately in another field in this table to input it to the getchunk method. but i dont want to have to input the size - i mean the data is already saved in the long-raw field so why should i additionally supply it from outside.

the property ActualSize for example delivers a wrong value. Theres also a property called DefinedSize which is the max size of the long-raw. But how to get the data size?

Can yomebody help me?

---

Saving And Retrieving Text Rpts Into A Long Raw Field (Oracle)
Hi,

What is the quickest way to insert a report (word doc imported into a rich textbox control) using ADO into a long raw (Oracle) field? Would you recommend the stream object without Append/Get Chunk, vice versa, or the use of both. If you have done this yourself, any insight will be greatly appreciated - especially if you were able to do so with little difficulty! I have used ideas from Microsoft's support site (using the append/getchunk methods), however, am getting garbage along with the text. I have run out of ideas.

Thanks!

---

HELPPPP!!!! How To Show A Long Raw Field From Oracle In Visual Basic's Picture Box
hI,

I NEED TO HOW A LONG RAW ORACLE 'S FIELD, IN A VISUAL BASIC'S FORM.
HOW CAN I DO THAT?

PLEASE HELP ME IS URGENT!!!

THANKS IN ADVANCE

---

Storing And Retrieving Image/Picture In BLOB Field Of Oracle Database
Can anybody help me by providing detail code of storing and retrieving Image/Picture in BLOB field of Oracle database?

---

How To Use CLOB In VB
I want to access a table with one attribute CLOB datatype using VB. Can anyone help me with this? An example or a piece of code on this would be gratly helpful.
thx

---

BLOB Or CLOB In ADO
How do you read a CLOB or BLOB field from oracle 8i in ADO from Visual Basic or Borland C++ Builder (either is fine)

Thanks

Robert

---

Help On BLOB/CLOB
Hi all,

I have got an array of bytes..for ex...
ary(0) = 32
ary(1)=48....ary(4000)=72

I need this entire array to be stored in a column in a oracle database..I know that i can store it in BLOB or CLOB object...but dunno how to do it?

Does anbody have any idea/inputs on this...Pls help...

---

ADO2.5 VB CLob
Hi
I wrote this code with VB6,ADO2.5 to execute stored procedure and obtain a parameter in Clob type in oracle8i.

'-----------------------Start Code------------------------------------------------------------
Dim MyCLob As String
Dim Conn As ADODB.Connection
Dim cmd As ADODB.Command
Set Conn = New ADODB.Connection
Conn.Open "Data Source=x; password=y; user id=z ;Provider=OraOLEDB.Oracle"
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Conn
cmd.CommandType = adCmdStoredProc

cmd.CommandText = "{call Test1(?)}"
cmd.Properties("SPPrmsLOB") = True 'Error Occurs
cmd.Parameters.Append cmd.CreateParameter("Result", adLongVarChar, adParamOutput, 100000)
cmd.Execute

MyCLob = cmd.Parameters(0).Value
MsgBox MyCLob
Set cmd = Nothing
Conn.Close
Set Conn = Nothing
'-----------------------End Code------------------------------------------------------------

The Property SPPrmsLOB does not appear in the properties collection of the command.
Any Advise???
Thanks in advancedVB6,ADO2.5

---