rewriting visual basic windows .exe to stored procedure

Question

Post reply

rewriting visual basic windows .exe to stored procedure

Viewing 15 posts - 16 through 30 (of 44 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Jeff Moden would call this RBAR Row By Agonizing Row.. And it is..

I'm looking at the TAG1 section, trying to get a handle on what it does and why.

It calculates the dates it works on by itself here:

Dim dteFromDate As Date = DateAdd(DateInterval.Day, -21, DateValue(Now))

dteFromDate = DateAdd(DateInterval.Day, -21, dteParm)

Dim dteToDate As Date = DateValue(Now)

Which is then used here:

If .Item("DtCaseStatus") < dteFromDate Or .Item("DtCaseStatus") > dteToDate Then

Continue For

End If

I would have included this in the temp table I mentioned earlier and would not have included any records outside the range of the last three weeks when I built it..

CEWII

farmkittie SSC Eights! Points: 956 More actions · Answer 2

Sounds logical Elliot. I think the person who coded it originally was someone who specialized in VB.

RBAR --> I had "RBAC", just a typo by me, unless I meant row-by-agonizing-crow

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

farmkittie (3/12/2012)
The code I posted is the entire thing except for the data source connection string. Could this: "Me.DsFutureHearings1.CaseStatusHist" be filled from transactions in the table via a user interface? I think that's what's happening.

Is there any other classes or modules? What is on the form itself? I'm thinking the data source is actually defined on the form. But I don't think the user has interaction at this point.

CEWII

farmkittie SSC Eights! Points: 956 More actions · Answer 4

Thanks for continuing to help! There is a data adapter, a connection, a cmdSelect, and a dataset at the bottom of the screen underneath the blank form. The data adapter is named "daFutureHearings", the connector is named "cnJustice", the sqlcommand is named "cmdSelect" and it uses the cnJustice connection. The dataset is named DsFutureHearings1 but the DataSetName in the properties is dsFutureHearings.

Before I meant that there is a user interface application that inserts and modifies data in the Justice database. Outside of this code. This code is some kind of add on that cancels future civil hearings if something (?) happens.

Please let me know if there are any properties or anything else that you need from the 4 objects I mentioned above. Other than properties, what should I look at in these objects, if anything?

P.S. There is nothing in the "CommandText" option under properties of cmdSelect.

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

more info:

Partial Class dsFutureHearings

Partial Class CaseStatusHistDataTable

End Class

when I double click on the CaseStatusHist data set "box" (tab shows dsFutureHearings.xsd). Wish I could include a screen shot but the "box" shows these fields in the table:

StatusID (Primary key)

CaseID

CaseStatClkCdID

DtCaseStatus

CaseStatComm

UserIDCreate

TimestampCreate

UserIDCHange

TimestampChange

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

All I know for sure is that something builds a query for the population of Me.DsFutureHearings1.CaseStatusHist. And I would bet is is a simple select statement. This would be helpful to know.

Still looking at TAG1

CEWII

farmkittie SSC Eights! Points: 956 More actions · Answer 6

I just edited to add some additional info to my previous post. I am not experienced in finding stuff "buried" in Visual Basic forms, I'm afraid. Can you think of anywhere else I should be looking if what I added to mny last post doesn't help?

Thanks

farmkittie SSC Eights! Points: 956 More actions · Answer 7

Also, something I did gave me an error box from Visual Studio that says: "Form1.daFutureHearings could not be previewed

The parameterized query '(@prmDate datetime)SELECT csh.* from ClkCaseHdr AS ' expects the parameter '@prmDate' , which was not supplied." In case that gives you a clue.

THis code, when compiled into an .exe, does work. Do you know where the SELECT above might come from? In other words, where else I could look. It's not in the code that I posted earlier today I don't think. I did a search for the string.

Thanks.

farmkittie SSC Eights! Points: 956 More actions · Answer 8

There is a table in the Justice database named CaseStatusHist that has the same fields as I listed in a previous (recent) post. In the database table, the key fields are StatusID (PK), CaseID (FK), and CaseStatClkCdID (FK). Could the data from this table be what is "feeding" the dataset via the data adaptor or cnJustice Connection?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

That value is filled here I think:

Dim dteParm As Date = DateValue(Now)

'dteParm = "#9-29-2011#"'

Me.daFutureHearings.SelectCommand.Parameters("@prmDate").Value = dteParm

So a better query souds like:

SELECT StatusID,

CaseID,

CaseStatClkCdID,

DtCaseStatus,

CaseStatComm,

UserIDCreate,

TimestampCreate,

UserIDCHange,

TimestampChange

FROM ClkCaseHdr

With some where clause and whatever else you didn't send..

We would create a temptable with the correct field names and type and do an insert, that temp table would be used for the rest of the process.. Previously I called it #CaseStatusHist

CEWII

farmkittie SSC Eights! Points: 956 More actions · Answer 10

Thanks Elliot. What did you mean by "whatever else you didn't send"? I'm sorry I don't understand.

Thank you.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 11

I thought there would be more to the SELECT statement. Such as a where clause.

Also its better not to go back and edit the posts, just add a new one. It makes it hard to follow when we are doing this in real-time..

CEWII

farmkittie SSC Eights! Points: 956 More actions · Answer 12

OK, I'll just reply next time. Do you think there is more to the select statement that i didn't add? I'm having a difficult time trying to find the "hidden" select statement. I just posted some clues based on what I stumbled upon by clicking and poking around in the 4 controls(is that what they're called?) under the form in form designer view. Do you think the data IS coming from the table that exists in the Justice database that has the same name as the dataset? CaseStatusHist?

edited to correct type from "dame" to "same"

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

farmkittie (3/12/2012)
OK, I'll just reply next time. Do you think there is more to the select statement that i didn't add? I'm having a difficult time trying to find the "hidden" select statement. I just posted some clues based on what I stumbled upon by clicking and poking around in the 4 controls(is that what they're called?) under the form in form designer view. Do you think the data IS coming from the table that exists in the Justice database that has the dame name as the dataset? CaseStatusHist?

Assuming you have the ability to point this to some test environment, there's a relatively easy way to find out: turn on profiler. Assuming you're the only one accessing that test environment, you can turn on a profiler session on the affected databases, then have the EXE run. Profiler will capture the various SQL statements that are run, which you should be able to isolate by user, so you can tell which are originated by your Visual Basic executable.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

farmkittie SSC Eights! Points: 956 More actions · Answer 14

Thanks Matt. I can definitely do that. I'll post again when I see what is happening.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 15

As a bit of a side bar... I find out which server the existing code is running against and try to setup to capture the SQL it's generating by the name of the application. It doesn't always work well but but it does, it works very well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)