Solved

Why don't my params populate ?

Posted on 2014-02-20
6
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 75

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 75

Expert Comment

by:käµfm³d 👽
ID: 39875938
What kind of database are you connecting to?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
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.​
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

707 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