Posts by Papa_Don

Group,

I'm trying to write some code that will read the clocktime. When it hits a predetermined time, I want it to run a routine. I know how to do this in VB.net. It would be done like this:

Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick

        If TimeOfDay = "10:01:00 PM" Then
            'Go do something important
        End If

End Sub

Unfortunately I don't know the equivalent to the same in vbScript (if there is something like it).

I did find a "timer" online that counts the number of seconds from midnight. It looks like this:

Function TimeIt(N)

    Dim StartTime, EndTime
    StartTime = Timer
    For I = 1 To N
    Next
    EndTime = Timer
    TimeIt = EndTime - StartTime

End Function

However I'm not sure how to use this. So my questions are:

1) What is the "(N)" representing (better, what is "N")? I see it used in the loop.
2) When using a "Function", how is it called within your primary sub-routine?

If you have a better code suggestion to do what I want to do, I'm all ears!

In reading, I gather that vbScript is similar to VB6. Thus the reason for posting here. If there is a better forum to send this to, please direct me.

In advance, thanks for any assistance.

Don

cgeier, the name of the program we use is called 'SecureCRT' (http://www.vandyke.com/products/securecrt/). I'm not sure if this is Java (although I'd bet $2 it is). You might be able to read something via their website that might answer the question. It does seem there may be some information there on using Visual Basic Script to retrieve screen data. I'm going to read further on that later today to see if I can learn something there.

Oxiegen, I'd prefer to link directly to the server. Unfortunately it's remote and the company won't allow me to do it (although I think I will ask again!). I'll read up on 'hook' you've referred to and hopefully that will lead me in the right direction. I'll also try doing the screen shot and OCR.

Thanks again to both of you for responding. I hope to learn how to do this!

cgeier, I don't meant to be cryptic. My apologies for appearing that way.

The desire to automate this screen scraping is due to the fact that we are having to do it manually and are doing it about 4 hours before it needs to be done. Lastly, as a group we are spending about 10 hours 5 days a week doing it. Plus, we miss doing it on the weekends (no one is at the office).

My goal is to have a computer to do this automatically every night at midnight. This way I get the full picture of what transpired before the data is lost (the current system still prints the daily details I need and doesn't store it in a history field). You'd think the computer would store this data until the next business day. Unfortunately it only stores the "important" parts of the data, but not the day and time it is created (which is part of what I need). One of the primary needs of capturing the info has to do with ensuring it's rate accuracy.

When I get to the office tomorrow, I'll confirm the name of the software we use to connect to the server.

cgeier, I'm not sure what to call the application that I'm using. The program in reference most likely is an old "green screen" product and may be UNIX based. I'm connecting to it via the web in some kind of third party party product. I'll be happy to get back with you to give you more info, I'll need to know what to look for to provide better info.

Hello group!

I need to scrape data from the screen of an open application. It will be used as text, so it will need to be converted into a string that I can parse. I've looked around and found scrapers that use the "picture" as a bitmap or jpeg. The few examples I've seen that do convert the scrape to text cover HTML info from websites. The few I've tried haven't worked in any application for me. So I need help. Can you tell me where to start? I've seen tools like "CopyFromScreen" and "Regex", but I'm not sure what will grab the text from the screen. Your direction will be appreciated.

If it helps to know, currently I do this screen scraping manually each day. Of course it's a simple scrape and copy, then paste the data into a text file (using Notepad). I feel sure I can automate some of this. I want to learn how to do this!

In advance, thanks for your comments, directions and assistance. It will be most appreciated.

Don

Mr. M, I changed the interval to 1000 as suggested. BINGO! That did the trick!!

By the way... I did not insert the "Timer1.Stop" code. Truthfully, I didn't want to do that. As this program developes further, I'm going to add some different tasks to it that will run at various times. Thus it needs to continue to run.

Thanks again for my lesson of the day! You've been of great help! Now on to the next issue!

For what it's worth, I've discovered that the formatting of what I'm searching with has to be in the same format as what I'm searching for. In other words if the search criteria is '04-OCT' then the date on sheet2 needs to be in the same format (which I thought was odd). I was truly under the impression that a date, regardless of how it was formatted on the spreadsheet, was being read as an integer representing the number of days beyond 1/1/1900. However I found that to be untrue.

Stuugie, thanks for the help. Although I didn't use your code, I do like the way you wrote it.

Hello group!

I've created a short program to open an application at a specific time. It's working but is attempting to open the application multiple times. What should I be doing to control the number of times it is opening? It only needs to open one time.

Here's my code:

Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
        Label7.Text = TimeOfDay
        If TimeOfDay = "12:02:00 AM" Then
            Process.Start("C:\Program Files (x86)\TestProgram\ftw.exe")
        End If
End Sub

How do I fix this? Any thoughts?

FYI... I'm using Visual Studio 2010/Visual Basic.net

In advance, thanks for the help!

Don Wilson

Hello Group,

I'm writing a macro to match dates from one spreadsheet to another. Specificially, SpreadsheetA has the date listed as text. I want to match that date to a range of dates in a column to find the row the matching date is on.

I have some things that I need to be prepared to overcome:
SpreadsheetA has the date I'm looking for and it's formatted as "20-SEP-2014". SpreadsheetB has all of the dates in my range formatted as "20-Sep". I've taken account for this in my code:

strngDate = Right(findDate, 11)
strngDate = Left(strngDate, 6)

rowA = Columns("A:A").Find(What:=strngDate, After:=[A2], LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, **MatchCase:= False**, _
SearchFormat:=False).Row

My challenge now is to eliminate the worry of the date formatting on spreadsheetB. In otherwords is there a way that I can convert everything to the date-serial-number ensure that, regardless how the date is formatted, the routine will always find the correct row?

In advance, thanks for your answers!

Don Wilson

cgeier,

Thanks for the tip. I'll do it.

Don

cgeier,

Forgive me for asking the above question. For what it's worth, I have some VB.net history here and didn't see a group for VBA users. Because of the similarities between VBA and VB.net, I thought that some of the more experienced programmers could offer some advice on how to get the routine to pick up that last group.

Don

I need to dynamically group rows together in an EXCEL worksheet. The obvious choice to do this will be VBA. I hope you can help.

My spreadsheet has 18 columns and (up to) 5,000 rows of data. I've written the following code which works "OK" however it fails to group the last group and those few rows afterwards.

Private Sub Worksheet_Activate()
    Dim myRange As Range
    Dim rowCount As Integer, currentRow As Integer
    Dim firstBlankRow As Integer, lastBlankRow As Integer
    Dim currentRowValue As String
    Dim neighborColumnValue As String

    'select range based on given named range
    Set myRange = Range("B16:B5000")
    rowCount = Cells(Rows.Count, myRange.Column).End(xlUp).Row

    firstBlankRow = 0
    lastBlankRow = 0
    'for every row in the range
    For currentRow = 16 To rowCount
        currentRowValue = Cells(currentRow, myRange.Column).Value
        neighborColumnValue = Cells(currentRow, myRange.Column - 1).Value

        If (IsEmpty(currentRowValue) Or currentRowValue = "") Then
            'if cell is blank and firstBlankRow hasn't been assigned yet
            If firstBlankRow = 0 Then
                firstBlankRow = currentRow
            End If
        ElseIf Not (IsEmpty(currentRowValue) Or currentRowValue = "") Then
            'if the cell is not blank and its neighbor's (to the left) value is 0,
            'and firstBlankRow hasn't been assigned, then this is the firstBlankRow
            'to consider for grouping
            If neighborColumnValue = 0 And firstBlankRow = 0 Then
                firstBlankRow = currentRow
            ElseIf neighborColumnValue <> 0 And firstBlankRow <> 0 Then
                'if firstBlankRow is assigned and this row has a value with a neighbor
                'who isn't 0, then the cell one row above this one is to be considered
                'the lastBlankRow to include in the …

Group,

I've got a protected spreadsheet at work that prevents me from creating links or writing macro's to do some repetitive copying and pasting from one spreadsheet to another. I hope someone can help.

I'd like to use VB (if possible) to copy from a specific range in Spreadsheet1.Sheet1 (let's say D5 to J15). I then want to go to Spreadsheet2.Sheet1 and "Paste Value" starting at cell D2. Is this actually possible? If so, can someone direct me to where I might find the commands/codes that would allow me to do this? Is this done very much like writing a macro?

In advance, thanks for the help. I certainly appreciate any articles or video's that might help me get started with this project.

Don Wilson

Here's the code that I use to make the connection to the database:

    Public sConnection As String = "Data Source=Don-PC;Initial Catalog=DataDesignSolutions;Integrated Security = True;User ID=DON-PC;Password=acd67011;"
    Public con As New SqlConnection
    Public cmd As New SqlCommand
    Public strQ As String
    Public sql As String
con = New SqlConnection(sConnection)
            cmd.CommandText = "SELECT LocationID, SellPrice1, SellPrice2, SellPrice3, SellPrice4, SellPrice5, ItemCost, FreightCost, UDCost1, UDCost2, AverageCost, Classification, VendorNumber, AltVenNumber, LotShadeSerialOut, TaxableItem, RequireDeposit, DepositPcntReq, CommissionPcnt FROM INVENTORY_LOCATIONS WHERE ProductID = " & GlobalResources.prodID & " AND Location = " & GlobalResources.loc1
            con.Open()
            cmd.Connection = con
Dim rdr As SqlDataReader = cmd.ExecuteReader
            Do While rdr.Read()
                GlobalResources.locID = rdr(0)
                GlobalResources.prc1 = rdr(1)
                GlobalResources.prc2 = rdr(2)
                GlobalResources.prc3 = rdr(3)
                GlobalResources.prc4 = rdr(4)
                GlobalResources.prc5 = rdr(5)
                GlobalResources.itemCost = rdr(6)
                GlobalResources.frtCost = rdr(7)
                GlobalResources.udCost1 = rdr(8)
                GlobalResources.udCost2 = rdr(9)
                GlobalResources.avgCost = rdr(10)
                GlobalResources.clsfic = rdr(11)
                GlobalResources.vndrNo = rdr(12)
                GlobalResources.altvndrNo = rdr(13)
                GlobalResources.lotSerlShadeOut = rdr(14)
                GlobalResources.taxItem = rdr(15)
                GlobalResources.reqDep = rdr(16)
                GlobalResources.depPcnt = rdr(17)
                GlobalResources.commPcnt = rdr(18)
            Loop
            rdr.Close()
            con.Close()

Good luck to you!

Hi Group!

I'm curious to know, is it is possible to build a custom control in Visual Basic?

Specifically, I want to build a control that would be similar that would be similar to a ListView (in the Detail "view"). However the difference would be in how it actually displays. Whereas the ListView displays selected data in horizontal manner one line at a time, I would like to display the data over 3 lines with the fields in varible widths.

I've created a 'Panel' in the past with multiple TextBoxes to make up my "ListView". However I wasn't happy the way in behaved and found it difficult to select (click) the panel to edit the fields. The true ListView works much better. However I want to rearrange it architecture to have it display differently.

Can this be done? If it can be, could you direct me where I might look to find how this is done?

Thanks again! You guys have been a great help in the past. Thanks for all you do.

Don

Never mind. I found it. Here are the steps:

To create a user control using Visual Basic Express Edition
On the File menu, click New Project.

On the Templates pane, in the New Project dialog box, click Class Library and then click OK.

On the Project menu, click Add User Control.

In the Add New Item dialog box, select User Control.

In the Name box, type NamesControl and then click Add.

A new User Control template is added to the project and the User Control Designer opens.

In the Solution Explorer, right-click Class1.vb and choose Delete, and then click OK.

On the File menu, click Save All.

In the Save Project dialog box, type NamesUserControl and then click Save.

Hi group,

I'm looking through the book "Mastering Microsoft Visual Basic 2010" by Evangelos Petroutsos. I'm trying to work through a chapter on designing windows controls. In the first step the directions read:

"Start a new project, and in the New Project dialog box, select the template Windows Forms Control Library."

I'm using VB 2010 Express. It doesn't seem to have a template called "Windows Forms Control Library". Is there some place I can download this? I've not found it yet.

Thanks for everything.

Don

Group,

I've got 1000 questions regarding this "Rocket" (Unidata) database, but I digress. To give you some background, I do have some experience with SQL, but nothing with Unidata. But given what I've read, I can't believe it is that much different from SQL, aside from the command syntax (I presume) when querying the database. This new system I'm working with is from Epicor (Manage2000 v7.3). I'm told (but haven't confirmed) it's residing on a Windows server. I want to know a couple of things:

1) is it possible to connect to the database using a VB.net based program? If so, how?
2) I'd like to make some changes to info within the database such as item description, item cost, and various other data that is generally "static". I'd like to make multiple changes to these tables at one time, preferably through an EXCEL spreadsheet. Can I do this? If so, how?

I'll start with this and then move forward.

In advance, thanks for your assistance. Please don't hesitate to make suggestions regarding places to garner addtional information. I'll share that the Manage2000 software package is very difficult to work with as it has multiple steps to change and/or create new items within the system. I hope there are multiple users of this software that can help me work with and around this system. Please speak up!

Don

Rev. Jim,

If I understand you correctly, you're saying that, if the insert fails, nothing will be executed.... even the "Call ClearFieldsAndVariables" as the program will immediately jump to the "Catch ex As Exception".

Am I understanding you correctly?

As always, thanks!

Don

Rev. Jim, let me try to be more specific:

I've created a 'Private Sub' that clears out the variable and textboxes one after the data is moved to the database. However, in the event that the command string fails (which is what it is doing right now), I want to put a "If/Then" statement in that will stop that sub-routine from running..... or better put, if it works, run the routine. If not, don't run it.

In thinking out loud about this, would this work correctly if I wrote the routine this way:

cmd.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
            Finally
                con.Close()
                Call ClearFieldsAndVariables()  'This to run if the data is moved to the database
            End Try

Hopefully this explains it better.

Thoughts?

Thanks for you interest. I appreciate the help. Watch for another post I'm about to make about a connections string. The one I'm using doesn't always work. I'm not sure why. So I'll be looking for a "failsafe" command.

Don

Hi group! I hope all is well!

As part of my connection string and database insert/update, I have an exception command in it to display an error message. Can this also be read or made into a true/false answer?

I would like to have my program run very short command if the insert/update should fail. I'm thinking the best way to do this may be asking if the "Catch ex As Exception" is "True Then" run my short command. Can something like this be done? Or is there a better way to do this?

In advance, thanks for the help!

Don

I'll give this one a whirl! I didn't realize you could do this! I learn something everyday!!

Thanks

Don

Hi group!

I have what I hope is a unique question:

In need to populate a combo Box with the information stored in 6 different columns on one row of a data table. I know how to do this if it just one column and one row.

To be specific, I have one row of data that contains 'Unit of Measure' information within 6 different column names. They are UMName, UMName2, UMName3, UMName4, UMName5 and UMName6. This data is attached to one specific part number. Would someone know how I can get the one piece of info from each one of these column into the combo box? Obviously a loop won't do it since all the info is within one row. So you know, I've been using the syntax as follows:

            cmbxUnitMeasure.Items.Add(umName) 'also need umName2, umName3......
            cmbxUnitMeasure.Text = "Select from..." 

Thoughts... Ideas?

In advance, thanks for your help.

Don

tinstaafl, thanks. I'm familiar with the listbox. You're info clears this up.

Thanks again.

Don

In trying to teach myself about the properties of the textbox, I'm wondering what the propert "Lines" is all about. The value in the property says "String[]Array". When I click this, a box comes up that asks "Enter the strings in the collections (one per line)". Can someone explain to me what the purpose of this is and how this might be used?

In advance, thanks for the help.

Don

LastMitch,

The server is Local. However I don't know what a firework port is. Consequently I can't answer. As suggested, I'll try SQL server services online.

Don

Group,

I may have made a huge mistake repairing SQL Server Management 2008 Express. Here are the details:

I'm running Windows 7 on a Toshiba Laptop.
SQL Manager 2008 had been installed and was working fine.
I had some registry errors popping up, so I ran ARO 2013 to do a clean up. Doing this caused SQL Server not to open.
I downloaded and ran the repair portion of SQL Server Mngmnt.

This does allow the program to startup. However I'm getting a error at login that says "A network-related or specific-instance error occured while establishing a connection to SQL Server was not found or was not accessible."

I've tried to open with both Windows Authentication and the Server Authentication with the password I had created orginally. I still can't get this to open.

Can someone help??

In advance, thanks for your help.

Don

Hmmmm.... I'm not the mathematician I thought I was. However I still don't know I follow. You're using a single quote vs. a double quote.

For what it's worth, in reviewing my data entry into my database, 'ToDecimal' does format the whole number (ex. 7) as 7.00, which is what I wanted it to do as I had formatted my SQL column as 'number(4, 2).

Thanks again for the help!

Don

Rev. Jim, As always, thank you. It seems that, for the most part, Int32 would be the most used is has a range of 4 Billion plus. With what I'm working with, that will suffice with no problems. If I ever do something for Exxon or Mobile, I'll use Int64! LOL!!

Thanks again group!! You guys are the best!

Don

Enternal Newbie,

I don't follow. When you say that 'Double' shows the 'e' values, what do you mean by 'e' values? What then does 'Decimal' show?

I'm building a sales and inventory program. So, using your understanding, it would make sense for me to use 'Decimal' instead of 'Double'.

Thanks again for the lesson. As a newbie, I'm trying to absorb everything like a sponge. And it's been a lot of fun. I really so appreciate and enjoy this site.

Don

Take it a little further for me then. Into16: would that be indicative of a numeric value that had 16 places (9,999,999,999,999,999)? Again, I'm just trying to understand why I would use 'ToInt32' as opposed to '16' or '64'. Or better, where would I best use '16' and/or '64'?

Again, thanks for participating in my lesson for the day!

Don

I've enlarged the field sizes in the database. That fixed it.

Group,

I'm not sure if this is a SQL Server issue or a Visual Basic issue. However during the runtime, I'm getting the following error message:

String or binary data would be truncated.
The statement has been terminated.

I'm not sure why or where this is being caught. Searching this online indicates that I have a value that is larger in size than what I'm trying to insert. However I'm struggling to find which value it is. Can someone suggest where I could look to find which value is too long?

Thanks,

Don

Eternal Newbie,

The '("n2")' fixed the display problem. Thanks.

Hmmm..... I didn't know you could Dim a variable as "Decimal" (yes, I'm a newbie myself).

This prompts a question: In SQL Server Management 2008, I've defined the field where these numbers will be saved as 'Number(4, 2)' with the idea that I'm allocating 2 whole numbers and 2 decimal places (up to 99.99). On the VB side, is it better to define the variable as "Double" or should I define it as 'Decimal'..... or does it matter?

I look forward to your answers.

Don

Group,

Historically I've used 'Convert.ToInt32(TextBox1.Text)' when moving the numbers stored in a TextBox to an integer variable. But it's made me wonder what the difference is between Int16, Int32 and Int64. In simple terms, can someone explain this to me (remember, I'm simple minded!)?

As a follow-up question, I'm finding that 'ToInt32' doesn't like decimal places even though the variable is defined as a "Double". I've used 'ToDecimal' to get around this. But is there a better syntax to use when it will be stored in a Database?

As always, thanks for the help.

Don

Eternal Newbie,

I tried the following code:

    Private Sub txbStateTaxPcnt_Leave(sender As System.Object, e As System.EventArgs) Handles txbStateTaxPcnt.Leave
        stateSlsTax = Convert.ToDecimal(txbStateTaxPcnt.Text)
        txbStateTaxPcnt.Text = stateSlsTax.ToString
    End Sub

It worked fine as long as the number in the TextBox was a number with decimals (ex. '2.25). However it did not display decimal places when the whole number was entered. Is it possible that I am using your suggestion incorrectly?

Again, thanks for the help. I look forward to hearing back from you.

Don

Group,

I've found code that will format a number found in a TextBox to display it with two decimal places. It looks like this:

    Private Sub txbStateTaxPcnt_Leave(sender As System.Object, e As System.EventArgs) Handles txbStateTaxPcnt.Leave
        stateSlsTax = Convert.ToInt32(txbStateTaxPcnt.Text)
        txbStateTaxPcnt.Text = stateSlsTax.ToString("n2")
    End Sub

This works great when a whole number is entered into the textbox. However when I enter a number with decimal places (ex. '2.25'), I get an error message saying, "Input string was not in a correct format." My assumption is that this "number" is recognized as a string instead of the integer that it really is.

Is there something different that I should use in place of "Convert.ToInt32(txbStateTaxPcnt.Text)"? If so, am I going to need to use a If/Then statement to differenciate the two different inputs?

As always, thanks for the help.

Don

Thanks, Jim. I'll head there now and read through it.

Don

Hello Group!

I've looked around to see if there is specific code in VB.net to do this, but I haven't seen any.

I'm using ListView/Detail and want to ensure that it is sorted ascending by one specific column. That column is called "hdrLineNo". I know that I can choose "Sorting - Ascending" in the properties box. However this field IS NOT the first column of the ListView. So my question is:

Can I dynamically program the ListView to sort by the column called "hdrLineNo"? If so, how is this done?

As always, thanks for your help.

Don

Mr. Waddell,

Thank you for the corrections. This worked absolutely perfect. And I learned some things!

Thanks for your help!!

Don

Group,

I've written a short stored procedure in SQL Server 2008 to create a new Order Number. The code looks like this:

INSERT INTO ORDRNUMBERREC(OrderNo,UserId,CreateDate)
SELECT MAX(OrderNo)+1,'system',GETDATE() FROM ORDRNUMBERREC
WITH (TABLOCKX)

In VB2010, I've written the following code to execute the stored procedure:

            con = New SqlConnection(sConnection)
            cmd = New SqlCommand
            Dim rdr As SqlDataReader
            cmd.CommandText = "Update_OrderNo"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = con

I now want to read this new OrderNo to insert it into a TextBox. This code sits under the above writen code:

            con.Open()
            rdr = cmd.ExecuteReader()
            Do While rdr.Read()
                ordno = rdr(0)
            Loop
            rdr.Close()
            con.Close()

Unfortunately this is returning a value of '0'. I've confirmed by querying the table the procedure is running and a new order number is being assigned. What I can't figure out is why '0' displays in the textbox.

Is it because I'm not querying the table correctly? Ultimately I need to have this new order number returned before the procedure is run by another user (which is why I'm using TABLOCKX). Can someone help me get this correctly?

As always, thanks!

Don

I'm glad I asked! Group, as always, thanks again!!

Gee.... that's much shorter than I was thinking... I certainly like that better.

To make sure I'm correct, TABLOCK will ensure any other user attempts to access the stored procedure will have to get in line, and won't have access until the first user is finished. Correct?

In this you're using TABLOCK. Is there a reason you're not using TABLOCKX?

Thanks, Jim, for the help.

Thanks. I didn't know there was a "SQL forum"!

Hi Group!

I've created a stored SQL procedure that will need to be modified to lock the table, run the commands and then unlock the table. I need to do this because I want any other users/programs that will need access to "wait in line" until the procedure is finished. It's short a very short procedure. So in essence, I want to

'Lock the table
'run the commands here
SELECT @today = GETDATE(), @ordno = OrderNo FROM ORDRNUMBERREC
SELECT MAX(OrderNumberID), @ordno FROM ORDRNUMBERREC
SELECT @ordno1 = @ordno + 1
INSERT INTO ORDRNUMBERREC
(OrderNo, UserID, CreateDate)
VALUES(@ordno1, 'system', @today)
'now Unlock the table

I've been reading on this and find the command, TABLOCKX, and this may be what I'm looking for.

My questions are:

1) where is this command inserted (within the SQL procedure or within the VB code)?
2) is there a "unlock" version of this or does the table unlock when the routine is exited?

In advance, thanks for the help.

Don

Found the whole answer:

CREATE PROCEDURE Update_OrderNo @ordno INT output, @ordno1 INT output, @today DATETIME output
AS
SELECT @today = GETDATE(), @ordno = OrderNo FROM ORDRNUMBERREC
SELECT MAX(OrderNumberID), @ordno FROM ORDRNUMBERREC
SELECT @ordno1 = @ordno + 1
INSERT INTO ORDRNUMBERREC
(OrderNo, UserID, CreateDate)
VALUES(@ordno1, 'system', @today)

This did exactly what I needed it to do.

Don

To create the stored procedure, I've gotten this far:

CREATE PROCEDURE Update_OrderNo @ordNo INT output, @ordno1 INT output, @today DATETIME output
AS
SELECT
@ordNo = 'OrderNo',
@ordno1 = @ordNo + 1,
@today = GETDATE()
SELECT @ordNo
FROM [DataDesignSolutions].dbo.ORDRNUMBERREC
WHERE OrderNumberID = (SELECT MAX(OrderNumberID)
INSERT INTO [DataDesignSolutions].dbo.ORDRNUMBERREC
(OrderNo, CreateDate)
VALUES(@ordno1, @today)

Unfortunately, it doesn't like "INSERT INTO".

Does anyone understand this enough to help me finish it?

Don

I've discovered some of the calculation syntax. Here is what I've written:

USE DataDesignSolutions
SELECT OrderNo + 1 AS 'NextOrderNo'
FROM ORDRNUMBERREC
WHERE OrderNumberID = (SELECT MAX(OrderNumberID)  FROM ORDRNUMBERREC)
INSERT INTO ORDRNUMBERREC
(OrderNo, CreateDate)
VALUES('NextOrderNo', '05/06/2013')

However, SQL is not recognizing 'NextOrderNo' as an integer. It thinks it is a variable character. Is there some kind of 'Convert.ToInt32' like there is in Visual Basic?

Again, thanks for your help.

Don

Hi group,

If you've seen my posts before on SQL Server stuff you know I'm a "raw rookie" with this. Again, I need some help.

I'm going to need SQL to create an order number for me. I've created a datatable with two fields: OrderNoID (key) and OrderNumber. I've already added the lowest order number I would use as the first line of the table (100000). I new need to do these steps in this order:

1) "lock" the data table. (I want to prevent anyone from accessing this table until the routine is completed)
2) Find the highest (or top) OrderNoID.
3) From this top ID number, get the OrderNumber
4) Add OrderNumber plus 1 creating a new OrderNumber
5) Create the next OrderNoID (and row) and insert this new OrderNumber
6) Return the new OrderNoID and new OrderNumber values to be read by a variable in Visual Basic.
7) Unlock the data table and close the proceedure (if that's required)

I've read through several articles but I'm unclear how to write such a proceedure as well as to name it and store it. Can someone help me through this?

In advance, thanks for your help!

Don

Ancient Dragon,

Thanks for the help. You are so right, I didn't want an advertisement.

Don