|
Hello everyone,
I am running VB.Net 2005 looking at an Access 2003 database.
I have a task that I am trying to get my head around and I need some help with. I have to read through a table and get the highest number from a certain field. I am kind of doing this already but I am doing a Count, I count the number of records returned from a filter/SQL statement and then using that amount, I add 1 to it and that is my Encounter number. But now I need change this process because it wasn't working out so great. Can someone look at this code and explain how I can change it to accomplish my task? Or point me to an article that will show me how to do this?
What you will see is that I am filtering out based on values in 2 fields, then counting the records returned. Instead I need run this filter/sql statement, but then look at a 3rd field in the table called PatSurgEnNo. and of the records returned with the filter/sql statement of the first 2 fields (MR number and VisitDate), I need to find the highest number in the PatSurgEnNo fields, so I can add 1 to it and that will become my new/current Encounter number.
Thanks in advance for any help that you can give!
Code I have now:
Dim PatSurgDate As String
MR = Me.txtMRNo.Text
PatSurgDate = Me.cbobxVisitDate.Text.ToString()
'Set Encounter to 0, count how many encounters for this date there are
' then add one to the encounter before creating a new entry
PatSurgEncounter = 0
Dim Count As Integer = 0
Using Connection As New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBPath)
Dim cmd As OleDbCommand = New OleDbCommand _
("Select Count(PatSurgAutoNo) as existingrecords from [Pat19PatSurg] where [PatSurgMRNo]=? and [PatSurgDate]=?", _
Connection)
cmd.Parameters.AddWithValue("@PatSurgMRNo", MR)
cmd.Parameters.AddWithValue("@PatSurgDate", PatSurgDate)
Connection.Open()
Count = cmd.ExecuteScalar
End Using
If Count >= 0 Then
'set new SurgEncounterNumber
PatSurgEncounter = Count + 1
Return
End If
|
|
|
|
|
You'd do better to use SQL for both of these, use COUNT and MAX ( from memory ) instead of doing it in memory and reading all the values.
select count(*) from Pat19PatSurg where [PatSurgMRNo]=? and [PatSurgDate]=?
select max(PatSurgEnNo) from Pat19PatSurg where [PatSurgMRNo]=? and [PatSurgDate]=?
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
To get the largest value in a field, you use Max...
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi,
SELECT MAX(PatSurgEnNo) as "MaxNo" FROM TableName; ... but you still need to worry about the multi-user changes.
hope this helps 
|
|
|
|
|
I am working on a import program that will import and update paradox file info in a SQL db. When I run my program I get this error: Cannot open database requested in login 'RAM'. Login fails.
Here is the code for the function:
Public Function ProductExist(ByVal PartNumber As String, ByVal SourceCode As String) As DataTable<br />
Dim con As New OleDbConnection<br />
Dim cmd As New OleDbCommand<br />
Dim dt As New DataTable<br />
Dim da As New OleDbDataAdapter<br />
<br />
'Tell the connection object where the database is.<br />
con.ConnectionString = "Provider=SQLOLEDB;Data Source=****,1433;Initial Catalog=RAM;User ID=****;Password=****;"<br />
<br />
'The command object needs to know which database it's connected to.<br />
cmd.Connection = con<br />
<br />
'The SQL command you want to perform<br />
cmd.CommandText = "SELECT ItemPartNumber, ItemSourceCode FROM Item_JS WHERE ItemPartNumber = '" & Trim(PartNumber) & "' AND " & "ItemSourceCode = '" & Trim(SourceCode) & "'"<br />
<br />
'Tell the DataAdapter which command object we're using.<br />
da.SelectCommand = cmd<br />
<br />
'Finally we instruct the DataAdapter to fill the DataTable<br />
da.Fill(dt)<br />
<br />
Return dt<br />
End Function
The error occurs on the line da.Fill(dt).
Any Ideas?
jds1207
|
|
|
|
|
I'm not a database guy, although it looks like DT is a null reference when you use it.
However, if "da.Fill(dt)" puts da into dt, then my theory falls apart, and could then be that da is not the same type as dt.
Perhaps you could check if the connection/command is being loaded correctly by adding breakpoints to each line (remembering that breakpoints come before the line is executed).
edit: Apologies. Just looked at it in a bit more depth. Ignore the null reference part. It seems I'm reading the lines wrongly, when I should read it like DirectX's CurrentParameters.
Need Another Seven Acronyms...
Confused? You will be...
|
|
|
|
|
Can you access the database by other means? I don't know much about Paradox, but it sounds like the code cannot find it or the login credentials could be wrong...
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
The error is thrown simply because nowhere in your code do you actually connect to the database.
jds1207 wrote: cmd.Connection = con
You tell the DataAdapter to use this connection, but not to actually open it.
If you want you can try to adapt this function to your needs:
Public Function OpenConnection(ByVal conn As SqlConnection, Optional ByVal db As String = "") As Boolean<br />
Try<br />
If conn.State = ConnectionState.Closed Then conn.Open()<br />
If conn.State = ConnectionState.Broken Then<br />
conn.Close()<br />
conn.Open()<br />
If db <> "" Then conn.ChangeDatabase(db)<br />
End If<br />
If conn.State <> ConnectionState.Open Then OpenConnection = False Else OpenConnection = True<br />
Catch Ex As Exception<br />
MsgBox(Ex.Message & "happened in: public function OpenConnection")<br />
End Try<br />
End Function
just call it before .Fill.
Good luck,
Johan
My advice is free, and you may get what you paid for.
|
|
|
|
|
I am assigned a task in my first job that is to take data stored on Sql Server and to Programmitically using VB.NET upload the files into a SharePoint Document library. Does anyone have any suggestions on how to begin this project? Anything will help. Thanks
|
|
|
|
|
I have lookied up the search and just can't quite find the info I need. (I'm nearly there!)
Q1/ If this is the path to save an open Excel file running in the background
xlSheet.SaveAs("c:\My Path\tk" & Format (Me.MonthCalender1.SelectionStart, "yyyyMMdd") & ".xls")
How would I open a file using the MonthCalender1 (Change-Date) as the trigger to determine the name? e.g. "tk20070727.xls" ?
Q2/ Having opened the file (running invisable in the background), how can I pull the data from a cell to a variable?
Q3/ How do I close the invisable running .xls file in runtime?
|
|
|
|
|
Sorry about the shouting, just read it's a No no. Not really "Shouting" just more trying to be seen. Again Sorry!
Frank (in desperate need of help)
|
|
|
|
|
hi all
i need to print the all data on a rich text box how can i do that
plz help
|
|
|
|
|
You might want to look at this: http://www.freevbcode.com/ShowCode.asp?ID=195[^]. Also, if I recall, an article search here on CP turns something up...
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
I have a program that sends packet data through a serial port to a device that requires CRC 16 checksum. I am not familiar with this. Can you suggest some beginner's resources to help me write my own in VB.Net.
|
|
|
|
|
|
I was given code for a crc16 from C# that I converted to VB. Can anyone look at the code and answer some questions for me. I am new at this type of stuff.
Private Sub crc_check(ByVal dat As Byte)
Dim nextone As UInteger
Dim n As Integer
nextone = Integer.Parse(dat)
nextone <<= 8
For n = 8 To 1 Step -1
If (nextone Xor crc) And &H8000 Then
crc = (crc << 1) Xor &H1021
Else
crc <<= 1
End If
nextone <<= 1
Next n
End Sub
The dat, is that one byte at a time or all bytes of my frame?
What is crc? what value should that be?
I get a error when I run this stating Arithmetic operation overflow. Why?
Can anyone help me? Thank you.
|
|
|
|
|
Hi,
I know C#, can read VB but dont speak it, and have done many CRCs
over the years.
There are thousands of CRC definitions; they vary in width, rotational
direction, polynomial value, and other details.
You will typically find two ways of implementing a CRC:
- the one you have, which is adding one item (a byte here) at a time by
iterating over its bits (that's the for loop inside); basically it says:
test the highest bit (that's H8000), if set rotate it back in at the
low end, not with value one, but with the selected polynomial value
(here H1021)
- the other standard implementation is by table lookup, where the for loop
is replaced by something that in the end does exactly the same, based
on a precalculated table (256 entries for a byte method). This way is
some ten times faster at the expense of higher complexity. Dont bother
for now.
Anyway, in the code at hand, clearly crc is a class member that needs to
be initialized once (depending
on the specific CRC that would most likely be to 0 or HFFFF), see C# code;
if no explicit initialization is present, it is an implicit zero, and
you can not reuse that class instance.
Then you feed all your data bytes to the method, one at a time, in the
intended order.
At the end, crc contains the CRC, except that for some CRC definitions
you may still have to invert it (i.e. exor with HFFFF).
The overflow occurs in one of the left shifts; the intention is to
ignore bits that "fall off"; when necessary they got noticed by the
overflow test (remember the H8000).
So either you see a way to turn them off in your code, or you must
modify your code to make sure they never happen.
I am not sure, it is likely though, that VB is defined such that
signed integers can cause overflow, and unsigned integers can not.
If so I suggest you declare both crc and nextone as unsigned.
Otherwise you must explicitly prevent overflow by first anding away
the most significant bit (and with H7FFF I guess), then do the shift left.
Some more comments:
1.
I dont see why you included nextone = Integer.Parse(dat)
I would expect nextone=dat to do exactly the same
2.
please publish code inside PRE tags, that will switch to a more
appropriate font and preserve indentation, making things easier to read.
3.
if starting from C# code it would have been wise to show that too.
One more advice:
as long as a single mistake is present in your code, the CRC will be wrong,
and so much so that you can not even start to guess what happened.
So my advice is make sure you have something else (an entire program on
your PC, on another machine, whatever) that does the same
thing correctly, and that you can feed any data you like; then
start with a single byte (try a couple of values, say H01, then H02, H04,
up to H80), and compare results from both implementations.
If not identical you must fix that first. Only then proceed to a two-byte
message (say H01 H80; or H02 H40; ...); and then a 9-byte message since
by then all integers have reached their 16-bit capacity and new bugs
]may pop up. If this also succeeds, you may be ready for real data of
arbitrary length.
Good luck with your CRC stuff.
|
|
|
|
|
C# code:
<code>void crc_check(unsigned char dat)
{
unsigned int next;
unsigned char n;
next = (unsigned int)dat;
next <<= 8;
for(n=8;n>0;n--)
{
if((next ^ crc) & 0x8000)
{
crc = (crc << 1) ^ 0x1021;
}
else
{
crc <<= 1;
}
next <<= 1;
}
}</code>
VBcode:
Private Sub crc_check(ByVal dat As Byte)
Dim nextone As UShort
Dim n As Integer
nextone = dat
nextone = nextone << 8 And (&H7FFFS << n - 1)
For n = 8 To 0 Step -1
If (nextone Xor crc) And &H8000 Then
crc = (crc << 1) Xor &H1021
Else
crc = crc << 1
End If
nextone <<= 1
Next n
End Sub
Did I convert this right? When I run it it turns everything to 0.
|
|
|
|
|
Cory Kimble wrote: Did I convert this right? When I run it it turns everything to 0
I think you have answered the question yourself.
BTW: The original code looks very much like C, not C#.
BTW: I read everything (everything in latin alphabet), but I don't speak VB
so I will not provide VB statements.
These are errors, the list may be incomplete:
Cory Kimble wrote: nextone = nextone << 8 And (&H7FFFS << n - 1)
1. n has not been assigned a value yet.
2. I don't get what you are trying to do; the C code was next=next<<8;
Together with 1. this probably explains all the zeroes you are getting.
Cory Kimble wrote: For n = 8 To 0 Step -1
3. the loop must execute 8 times, not 9
Cory Kimble wrote: If (nextone Xor crc) And &H8000
4. Does this compile ? the C code
if((next ^ crc) & 0x8000)
is shorthand for
if(((next ^ crc) & 0x8000)!=0)
I don't know whether VB accepts it without a zero-compare.
It is proof the original is not C#
That's it for now.
If you have to publish again, please do it again with original and yours, in
PRE tags, as you did; but then include the definition of crc variable, and
the calling code (initializing crc, looping over the data, etc).
Good luck!
|
|
|
|
|
hi all,
how can i remove icon from any form. i already try the property ShowIcon=false ,but it show .NET default icon in MDI child form
thanks in advance
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|
|
I'm assuming you're form is maximized in the mdi form
if that is the case it's very difficult to remove the form icon
the only way I have found untill now is to set the controlbox property to false, downside you lose alle buttons (minimize,maximize,close) so you'll have to add those yourself
|
|
|
|
|
My form has a TabControl consisting of several TabPages, one of which is called processingTabPage. When the forms Load event fires I remove the processingTabPage for the TabControl's TabPages with :-
TabControl.TabPages.Remove(processingTabPage)
When a particular event occurs I add the tabPage back onto TabControl
TabControl.TabPages.Add(processingTabPage)
Then I would like the processingTabPage to show to the user automatically.
TabControl.TabPages(TabControl.TabPages.IndexOf(processingTabPage)).Show()
All works ok accept the showing of the TabPage to the user. Why?
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
I believe you want to set the TabControl.SelectedIndex or the TabControl.SelectedTab. If you are using .net 2.0 there is also a new method in the TabControl called SelectTab().
Hope that helps.
Ben
|
|
|
|
|
Thanks Ben, that sorted it.
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
hi!
please help me how can i use VScrollBar to Scroll a long page.... using vb.net of vb6
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
