Update table in SQL Database

Hi, 

I have a id field in sql database which is of data type varchar. I want to update multiple records in this table at once or in one query. Here is the code

sqlBuilder.Append("Update Students set EventDate='2004/12/24' ")
sqlBuilder.Append("Where ID IN (@StudIDs)")
cmdStudent= New SqlCommand
cmdStudent.Connection = connDB
cmdStudent.CommandType = CommandType.Text
cmdStudent.CommandText = sqlBuilder.ToString()
cmdStudent.Parameters.Add("@StudIDs", SqlDbType.VarChar, 1000).Value = strStudentIDs
cmdStudent.Prepare()
cmdStudent.ExecuteNonQuery()

The value of strStudentIDs is "A2227,B5629".
Its working fine if I am pasing only one id but its not updating the table if I am passing more that one IDs and I am not getting any error message. Then I tried to get it working by passing the strings with single quotes as:
The value of strStudentIDs is "'A2227','B5629'".
Still it did not work.
I got this to work by using Dynamic SQL but I want to do it with string builder instead.

How can I solve this problem?
Thanks!
0
Desi29
11/23/2004 5:51:02 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
560 Views

Similar Articles

[PageSpeed] 15

if your student id's are in a string[] array you might be able to...
string[] StudentIds = new string[10];

SqlParameter[] SqlParams = new SqlParameter[StudentIds.Length];

sqlBuilder.Append("Where ID IN (");
for (int i = 0; i <= (StudentIds.Length - 1); i++)
{
SqlParams[0] = new SqlParameter("@p" + i.ToString(), SqlDbType.VarChar);
SqlParams.Value = StudentIds[i];
sqlBuilder.Append("@p" + i.ToString() + ",");
}
sqlBuilder.Append(")");
....

Not tested... There might be a better way.

http://www.heliosfx.com
0
mxmissile
11/24/2004 12:53:16 AM
Perhaps the following method will help.

http://www.sqlteam.com/item.asp?ItemID=11499
0
ehorn
11/24/2004 3:29:57 AM
Thanks ehorn, this is useful for int values, I need to pass string values.

Thanks
0
Desi29
11/24/2004 6:49:43 PM
Just adjust the udf to accomodate stings:


create Function dbo.CsvToChar ( @Array varchar(1000))
returns @IntTable table
(CharValue varchar(20))
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (@array_value)
select @array = stuff(@array, 1, @separator_position, '')
end
return
end
go
Select *
from dbo.CsvToChar('A2227,B5629')
0
ehorn
11/24/2004 7:46:18 PM
Reply:

Similar Artilces:

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem?  Thanks. Try running the UPDATE via Query Analyzer...and see if you get an error.***********************Dinakar NethiLife is short. Enjoy it.***********************...

Move a Table from one SQL 2005 Database to Another SQL 2005 Database
I created a table in an SQL 2005 database, however, I want to move that table and the data to another SQL 2005 database (within the same server). I am new to SQL 2005 and detailed instructions would be greatly appreciated. Thanks Bill You have two options the first is clean but the second is easier.  Try the links below for details.  Hope this helps. http://msdn2.microsoft.com/en-us/library/ms174335.aspx http://msdn2.microsoft.com/en-us/library/ms188029.aspx  Kind regards,Gift Peddie Thanks, but the documentations is somewhat overwhelming and was hoping of a ba...

Wanting to take info from one SQL table and update to a seperate SQL table
Anyone have an idea of how I can take information from one table and edit it and insert into another SQL table.  I am using stored procedures and would like to continue to do so. Here is a copy of what I have so far:   <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="mp_Customers" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </div> <h2><strong><font color="#C00000">Please enter a valid account number:</font></strong><...

sqldataadapter update comand not updating sql database table
hi I am using follwing code to update a database table from datagrid . But it is not working . It is not giving any errors also .Pl help   <code> Dim adaptStatus As New SqlDataAdapter Dim paramsrno As New SqlParameter("@SRNO", SRNO) With adaptStatus .SelectCommand = New SqlCommand("usp_lpghse_getSelectedQuestion", Util.getConnection) .SelectCommand.CommandType = CommandType.StoredProcedure .SelectCommand.Parameters.Add(paramsrno) End With With adaptStatus .UpdateCommand = New SqlCommand("usp_LpgHse_UpdateStatus", Util.getConnection) .UpdateCommand.CommandType = Comm...

Commit Update to SQL Database Table
The following code will not update and commit the update to a SQL Database Table.  Now my where statement is looking for a Date field.  Could this be the problem? Dim DBConn As SqlConnection Dim DBAdd As New SqlCommand Dim strConnect As String = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString DBConn = New SqlConnection(strConnect) 'Update a existing row in the table DBAdd.CommandText = "UPDATE [D12_MIS] SET [CSJ] = @CSJ, [EST_DATE] = @EST_DATE, [RECORD_LOCK_FLAG] = @RECORD_LOCK_FLAG, [EST_CREATE_BY_NAME] = @EST_CREATE_BY_NAME, [EST_...

Link FoxPro Database table to SQL Server Database table
I have LAN Server in witch Visual Foxpro Database reside.I have another remote server in witch SQL Server 2005 installed.I want to link Foxpro table to SQL Server Database Table.Is it possible? Can please someone help? Thanks MS...

newbie: update sql string unable write to sql table
Hi, I made myself a update sql stmnt, yet I'm unable to write that to my sql table.  Hope u guys can help point out where my problem is: string connectionString = ("connect goes here"); string sqlString = "UPDATE TABLE SET value1 = @value1, value2 = @value2, value3 = @value3 " sqlString += "WHERE ID = @ID"; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(sqlString, con);             cmd.Parameters.Add("@value1", textBox1.Text);             cm...

updating table schema from sql-express to sql-server in VStudio 2005
During web-site development, I am using VStudio 2005 with SQL-Express. I frequently publish changes to my web server that is running SQL- Server 2000 standard edition. Is there a simple way to replicate changes in database tables design without copying the whole mdf-file and loosing the existing data in the target database? thanks, Reinhard Hi, I suggest you to use aspnet_regsql to import the SQLExpress database into your SQLServer database.  The Aspnet_regsql.exe file is located in the [drive:]\%windir%\Microsoft.NET\Framework\version folder on your Web server. Visit the follow...

Can I update a SQL Server table from within a SQL Anywhere trigger?
I have a SQL Server table set up that I'm able to query in iSQL as if it were a SQL Anywhere table. When I try to update it in a SQL Anywhere insert or update trigger however, it brings the database down. I tried looking at the transaction log and it's no help. Does anyone know why this might be happening or how I might debug this? Thanks. Libby, I would enable ODBC tracing on the SQL Anywhere server before the update and swithc it off after, and the look at the log file. Frank On Thu, 13 Sep 2007 17:35:30 +0200, Libby <Engelbret> wrote: > I have a SQ...

table is not update ,database 2005 sql server
I want to update a table this the code but table is not update ,database 2005 sql server this.Validate(); this.bindingSource1.EndEdit(); this.competencyTableAdapter.Update(this.database1DataSet.Competency); this.competencyTableAdapter.Connection.Close(); saikat...

sql session to flag if database table was updated?
Hi, How can i create sql session to flag if database table was updated..? Thanks Sorry for my poor understanding, whould you please explaining a little bit more clear? I am not sure myself, i just know somebody told me that I could use SqkdataSource to refresh my GridView object only when the database has been updated. Otherwise it would remain in the view state..   That is cache, you can use it to reduce the time you have to visit database. And you can use cache dependency to ask it update its content passively.Please look at this article:Using SQL Cache Dependency Hope i...

How can I update related tables into sql database?
I have 2 tables are: 'customers (parent)' and 'open_ac (child)'I have tried to insert and update data into sql database by using textboxes (don't use datagrid)My tables details are below  <-------this table uses for keeping user data customers fields:   Column name                   type           length                                 ...

Update table where view is datasource (LINQ to SQL)
I'd like to ask you, if it's possible to update data in a database where view is datasource for gridview. I've got simple database, I created some views because I needed gridviews with combined source (not only based on one table).  There are many good sources on the internet how to update/delete/insert new data programmaticaly. But is it possible to do something so that I can change/insert/delete data using integrated insert/update/delete buttons within gridivew? thanks alot.Mato Hi matejkralik, What do you mean "integrated insert/update/delete buttons"? Her...

How to enabel sql server notification on database as well as table using sql server 2005
HiI am ASP.NET developer. I have  implemented caching in my site.I am using "System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications("Data Source=<Data Source name>;Initial Catalog=<database name>User ID=<user id>;pwd=<password>;Pooling=true;Min Pool Size=0;Max Pool Size=1000;") & "SqlCacheDependencyAdmin.EnableTableForNotifications("Data Source=<Data Source name>;Initial Catalog=<database name>User ID=<user id>;pwd=<password>;Pooling=true;Min" Pool Size=0;Max Pool Size=1000;","<tabl...

Web resources about - Update table in SQL Database - asp.net.sql-datasource

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Social Side of Code, Database CI and REST API Testing in Methods & Tools Winter 2015 issue
... for software developers, testers and project managers – has published its Winter 2015 issue that discusses the social side of code, database ...

Dodgy database exposes details of 191 million US voters
... years, there's a high chance that your personal details are now out in the wild. Security researcher Chris Vickery found a misconfigured database ...

Huge database of 191 million U.S. voters found on the open Internet
... such bulk information online, a recent discovery seems to indicate. According to VentureBeat , a security researcher found the exposed database ...

IDG Contributor Network: Shrinking an already ginormous SharePoint database transaction log
Last month, I wrote about how to avoid having ginormous transaction logs within SharePoint databases. But what if it’s too late, and your files ...

Massive Voter Database Left Open For Public Consumption
Gosh, we could have avoided all the angst about the Clinton/Sanders voter database breach if only we'd known about this major database , accessible ...

Database configuration issues expose 191 million voter records
A misconfigured database has led to the disclosure of 191 million voter records. The database, discovered by researcher Chris Vickery, doesn't ...

Botched database leaks records for 191 million voters
... sometime this century? If so, your personal info may be out in the open. Researcher Chris Vickery has discovered that a badly configured database ...

191 Million US Voter Registration Records Leaked In Mystery Database
A huge database of 191 million US citizens is uncovered. But no one knows who carelessly left it open, whilst the FBI have been contacted.

Resources last updated: 1/16/2016 8:48:33 AM