'Auto-Increment' a character field

[tboogerd]

I have a SQL table that uses a Character field as it's primary key. Using an Alpha Dialog I want to find the greatest value of that field, add 1 (increment), and use that new number for my insert statement. I'm looking for the code to help me do this or get a start on it and also where do I put the code? I want this to happen when the form is loaded and then again after the user submits.

[Keith Hubert]

Re: 'Auto-Increment' a character field

Hi Toby,

From what I have learned so far, the PK should be Numeric and set to INT. There is no code to do this, it is how you set the field property in the table.

I want this to happen when the form is loaded and then again after the user submits.

I dont understand what you want to happen here. You do not need to see the PK when the form is opened, the record will only be updated if all the fields have passed validation. (That is if you have set any).

[Pat Bremkamp]

Re: 'Auto-Increment' a character field

In MySQL, there is no problem using a character value for a primary key, but as you see, it must be numeric in order to also be autoincrement.

You can do the "autoincrement" yourself by getting the max() of the field, then using the Alpha function increment_value() function Output_Value as P = INCREMENT_VALUE( Variable as P )

The only risk is that, if you get the value too early in the process, someone else may get the same number, then SQL will display an error, or simply not work depending on your settings.

[tboogerd]

Re: 'Auto-Increment' a character field

Pat - your example is exactly what I want to implement, however I am a new Alpha user and I am still trying to learn all this (I come from MSSQL VB.NET world). So I am integrating my own web app into a backend database of another software. The PK field is a Character. If I use their software form, they 'increment' that field by one every time a new record is created and they always start with an S. For example S1200,next in line is S1201. It makes sense to get the MAX and then increment but I don't understand where or how to do that in Alpha. I have a dialog form. I am wondering where I put the code, and what is the code I need to use? Here is my select statement:
SELECT MAX(SheetName) AS Expr1 FROM Stock

Do I call this query on the dialog's form initialize? If not where do I call it and what is the code look like to run a query like this and what/how to I assign it to a value. I then understand I need to increment that value and then I want that store in my dialog's field called SheetName.

Sorry I am scu a newb. I will continue to search for my own answers as well but I appreciate any additional feedback.

Thanks

[tboogerd]

Re: 'Auto-Increment' a character field

OK so I think I have a good start. I used the xbasic query genie on my form and created this code. I have the value stored in a variable NewSheetNumber, so only question I have now is how do I get that value into my field on my form?

'Execute a query on a SQL database
'DIM a connection variable
DIM cn as SQL::Connection
dim flagResult as l
flagResult = cn.open("::Name::SigmaNest")
if flagResult = .f. then
ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
end
end if

'Specify if you are using Portable SQL syntax, or not
cn.PortableSQLEnabled = .f.
dim sqlStatement as c
sqlStatement = <<%sql%
SELECT Max(dbo_Stock.SheetName) AS MaxSheetname FROM dbo.Stock dbo_Stock
%sql%
'Execute the Query
flagResult = cn.Execute(sqlStatement)
if flagResult = .f. then
ui_msg_box("Error",cn.CallResult.text)
cn.close()
end
end if
dim rs as sql::resultset
rs = cn.ResultSet
'Now, dump the contents of the ResultSet to a variable
dim NewSheetNumber as c
NewSheetNumber = rs.ToString()

'Now, close the connection
cn.close()

[tboogerd]

Re: 'Auto-Increment' a character field

Can't believe it, but I figured this out. Just added e._set.SheetName.value = NewSheetNumber and called it in onInitializeComplete of my form using {dialog.object}.runAction('GetSheetMaxNum');