VBA syntax
 

I keep getting a runtime error stating a syntax error converting the varchar
value ' & CellValue1 & ' to a column of datatypr int. The value is 1 and my
SQL table in dataType Integer. I have also tried with various removal of '
and & from the insert statement. Any ideas of the correct syntax?

Private Sub btnUpdate_Click()

Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
Dim rw As Integer
Dim cellValue1 As Integer
Dim cellValue2 As String
Dim ws As Worksheet

strCn = "Server=ServerName;Database=dbName;User Id=mm;password=ma"

c.Provider = "sqloledb"
c.Open strCn

rw = 1
Do While Len(Cells(rw, 1)) 0

Set ws = Sheets("Pivot")

cellValue1 = ws.Range("A" & rw).Value
cellValue2 = ws.Range("B" & rw).Value

MsgBox (cellValue1)
MsgBox (cellValue2)

sq = "Insert into marc_temp_excel (Policy_id, Policy_desc) values
('& cellValue1 &', ' & cellValue2 & ')"

Set r = c.Execute(sq)
rw = rw + 1
Loop

c.Close
Set c = Nothing
MsgBox ("marc_temp_excel Table Successfully Updated.")
End Sub

VBA syntax
 

You need

sq = "Insert into marc_temp_excel (Policy_id, Policy_desc) values ('" & _
cellValue1 &"', '" & cellValue2 & "')"


--

HTH

RP
(remove nothere from the email address if mailing direct)


"marcmc" wrote in message
...
I keep getting a runtime error stating a syntax error converting the
varchar
value ' & CellValue1 & ' to a column of datatypr int. The value is 1 and
my
SQL table in dataType Integer. I have also tried with various removal of '
and & from the insert statement. Any ideas of the correct syntax?

Private Sub btnUpdate_Click()

Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
Dim rw As Integer
Dim cellValue1 As Integer
Dim cellValue2 As String
Dim ws As Worksheet

strCn = "Server=ServerName;Database=dbName;User Id=mm;password=ma"

c.Provider = "sqloledb"
c.Open strCn

rw = 1
Do While Len(Cells(rw, 1)) 0

Set ws = Sheets("Pivot")

cellValue1 = ws.Range("A" & rw).Value
cellValue2 = ws.Range("B" & rw).Value

MsgBox (cellValue1)
MsgBox (cellValue2)

sq = "Insert into marc_temp_excel (Policy_id, Policy_desc) values
('& cellValue1 &', ' & cellValue2 & ')"

Set r = c.Execute(sq)
rw = rw + 1
Loop

c.Close
Set c = Nothing
MsgBox ("marc_temp_excel Table Successfully Updated.")
End Sub

VBA syntax
 

sq = "Insert into marc_temp_excel (Policy_id, Policy_desc) values
('& cellValue1 &', ' & cellValue2 & ')"

Since the above is all in a double quote all variables are taken as string
literals. I have no way to test this but you should do something like this
I would guess:

sq = "Insert into marc_temp_excel (Policy_id, Policy_desc) values (" &
cellValue1 & ", " & cellValue2 & ")"

You'll have to fine-tune this but the point is not to enclose variables in
quotes.

--
Jim
"marcmc" wrote in message
...
|I keep getting a runtime error stating a syntax error converting the
varchar
| value ' & CellValue1 & ' to a column of datatypr int. The value is 1 and
my
| SQL table in dataType Integer. I have also tried with various removal of '
| and & from the insert statement. Any ideas of the correct syntax?
|
| Private Sub btnUpdate_Click()
|
| Dim c As ADODB.Connection
| Dim r As ADODB.Recordset
| Set c = New ADODB.Connection
| Dim rw As Integer
| Dim cellValue1 As Integer
| Dim cellValue2 As String
| Dim ws As Worksheet
|
| strCn = "Server=ServerName;Database=dbName;User Id=mm;password=ma"
|
| c.Provider = "sqloledb"
| c.Open strCn
|
| rw = 1
| Do While Len(Cells(rw, 1)) 0
|
| Set ws = Sheets("Pivot")
|
| cellValue1 = ws.Range("A" & rw).Value
| cellValue2 = ws.Range("B" & rw).Value
|
| MsgBox (cellValue1)
| MsgBox (cellValue2)
|
| sq = "Insert into marc_temp_excel (Policy_id, Policy_desc) values
| ('& cellValue1 &', ' & cellValue2 & ')"
|
| Set r = c.Execute(sq)
| rw = rw + 1
| Loop
|
| c.Close
| Set c = Nothing
| MsgBox ("marc_temp_excel Table Successfully Updated.")
| End Sub
|