Visual Basic

Re: Populate Data form MySQL Database Table in Datagridview by Datagridview autocomplete Textbox cell

27-Sep-13 19:24

Is nobody there to solve me ?

Richard MacCutchan27-Sep-13 21:23

ADODB recordset not returning case-sensitive results

27-Sep-13 21:23

If anyone knows the answer they will suggest it. In the meantime try running your code through the debugger to see what is happening.

Veni, vidi, abiit domum

Member 1026522824-Sep-13 9:07

Re: ADODB recordset not returning case-sensitive results

24-Sep-13 9:07

I have an application using an ADODB connection to a DBase database (simple and easy to distribute). I captured the SQL string sent to the connection object using Quickwatch. I pasted the SQL statement into an SQL tool. I executed the SQL and it returned no rows which is correct. However when the same SQL string is passed to the ADODB connection it returns 189 rows.

Here is the code the initializes the ADODB connection:

' Define DBase connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\DataStore;Extended Properties=DBASE III"
ConnDBF = New ADODB.Connection

' Open DBase database connection
ConnDBF.Open(strConn)
OK = ConnDBF.State

' Initialize recordset
rstGRK = New ADODB.Recordset
rstGRK.CursorType = ADODB.CursorTypeEnum.adOpenStatic

Here is the SQL string being passed:

' Get result set
rstGRK.Open(SQLString, ConnDBF)

' Get number of hits
ResultCnt = rstGRK.RecordCount

Here is the SQL string:

XML

<pre>SELECT BKL.BkShtName,
       SRC.BookNo,
       SRC.ChapNo,
       SRC.VerseNo,
       SRC.VerseText1,
       SRC.VerseText2
FROM BIENBVS SRC,
     BIBKLST BKL
WHERE SRC.BookNo = BKL.BookNumber
      AND (SRC.VerseText1 LIKE '%frank jones%'
           OR SRC.VerseText2 LIKE '%frank jones%' )
ORDER BY 2, 3, 4</pre>

The correct results is NO ROWS because the names begin with upper case. The ADODB connection returns 189 rows which is incorrect.

Does anybody have any ideas what could be going on and how to force the connection to be case sensitive?

Dave Kreskowiak24-Sep-13 12:12

Re: ADODB recordset not returning case-sensitive results

24-Sep-13 12:12

The Jet database engine is inherently NOT case sensitive, so you have to force it to be.

I don't use Access (EVER!) but IIRC, you're looking for something like this:

SELECT ... FROM ... WHERE SRC.BookNo = BKL.BookNumber
    AND (StrComp(SRC.VerseText1, "frank jones", 0) = 0
    OR  (StrComp(SRC.VerseText2, "frank jones", 0) = 0)
ORDER BY ...

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Member 1026522825-Sep-13 5:33

Re: ADODB recordset not returning case-sensitive results

25-Sep-13 5:33

The database is actually DBase (good reason to use, but it is a long explanation).

I converted the VB app to use OdbcConnection & OdbcDataReader methods and the ODBC DSN created with driver - "Microsoft dBase Driver (*.dbf)" . The results are the same - the query in VB still refuses to be case-sensitive.

If I put a breakpoint in the code and capture the SQL string and paste the string verbatim into WinSQL (an SQL tool using the same exact ODBC DSN), and execute the query, no results are returned.

The query behaves differently in VB that in an SQL tool using the exact same driver and ODBC definition.

Do not know why it works differently in VB than in WinSQL !!!!!

Dave Kreskowiak25-Sep-13 7:57

Re: ADODB recordset not returning case-sensitive results

25-Sep-13 7:57

I have no idea since I don't have any access to any dBase databases. Thats one I have used since 1986.

I have no idea how to make it case sensitive for dBase.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

TnTinMn25-Sep-13 13:34

TnTinMn

25-Sep-13 13:34

I played around with this bit and was perplexed by your problem with case-insensitivity as I was not seeing it. That was until I noticed that you included an "Order By" clause. At least on my end, it is the "Order By" that makes it case-insensitive. Weird! WTF | :WTF:

I only used a single table, so I don't know if that will effect anything, but it may worth a try to drop the "Order By" clause and see what happens.

If that resolves the issue, you could load a DataTable with your query results and apply a sort on the DefaultView to achieve the needed sort order.

Good Luck.

Re: ADODB recordset not returning case-sensitive results

Member 1026522826-Sep-13 4:51

Re: ADODB recordset not returning case-sensitive results

26-Sep-13 4:51

I tried removing the "ORDER BY" clause, but the results were the same.

However, after running the query from within VB, I ran the same exact query in WinSQL and it returned multiple rows. Then I ran it a second time and it returned 0 rows. Running it a 3rd and 4th time still returned 0 rows.

I returned to VB and ran the query again. Then I reran it in WinSQL and the first time the query returned many rows and the 2nd, 3rd, 4th, ..... runs returned 0 rows. This behavior is repeatable.

That tells me that the VB app is setting some unknown property that is causing the query to be case-insensitive.

I am going to turn SQL tracing on and see if I can find a difference in the SQL.LOG created from VB and the SQL.LOG created by a good run in WinSQL.

Don't you just love it when the application/objects help you by doing something you did not ask it/them to do !!!!!

Member 1026522826-Sep-13 6:07

26-Sep-13 6:07

Based on a suggestion from someone on another forum, and mega frustration, I decided to abandon the current ODBC DSN. I found and installed a Visual FoxPro driver and created a new DSN using that driver.

The query is now case-sensitive as it should be.

Select Audio Track

Andre.Adrian23-Sep-13 0:32

Andre.Adrian

23-Sep-13 0:32

How To select audio track use vb.net using cronoapi.dll?

Dave Kreskowiak23-Sep-13 1:23

23-Sep-13 1:23

Never heard of it.

I have no idea where you got it from, so I don't know where you should ask about it either.

You ask the people who write the .DLL about how to use it.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Brisingr Aerowing24-Sep-13 11:39

Brisingr Aerowing

24-Sep-13 11:39

ChronoTron[^] it seems.

Keep Clam And Proofread
--
√(-1) 2³ ∑ π...
And it was delicious.

Dave Kreskowiak24-Sep-13 12:05

24-Sep-13 12:05

Yeah, I Goggled the sh*t out of the .DLL name and came up empty. Either way, the advice is still the same. ask the people who wrote it, not the people who are guessing at what you're using! Wink | ;)

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Brisingr Aerowing24-Sep-13 14:23

Brisingr Aerowing

24-Sep-13 14:23

Dave Kreskowiak wrote:
Goggled

That's your issue, you needed to Google it!

Keep Clam And Proofread
--
√(-1) 2³ ∑ π...
And it was delicious.

Dave Kreskowiak24-Sep-13 15:45

24-Sep-13 15:45

Damn spellcheck built into IE!

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Andre.Adrian25-Sep-13 23:42

Andre.Adrian

25-Sep-13 23:42

can't you show me how to implement it to vb.net source?

Currency Round Off

Biplob Singha Shee22-Sep-13 4:09

22-Sep-13 4:09

Hi all,

I am trying to create a sales invoicing system. Everything is OK but I want to round off (+ or -) the value.

Suppose if Invoice Amount is Rupees 2500.65/-

The round off amount will be Rupees 2501.00/- (i.e. 2500.65 + 0.35)

but if the Invoice Amount is Rupees 2500.45/-

The round off amount will be Rupees 2500.00/- (i.e. 2500.45 - 0.45)

I tried the following function...

Private Function CalculateRoundOffAmount(ByVal autoCalculateRoundOff As Boolean) As Double
        
        Dim result As Double = 0.0
        
        result += CDbl(Me.TbRoundOff.Text)
        Dim roundOff As Double = 0
        Dim roundedOffTo As Double = 0
        If autoCalculateRoundOff = True Then
            roundOff = Math.Ceiling(result) - result
        Else
            roundOff = Me.TbRoundOff.Text
        End If
        roundedOffTo = result + roundOff
        Me.TbRoundOff.Text = roundOff
        Me.TbInvoiceValue.Text = roundedOffTo
        Return roundedOffTo
    End Function

TbRoundOff = A TextBox where the round off amount will be calculated and TbInvoiceValue.Text is the textbox where invoice value is being calculated.

and I implemented it in SaveData() method like CalculateRoundOffAmount(True) but nothing happened.

Please help...

Richard MacCutchan22-Sep-13 4:55

22-Sep-13 4:55

You should not use floating point numbers for financial calculations, as they tend not to hold exact values. Use integer values and convert all your totals to NP. You can then do the rounding quite easily by converting to whole rupees and using the remainder to decide which way to round off.

Veni, vidi, abiit domum

Biplob Singha Shee22-Sep-13 6:00

22-Sep-13 6:00

Hi Richard,
many thanks for your quick reply. But could you please help me on coding method how would I do that ?

Richard MacCutchan22-Sep-13 7:01

22-Sep-13 7:01

When you enter the money amounts, multiply the rupees by 100 and add the paisa, to give the total and convert to (and save as) integer type. You can then perform all currency calculations on these integer values. When you wish to print or display the values you will divide by 100 to get the round number of rupees and use the Mod operator to get the remainder (paisa). You can then print the two values to give tha absolute total, or use the paisa value to decide whether to round up or down. My knowledge of Visual Basic is not very good, so you will have to turn that into code for yourself.

Veni, vidi, abiit domum

Biplob Singha Shee22-Sep-13 7:13

22-Sep-13 7:13

Thanks Richard,
Let me try it first.

Thanks again.

Richard Deeming23-Sep-13 1:56

Richard Deeming

23-Sep-13 1:56

Richard MacCutchan wrote:
Use integer values and convert all your totals to NP.

Or use the built-in Decimal[^] type, which does that for you.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Richard MacCutchan23-Sep-13 3:05

23-Sep-13 3:05

Thanks, I forgot about that.

Veni, vidi, abiit domum

Biplob Singha Shee23-Sep-13 10:21