Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Posts
    9

    Unanswered: A very big comand INSERT

    I need to pass information to mysql using the command Insert but i am using VBA for excel and i need 70 fields of a table . So when i use conexao.Execute ("Insert into dados_anterior (COD_GERENCIA_NEGOCIOS,) values('" & CODGERENCIANEGOCIOS & "')") but its 70 fileds and the command stay too long and i can´t read it in vba ...


    What should I do to send information to 70 fields in a table using the comand INSERT , but the line of the command stay too long....

  2. #2
    Join Date
    Mar 2005
    Posts
    22
    Instead of putting the insert statement in your code use the command object to access a stored procedure within your database for example

    set cm.CommandText = "StoredProcedureName"
    cm.cmdCommandType = adCmdStoredProc

    set p = cm.Parameters

    p.Append cm.CreateParameter("@fieldname1",adChar,adParamInp ut,50)
    p.Append cm.CreateParameter("@fieldname2", adChar, adParamInput,50)
    p.Append cm.CreateParameter("@Fieldname3",adInteger,adParam Input)

    cm.execute

    You may have to play around with the code alittle but this is a better way to do a large insert statement. It will offer better performance and easier to debug

  3. #3
    Join Date
    Jun 2006
    Posts
    9

    Thanks for the tip but it wasn´t enough.

    Helo. I am using VBA for excel. How can I declare cm? when i write cm.execute what is going to happen? After doing this how can i use the command insert to send values to a mysql server using cm?
    I use append to ??

  4. #4
    Join Date
    Mar 2005
    Posts
    22
    Here is any example of how to declare a ado command object.

    dim cm as ADODB.Command

    and when you execute the cm you will be running the stored procedure that you specified and pass the values to the stored procedure. The stored procedure will do what you want on your mysql. So if your procedure appends the data, it will append the parameter values.

    The stored procedure are stored inside your database.

Posting Permissions

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