How to write SQL statement with LIMIT in it in Visual basic

[sivadhanekula]

Hi...every one...

I have a problem with below statement

selectstatement = "SELECT Messwert,UPLimWert,LOLimWert,Fullname FROM abssubtest Where Fullname LIKE """ & Fullname & """ LIMIT 0, 5 "

here the underlined fullname is the item from combo box

If I have the same statement without LIMIT then the program is working...if it is with LIMIT I have some problem...is the statement I have given correct?...

if not can you plzz kindly give me suggestions of how to write an SQL statement with LIMIT in Visual Basic...

Thanks in Advance

Regards
SIva

[kadghar]

perhaps you should use chr(34) instead of """

selectstatement = "SELECT Messwert,UPLimWert,LOLimWert,Fullname FROM abssubtest Where Fullname LIKE " & chr(34) & Fullname & chr(34) & " LIMIT 0, 5 "

[sivadhanekula]

Hi...Thank you for the reply...

Problem is not with that...it is with LIMIT...if you look at the whole code...I have too many values in Fullname...but I want only the values of certain interval...and for that reason I have used LIMIT statement...but when I use this I am getting only one column and the other columns are nill...The column I am getting is Fullname with all names in it....without any limit....my question is...is there any particular format for addressing the LIMIT statement in vb 6.0...

Private Sub Okbutton_Click()
On Error Resume Next

Dim Fullname As String
Dim CSV_line As String
'Dim Doublestring As String
Dim UppLimitstring As String
Dim Messwertstring As String
Dim LowLimitstring As String
Dim selectstatement As String
Dim Fullnamestring As String
Dim xs As Integer
Dim ys As Integer
Dim rs3 As Recordset

Fullname = FullnameList.Text
xs = x.Text
ys = y.Text


selectstatement = "SELECT Messwert,UPLimWert,LOLimWert,Fullname FROM abssubtest Where Fullname LIKE " & Chr(34) & Fullname & Chr(34) & " LIMIT 0, 5 "

Set rs3 = db.OpenRecordset(selectstatement)

Open "K:\user\Dhanekula\Visual Baisc\file.csv" For Output As #1
Print #1, "Messwert;UpperLimit;Lowerlimit;Fullname"

Do While Not rs3.EOF
Messwertstring = rs3.Fields("Messwert")
LowLimitstring = rs3.Fields("LOLimWert")
UppLimitstring = rs3.Fields("UPLimwert")
Fullnamestring = rs3.Fields("Fullname")
Messwertstring = Replace(Messwertstring, ".", ",")
LowLimitstring = Replace(LowLimitstring, ".", ",")
UppLimitstring = Replace(UppLimitstring, ".", ",")
Fullnamestring = Replace(Fullnamestring, ".", ",")
CSV_line = Messwertstring & ";" & LowLimitstring & ";" & UppLimitstring & ";" & Fullnamestring '& ";"
Print #1, CSV_line
rs3.MoveNext
Loop
Close #1

End Sub

[kadghar]

I see, the problem has nothing to do with VB but with the statement you're sending to your DB Server.

Perhaps it's because some SQL Server versions dont support the LIMIT statement (just guessing). Why dont you try with something like SET ROWCOUNT 5.

HTH