Update query with certain parameters
Hello,
I hope I can explain this without making myself sound an idiot.
I'm getting to grips with creating SQL adapters. I can create an update command with parameters and pass those values in for the update with no problem.
The disadvantage of this as far as I can see is that you have to create a new update command every time you want to change a record to reflect the changes that you are making at that point. My question is if it is possible to create a "global" update command for the table ( with all the columns in the table as parameters ) which will use the existing values for each column as the parameters values unless you provide new values for one or more of the parameters.
Does that make sense?
I hope I can explain this without making myself sound an idiot.
I'm getting to grips with creating SQL adapters. I can create an update command with parameters and pass those values in for the update with no problem.
The disadvantage of this as far as I can see is that you have to create a new update command every time you want to change a record to reflect the changes that you are making at that point. My question is if it is possible to create a "global" update command for the table ( with all the columns in the table as parameters ) which will use the existing values for each column as the parameters values unless you provide new values for one or more of the parameters.
Does that make sense?
ASKER
Hello, thanks for looking at this.
It's not really what I was trying to get at.
Let's say I have a two column table called tblContacts with columns FirstName and LastName.
If I want to update just the first name my update command will be "UPDATE tblContacts SET FirstName = @FirstNameParam WHERE....." and I then have to pass the first name value in. If in a different form I want change the LastName I have to create another update command with "UPDATE tblContacts SET LastName = @LastNameParam WHERE....." and then provide the last name as a value for the parameter.
What I would like to achieve is to create one update command of "UPDATE tblContacts SET FirstName = @FirstNameParam, LastName = @LastNameParam WHERE....." which can be used anywhere in my application and where I can choose to provide a new value for any of the parameters but to take the existing value of a column for the parameter value for those columns which I don't want to change at that point.
Thanks
It's not really what I was trying to get at.
Let's say I have a two column table called tblContacts with columns FirstName and LastName.
If I want to update just the first name my update command will be "UPDATE tblContacts SET FirstName = @FirstNameParam WHERE....." and I then have to pass the first name value in. If in a different form I want change the LastName I have to create another update command with "UPDATE tblContacts SET LastName = @LastNameParam WHERE....." and then provide the last name as a value for the parameter.
What I would like to achieve is to create one update command of "UPDATE tblContacts SET FirstName = @FirstNameParam, LastName = @LastNameParam WHERE....." which can be used anywhere in my application and where I can choose to provide a new value for any of the parameters but to take the existing value of a column for the parameter value for those columns which I don't want to change at that point.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you use "SET FirstName = @FirstNameParam, LastName = @LastNameParam" then you have to provide both @FirstNameParam and @LastNameParam.
If you want to reuse that code then you must provide also the value of the column that is not changed. This means that you only can do this if you know that value in your programm.
There can be ugly workarounds for 'NOT NULL'-fields providing a DbNULL-value and then using the isnull in your update. If you pass the null-value then it is set to itself.
.... but again ugly
If you want to reuse that code then you must provide also the value of the column that is not changed. This means that you only can do this if you know that value in your programm.
There can be ugly workarounds for 'NOT NULL'-fields providing a DbNULL-value and then using the isnull in your update. If you pass the null-value then it is set to itself.
.... but again ugly
UPDATE tblContacts SET FirstName = isnull(@FirstNameParam,FirstName)
,LastName = isnull(@LastNameParam, LastName)
ASKER
Thanks!
Performing Batch Operations Using DataAdapters
you may use stored procedure if u need to update large number of rows.