SQL Query

[jan]

Hello all,

I am using VBA but not Word. I could not find a general VBA group to
post to. I am using RsView Studio which is an application to create
HMI (Human Machine Interface) Visualization Screens. I need to create
a screen that does a query and supposed VBA would be the tool to use.

My screen as 1 input box, 1 display box, and 1 button. The user needs
to enter a UPC code in the input box and press the button to display
the description from database. So my sql statement would look like;
"SELECT im_desc FROM item_master where im_upc = 'UPC_Input' "
Currently I have been printing the results to a text file to test.

My problem is I do not understand how to get the value from the Input
Box into my query. Here is the code:

Private Sub Button1_Released()
Dim cnSQL As ADODB.Connection
Dim rsSQL As ADODB.Recordset

Set cnSQL = New ADODB.Connection
Set rsSQL = New ADODB.Recordset

Open "C:\testing.txt" For Output As #1

With cnSQL
.Provider = "MSDASQL"
.ConnectionString = "driver={SQL Server};" & _
"server=myDB;uid=myUID;pwd=myPASS;database=DB"
.Open
End With

With rsSQL
.ActiveConnection = cnSQL
.Open "SELECT im_desc_short FROM item_master where im_upc =
UPC_SInput"
End With

While Not rsSQL.EOF
Print #1, rsSQL!im_desc_short
rsSQL.MoveNext
Wend

Close #1
VBA.Shell "Notepad /p" & "C:\testing.txt"
rsSQL.Close
Set rsSQL = Nothing
Set cnSQL = Nothing

End Sub

Everything works when I remove the where clause. Any assistance is
greatly appreciated.

 

[jan]

I have also tried to declare my string value such as:

Dim cnSQL As ADODB.Connection
Dim rsSQL As ADODB.Recordset
Dim strValue As String

Set cnSQL = New ADODB.Connection
Set rsSQL = New ADODB.Recordset
Set strValue = ThisDisplay.StringInput1

........................more code........................
With rsSQL
.ActiveConnection = cnSQL
.Open "SELECT im_desc_short FROM item_master where im_upc =
strValue"
End With


But this gives me a compile error : object required error.

Thanks!

 

[old man]

Hi,

You want to do something like this:

Private Sub Button1_Released()

Dim cnSQL As ADODB.Connection
Dim rsSQL As ADODB.Recordset

dim str1 as string
str1 = "SELECT im_desc_short FROM item_master where im_upc = "
' inputbox value is surronded by a doublequote singlequote doublequote
on each side and str1 has the input box result surronded by single quotes.
str1 = str1 & "'" & trim(inputbox1.text) &"'"

 

[jan]

Hi,

You want to do something like this:

Private Sub Button1_Released()> Dim cnSQL As ADODB.Connection

dim str1 as string
str1 = "SELECT im_desc_short FROM item_master where im_upc = "
' inputbox value is surronded by a doublequote singlequote doublequote
on each side and str1 has the input box result surronded by single quotes.
str1 = str1 & "'" & trim(inputbox1.text) &"'"

Thanks for the reply!! I finally figured it out.