UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL

Jul 23, 2005

Hello,
I am trying to update records in my database from excel data using vba
editor within excel.
In order to launch a query, I use SQL langage in ADO as follwing:
------------------------------------------------------------
Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim sConn As String
Dim sSql As String
Dim sOutput As String

sConn = "DSN=MS Access Database;" & _
"DBQ=MyDatabasePath;" & _
"DefaultDir=MyPathDirectory;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &
_
"PWD=xxxxxx;UID=admin;"

ID, A, B C.. are my table fields
sSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,
`K`, L" & _
" FROM MyTblName" & _
" WHERE (`A`='MyA')" & _
" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"
& _
" ORDER BY `C` DESC"
Set adoConn = New ADODB.Connection
adoConn.Open sConn

Set adoRs = New ADODB.Recordset
adoRs.Open Source:=sSql, _
ActiveConnection:=adoConn

adoRs.MoveFirst
Sheets("Sheet1").Range("a2").CopyFromRecordset adoRs
Set adoRs = Nothing
Set adoConn = Nothing

---------------------------------------------------------------
Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statements
in this environement? Copying SQL statements from access does not work
as I would have to reference Access Object in my project which I do not
want if I can avoid. Ideally I would like to use only ADO system and
SQL approach.

Thank you very much


Nono

View 1 Replies


ADVERTISEMENT

JDBC 2005 Update Statement - Failing Multi Row Update.

Nov 9, 2007

It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.

View 5 Replies View Related

Stored Procedure - Update Statement Does Not Seem To Update Straight Away

Jul 30, 2007

Hello,

I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.

I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?

View 6 Replies View Related

SQL UPDATE Database From Excel Table

Jul 23, 2005

I had previously posted this in an Access forumwith negative results so will try here.Although this question specifies an Access database,I also wish to accomplish this with a large MS SQL Serverdatabase that we have.Question follows:The following SQL statement, used in VBScript,will COPY a table from Excel to an Access mdb.SQL = "SELECT * INTO C1R0" & _" FROM [C1R0$] IN ''" & _" 'Excel 8.0;database=c:excelUpdateFinal1.xls';"What is the SQL statement that willUPDATE an already existing Access tablewith all rows from Excel spreadsheet?The columns of both Spreadsheet and database are thesame.ThanksJim

View 12 Replies View Related

Update SQL Database Using Excel Sheet As Input

Apr 24, 2007

Hi

I am a very beginner in SQL and know a little bit VB.

I created a database table with two columns, one for key and the other for data. I'd like to update the data column using excel sheet which contains the same columns, key and data.

Does anyone have a sample VB code to share.

Thanks

Al

View 3 Replies View Related

Cannot Update Database Using Sql Statement... Help Please

Apr 19, 2008

Hi All,
 I have watched one of the video tutorials under SQL Server section called "Connecting your web application to SQL server 2005 express edition" (number 8).
It was about creating a database with single table and then updating it by inserting new records in a text box an clicking on submit button. In code section there is a sql insert statement and connection string is via configurationManager (name of the connection string is taken from web.config and is spelled properly)
Anyway, my problem is that updating that database by filling the textbox and clicking submit button is not working.
I have no error message (nothing happens) - database is not updated.
If I add to the page a data grid view with updating and inserting selected then it all works. I was trying to use debbuging with break point setup but couldn't find any errors.I have done everything the same way as on the video.... I am beginner in C# so your help will be much appreciated.
Here is the code:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;public partial class _Default : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{
}protected void Submitbutton_Click(object sender, EventArgs e)
{
 
 SqlDataSource dashDataSource = new SqlDataSource();dashDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["dashConnectionString1"].ToString();
 
dashDataSource.InsertCommandType = SqlDataSourceCommandType.Text;dashDataSource.InsertCommand = "INSERT INTO Email(EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress, @IPAddress, @DateTimeStamp)";
dashDataSource.InsertParameters.Add("EmailAddress", EmailAddressTextBox.Text);dashDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString());
dashDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now.ToString());int rowsaffected = 0;
try
{
rowsaffected = dashDataSource.Insert();
}catch (Exception ex)
{Server.Transfer("Newsletter_problem.aspx");
}
finally
{dashDataSource = null;
}if (rowsaffected != 1)
{Server.Transfer("Newsletter_problem.aspx");
}
else
{Server.Transfer("Newsletter_confirm.aspx");
}
 
 
}
}

View 4 Replies View Related

Flat File Connection Manager Editor Update Not Saving?

Apr 10, 2008

Hello everyone

I'm very new to SSIS and seemed to have stumbled into an expected problem. I'm working on a package that was converted from a DTS package and I have a problem with the Flat File Connection manager. I want to change the file name in my connection, but I do not seem to be able to save the change. After clicking "OK" to save the name change I can then save the package, close BIDS, open everything up again and for some reason the "old" filename is still present in the connection. Note that the "new" file has exactly the same layout as the "old" file, just a different name. And I can oreview the data without any difficulty.


Any ideas on this one...?

Thanks, John

View 2 Replies View Related

Update SQL Database With Entries From MS Access

Sep 16, 2003

Hi,

Is there a way to link an Access 97 database to a SQL database which would get updated as changes in the Access 97 database take place? In fact, the SQL database will be just a copy of an Access database. If there were changes made to the fields in the Access database these changes should be made to the entries in the SQL database as well.

Thank you,
Misha

View 3 Replies View Related

Database Create ODBC Connections To Access Database Directly And Update Data?

Sep 10, 2012

We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?

View 1 Replies View Related

SQL Server 2012 :: Update Statement Will Not Update Data Beyond 7 Million Plus Rows Out Of 38 Millions Rows

Dec 12, 2014

I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).

SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0

View 5 Replies View Related

Help Send An Personal Email From Database Mail On Row Update-stored PROCEDURE Multi Update

May 27, 2008

hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email


i use FUNCTION i get on this forum to use split from multi update

how to loop for evry update send an single eamil to evry employee ID send one email

i update like this


Code Snippet
:

DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3

now
how to send an EMAIL for evry ROW update but "personal email" to the employee



Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'


TNX

View 2 Replies View Related

Trigger To Update One Record On Update Of All The Tables Of Database

Jan 3, 2005

hi!

I have a big problem. If anyone can help.

I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.

I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.

But i don't know exactly how to do the coding for this?

Is there any other way to do this?

can DBCC help to retrieve this info?

Please advise me how to do this.

Thanks in advance.

Vaibhav

View 10 Replies View Related

Update One Colum With Other Column Value In Same Table Using Update Table Statement

Jun 14, 2007

Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani

View 3 Replies View Related

How Can I Update A SQL Database With An Access Database

Feb 14, 2008

I need to update an SQL database with an access database that is built on queries.  I need to do this nightly.
 
Can someone explain how to write the query for this and then I could add it to my nightly maintenance.
 
Thank you
Dee

View 10 Replies View Related

How Can I Update A SQL Database With An Access Database

Feb 14, 2008

I need to update an SQL database with an access database that is built on queries. I need to do this nightly.

Can someone explain how to write the query for this and then I could add it to my nightly maintenance.

Thank you
Dee

View 25 Replies View Related

How Can I Update A SQL Database With An Access Database

Feb 14, 2008


I need to update an SQL database with an access database that is built on queries. I need to do this nightly.

Can someone explain how to write the query for this and then I could add it to my nightly maintenance.

Thank you
Dee

View 9 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

Update Query In Ms-access Doesn't Workin C#, But Does Work In Ms-access

Apr 18, 2007

Hi,

I have an application that uses following code:



Code Snippet







using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;

namespace TimeTracking.DB
{
public class sql
{
OleDbConnection conn;

//
//the constructor for this class, set the connectionstring
//
public sql()
{
DBConnectionstring ConnectToDB = new DBConnectionstring();
conn = ConnectToDB.MyConnection();
}

//
//
//
public void UpdateEntry(int ID, string Week, string Year, string Date, string Project, string Action, string Time, string Comment)
{
int m_ID = ID;
int m_Week = (Convert.ToInt32(Week));
int m_Year = (Convert.ToInt32(Year));
string m_Date = Date;
string m_Project = Project;
int m_ProjectID = new int();
string m_Action = Action;
int m_ActionID = new int();
Single m_Time = (Convert.ToSingle(Time));
string m_Comment = Comment;

//
//get the project ID from the database and store it in m_ProjectID
//
OleDbCommand SelectProjectID = new OleDbCommand("SELECT tblProject.ProjectID FROM tblProject"
+ " WHERE (((tblProject.Project) LIKE @Project))", conn);

SelectProjectID.Parameters.AddWithValue("@Project", m_Project);

try
{
//open the connection
conn.Open();

OleDbDataReader Dataset = SelectProjectID.ExecuteReader();

while (Dataset.Read())
{
m_ProjectID = (int)Dataset["ProjectID"];
}

Dataset.Close();
}

//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}

//
//get the action ID from the database and store it in m_ActionID
//
OleDbCommand SelectActionID = new OleDbCommand("SELECT tblAction.ActionID FROM tblAction"
+ " WHERE (((tblAction.Action) LIKE @Action))", conn);

SelectActionID.Parameters.AddWithValue("@Action", m_Action);

try
{
OleDbDataReader Dataset = SelectActionID.ExecuteReader();

while (Dataset.Read())
{
m_ActionID = (int)Dataset["ActionID"];
}

Dataset.Close();
}

//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}



//
//
//
OleDbCommand Query = new OleDbCommand("UPDATE [tblEntry] SET [tblEntry].[Weeknumber] = @Week,"
+ " [tblEntry].[Year] = @Year, [tblEntry].[Date] = @Date, [tblEntry].[Project] = @ProjectID, [tblEntry].[Action] = @ActionID,"
+ " [tblEntry].[Hours Spent] = @Time, [tblEntry].[Comments] = @Comment WHERE (([tblEntry].[ID]) = @ID)", conn);

Query.Parameters.AddWithValue("@ID", m_ID);
Query.Parameters.AddWithValue("@Week", m_Week);
Query.Parameters.AddWithValue("@Year", m_Year);
Query.Parameters.AddWithValue("@Date", m_Date);
Query.Parameters.AddWithValue("@ProjectID", m_ProjectID);
Query.Parameters.AddWithValue("@ActionID", m_ActionID);
Query.Parameters.AddWithValue("@Time", m_Time);
Query.Parameters.AddWithValue("@Comment", m_Comment);

try
{
Query.ExecuteNonQuery();
}

//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}

finally
{
//close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
}

Code Snippet



The update statement is not working in my application, no error in C# and no error in ms-access. When I paste the update query into the ms-access query tool and replace the parameter values (@....) with real values, is will update the record.

What am I overseeing here?
--Pascal

View 13 Replies View Related

Need Help With Insert Or Update From Excel

Nov 26, 2006

Hello -

I have an Excel data source from a customer. The data source is a daily dump from the customer's HR system - bascially data about people. Some of these are new (to the data source) and some already exist in my database (from a previous import).

What I am trying to do is: If the row in Excel is new, then INSERT the data into my table. If the row in Excel already exists in my database, then I just want to UPDATE the data in my table.

The Excel data contains an ID field from the HR system that I store in my table for lookups. However, I also generate an ID from my table, when the data is inserted.

I have a Lookup task that uses the HR system ID from the Excel file and this will return the rows that already exist. These are the rows I want to update. One question is: Do I use the OLE DB Command task for this operation?

Second, how do I determine the rows that don't exist? I am assuming that the Lookup task only returns those rows that match. And, unlike the conditional split, there doesn't appear to be another path that the unmatched data can be sent to.

thanks

- will

View 4 Replies View Related

Update Table With Excel Data

Jun 17, 2005

Can anyone tell me how to update a sql server table with the data from excel sheet? That would be very helpful.Thanks.

View 6 Replies View Related

Possible To Update A Table Based On Excel?

May 14, 2006

Hi all
I was wondering if it was possible to update a table based off of information from Excel. here is what I though would have worked.


update MyTest Set acctNumber='111' FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:MyFile.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') where [ProductGroup]='Hal Butts'

with 'Update MyTest' being the table name. It does have the same name as the excel file. Just to rule that out.

It gives me this error

Ambiguous column name 'ProductGroup'.

If it is possible what is the correct syntax??

tibor

View 7 Replies View Related

Update Table From Excel File

Oct 20, 2012

I use sql 2000. I have an excel table containing some fields with the first field named code, the same table with more fields exits in my sql database, the issue:

I want to UPDATE all of the fields in sql-db that matches the ones in the excel file where code of the record matches, in sql it sorta look like this:

update mytable (Fields 2, 3, 4,... till last one /the first is the code field/)
select * from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=C:Book1.xls','select * from [Sheet1$]')
WHERE mytable.code = myexcel.code

View 6 Replies View Related

OleDB Update To Excel Spreadsheet

Mar 12, 2008

Is there a limit to the number of fields that can be set in an OleDB Update Statement?

This works with 6 fields:
cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000'"

This Fails with 7 fields:
cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000', F7 = '123'"

The range should be plenty big with A2:AP2. In the end I'm trying to push 42 fields.


The complete segment is:

Dim ExcelConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ExcelFileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim conn As New System.Data.OleDb.OleDbConnection(ExcelConnection)
Dim cmd As New System.Data.OleDb.OleDbCommand()

conn.Open()
cmd.Connection = conn

cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000', F7 = '1234'"

cmd.ExecuteNonQuery()

View 1 Replies View Related

Update Statement

Aug 22, 2007

Dim lblock As Boolean           chkChecked = lblock        strSQL = "UPDATE CLIENTS SET "            If blnCompleted = True Then            strSQL = strSQL & "COMPLETED_DT = '" & Format(Now(), "MM/dd/yyyy") & "', "            Else            strSQL = strSQL & "LAST_SAVED_DT = '" & Format(Now(), "MM/dd/yyyy") & "', "            End If            strSQL = strSQL & "COMMENTS = '" & FixString(txtcomments.Text) & "' " _            & "WHERE client_ID = " & iclientID & ""I want to put my booleen value lblock to sql too, I probably need value of it, It is checkbox, called  chkblock, . how would I include this to update statement  database field for that  BLOCK =

View 1 Replies View Related

Help On Update Statement

Sep 7, 2007

Hi, i nid help on update statement. I using 03 and a microsoft sql server 2000 database.
I use a more simple example of my error. A Northwind Database is use to update the Region table(RegionDescription)
User will 1st go in WebForm2.aspx and enter a id, if found will retrieve the data to WebForm1.aspx. User type "1" and retrieve Eastern to  TextBox1.
User can choose to update the table by typing in a diff word into TextBox1. But when i type any word(e.g East) the page is refresh back to Webform1.aspx with the not updated data and the database is also not updated. Any idea? 
 
WebForm2.aspx.vb Imports System.Data.SqlClient Public Class WebForm2
Inherits System.Web.UI.PageWeb Form Designer Generated Code Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page hereEnd SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Session("id") = TextBox1.Text
Response.Redirect("WebForm1.aspx")End Sub
End Class
 
WebForm1.aspx.vb Imports System.Data.SqlClient Public Class WebForm1
Inherits System.Web.UI.Page
Web Form Designer Generated CodeDim cnn As New SqlConnection("Data Source=(local); Initial Catalog=Northwind;User ID=******; Password=******") Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Label1.Text = Session("id")
retrieveTitle()End SubSub retrieveTitle()
cnn.Open()Dim cmd As New SqlCommand
cmd.CommandText = "SELECT * FROM Region WHERE RegionID = '" + Session("id") + "'"
cmd.Connection = cnnDim dr As SqlDataReader
dr = cmd.ExecuteReader()
If dr.Read() Then
TextBox1.Text = dr("RegionDescription").ToString
End If
cnn.Close()End SubSub UpdateTitle(ByVal title As String)
cnn.Open()Dim sqlstr As String = "UPDATE Region SET RegionDescription = '" + title + "' WHERE RegionID = '" + Session("id") + "'"
Trace.Write(sqlstr)Dim cmd As New SqlCommand(sqlstr, cnn)
cmd.ExecuteNonQuery()
cnn.Close()End SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
UpdateTitle(TextBox1.Text)End Sub
End Class

View 4 Replies View Related

Update SQL Statement

Mar 13, 2008

I have a SQL Table with the following columns
ID, Date, Meeting, Venue, Notes
What Update statement do i need to update a simple grid view in Visual Studio?I have been experementing but when i click update it updates the whole column insted of the one i was trying to update.
Please can you help? Thanks

View 1 Replies View Related

Sql Update Statement

Jun 4, 2008

I need some help. please.  Here is what I got.  From the webpage I can pull the following data to update a table. update Vehicle set Name='TestUnitName' ,Make='TestMake', Model='TestModel', SoftwareVersion='TestVersion', DynamicChange='1', ProviderID='1', Description='TestDescription', VIN='TestVIN', IMEI='TestSIM', EngineTypeId=' ', Phone=' ', MobilePhoneProviderID='' where VehicleID=64 But I also get the error: "The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Vehicle_EngineTypes". The conflict occurred in database "Telemetry", table "dbo.EngineTypes", column 'EngineTypeId'.The statement has been terminated." How do I solve this issue? Actually what I am trying to do is, when EngineTypeId=' ' , I want to set is to NULL and same for the MobilePhoneProviderID.  Any help would be appreciated. Thanks in advance.

View 14 Replies View Related

UPDATE Statement

May 17, 2004

Hey all,

I need to set a column value where the id is within a string. However, I need to set the column to a default value if the id is not within the string. Hope that was easy to understand!

ie:

This currently works...

SET @strSQL = 'UPDATE tblTest SET Archive = 1 WHERE RecID IN (' + @IDList + ')

If the ID is not in the list then I want that column set to 0. How can I do this?

Thanks in advance,

Pete

View 5 Replies View Related

UPDATE Statement

Sep 2, 2004

Hi
I use a update sub, the problem is that i got an error, the error is:
Syntax error in UPDATE statement.
I guess the UPDATE statement is:
strUpdate = "Update tblUsers Set UserName=@UserName, Password=@Password, RetypePassword=@RetypePassword, Email=@Email, Comments=@Comments Where UserID=@UserID"

Remark:I use Acceess DataBase, exactly the same code works fine in SQL, i just changed the DataBase(From Access to SQL).

Is the problem can be in other place?
Thank you very much for your assistance.

View 3 Replies View Related

Update Statement ?

Oct 14, 2004

Hi, I'm having trouble writing this update statement and was wondering if anyone could help me out :

My database is sort of set up lilke this:
There are 3 tables: Orders, OrderDetails and Inventory

Orders has a pk called OrderID.
OD has several ProductIDs listed for that OrderID
Inventory has 2 fields, the pk InventoryID(which is the same as
ProductID) and QOH

So OD kinda looks like this:
OrderID ProductID Quantity Cost
192 12 2 $10
192 3 1 $12
192 14 2 $50
193 12 1 $11
.... .
...
...

so what i want to do is take each productID for a specific orderid
and decrement the inventory for it by OD.quantity


This is what I was trying

UPDATE Inventory
Set QOH = QOH - @qty
WHERE Inventory.InventoryID IN (
SELECT ProductID
FROM OrderDetails
WHERE OrderDetails.OrderID = @OrderID
and @qty = OrderDetails.Quantity
)


but, i'm having no luck...... any suggestions ?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved