Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

Why don't my params populate ?

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
sidwelle
Asked:
sidwelle
  • 3
  • 3
1 Solution
 
käµfm³d 👽Commented:
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
 
sidwelleAuthor Commented:
And it did, Thanks for the help.

Is there a connection that would be better suited to what I am doing than OleDb ?
0
 
käµfm³d 👽Commented:
What kind of database are you connecting to?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
sidwelleAuthor Commented:
MS SQL 2005 Enterprize.
0
 
käµfm³d 👽Commented:
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
 
sidwelleAuthor Commented:
I switch the project over to using SqlCommand class. had to remove "Provider=SQLOLEDB" from the conn string.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now