Basic 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

>>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" <[email protected]> wrote in message
news:[email protected]...
|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
|