none
Simple INSERT command to enter data into sql database RRS feed

  • Question

  • Hi,

    I want to insert some simple values into a table from a button click but I cannot get the code correct to do this. So far I have the following:

    Sub Button2_Click()


    Dim cn As New ADODB.Connection
    Dim sConnect As String
    Dim sSQL As String


    With cn

    .ConnectionString = "Provider=SQLOLEDB;" & _
    "Data Source=Server;" & _
    "Initial Catalog=DB;" & _
    "User ID=User;" & _
    "Password=PASSWORD;"

    sSQL = "INSERT INTO KPI_Penalties (ID,KPI 3a,KPI 3b,KPI 3c,KPI 3d,KPI 3e)" & _
    "VALUES ('2','0','0','0','0','0');"

    .Open


    End With

    End Sub

    What do I need to do next? I tried to do a Dim My command As SQLCommand but I do not get the correct reference (does somebody know how to add it?)

    Any help would be great, thanks.

    Jas

    Monday, October 25, 2010 9:45 AM

Answers

  • Managed to solve it, just required two additional apostrophes around the DateTime Value.

    • Marked as answer by Jas10 Monday, October 25, 2010 12:20 PM
    Monday, October 25, 2010 12:20 PM

All replies

  • Ok, I've figured out how to INSERT data. One question I wanted to ask is if I can reference a cell value in a SQL query?

    Thanks again,

    Jas

    Monday, October 25, 2010 10:14 AM
  • You would concatenate the cell value into the query string.
     

    HTH

    Bob

    "Jas10" wrote in message news:11afe2a8-b8df-418b-8904-d6fde1ffcbf6@communitybridge.codeplex.com...

    Ok, I've figured out how to INSERT data. One question I wanted to ask is if I can reference a cell value in a SQL query?

    Thanks again,

    Jas

    Monday, October 25, 2010 10:45 AM
  • Ok, i've managed to get the query string working albeit one issue with the DateTime value, for some reason when the date time is passed in to the string and then inserted into the DB it shows up as '0' or Jan 1 1900. I've tried placing it as a string but the quotation marks surrounding it cause an error. The datetime contains the following value '25/10/2010 12:52' which seems to cause an issue. I inputted said value into the querystring manually and it works fine however when it is passed as a variable it falls over.

    I've checked through the debugger as well and it seems to pass the value correctly however the DB still displays either the results above or an error.

    Can someone please help?

    Sub Button2_Click()
    Dim KPI3a As Currency
    Dim KPI3b As Currency
    Dim KPI3c As Currency
    Dim KPI3d As Integer
    Dim KPI3e As Currency
    Dim DateTime As String
    
      
      
      KPI3a = Worksheets("Sheet1").Cells(2, 2).Value
      KPI3b = Worksheets("Sheet1").Cells(2, 3).Value
      KPI3c = Worksheets("Sheet1").Cells(2, 4).Value
      KPI3d = Worksheets("Sheet1").Cells(2, 5).Value
      KPI3e = Worksheets("Sheet1").Cells(2, 6).Value
      ' DateTime = Worksheets("Sheet1").Cells(2, 7).Value
      DateTime = Date
    
    Dim cn As New ADODB.Connection
    Dim sConnect As String
    Dim sSQL As String
    
    
    With cn
    
    .ConnectionString = "Provider=SQLOLEDB;" & _
    "Data Source=server;" & _
    "Initial Catalog=db;" & _
    "User ID=user;" & _
    "Password=password;"
    
    sSQL = "INSERT INTO KPI_Penalties ([KPI 3a],[KPI 3b],[KPI 3c],[KPI 3d],[KPI 3e],[KPI Date])" & _
    "VALUES (" & KPI3a & "," & KPI3b & "," & KPI3c & "," & KPI3d & "," & KPI3e & "," & DateTime & ") ;"
    
    .Open
    
    .Execute (sSQL)
    End With
    
    End Sub
    Monday, October 25, 2010 12:12 PM
  • Managed to solve it, just required two additional apostrophes around the DateTime Value.

    • Marked as answer by Jas10 Monday, October 25, 2010 12:20 PM
    Monday, October 25, 2010 12:20 PM