help with mySQL syntax

hello.  I have an sql command that pulls the ID number (which is an auto-incrimenting field) from the row a user selects to delete and binds it to a variable i can use.  I need to setup an SQL statement that then takes all the ID rows greater than that deleted row ID's number, and subtracts 1 from them, updating them with the new value. I think i may be on the right path, i'm not sure. if someone could help me with this as i've never really setup complex SQL statements before.

'gets the id value of the field being deleted from a dropdownlist selection 

Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("new").ConnectionString()

Dim filename2 As String

filename2 = DDLdelete.SelectedValue.ToString

Dim conn As New MySqlConnection(strConnString)


Dim cmd As New MySqlCommand("select id from uploads where filename=@filename", conn)

cmd.Parameters.AddWithValue("@filename", filename2)

Dim id As Integer = CInt(cmd.ExecuteScalar())


'subtract 1 from the id value of all rows

Dim strQueryIDCHANGE As String = "update uploads set id=id-1 where id=@id.........THIS IS WHERE I NEED HELP

Dim cmdIDCHANGE As New MySqlCommand(strQueryIDCHANGE)cmdIDCHANGE.Parameters.AddWithValue("@file", File)






"with every fix comes a new bug"
4/22/2009 1:33:38 AM 1929 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 12

ok, odd stuff....i tried this command:

'subtract 1 from the id value of all rows

Dim strQueryIDCHANGE As String = "update uploads set id=id-1 where id>@id"

Dim cmdIDCHANGE As New MySqlCommand(strQueryIDCHANGE)cmdIDCHANGE.Parameters.AddWithValue("@id", id)


which doesn't change anything, however, if i make the greater than, a less than < it will minus all the ID fields before it by come it won't -1 all the fields above it?


"with every fix comes a new bug"
4/22/2009 1:51:25 AM

Similar Artilces:

Currently have a site coded in PHP but the customer wants to go to .NET. Can I use the existing MySQL DB or am I required to use MSSQL? Thanks, Ken Check out this forum which is specialized for this kind of questions: ASP.NET MySQL forumBart De Smet [MVP] Visit,

mysql php5-mysql
Hi, i have installed mysqk from binaries itworks!!! but now how can i install php5-mysql and dependencies? Many thanks Thierry -- trazomtg ------------------------------------------------------------------------ When you say from the binaries, do you mean you installed via Yast and the Suse repositories?? It is always best to install via Yast-Software-Software Management if you can. This way all dependencies are done for you. -- gogalthorp ------------------------------------------------------------------------ gogalthorp's Profile: http://forums.opensuse....

Converting SQL syntax to MySQL syntax
 Hi, the following is a stored procedure written for SQL,  would this work in MySQL or do I have to change the syntax? If so would you please point me to the site that will show me how to convert it to one that is equivalent to the MySQL syntax?  Thank you in advance for your help.CREATE PROCEDURE sp_PagedItems    (     @Page int,     @RecsPerPage int    )AS-- We don't want to return the # of rows inserted-- into our temporary table, so turn NOCOUNT ONSET NOCOUNT ON--Create a temporary tableCREATE TABLE ...

Help: MySQL Time Type Null (ODBC.NET) Urgent ! ! !
Hi, All In my web application, I'm using MySQL 4.0.21 (mysqld-max-nt)+ASP.NET 1.1 (SP1)+ODBC.NET + MyODBC 3.15.09 @window 2000 SP4 (tried 2000 standard server & professional with same error) MDAC 2.80 I have following error: I have a table named 'SHIFT': CREATE TABLE `shift` ( `SHIFT_ID` tinyint(3) unsigned NOT NULL default '0', `SHIFT_CODE` varchar(10) NOT NULL default '' `SHIFT_STARTTIME` time NOT NULL default '00:00:00', `SHIFT_ENDTIME` time NOT NULL default '00:00:00', `SHIFT_MIDDLETIME` time NOT NULL default '00:00:00', `SHIFT_HALF_END` time NOT N...

connection Mysql in web.config problem help
 webconfig this code <connectionStrings> <add name="planku" connectionString="Server=localhost;user id=root;password=;database=planku;" providerName=""/></connectionStrings> <system.web><sessionState timeout="60" cookieless="AutoDetect" regenerateExpiredSessionId="true"/> <compilation debug="true" strict="false" explicit="true"><assemblies> <add assembly="System.Design, Version=, Culture=neutral, Publ...

MYSQL syntax
I have the following statement to extract data from the last 5 years of a table: SELECT `invoice`.`Inv_Num`, `invoice`.`Cust_Num`, `invoice`.`Inv_Date`, `invoice`.`Vat_Rate`, `inv_list`.`Line_ID`, `inv_list`.`Quantity`, `inv_list`.`Sell_price`, `inv_list`.`Line_Total`, `inv_list`.`Description` FROM `invoice` Inner Join `inv_list` ON `invoice`.`Inv_Num` = `inv_list`.`Inv_Num` WHERE `inv_list`.`Description` LIKE '%parking charge%' AND `invoice`.`Inv_Num` > '670' I need to extract each months data and sum the line total. Is there a simple way to do this? ...

Hi all,   i have to use MYSQl database server as a beckend of 2.0 application . i have installed the MYSQl on windows machine (XP), but it is a command Propmt i mean for creating databases or tables i will use command Prompt Is there any GUI Interface beside this ?   best Regards Rameezwaheed  life is name of learning!Mark as an answer if it helps...

MySQL help!!
I think I screwed up my Administrative username in mysql. mysql -u root mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' Can I just copy all the files in /var/lib/mysql >>reinstall MySQL >> copy all the old folders into the new /var/lib/mysql ( while MySql is off) -- sczlittle ------------------------------------------------------------------------ -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Google: mysql lost root password The second hit is from the MySQL docs. I usually use the - --sk...

mySQL connector/net will work in development but not on
hello, i got mySQL connector/net v6.0 to finally work on my development computer.  I uploaded the asp/vb page to my webserver, along with an added assembly reference in my web.config and updated the bin folder on the webserver, however i am gettting the error 'MySqlCommand' is ambiguous in the namespace 'MySql.Data.MySqlClient'. anyone know how i can fix this? my web.config looks like this   <assemblies>     <add assembly="System.Core, Version=, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>   ...

mySql help ?
I give up. Ive spent way too much time on this one, so its time to call in the othe guru's to tell me what little peice im missing. I am trying to query a database for all records where a user's login session (multiple logins/outs per day) was logged in for any portion of a specified day, so would include cases where the login session spans midnight at either end of the day. The following query is what I have, and from everything Ive ever done, should work. There are a few dozen records that should be returned by this query, but the query returns NONE. WTH ? if i l...

mysql and .net
Hi there, i am new to .net and i am trying to setup a simple form. I am an open source person and what i am asking can be done easily using php and mysql; but i need to know how to do this in .net project asap. i have three forms; form1 submit to the database and redirect to form2 and form2 submit to the database and redirect to form3. i want to grab mysql last inserted id from form1 to form2 while redirecting and pass it via the url. i am able to select from the database, but i don't know how to assign it to a variable; here is what i have so far: str2 = "select last_ins...

linked server MySql to Mysql
Can i make linked server from MySql to another Mysql server?...

Suse MySQL and MySQL workbench
I am looking at the downloads here 'MySQL :: Download MySQL Workbench' ( and 'MySQL :: Download MySQL Community Server' ( As one is source code and the other is suse enterprise linux, are these the best downloads for MySQL on suse 11.3? Is there a best source for the install? Suse specific rpm's? -- flebber ------------------------------------------------------------------------ Try zypper or YAST first to look at the OpenSuSE repositories. ...

------=_NextPart_000_0026_01C30A6E.486AE070 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Anyone run into this problem=20 $mysql->query(q{ INSERT INTO dbname (table_name) VALUES ( $var ) }); The problem is, when I assign a value to a $var that value is ignored in = the SQL statement. Ive tried every operator I know to get it to look at = it and accept the value as input into the DB. Ive tried=20 ( '$var' ) ("$var") ("\$var") ([$var]) ({$var}) Any ideas ? Kind regards...

Web resources about - help with mySQL syntax -

Syntax - Wikipedia, the free encyclopedia
In addition to referring to the discipline, the term syntax is also used to refer directly to the rules and principles that govern the sentence ...

A Better Syntax for Scheduled Tasks
... in progress, if you'd like to know when it's ready, follow me on Twitter ... where I almost never tweet anything). Schyntax Syntax In contrast ...

Syntax City on the App Store on iTunes
Get Syntax City on the App Store. See screenshots and ratings, and read customer reviews.

The Syntax Of Psychedelic Time (Terence McKenna) [FULL] - YouTube Terence McKenna, speaking in Berkeley, California in July of 1983. Terence McKenna talks about the I ...

Nick Cummins bringing his unique sense of style, and syntax, to Japanese Rugby
THE eyes of Australian rugby fans will be trained on Eden Park this weekend as the Wallabies look to make history against the All Blacks.

Panic Updates Diet Coda For iPad With Local File Support, New Syntax Modes And More
... sites in the case of workgroups, shared projects, etc. Diet Coda The latest update to Diet Coda also adds support for a number of new syntax ...

WordEver HD - Text Editor with Redesigned Keyboard and MarkDown syntax & formatting highlight
Holen Sie sich „WordEver HD - Text Editor with Redesigned Keyboard and MarkDown syntax & formatting highlight“ im App Store. Sehen Sie sich Screenshots, ...

Mastering Windows Search using Advanced Query Syntax
Search is a pervasive feature of Windows 7, but few really know how to use it …

Ten Things You Should Know About Haskell Syntax
I have posted a new blog about Haskell that I think may be read and understood by any curious programmers. It’s about Haskell syntax, which seems ...

Syntax era: Sir Clive Sinclair's ZX Spectrum revolution
Sir Clive Sinclair discusses the long-term impact of his Spectrum computers and why their successors are likely to wipe out humankind.

Resources last updated: 1/18/2016 11:01:28 AM