none
The parameterized query ... expects the parameter ..., which was not supplied.

    Question

  • I'm trying to add records to a table called UA, with two fields, uaID (int) (identity) and ua (varchar(255), using the code down below.

    I am getting the error "The parameterized query '(@a varchar(255))insert into UA (ua) values (@a)' expects the parameter '@a', which was not supplied." on the update line daUA.Update().

    Using sqlConn As New SqlConnection("Server=10.1.1.10; Database=MyDB; User ID=someUser; Password=pwd")
    
    	sqlConn.Open()
    
    	Dim logsDS As DataSet = New DataSet
    
    	Dim daUA As SqlDataAdapter = New SqlDataAdapter()
    
    	daUA.SelectCommand = New SqlCommand("select * from UA", sqlConn)
    
    	daUA.InsertCommand = New SqlCommand("insert into UA (ua) values (@a); set @i = SCOPE_IDENTITY()", sqlConn)
    	daUA.InsertCommand.Parameters.Add("@a", SqlDbType.VarChar, 255)
    	daUA.InsertCommand.Parameters.Add("@i", SqlDbType.Int)
    	daUA.InsertCommand.Parameters("@i").Direction = ParameterDirection.Output
    
    	daUA.Fill(logsDS, "UA")
    
    	Dim newkeyrow As DataRow = logsDS.Tables("UA").NewRow
    	newkeyrow("ua") = "Some string"
    	logsDS.Tables("UA").Rows.Add(newkeyrow)
    	daUA.Update(logsDS.Tables("UA"))
    
    	sqlConn.Close()
    End Using
    
    

    I don't understand why, when it looks like I'm adding the parameter to the insert command just before I use the data adapter to fill the table. Can you tell me what I'm doing wrong?

    Thanks.
    Tuesday, February 09, 2010 9:06 PM

Answers

  • Robert,

    >>Yes, thanks, Asad, I figured that out.  But I it looks like I am supplying it, via the newkeyrow row object. Obviously the connection I think is there between adding the >>new row to the dataset table and the InsertCommand isn't there.

    >>So, why isn't it? Why doesn't the value I gave it in newkeyrow not get passed iinto the InsertCommand when I add newkeyrow to the table in the dataset?

    I don't thing you need to pass those parameter value explicitly,

    Look at this example, this is exactly you are trying to achieve correct me..?
    http://www.java2s.com/Code/VB/Database-ADO.net/Insertcommandwithparameters.htm

    insertCmd.Parameters.Add("@FirstName", _
                SqlDbType.NVarChar, 10"FirstName")
             insertCmd.Parameters.Add("@LastName", _
                SqlDbType.NVarChar, 20"LastName")
             insertCmd.Parameters.Add("@ID", _
                SqlDbType.Int, 15"ID")

             ' 3. Insert employees
             da.InsertCommand = insertCmd
             da.Update(ds, "Employee")

    Once columns are mapped in the insert command DataAdapter' update method should take care of your new rows automatically.





    • Marked as answer by Robert Sewell Wednesday, February 10, 2010 10:35 PM
    Wednesday, February 10, 2010 5:34 PM

All replies


  • Your syntax is incorrect; refere to this support KB for syntax and usage

    http://support.microsoft.com/kb/308055/en-us


    You new to add NEW SQLParameter

    Using sqlConn As New SqlConnection("Server=10.1.1.10; Database=MyDB; User ID=someUser; Password=pwd")

     

              sqlConn.Open()

     

              Dim logsDS As DataSet = New DataSet

     

              Dim daUA As SqlDataAdapter = New SqlDataAdapter()

     

              daUA.SelectCommand = New SqlCommand("select * from UA", sqlConn)

     

              daUA.InsertCommand = New SqlCommand("insert into UA (ua) values (@a); set @i = SCOPE_IDENTITY()", sqlConn)

              daUA.InsertCommand.Parameters.Add(New SqlParameter("@a", SqlDbType.VarChar, 255))

              daUA.InsertCommand.Parameters.Add(New SqlParameter("@i", SqlDbType.Int))

              daUA.InsertCommand.Parameters("@i").Direction = ParameterDirection.Output

     

              daUA.Fill(logsDS, "UA")

     

              Dim newkeyrow As DataRow = logsDS.Tables("UA").NewRow

              newkeyrow("ua") = "Some string"

              logsDS.Tables("UA").Rows.Add(newkeyrow)

              daUA.Update(logsDS.Tables("UA"))

     

              sqlConn.Close()

    End Using

     

    • Edited by Chirag Shah Tuesday, February 09, 2010 9:45 PM content
    Tuesday, February 09, 2010 9:43 PM
  • Thanks, but...

    I added New SqlParameter(...) to both lines as you typed it.  Same error.

    Incidently, I'm pretty sure the syntax I used is valid, not only according to the intellisense popup, but I've successfully used this exact version in ASP.NET.  Although in all those cases, I'm not using the SqlDataAdapter to update the table, but the command object's ExecuteNonQuery method.

    Which makes me wonder if the problem is that the data I'm adding in the line newkeyrow("ua") = "Some string" is not making it to the insert command's @a parameter value. If that's where my problem is, how do I fix it?
    Tuesday, February 09, 2010 10:01 PM
  • You are right... You do not need SQL Parameter as I check intellisense in Visual Studio


    I was able to duplicate the error you have received,



    Here is the working code
    I have tested it and it appears to be working fine on my machine

     Using sqlConn As New SqlConnection("Server=10.1.1.10; Database=MyDB; User ID=someUser; Password=pwd")
    
               sqlConn.Open()
    
               Dim logsDS As DataSet = New DataSet
    
               Dim daUA As SqlDataAdapter = New SqlDataAdapter()
    
               daUA.SelectCommand = New SqlCommand("select * from UA", sqlConn)
    
               daUA.Fill(logsDS, "UA")
    
               Dim newkeyrow As DataRow = logsDS.Tables("UA").NewRow
               newkeyrow("ua") = "Some string"
               logsDS.Tables("UA").Rows.Add(newkeyrow)
    
               daUA.InsertCommand = New SqlCommand("insert into UA (ua) values (@a);select @i = scope_identity()", sqlConn)
               daUA.InsertCommand.Parameters.Add("@a", SqlDbType.VarChar, 50, "UA")
    
               daUA.InsertCommand.Parameters.Add("@i", SqlDbType.Int)
               daUA.InsertCommand.Parameters("@i").Direction = ParameterDirection.Output
    
                'daUA.Update(logsDS.Tables("UA"))
                daUA.Update(logsDS, "UA")
    
               sqlConn.Close()
    End Using
    • Edited by Chirag Shah Wednesday, February 10, 2010 2:59 AM code
    Wednesday, February 10, 2010 2:55 AM
  • You are not supplying the value for parameter @a. And remember if the value is Nothing you should set the parameter value to DBNull (and not Noting).

    Asad
    Wednesday, February 10, 2010 4:27 PM
  • Yes, thanks, Asad, I figured that out.  But I it looks like I am supplying it, via the newkeyrow row object. Obviously the connection I think is there between adding the new row to the dataset table and the InsertCommand isn't there.

    So, why isn't it? Why doesn't the value I gave it in newkeyrow not get passed iinto the InsertCommand when I add newkeyrow to the table in the dataset?

    Since this actually happens in a loop where I'm looking to see if the data I want to add is already there or not, the whole purpose of the way I wrote this code is to cut down on trips to the server by keeping the table iin memory in the dataset.  If the values of the new row aren't really passed to the InsertCommand and I have to handle the insert into the database as well as the dataset, rather than writing it to the dataset and letting it handle to database update, then I might as well rewrite the whole routine.

    I would like to understand what's really going on here.

    And thanks again, Chirag, for your help.  Your changes still didn't work on my system until I added the value to the @a parameter directly, rather than depending on that happening when I added newkeyrow to the dataset.
    Wednesday, February 10, 2010 5:12 PM
  • Robert,

    >>Yes, thanks, Asad, I figured that out.  But I it looks like I am supplying it, via the newkeyrow row object. Obviously the connection I think is there between adding the >>new row to the dataset table and the InsertCommand isn't there.

    >>So, why isn't it? Why doesn't the value I gave it in newkeyrow not get passed iinto the InsertCommand when I add newkeyrow to the table in the dataset?

    I don't thing you need to pass those parameter value explicitly,

    Look at this example, this is exactly you are trying to achieve correct me..?
    http://www.java2s.com/Code/VB/Database-ADO.net/Insertcommandwithparameters.htm

    insertCmd.Parameters.Add("@FirstName", _
                SqlDbType.NVarChar, 10"FirstName")
             insertCmd.Parameters.Add("@LastName", _
                SqlDbType.NVarChar, 20"LastName")
             insertCmd.Parameters.Add("@ID", _
                SqlDbType.Int, 15"ID")

             ' 3. Insert employees
             da.InsertCommand = insertCmd
             da.Update(ds, "Employee")

    Once columns are mapped in the insert command DataAdapter' update method should take care of your new rows automatically.





    • Marked as answer by Robert Sewell Wednesday, February 10, 2010 10:35 PM
    Wednesday, February 10, 2010 5:34 PM
  • That did the trick.  With your code shifting off to the right, I did not see the column mapping you added to the parameter definition. Once I added it to my code, it works, and as a special bonus, it now makes sense.

    Thank you, Chirag!

    By the way, the syntax requires adding the parameter size before the column mapping string.  What do I put as a size for a non-string, such as SqlDbType.Int or SqlDbType.Bit?

    Wednesday, February 10, 2010 10:35 PM
  • I am sure you found answer to this already,

    in case,
    SqlDbType.Int size is 4
    for sqldbtype.bit size is 1.

    search ADO.net documentation

    Thursday, February 11, 2010 3:27 PM
  • Would int and bit be considered a fixed length data type?  MS says "For fixed length data types, the value of Size is ignored. It can be retrieved for informational purposes, and returns the maximum amount of bytes the provider uses when transmitting the value of the parameter to the server."

    Tuesday, February 16, 2010 4:51 PM
  • >>Would int and bit be considered a fixed length data type?  MS says "For fixed length data types, the value of Size is ignored.

    That is correct.  Size is releveant for variable length datatypes (i.e. varchar) only
    Tuesday, February 16, 2010 5:42 PM