Posts by rakuen

    Re: Sort alphanumeric list to show numeric values at top


    Hello and thanks for your reply.

    The values i have listed in A1 column are as follows:

    5001
    5004
    5009
    500D
    500G
    5010
    5011
    and so on...

    That is the way it sorts if i click "sort from A to Z" by using the excel filter option.

    There are letter codes between the numerical codes. I need the sorting to go like this:

    5001
    5004
    5009
    5010
    5011
    500D
    500G

    Is this possible?

    Hello

    I have a big excel file, in which product codes have been saved in alphanumeric values.

    For example:

    AAA1
    C391
    833F
    9004
    ...

    How can i list the numeric values first? The values that have a letter should be listed after numeric values.

    For example:

    1234
    1244
    1588
    ...

    Is this possible?

    I tried using also visual basic code, but couldn't find out a solution.

    Hello

    I need to check that a name does not have space as first or last character.

    This is my code:

    Code
    If PName = "" Then
            MsgBox "Please enter a valid Person name", vbOKOnly, "Information check"
            Exit Sub



    I can only check if the field is completely empty.

    How can i check the first and last character?

    Re: Open database exclusively


    I have now tried this with MS Access database without success.

    The connection is from Excel with ADODB connection defined to be .admodeshareexclusive

    When i open this kind of connection to the database, other users are still able to connecto to it at the same time and read data.

    Any ideas why?

    Re: Recordcount 0 eventhough there are records


    Hi

    It works, thanks.

    Do you know why it doesn't work directly in MS Access? In there it only accepts * as a wildcard. If i try using % there, i get 0 results.

    Does the ADO convert the % into * ?

    Hello

    I'm conducting a search which returns rows. For example this works fine:

    Code
    .CommandText = "SELECT * FROM orders WHERE customer LIKE 'Jack';"
    .ActiveConnection = cnn
    .Execute
    
    
    rs.Open adoCmd, , adOpenKeyset, adLockReadOnly
    MsgBox "Recordcount: " & rs.RecordCount



    With this i can get recordcount normally.

    But when i define the CommandText to use a wildcard, i always get 0 records. Like the following:

    Code
    .CommandText = "SELECT * FROM orders WHERE customer LIKE '*jac*';"
    .ActiveConnection = cnn
    .Execute
    
    
     
    rs.Open adoCmd, , adOpenKeyset, adLockReadOnly
    MsgBox "Recordcount: " & rs.RecordCount



    Recordcount should be over 10, but i always get 0. Why?

    The database is MS Access, and the SQL query run directly to the database works fine. But when i try using this code, it doesn't :(

    Hello

    If i want to clear out recordset, before inserting new values into it, what should i use?

    Code
    If rs.State = adStateOpen Then rs.Close
    Set rs = Nothing
    Set rs = New ADODB.Recordset



    OR

    Code
    Set rs = Nothing



    Help!

    Re: Read Excel workbook to database


    Hello

    The database is sybase based.

    I have different excel sheets that should first be saved into databases, and then merge those into a single database.

    The database structure is identical in all of them:

    • cust_id (PK) -> id number
    • cust_alt (PK) -> alternative name identifier
    • cust_name -> customer name
    • date -> date


    I thought the best solution could be:

    1. Save excel sheet to .csv with tab delimited
    2. Read .csv to database
    3. Combine databases

    Do you have other suggestions ?

    On step 3. what would be the best way to combine them ? Can you give me SQL help how to execute combining ?

    Thanks!

    Hello

    I'm working on different kinds of excel workbooks, that need to be read to databases.

    Which kind of approach do you suggest i should use?

    Read the sheets to a single recordset, and then dump it into the database?

    Read row by row?

    Do you know if there is some ready-made solution for this kind of task?

    Thanks

    Hello

    I'm defining and checking the ADO connection with:

    Code
    Dim cnn As ADODB.Connection
        Set cnn = New ADODB.Connection
        With cnn
            .Provider = "ASAProv"
            .ConnectionString = "DSN=MyDatabase;"
            .Mode = adModeShareDenyWrite
            .Open
        End With
     
    MsgBox "Mode is " & cnn.Mode



    Msgbox still says mode is 3 - Read/write.

    What's up with that?

    Hi

    I'm using the mid function to return a character.

    I was wondering what is the difference in Mid and Mid$ ?

    I saw somebody using the dollar sign, and i can't find the reason for this.
    For example

    Code
    Mid$(baseString, Int(Rnd() * Len(baseString) + 1), 1)

    Re: Too many records in recordset


    Hello and thanks for your reply

    I am unable to use the this code:

    Code
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQL, cnn, adOpenKeyset, adLockReadOnly



    I get the error: "Object required".

    I don't know why it happens. If i use this code, i don't get that error, but i am unable to use the recordcount:

    Code
    Set rs = New ADODB.Recordset
    Set rs = cnn.Execute(strSQL)



    Any ideas?

    Hi

    Does the rs.requery method clean out the recordset?

    The documentation says it "refreshes" the recordset. Does it complelety empty it before filling it again?

    If i have defined the recordset:

    Code
    strSQL = "SELECT * Customer"
    Set rs = New ADODB.Recordset
    Set rs = cnn.Execute(strSQL)



    Does the rs.requery run that strSQL string again?

    Thanks

    Re: Too many records in recordset


    Hi

    I've been trying to use the recordcount, but i keep getting -1

    I've tried to use the static cursor, but still no luck. Can you tell me what is wrong with this code:

    Code
    strSQL = "SELECT * from Customer"
     
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQL, cnn, adOpenStatic, adLockOptimistic
    
    
    Dim count
    count = rs.RecordCount
    MsgBox "the number of records is " & count



    What is the difference between defining the recordset in:

    Set rs = Server.CreateObject("ADODB.Recordset")

    OR

    Set rs = New ADODB.Recordset