Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    18

    Exclamation Unanswered: Select Max In Sql

    HI evybody,

    I've got a "type error " when using "MAX clause in sql server" .

    When i test my query in thez query analyser, it works and return the value I want in my sql table but when i launch it from my asp page , it don't works.

    This is my connexion code :

    Sub OpenDatabase(ByRef dbConn)
    Set dbConn = Server.CreateObject("ADODB.Connection")
    dbConn.ConnectionTimeout = 15
    dbConn.CommandTimeout = 30
    dbConn.Open " DSN=BIZCONNEX;UID=sa;PWD=%!ellAir;DATABASE=BIZMANA
    G"

    End Sub

    The above connection code is in a tools.inc



    and then this is my asp page code with the "MAX query " :


    The following code is in my asp page :

    OpenDatabase dbconn


    Dern_num_aff_pro = "SELECT MAX(numaffaire_pro) FROM [BIZMANAG].[dbo].[FACTURES]"

    set res_Dern_num_aff_pro = dbconn.Execute(Dern_num_aff_pro)



    ajout_affaire_chantier = "INSERT INTO BIZMANAG.dbo.CHANTIERS(shift, status, type, test) VALUES(' " & shift & " ', '" & Request.Form("status") & "' , '" & Request.Form("type") & "', '" & res_Dern_num_aff_pro & "' ) "

    dbconn.Execute(ajout_affaire_chantier)



    Its return me an error at the line of the INSERT query

    Thank U

  2. #2
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    What is your asp error message ?

    FYI, the ADO connection engine sometimes cannot accomadate the result
    sets that are return to them from MS SQL server.

    For more information, read more about it in books online under "ADO and SQL Server---Processing Results".

    You could try putting the T-SQL script of:

    " SET NOCOUNT ON "

    before your insert statement.

    Not certain this will work , because have to know your asp error message first, but no harm trying.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  3. #3
    Join Date
    Aug 2003
    Posts
    18
    Hi ,

    The error message is : "Microsoft VBScript runtime error '800a000d',
    Type mismatch


    I don't understant because my source field type and my destination field type are the same.

  4. #4
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    I think you got all your ASP coding mix up.
    You can't put your "res_Dern_num_aff_pro" into the insert statemet because its a recordset not a variable.
    Try not to declare your RS to long . Instead of "res_Dern_num_aff_pro", you maybe just name it "RS"
    You maybe should try something like this.


    Dim maxnumber

    Dern_num_aff_pro = "SELECT MAX(numaffaire_pro) as MaxNum FROM [BIZMANAG].[dbo].[FACTURES]"

    set RS = dbconn.Execute(Dern_num_aff_pro)

    set Maxnumber=RS("Maxnum")

    'Just to clear the RS, may or may not include
    set RS=nothing


    ajout_affaire_chantier = "INSERT INTO BIZMANAG.dbo.CHANTIERS(shift, status, type, test) VALUES(' " & shift & " ', '" & Request.Form("status") & "' , '" & Request.Form("type") & "', '" & Maxnumber & "' ) "

    set RS=dbconn.Execute(ajout_affaire_chantier)
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  5. #5
    Join Date
    Aug 2003
    Posts
    18

    Thumbs up SQL MAX

    That's all right Patrick


    It works !!

    To correct the exeption error i havn't to set the recordset RS to "nothing" before the INSERT query.

    Thank U again man : ))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •