Solved

Why don't my params populate ?

Posted on 2014-02-20
6
232 Views
Last Modified: 2014-02-21
Trying to get an update with param to work, trying to populate a field with a string of text about 1k.  I don't get an error,  fields just don't populate.


            Dim cmd As New OleDb.OleDbCommand

            'VarChar DECLARE @msg VarChar 
            'cmd.Parameters.AddWithValue("@msg", Msg)

            '**** This works, but I don't want to worrry about ' and "
            'cmd.CommandText = _
            '    "DECLARE @msg VarChar " & _
            '    "UPDATE tblMsgs01 " & _
            '    "SET msg = '" & Msg & "' " & _
            '    "WHERE id = '" & sID & "'"

            '**** this does not work and I have to 'DECLARE @msg ...' or the SQL conn complains, ("You must declare scaler variable ...")
            cmd.CommandText = _
                 "DECLARE @msg VarChar " & _
                 "UPDATE tblMsgs01 " & _
                 "SET msg = @msg " & _
                 "WHERE id = '" & sID & "'"

            '*** Try 1, failed
            cmd.Parameters.Add("@msg", OleDb.OleDbType.VarChar)
            cmd.Parameters("@msg").Value = "test"

            '*** Try 2, failed
            'Dim param As New OleDb.OleDbParameter
            'param.Value = Msg
            'cmd.Parameters.AddWithValue("@msg", param)

            '*** Try 3, failed
            'cmd.Parameters.Add("@Msg", OleDb.OleDbType.VarChar)
            'cmd.Parameters("@Msg", msg)

            '*** Try 4, failed
            'cmd.Parameters.AddWithValue("@msg", msg)

            cmd.ExecuteNonQuery()

Open in new window

0
Comment
Question by:sidwelle
  • 3
  • 3
6 Comments
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 39875833
OleDb does not use named parameters. It uses question marks for place holders. Try this:

cmd.CommandText = "UPDATE tblMsgs01 " & _
                  "SET msg = ? " & _
                  "WHERE id = '" & sID & "'"
                  
cmd.Parameters.AddWithValue("@msg", "test")  ' @msg doesn't matter. it can be whatever you like

Open in new window


When you have multiple parameters, you still use the question marks, but you add your parameters to the Parameters collection in the order in which the parameters appear in the query.
0
 

Author Closing Comment

by:sidwelle
ID: 39875874
And it did, Thanks for the help.

Is there a connection that would be better suited to what I am doing than OleDb ?
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 39875938
What kind of database are you connecting to?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:sidwelle
ID: 39876669
MS SQL 2005 Enterprize.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 39877069
The classes found under the System.Data.SqlClient namespace are typically used to connect to MS SQL. The SqlCommand class does allow named parameters.
0
 

Author Comment

by:sidwelle
ID: 39877637
I switch the project over to using SqlCommand class. had to remove "Provider=SQLOLEDB" from the conn string.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now