|
I wrote this query:
select name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,memberpayment,month,year,amount,vat,expirydate,membercar,timezone,timezonename,passback
from (select name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,memberpayment,month,year,amount,vat,expirydate,membercar,timezone,timezonename,passback,
row_number() over (partition by name order by serial) as seqnum
from membersvw
where familyname='Eliane El Asmar'
) membersvw
where seqnum = 1;
it gave me what i wanted. But once i put it in my vb code. it gives me an error: incorrect syntax near the keyword select.
This is the code:
strsql = "select serial,name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,memberpayment,month,year,amount,vat,expirydate,membercar,timezone,timezonename,passback"
strsql = strsql & "from (select name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,memberpayment,month,year,amount,vat,expirydate,membercar,timezone,timezonename,passback,"
strsql = strsql & " row_number() over (partition by name order by serial) as seqnum"
strsql = strsql & "from(membersvw)"
strsql = strsql & "where familyname='Eliane El Asmar'"
strsql = strsql & " ) membersvw"
strsql = strsql & "where seqnum = 1;"
Any suggestions?
And if i put * insted of : serial,name,familyname,address,mobile,phone,fax,em ail,website,space,make,model,color,plate,tag,membe rpayment,month,year,amount,vat,expirydate,memberca r,timezone,timezonename,passback
I get another error: Incorrect syntax near '('
|
|
|
|
|
Concatinate the query properly, atleast give space before/after double quotes.
4th line of code is wrong I think. Why there're brackets? In your query, there's no brackets. Check the below query( corrected little bit(?) )
strsql = "select serial,name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,memberpayment,month,year,amount,vat,expirydate,membercar,timezone,timezonename,passback "
strsql = strsql & " from (select name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,memberpayment,month,year,amount,vat,expirydate,membercar,timezone,timezonename,passback, "
strsql = strsql & " row_number() over (partition by name order by serial) as seqnum "
strsql = strsql & " from membersvw "
strsql = strsql & " where familyname='Eliane El Asmar' "
strsql = strsql & " ) membersvw "
strsql = strsql & " where seqnum = 1;"
thatrajaCode converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute
|
|
|
|
|
strsql = "select y"
strsql = strsql & "from (select x)"
creates the following output
select yfrom (select x)
there is missing a space in front of the "from" keyword
It's easiest if you just prepend an "@" sign infront of the whole statement (which enables you to write multiline strings" so no need for string concatenation 
|
|
|
|
|
Put a space at the end of each line inside the quote marks. Remember, there are no line breaks inside the string you built. The way you have this written now results in this:
select serial,name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,
tag,memberpayment,month,year,amount,vat,expirydate,membercar,timezone,timezonename,passbackfrom (select
name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,memberpayment,
month,year,amount,vat,expirydate,membercar,timezone,timezonename,passback, row_number() over (partition by name order by serial) as seqnumfrom(membersvw)where familyname='Eliane El Asmar' ) membersvwwhere seqnum = 1;
(Line breaks were added by me. Ignore them because they don't exist in your SQL statement)
Look carefully. See any problems in there??
|
|
|
|
|
I also have the same problem and i don't know how to sort out this weird issue, some help would be great ^^
|
|
|
|
|
It's easy to solve. Look at the resulting string you built since you insisted on using string concatenation to build your query. Chances are really good you put two words together because you forgot to include a space at the end of a string or the beginning of the next.
|
|
|
|
|
Thank you guys.You helped very much
The problem was the space
Thanks  
|
|
|
|
|
I am reading a MSWorld doc template and replcaleing tag with values from db.
Here is my code but it's not working... could you help me
Public Sub SearchAndReplace(ByVal document As String)
Dim wordDoc As WordprocessingDocument = WordprocessingDocument.Open(document, True)
Using (wordDoc)
Dim docText As String = Nothing
Dim sr As StreamReader = New StreamReader(wordDoc.MainDocumentPart.GetStream)
Using (sr)
docText = sr.ReadToEnd
End Using
Dim regexText As Regex = New Regex("##ReplTag##")
docText = regexText.Replace(docText, "ReplValue")
Dim sw As StreamWriter = New StreamWriter(wordDoc.MainDocumentPart.GetStream(FileMode.Create))
Using (sw)
sw.Write(docText)
End Using
End Using
End Sub
|
|
|
|
|
Define "not working."
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Hello. I am making a crystal report. i have a member that have three cars. I need to load the informations about the three cars and the informations aboout the member in the crystal report. How can i do that?
the crystal report should look something like this:
Name Name
FamilyName FamilyName
Space space
Address Address
Cars: (this is a text object)
-----
Make Model Color Plate
Make1 Model1 Color1 Plate1
Make2 Model2 Color2 Plate2
Make3 Model3 Color3 Plate3
Is that possible to do?
Thank you for your help
modified 21-Feb-14 9:47am.
|
|
|
|
|
Member 10506215 wrote: Is that possible to do?
What reason is there to assume that it can't be done?
Member 10506215 wrote: How can i do that?
Described here[^].
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hey i checked. Unfortunately i couldn't find what i was looking for :/
I'm trying now to use grouping in the report to see where i can get to. Thank you.
|
|
|
|
|
From the original question I assumed you had problems with creating a report. The word "grouping" reeks like SQL.
How does your query look like, and what's the table structure?
Member 10506215 wrote: Thank you.
I shouldn't have assumed but asked; good point there 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
If cbOr.Checked = True And cmbCarMake.Text = "" And cmbCarModel.Text <> "" And cmbMembers.Text <> "" Then
strsql = "select * from membersvw where model = '" & cmbCarModel.Text & "' and serial = '" & cmbMembers.SelectedValue & "' ; "
I tried to put select distinct. same result. i get this in the report:
Honda Accord Dark Blue 123456 2
Honda Accord Dark Blue 123456 2
and i need only one record to appear.
|
|
|
|
|
|
i managed to change my sql query. it works in sql but when i out it in my code. It gives me an error. So i'm gonna post the problem in a new thread.
Thank you guys
|
|
|
|
|
Member 10506215 wrote: It gives me an error.
Why didn't you include that details?  Always include those details in your question, never forget.
thatrajaCode converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute
|
|
|
|
|
I have the following codes which works fine.
Dim strText As String = ("อาหามะ กาเจ")
Dim strData As String
Dim byteArray() As Byte
byteArray = Encoding.GetEncoding(874).GetBytes(strText)
'>> 874 is Thai Code Page
strData = Encoding.Default.GetString(byteArray)
'>> strData contains "ÍÒËÒÁÐ ¡Òà¨"
strText = ("عمر الحاج الحضرى")
byteArray = Encoding.GetEncoding(1256).GetBytes(strText)
'>> 1256 is Arabic Code Page
strData = Encoding.Default.GetString(byteArray)
'>> strData contains "ÚãÑ ÇáÍÇÌ ÇáÍÖÑì"
My problem is that the content strText will be read from a
database table field which could be in Thai or Arabic. How
can I determine to use the right copde page in the below VB
statement
Encoding.GetEncoding(1256).GetBytes(strText)
Would appreciate some can help.
|
|
|
|
|
You would need to analyse every character in the string, and even then it may not be 100% certain. You need some other information with the data that tells you what language it is.
|
|
|
|
|
You'll need to save the code page in a separate column next to the VARCHAR, or turn that into an NVARCHAR. You can't "determine" the codepage from a random string of bytes.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Why MemoryStream object uses double the memory?
For example, whether the capacity of MemoryStream is 1GB in Task Manager I see that is consuming 2GB.
Module Module1
Sub Main()
Dim ObjMemoryStream As System.IO.MemoryStream
ObjMemoryStream = New System.IO.MemoryStream()
System.Console.WriteLine("Capacity: {0}", ObjMemoryStream.Capacity.ToString())
System.Console.WriteLine("Length: {0}", ObjMemoryStream.Length.ToString())
Dim j As Integer
For i As Integer = 0 To 1073741824 Step 1
If (i Mod 256) = 0 Then j = 0 Else j = i Mod 256
ObjMemoryStream.WriteByte(j)
Next i
System.Console.WriteLine("Length: {0}", ObjMemoryStream.Length.ToString())
Dim ObjFileStream As System.IO.FileStream
ObjFileStream = System.IO.File.Create("C:\TEST.TXT", 1024)
Dim ObjBinaryWriter As System.IO.BinaryWriter
ObjBinaryWriter = New System.IO.BinaryWriter(ObjFileStream, System.Text.Encoding.ASCII)
ObjMemoryStream.Position = 0
ObjBinaryWriter.Write(ObjMemoryStream.ToArray())
ObjBinaryWriter.Flush()
ObjBinaryWriter.Close()
System.Console.ReadKey()
End Sub
End Module
|
|
|
|
|
First of all, you don't see the MemoryStream in isolation as far as memory consumption goes. There's the rest of the program, including all the assemblies that get loaded in - they all take up memory. Secondly, don't rely on Task Manager to determine memory profile. Task manager is a notoriously poor way of judging memory usage. Part of the reason for this is that .NET grabs more memory than it needs - this is to give the application room "to grow into" as it runs. Memory allocation is an expensive operation, so having your application grab just the bytes it needs while it's running would be a waste of time.
|
|
|
|
|
That's how much memory the MemoryStream has set aside for use. Since you didn't specify a capacity, it starts off with 256 bytes by default. As the stream is used and the current capacity is exceeded, it reallocates it's internal buffer and sets a new size larger than the current capacity. That new size is always twice the last size it used.
Arrays in .NET are immutable. Once created, you cannot change the size of an array. That limitation applies to everything in the .NET Framework, including the buffer used by MemoryStream. In order to increase the size, a new array of some size must be created and all the elements of the previous array copied to it, then the previous array is destroyed.
|
|
|
|
|
I think that object are storing the the data using two memory byte, please check the next example:
Module Module1
Sub Main()
Dim ObjMemoryStream As System.IO.MemoryStream
ObjMemoryStream = New System.IO.MemoryStream(1073741825)
System.Console.WriteLine("Capacity: {0}", ObjMemoryStream.Capacity.ToString())
System.Console.WriteLine("Length: {0}", ObjMemoryStream.Length.ToString())
Dim j As Integer
For i As Integer = 0 To 1073741824 Step 1
If (i Mod 256) = 0 Then j = 0 Else j = i Mod 256
ObjMemoryStream.WriteByte(j)
Next i
System.Console.WriteLine("Capacity: {0}", ObjMemoryStream.Capacity.ToString())
System.Console.WriteLine("Length: {0}", ObjMemoryStream.Length.ToString())
Dim ObjFileStream As System.IO.FileStream
ObjFileStream = System.IO.File.Create("C:\TEST.txt", 1024)
Dim ObjBinaryWriter As System.IO.BinaryWriter
ObjBinaryWriter = New System.IO.BinaryWriter(ObjFileStream, System.Text.Encoding.ASCII)
ObjMemoryStream.Position = 0
ObjBinaryWriter.Write(ObjMemoryStream.ToArray())
ObjBinaryWriter.Flush()
ObjBinaryWriter.Close()
System.Console.ReadKey()
End Sub
End Module
|
|
|
|
|
No, it's not. What you're storing is irrelevant. If you keep exceeding the capacity of the MemoryStream, it will keep allocating new arrays, twice as large as the last, until it can't grow any more (2GB).
These are the sizes the MemoryStream went through after you created it and keep adding more and more data to it:
256
512
1024
2048
4096
8192
16384
32768
65536
131072
262144
524288
1048576
2097152
4194304
8388608
16777216
33554432
67108864
134217728
268435456
536870912
1073741824
2147483648
Oh, look at that. It managed to hit 2GB in size right after you added 1073741824 bytes of information to it. It's allocating bigger and bigger blocks of memory because it has no way of knowing how big to make the array to that it fits your data perfectly.
If you want to keep it from getting to 2GB of memory, allocate the MemoryStream with the expected size as a parameter, say 1.5GB:
Dim ms As New MemoryStream(1610612736)
Now, if you exceed that, the MemoryStream will try to reallocate itself using twice the size you initially specified and that will lead to an OutOfMemory exception.
You can NOT look in Task Manager to see how much memory your app is using. It's telling you how much memory the .NET CLR as RESERVED for your app. This will always be larger than what your app is actually using.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
