Inserting data from Excel into SQL database table

You're overwriting the command text in the second line:

        With varConnection
            .CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time"   & _
                                        " VALUES (VALKD, VALLow_Target, VALLow_LowerTol, VALLow_UpperTol, VALLow_Deflection, VALNom_Target, VALNom_LowerTol, VALNom_UpperTol, VALNom_Deflection, VALUpp_Target, VALUpp_LowerTol, VALUpp_UpperTol, VALUpp_Deflection, VALTolPercent, VALTol2N, VALTolInput, VALDate_Time"
            .Name = "Query from 159.157.10_1"
            .RefreshStyle = xlOverwriteCells
            .Refresh BackgroundQuery:=False
       
        End With

Thanks.  I tried that, but I can't get my syntax right.  What am I doing wrong?

With varConnection
            .CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time) & _                            ''6/1/2010 00:00:00'"
                VALUES (" & VALKD & ", " & VALLow_Target & ", " & VALLow_LowerTol & ", " & VALLow_UpperTol & ", " & VALLow_Deflection & ", " & VALNom_Target & ", " & VALNom_LowerTol & ", " & VALNom_UpperTol & ", " & VALNom_Deflection & ", " & VALUpp_Target & ", " & VALUpp_LowerTol & ", " & VALUpp_UpperTol & ", " & VALUpp_Deflection & ", " & VALTolPercent", " & VALTol2N & ", " & VALTolInput & ", " & VALDate_Time & ")
            .Name = "Query from 159.157.10_1"
            .RefreshStyle = xlOverwriteCells
            .Refresh BackgroundQuery:=False
        
        End With

Select allOpen in new window

Copy and paste mine. You missed out the " on the second line beginning VALUES. The & _ is a line continuation character for VBA.

So, In the second line beginning " VALUES there is a deliberate space. It is not strictly speaking needed but if you output you CommandText to the console or immediate window you will be able to see your string.

Lee

.CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time) & _                            ''6/1/2010 00:00:00'"
                " VALUES (" & VALKD & ", " & VALLow_Target & ", " & VALLow_LowerTol & ", " & VALLow_UpperTol & ", " & VALLow_Deflection & ", " & VALNom_Target & ", " & VALNom_LowerTol & ", " & VALNom_UpperTol & ", " & VALNom_Deflection & ", " & VALUpp_Target & ", " & VALUpp_LowerTol & ", " & VALUpp_UpperTol & ", " & VALUpp_Deflection & ", " & VALTolPercent", " & VALTol2N & ", " & VALTolInput & ", " & VALDate_Time & ")

Select allOpen in new window

Well, technically the _ is a line continuation character. The & is there to concatenate the two separate strings together.

Consider:

dim sMyString
sMyString = "hello world"

is syntactically identical to:

dim sMyString
sMyString = "hello " & "world"

and the same as:

dim sMyString
sMyString = "hello " & _
                   "world"


All 3 will return the same result.

Lee

I appreciate your help.  I get the following error when using your code.

Thanks

CODE-ERROR.PNG

Here is more of my code.  Now I am getting an 'Object Required' error in the 'CommandText' line of the code.  I have difined all variables and checked spelling in my code.

Any help?

Thanks in advance.

Dim VALKD As String
    Dim VALLow_Target As Single
    Dim VALLow_LowerTol As Single
    Dim VALLow_UpperTol As Single
    Dim VALLow_Deflection As Single
    Dim VALNom_Target As Single
    Dim VALNom_LowerTol As Single
    Dim VALNom_UpperTol As Single
    Dim VALNom_Deflection As Single
    Dim VALUpp_Target As Single
    Dim VALUpp_LowerTol As Single
    Dim VALUpp_UpperTol As Single
    Dim VALUpp_Deflection As Single
    Dim VALTolPercent As Single
    Dim VALTol2N As Single
    Dim VALTolInput As Single
    Dim VALDate_Time As Date
    
    VALKD = cboDIE_NO
    VALLow_Target = txtLOWER
    VALLow_LowerTol = txtLOWER_TOL_L
    VALLow_UpperTol = txtUPPER_TOL_L
    VALLow_Deflection = txtLOWER_DEF
    VALNom_Target = txtNOMINAL
    VALNom_LowerTol = txtLOWER_TOL_N
    VALNom_UpperTol = txtUPPER_TOL_N
    VALNom_Deflection = txtNOMINAL_DEF
    VALUpp_Target = txtUPPER
    VALUpp_LowerTol = txtLOWER_TOL_U
    VALUpp_UpperTol = txtUPPER_TOL_U
    VALUpp_Deflection = txtUPPER_DEF
    VALTolPercent = chkTOL_PERCENT
    VALTol2N = chkTOL_2N
    VALTolInput = chkTOL_INPUT
    VALDate_Time = Format(Now(), "m/d/yyyy h:mm AM/PM")
    
    'Update table_name
    'SET column1=value, column2=value2,...
    'WHERE some_column = some_value
    
     
    Sheets("CLD CURVES").Activate
    For TOLCOLUMN = 1 To 200
        If Sheets("CLD CURVES").Cells(1, TOLCOLUMN) = "KD_TOL" Then
            
                If Sheets("CLD CURVES").Cells(2, TOLCOLUMN) = "" Then
                GoTo ADDRECORD
                Else:
                GoTo UPDATERECORD
                End If
        End If
            
    Next TOLCOLUMN
    
ADDRECORD:
        varConnection = "ODBC;DSN=CLD;UID=CLD;PWD=cld;APP=Microsoft Office 2003;WSID=SID14412"  ';DATABASE A=CLD;TABLE=dbo.Data"'

        With varConnection
            .CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time) VALUES('" & VALKD & "', '" & VALLow_Target & "', '" & VALLow_LowerTol & "', '" & VALLow_UpperTol & "', '" & VALLow_Deflection & "', '" & VALNom_Target & "', '" & VALNom_LowerTol & "', '" & VALNom_UpperTol & "', '" & VALNom_Deflection & "', '" & VALUpp_Target & "', '" & VALUpp_LowerTol & "', '" & VALUpp_UpperTol & "', '" & VALUpp_Deflection & "', '" & VALTolPercent & "', '" & VALTol2N & "', '" & VALTolInput & "', '" & VALDate_Time & "')"
            '.Name = "Query from 159.157.10_1"
            '.RefreshStyle = xlOverwriteCells
            '.Refresh BackgroundQuery:=False
        
        End With

Select allOpen in new window

Where is labe UPDATERECORD defined ??

Why use goto's? Surely a function call is good here?

After the with block put a break point and then output the commandtext to see if it is valid. Then try running the insert in SQL Management Studio to see if it works.

Lee

I think there are deeper issues with the code here rather than just a syntax error defining the command text. The connection string doesn't look right to me to insert into SQL 2005.

Use debug.print to output your debug data to the immediate window to see if your command is being constructed correctly.

UPDATERECORD is defined in the next line of code that is not shown - not included in this step for this procedure based on the condition of the data.

The connection string used here is the same one as that used for selecting data out of the same table - it works fine - no issues.

I tried using the same query text I have in my code in the sql query window and it works fine.  Does exactly what it should.

How do I use debug.print?  Where?

it is in the VBA code. After this:

.CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time) VALUES('" & VALKD & "', '" & VALLow_Target & "', '" & VALLow_LowerTol & "', '" & VALLow_UpperTol & "', '" & VALLow_Deflection & "', '" & VALNom_Target & "', '" & VALNom_LowerTol & "', '" & VALNom_UpperTol & "', '" & VALNom_Deflection & "', '" & VALUpp_Target & "', '" & VALUpp_LowerTol & "', '" & VALUpp_UpperTol & "', '" & VALUpp_Deflection & "', '" & VALTolPercent & "', '" & VALTol2N & "', '" & VALTolInput & "', '" & VALDate_Time & "')"

add:
debug.print .CommandText

It will appear in the immediate window. If that isn't visible, press Ctrl-G to enable it.

I still get the same error.  It does not go as far as the 'add:' line.

Thanks for you help.
ERROR-MESSAGE.PNG

The image here shows the immediate window that I have so far.
ERROR-W-IMMEDIATE-WINDOW.PNG

Take out the add:

It thinks this is another label. Ideally you shouldn't be using labels like this as it is not good practice. Basically you need to output the contents of the .CommandText to the immediate window to see if that is even producing valid SQL. It is difficult to see where the error could be because I don't have all your code to view.

This is all of the code from the sub up to the next step which has nothing to do with what the procedure is doing here.

Thanks for your help.

Private Sub cmdCLD_CURVE_Click()
    Dim Date_Time As Date
    Dim SERIALNO As Single
    Dim COLORID As Integer
    Dim varConnection
    
    
    
    COLORID = 1
    
    Dim VALKD As String
    Dim VALLow_Target As Single
    Dim VALLow_LowerTol As Single
    Dim VALLow_UpperTol As Single
    Dim VALLow_Deflection As Single
    Dim VALNom_Target As Single
    Dim VALNom_LowerTol As Single
    Dim VALNom_UpperTol As Single
    Dim VALNom_Deflection As Single
    Dim VALUpp_Target As Single
    Dim VALUpp_LowerTol As Single
    Dim VALUpp_UpperTol As Single
    Dim VALUpp_Deflection As Single
    Dim VALTolPercent As Single
    Dim VALTol2N As Single
    Dim VALTolInput As Single
    Dim VALDate_Time As Date
    
    VALKD = cboDIE_NO
    VALLow_Target = txtLOWER
    VALLow_LowerTol = txtLOWER_TOL_L
    VALLow_UpperTol = txtUPPER_TOL_L
    VALLow_Deflection = txtLOWER_DEF
    VALNom_Target = txtNOMINAL
    VALNom_LowerTol = txtLOWER_TOL_N
    VALNom_UpperTol = txtUPPER_TOL_N
    VALNom_Deflection = txtNOMINAL_DEF
    VALUpp_Target = txtUPPER
    VALUpp_LowerTol = txtLOWER_TOL_U
    VALUpp_UpperTol = txtUPPER_TOL_U
    VALUpp_Deflection = txtUPPER_DEF
    VALTolPercent = chkTOL_PERCENT
    VALTol2N = chkTOL_2N
    VALTolInput = chkTOL_INPUT
    VALDate_Time = Format(Now(), "m/d/yyyy h:mm AM/PM")
    
    'Update table_name
    'SET column1=value, column2=value2,...
    'WHERE some_column = some_value
    
     
    Sheets("CLD CURVES").Activate
    For TOLCOLUMN = 1 To 200
        If Sheets("CLD CURVES").Cells(1, TOLCOLUMN) = "KD_TOL" Then
            
                If Sheets("CLD CURVES").Cells(2, TOLCOLUMN) = "" Then
                GoTo ADDRECORD
                Else:
                GoTo UPDATERECORD
                End If
        End If
            
    Next TOLCOLUMN
    
ADDRECORD:
        varConnection = "ODBC;DSN=CLD;UID=CLD;PWD=cld;APP=Microsoft Office 2003;WSID=SID14412"  ';DATABASE A=CLD;TABLE=dbo.Data"'

        With varConnection
            .CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time) VALUES('" & VALKD & "', '" & VALLow_Target & "', '" & VALLow_LowerTol & "', '" & VALLow_UpperTol & "', '" & VALLow_Deflection & "', '" & VALNom_Target & "', '" & VALNom_LowerTol & "', '" & VALNom_UpperTol & "', '" & VALNom_Deflection & "', '" & VALUpp_Target & "', '" & VALUpp_LowerTol & "', '" & VALUpp_UpperTol & "', '" & VALUpp_Deflection & "', '" & VALTolPercent & "', '" & VALTol2N & "', '" & VALTolInput & "', '" & VALDate_Time & "')"
            '.Name = "Query from 159.157.10_1"
            '.RefreshStyle = xlOverwriteCells
            '.Refresh BackgroundQuery:=False
            Debug.Print .CommandText
        
        End With

Select allOpen in new window

I'm struggling here. What happens AFTER the with block? You are using a GOTO to push control to a label. Does it return afterwards? Your error may not be where you think it is as this code is not well structured. In line 58 of your code snippet above you have

Else:

It should be

Else

The interpreter will see this as a label. Labels are typically used to alter control flow when errors occur as VB and VBA did not have proper error handling. Labels should be used to alter the flow of control in the way the code above is trying to do. Also, you have defined a string variable called varConnection which holds a string containing:

ODBC;DSN=CLD;UID=CLD;PWD=cld;APP=Microsoft Office 2003;WSID=SID14412

Then below it you're using a With block on the string which is why you're getting an object error as you haven't created a connection object yet. Typically you create a connection object USING and connection string. Then you create a command object and if need be you create a recordset object to return the data. See example below.

Public Function InsertRates() As String
On Error GoTo InsertRates_ERROR:
  Dim bFoundStartPoint                  As Boolean
  Dim xlWS                              As Excel.Worksheet
  Dim sReturn                           As String
  Dim lCurrenciesAvailable              As Long
  Dim bOK                               As Boolean
  Dim oConADODB                         As ADODB.Connection
  
  Set oConADODB = New ADODB.Connection
  oConADODB.ConnectionString = "driver=" & DSN_SQL_DRIVER & ";server=" & DSN_SQL_SERVER & ";uid=" & DSN_SQL_UID & ";pwd=" & DSN_SQL_LIVE_PWD & ";database=" & DSN_SQL_DATABASE & ";"
  oConADODB.ConnectionTimeout = 30
  oConADODB.Open

Select allOpen in new window

No it does not return.  The GOTO statement only directs the procedure to the correct query based on the condition (data or no data).  It will either perform an 'Enter New Data' or 'Update Current Data' task.  The Update function is below the code snippet you see.

What trouble shooting and stepping through the code it seems to work as intended in that it evaluates the condition and determines the correct procedure to execute and goes there with teh GOTO statement.

You may be right - it may not be well constructed, but this is the type of code I wright from time with informal self training.

The error is not the control flow here. The problem is that you're using a With block on a string variable that contains the connection string. You have no code there that actually makes the connection to the database.

OK.

As I understand it then I need to create 2 objects in my code.  Connection and then command.  Below is my defnition and connection string.  When I make the command text string what is the syntax to use them together to actually perform the query.  I also enabled the 'Microsoft ActiveX Data Library 6.0' could that have also been part of the problem?
 
Dim varConnection As ADODB.Connection
Set varConnection = New ADODB.Connection
    varConnection.ConnectionString = "Driver=SQL Server;Server=159.157.10.1;UID=CLD;PWD=cld;Database=CLD;APP=Microsoft Office 2003;WSID=SID14412"

See here for example:  http://vbadud.blogspot.com/2009/03/how-to-connect-sql-express-2005-from.html

You probably don't need the recordset object as you're only updating the database rather than returning data. The reference you have used may not be right. You should be using Microsoft ActiveX Data Objects of one version or another. depending on what you have installed on your machine.

Lee

How do I define and set up the command text.  What I have done is obviously all wrong.

Add Microsoft Activex DataObject Library in your project & replace ADDRECORD segment with following cade

 

ADDRECORD:
        varConnection = "ODBC;DSN=CLD;UID=CLD;PWD=cld;APP=Microsoft Office 2003;WSID=SID14412"  ';DATABASE A=CLD;TABLE=dbo.Data"'
        Dim m_oConn As New ADODB.Connection

        m_oConn.Open varConnection
        With m_oConn
            m_oConn.Execute ("INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time) VALUES('" & VALKD & "', '" & VALLow_Target & "', '" & VALLow_LowerTol & "', '" & VALLow_UpperTol & "', '" & VALLow_Deflection & "', '" & VALNom_Target & "', '" & VALNom_LowerTol & "', '" & VALNom_UpperTol & "', '" & VALNom_Deflection & "', '" & VALUpp_Target & "', '" & VALUpp_LowerTol & "', '" & VALUpp_UpperTol & "', '" & VALUpp_Deflection & "', '" & VALTolPercent & "', '" & VALTol2N & "', '" & VALTolInput & "', '" & VALDate_Time & "')")
            '.Name = "Query from 159.157.10_1"
            '.RefreshStyle = xlOverwriteCells
            '.Refresh BackgroundQuery:=False
        
        End With
        m_oConn.Close

Select allOpen in new window

It looks like this is making prorgress - I am getting different errors now.  I don't understand if my connection works on the other querys that are performed in the same program why should I have a problem here?

 

Thanks, but I get the same error at the con.open varConnection line.

I tried this connection and I get no error, but nothing else happens either.  No data is entered into database.

varConnection = "Provider=SQLOLEDB; Data Source=iowaeng; Initial Catalog=CLD; User ID=cld; Password=cld"

Run a SQL trace on the database while it attempts to run so you can see what (if anything) is actually getting to the SQL server.

How do i do that?

This is what I finally had to do to make it work.  Using RecordSet.

Thanks, you were a big help.

Dim QCommand As ADODB.Command
    Dim varConnection As String
    varConnection = "Provider=SQLOLEDB;Data Source=iowaeng;Initial Catalog=CLD;User ID=CLD;Password=cld"

    con.Open varConnection
   
    Set QCommand = New ADODB.Command
    myFields = "KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time"
    myTable = "SELECT " & myFields & " FROM CLDTolerances"
    rs1.Open myTable, con, adOpenStatic, adLockOptimistic, adCmdText
    rs1.AddNew

    With rs1
        !KD_TOL = VALKD
        !Low_Target = VALLow_Target
        !Low_LowerTol = VALLow_LowerTol
        !Low_UpperTol = VALLow_UpperTol
        !Low_Deflection = VALLow_Deflection
        !Nom_Target = VALNom_Target
        !Nom_LowerTol = VALNom_LowerTol
        !Nom_UpperTol = VALNom_UpperTol
        !Nom_Deflection = VALNom_Deflection
        !Upp_Target = VALUpp_Target
        !Upp_LowerTol = VALUpp_LowerTol
        !Upp_UpperTol = VALUpp_UpperTol
        !Upp_Deflection = VALUpp_Deflection
        !Date_Time = VALDate_Time
        .Update
    End With
    con.Close

Although the solution may not have been complete.  I would not have been lead in the direction I went without the recommendations for the expert.

Woo, that took some time :) we got there though. Glad to be of help.

Lee