Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA to SQL DB with user input and results to worksheet
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default VBA to SQL DB with user input and results to worksheet
I want to pull data from SQL server by Excel using vba and ado
connection.

I have created a inputbox to get to parameter from user and pass the
input data to SQL

My VBA script as follow:

Public Sub ConnectToDatabase()


Const ConnectionString As String = _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;User ID=paul;Initial Catalog=Northwind;Data
Source=HP-95097A19B77A"

Dim InP As String
Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString
Connection.Open


InP = Application.InputBox(prompt:="Country", Type:=2)



Debug.Print Connection.State = ObjectStateEnum.adStateOpen

Const SQL As String = _
"SELECT * FROM Customers WHERE Country = '" & InP & "'"

Dim Recordset As Recordset
Dim RowsAffected As Long
Set Recordset = Connection.Execute(SQL, RowsAffected,
CommandTypeEnum.adCmdText)

Debug.Print "Rows affected " & RowsAffected
Call ActiveSheet.Range("A3").CopyFromRecordset(Recordse t)

If (Connection.State = ObjectStateEnum.adStateOpen) Then
Connection.Close
End If

End Sub

I get error message as compile error: constant expression required.
the Cuspo is pointing to the input variable InP.

If I replace InP with a contant say 'USA', then I got the correct
result.

I don't know why I can't get the result by a input box parameter.

Please help me out

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default VBA to SQL DB with user input and results to worksheet
SQL should be a variable, as I don't think you can add a variable to a
constant, so do:

Dim SQL As String

SQL = "SELECT * FROM Customers WHERE Country = '" & InP & "'"

RBS


wrote in message
ups.com...
I want to pull data from SQL server by Excel using vba and ado
connection.

I have created a inputbox to get to parameter from user and pass the
input data to SQL

My VBA script as follow:

Public Sub ConnectToDatabase()


Const ConnectionString As String = _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;User ID=paul;Initial Catalog=Northwind;Data
Source=HP-95097A19B77A"

Dim InP As String
Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString
Connection.Open


InP = Application.InputBox(prompt:="Country", Type:=2)



Debug.Print Connection.State = ObjectStateEnum.adStateOpen

Const SQL As String = _
"SELECT * FROM Customers WHERE Country = '" & InP & "'"

Dim Recordset As Recordset
Dim RowsAffected As Long
Set Recordset = Connection.Execute(SQL, RowsAffected,
CommandTypeEnum.adCmdText)

Debug.Print "Rows affected " & RowsAffected
Call ActiveSheet.Range("A3").CopyFromRecordset(Recordse t)

If (Connection.State = ObjectStateEnum.adStateOpen) Then
Connection.Close
End If

End Sub

I get error message as compile error: constant expression required.
the Cuspo is pointing to the input variable InP.

If I replace InP with a contant say 'USA', then I got the correct
result.

I don't know why I can't get the result by a input box parameter.

Please help me out


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default VBA to SQL DB with user input and results to worksheet
On Apr 15, 4:08 pm, "RB Smissaert"
wrote:
SQL should be a variable, as I don't think you can add a variable to a
constant, so do:

Dim SQL As String

SQL = "SELECT * FROM Customers WHERE Country = '" & InP & "'"

RBS

wrote in message

ups.com...



I want to pull data from SQL server by Excel using vba and ado
connection.

I have created a inputbox to get to parameter from user and pass the
input data to SQL

My VBA script as follow:

Public Sub ConnectToDatabase()

Const ConnectionString As String = _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;User ID=paul;Initial Catalog=Northwind;Data
Source=HP-95097A19B77A"

Dim InP As String
Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString
Connection.Open

InP = Application.InputBox(prompt:="Country", Type:=2)

Debug.Print Connection.State = ObjectStateEnum.adStateOpen

Const SQL As String = _
"SELECT * FROM Customers WHERE Country = '" & InP & "'"

Dim Recordset As Recordset
Dim RowsAffected As Long
Set Recordset = Connection.Execute(SQL, RowsAffected,
CommandTypeEnum.adCmdText)

Debug.Print "Rows affected " & RowsAffected
Call ActiveSheet.Range("A3").CopyFromRecordset(Recordse t)

If (Connection.State = ObjectStateEnum.adStateOpen) Then
Connection.Close
End If

End Sub

I get error message as compile error: constant expression required.
the Cuspo is pointing to the input variable InP.

If I replace InP with a contant say 'USA', then I got the correct
result.

I don't know why I can't get the result by a input box parameter.

Please help me out- Hide quoted text -

- Show quoted text -

I still got the same error message after using the following:

Dim SQL As String

SQL = "SELECT * FROM Customers WHERE Country = '" & InP & "'"





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default VBA to SQL DB with user input and results to worksheet
It works fine here and I had the same compile error with
your old code.
Could you post the whole Sub again?

RBS


wrote in message
oups.com...
On Apr 15, 4:08 pm, "RB Smissaert"
wrote:
SQL should be a variable, as I don't think you can add a variable to a
constant, so do:

Dim SQL As String

SQL = "SELECT * FROM Customers WHERE Country = '" & InP & "'"

RBS

wrote in message

ups.com...



I want to pull data from SQL server by Excel using vba and ado
connection.

I have created a inputbox to get to parameter from user and pass the
input data to SQL

My VBA script as follow:

Public Sub ConnectToDatabase()

Const ConnectionString As String = _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;User ID=paul;Initial Catalog=Northwind;Data
Source=HP-95097A19B77A"

Dim InP As String
Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString
Connection.Open

InP = Application.InputBox(prompt:="Country", Type:=2)

Debug.Print Connection.State = ObjectStateEnum.adStateOpen

Const SQL As String = _
"SELECT * FROM Customers WHERE Country = '" & InP & "'"

Dim Recordset As Recordset
Dim RowsAffected As Long
Set Recordset = Connection.Execute(SQL, RowsAffected,
CommandTypeEnum.adCmdText)

Debug.Print "Rows affected " & RowsAffected
Call ActiveSheet.Range("A3").CopyFromRecordset(Recordse t)

If (Connection.State = ObjectStateEnum.adStateOpen) Then
Connection.Close
End If

End Sub

I get error message as compile error: constant expression required.
the Cuspo is pointing to the input variable InP.

If I replace InP with a contant say 'USA', then I got the correct
result.

I don't know why I can't get the result by a input box parameter.

Please help me out- Hide quoted text -

- Show quoted text -

I still got the same error message after using the following:

Dim SQL As String

SQL = "SELECT * FROM Customers WHERE Country = '" & InP & "'"






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default VBA to SQL DB with user input and results to worksheet
Here is my old code:

Public Sub ConnectToDatabase()


Const ConnectionString As String = _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;User ID=paul;Initial Catalog=Northwind;Data
Source=HP-95097A19B77A"
Dim InP As String

Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString
Connection.Open


InP = Application.InputBox(prompt:="Country", Type:=2)



Debug.Print Connection.State = ObjectStateEnum.adStateOpen

Const SQL As String = _
"SELECT * FROM Customers WHERE Country = '" & InP & "'"

Dim Recordset As Recordset
Dim RowsAffected As Long
Set Recordset = Connection.Execute(SQL, RowsAffected,
CommandTypeEnum.adCmdText)

Debug.Print "Rows affected " & RowsAffected
Call ActiveSheet.Range("A3").CopyFromRecordset(Recordse t)

If (Connection.State = ObjectStateEnum.adStateOpen) Then
Connection.Close
End If

End Sub




On Apr 15, 11:06 pm, "RB Smissaert"
wrote:
It works fine here and I had the same compile error with
your old code.
Could you post the whole Sub again?

RBS

wrote in message

oups.com...



On Apr 15, 4:08 pm, "RB Smissaert"
wrote:
SQL should be a variable, as I don't think you can add a variable to a
constant, so do:

Dim SQL As String

SQL = "SELECT * FROM Customers WHERE Country = '" & InP & "'"

RBS

wrote in message

roups.com...

I want to pull data from SQL server by Excel using vba and ado
connection.

I have created a inputbox to get to parameter from user and pass the
input data to SQL

My VBA script as follow:

Public Sub ConnectToDatabase()

Const ConnectionString As String = _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;User ID=paul;Initial Catalog=Northwind;Data
Source=HP-95097A19B77A"

Dim InP As String
Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString
Connection.Open

InP = Application.InputBox(prompt:="Country", Type:=2)

Debug.Print Connection.State = ObjectStateEnum.adStateOpen

Const SQL As String = _
"SELECT * FROM Customers WHERE Country = '" & InP & "'"

Dim Recordset As Recordset
Dim RowsAffected As Long
Set Recordset = Connection.Execute(SQL, RowsAffected,
CommandTypeEnum.adCmdText)

Debug.Print "Rows affected " & RowsAffected
Call ActiveSheet.Range("A3").CopyFromRecordset(Recordse t)

If (Connection.State = ObjectStateEnum.adStateOpen) Then
Connection.Close
End If

End Sub

I get error message as compile error: constant expression required.
the Cuspo is pointing to the input variable InP.

If I replace InP with a contant say 'USA', then I got the correct
result.

I don't know why I can't get the result by a input box parameter.

Please help me out- Hide quoted text -

- Show quoted text -

I still got the same error message after using the following:

Dim SQL As String

SQL = "SELECT * FROM Customers WHERE Country = '" & InP & "'"- Hide quoted text -

- Show quoted text -


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Extracting Data from another worksheet based on user input Alvyn Excel Worksheet Functions 14 August 6th 08 05:41 PM
VBA to SQL DB with user input and results to sheet Stephen Excel Programming 7 July 17th 06 07:00 PM
Have user input converted to uppercase in same cell as input? Shannonn New Users to Excel 1 June 20th 06 03:19 AM
Creating and renaming a worksheet from user input jonco Excel Programming 1 April 29th 06 11:18 AM
CODE to select range based on User Input or Value of Input Field Sandi Gauthier Excel Programming 4 December 8th 03 03:22 PM


All times are GMT +1. The time now is 07:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement