how to get sql server express to accept a textbox value to a stored procedure

sql server 2008 sp2
excel 2010:  Userform with textbox

I need to see if there are examples or other help places where i can see how to pass a variable to a stored procedure in sql server 2008.

I have a dsn connection a sql server.

Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
You would have to do it in VBA:

To get the text:
    ActiveSheet.OLEObjects("boxname").Object.Text  
<link to competing site removed - GaryC123>


You can then use the following code:
Dim objMyConn As New ADODB.Connection
Dim objMyRecordset As New ADODB.Recordset

        Set objMyConn = New ADODB.Connection
        objMyConn.CursorLocation = adOpenStatic
        
        Dim strSQL As String

        'Open Connection
        objMyConn.Open "Provider=SQLNCLI10;Server=<SERVER>;Database=<DB>;Uid=<USER>;Pwd=<PWD>;"


        strSQL = "exec MyStoredProc '" +   ActiveSheet.OLEObjects("boxname").Object.Text  +"'"

                With objMyRecordset
                 .ActiveConnection = objMyConn
                 .Open strSQL
                     If Not .EOF Then
                        'has a result
                     else
                         'doesn't have a result
                 end if
                .Close
             End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Peter HutchisonSenior Network Systems SpecialistCommented:
TRy this example:

' Format: Execute_Select_with_Para(Name of stored procedure, Column name, Param type, length of field, actual value to pass)

    Function Execute_Select_with_Param(ByRef ProcName As String, ByRef PName As String, ByRef PType As String, ByRef PLength As Short, ByRef PValue As String) As ADODB.Recordset
        Dim ureg As New ADODB.Connection
        Dim cmd As New ADODB.Command
        Dim param As New ADODB.Parameter

        ureg.Open(UREGCONN)
        cmd.let_ActiveConnection(ureg)
        cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
        cmd.CommandText = "dbo." & ProcName
        param = cmd.CreateParameter(PName, CShort(PType), ADODB.ParameterDirectionEnum.adParamInput, PLength, PValue)
        cmd.CommandTimeout = 600
        cmd.Parameters.Append(param)
        Return cmd.Execute

    End Function

Sub MyRoutine
 Dim records as New ADODB.Recordset
 records = Execute_Select_with_Param("MYPROCEDURE", "CustomerNo", CStr(ADODB.DataTypeEnum.adChar), 10, CustNum)
End sub
0
FordraidersAuthor Commented:
I slighly changed the textbox1 but will still not return data.

Dim objMyConn As New ADODB.Connection
Dim objMyRecordset As New ADODB.Recordset

        Set objMyConn = New ADODB.Connection
        objMyConn.CursorLocation = adOpenStatic
       
        Dim strSQL As String
objMyConn.Open "Provider=SQLOLEDB;Data Source=R9NY9X4;Initial Catalog=XRef_Master_TD;Trusted_connection=yes;"
        strSQL = "exec get_View_SAP_Listbox1 '" + UserForm2.TextBox1.Text + "'"

                With objMyRecordset
                 .ActiveConnection = objMyConn
                 .Open strSQL
                     If Not .EOF Then
                        MsgBox "Yep" 'has a result
                     Else
                        MsgBox "nope" 'doesn't have a result
                 End If
                .Close
             End With



in stored procedure: in sql sexpress


ALTER  PROCEDURE [dbo].[get_View_SAP_Listbox1](

@param1 nvarchar(30)                   --Input  

)
AS
BEGIN
SELECT * from xreftable where mfrnumber = @param1
0
Kyle AbrahamsSenior .Net DeveloperCommented:
does strSql look correct?

if you open a connection to Xref_Master_Td with your own account and paste in strSql do you get back results?
0
FordraidersAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.