Thread: Parameterized Dynamic Query

Hmmm...something about this doesn't quite look right. Why are you calling your DAL in your MySQLCommand?

And why won't you use a Stored Procedure?

Anyway, change CommandType.StoredProcedure to CommandType.CommandText, create a dynamic SQL string, but replace the variables in the string (i.e. @id) to a question mark.

Code:

Dim sql As String
sql = "INSERT INTO yourtable (id, street, housenumber, ...) VALUES(?,?,?, ...)"
Dim myCommand = New MySqlCommand(sql, myConnection)
                myCommand.CommandType = CommandType.CommandText                
                myCommand.Parameters.AddWithValue("@id", myProjects.Idkey)
                myCommand.Parameters.AddWithValue("@street", myProjects.TheDate)
                myCommand.Parameters.AddWithValue("@houseNumber", myProjects.Name)
                myCommand.Parameters.AddWithValue("@zipCode", myProjects.ProjectNumber)
                myCommand.Parameters.AddWithValue("@city", myProjects.Description)
                Dim returnValue As DbParameter
                returnValue = myCommand.CreateParameter
                returnValue.Direction = ParameterDirection.ReturnValue
                myCommand.Parameters.Add(returnValue)
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                result = Convert.ToInt32(returnValue.Value)
                myConnection.Close()

If you're calling your DAL like that, then it's completely wrong. In fact, all of this code above, should BE IN your DAL.

JMH got in here while I was typing out an example, but I'll post it anyway:

Code:

Dim sqlStr as String = "INSERT INTO someTable " + _
         "Values(@id, @street, @houseNumber, @zipCode, @city)"

Dim myCommand = New MySqlCommand(sqlStr, myConnection)
 
myCommand.Parameters.AddWithValue("@id", myProjects.Idkey)
myCommand.Parameters.AddWithValue("@street", myProjects.TheDate)
myCommand.Parameters.AddWithValue("@houseNumber", myProjects.Name)
myCommand.Parameters.AddWithValue("@zipCode", myProjects.ProjectNumber)
myCommand.Parameters.AddWithValue("@city", myProjects.Description)

Dim rowsAffected as Int32

myConnection.Open()
rowsAffected = smyCommand.ExecuteNonQuery()
myConnection.Close()