Solved

Why don't my params populate ?

Posted on 2014-02-20
6
236 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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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.​
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

808 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