Failed to update and insert the data to the .mdb

Hello everyone,


can someone show me the right way to update and save data to the database? I have a .mdb and have set "STANDDARD" and "STATE" to a primary key
so that way it won't be duplicate the "STANDARD" and "STATE" data in the database. So when a user selects a list of STANDARD and STATE from the dropdownlist,
it will read the db and select the matching name in the STANDARD and STATE field and update that row fields corresponding to the field in the form.
The connection to the database is working but it fails to read in the data where the query is "myUpdateQuery". Does anyone have any suggestion of where I did
wrong in the code below or have any sample code that I could use to update and insert data to the .mdb?  Thankyou very much!

 

protected void LinkSave_SelectedNodeChanged(object sender, EventArgs e)

{

 

string username;

string State;

string trStandard;

string myConnString;

OleDbConnection myConnection = null;

OleDbCommand myCommand = null;

OleDbDataReader myReader;username = Session["user"].ToString();

State = ListOfStates.SelectedItem.Text;

trStandard = dbStd.Items[dbStd.SelectedIndex].Text;

 

 

string dbName = ConfigurationManager.AppSettings.Get("RootDir") + username + ConfigurationManager.AppSettings.Get("DbState");

string myUpdateQuery, mySelectQuery;

string retVal;

try

{

mySelectQuery =
"Select STANDARD, STATE from dbdefaults where STANDARD = " + "'" + trStandard + "'" + "STATE = " + "'" + State + "'";

myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName;

myConnection = new OleDbConnection(myConnString);myCommand = new OleDbCommand(mySelectQuery, myConnection);

myConnection.Open();

myReader = myCommand.ExecuteReader();

if (myReader.Read())

{

myUpdateQuery =
"Update dbdefaults Set STANDARD='" + trStandard + "' Where STATE= '" + State + "'";

myUpdateQuery = "Update dbdefaults Set asa='" + checkDb0.Checked + "' Where STANDARD='" + trStandard + "'STATE= '" + State + "'";

myUpdateQuery = "Update dbdefaults Set pws='" + checkDb0.Checked + "' Where STANDARD='" + trStandard + "'STATE= '" + State + "'";

 

}

else

{

myUpdateQuery =
"INSERT INTO dbdefaults (asa, pws) Values (";

myUpdateQuery = myUpdateQuery + "'" + checkDb0.Checked + "',";

myUpdateQuery = myUpdateQuery + "'" + checkDb1.Checked + "')";

}

myReader.Close();

if (myConnection.State != ConnectionState.Open)

myConnection.Open();

OleDbCommand newCommand = new OleDbCommand(myUpdateQuery, myConnection);

newCommand.ExecuteNonQuery();

myConnection.Close();

retVal =
"Success: InsertRequestorInfor";

}

catch (Exception)

{

retVal = (
"Error: " + "dbPath = " + dbName);

}

 

0
boston_ma
11/9/2007 4:46:20 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

3 Replies
381 Views

Similar Articles

[PageSpeed] 52

The SQL that results from your concatenation of values is not valid.  For example, if  we take this query:

"Update dbdefaults Set asa='" + checkDb0.Checked + "' Where STANDARD='" + trStandard + "'STATE= '" + State + "'";

and added the value true, standard and CA, the result would read like this:

"Update dbdefaults Set asa='true' Where STANDARD='standard'STATE= 'CA';

It needs to read: "Update dbdefaults Set asa='true' Where STANDARD='standard' AND STATE= 'CA';

Also, I'm not convinced that you have delimited the value that gets applied to asa correctly.  You would be much better off using parameters: http://www.mikesdotnetting.com/Article.aspx?Article=45 

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
11/9/2007 5:34:08 PM

Thanks Mike for replying and also for link...

 If I change the query like this and get the newline in constant error:  myUpdateQuery = "Update dbdefaults Set asa='true' Where STANDARD='standard' AND STATE= 'CA;

 Also, I try one of your parameter update sample, it reads the "myUpdateQuery" and "myCommand" but not really updating the data in the database.  Can you tell me what I did wrorng?  again thanks for your help

if (myReader.Read())

{

myCommand.CommandType =
CommandType.Text;

myUpdateQuery = "Update dbdefaults Set sort_order= ?, asa= ?, pws = ?";

myCommand.Parameters.AddWithValue("sort_order", ListSortOrder.SelectedItem.Text);

myCommand.Parameters.AddWithValue("asa", checkDb0.Checked);

myCommand.Parameters.AddWithValue(" pws", checkDb1.Checked);

 

}
0
boston_ma
11/9/2007 8:34:33 PM

The first example that you showed is still wrong.  You aren't looking at what you have typed.  You haven't delimited CA correctly, nor enclosed the string with a final quote and semicolon.  Also, you haven't actually read the article at the link I offered.  You've mixed up some sample code, and haven't shown all the code that you are currently trying to get to work.  Why are you setting parameters while reading data from a datareader?  Why haven't you set the commandtext of myCommand?

I assume that checkDb0 and checkDb1 are, what? Checkboxes? CheckboxLists? Either way, that's not how you get the value.  If it's a Checkbox, you need to establish whether it has been checked, then get its text value.  For a checkboxlist, you need to iterate the items and find out which ones are checked.

I strongly recommend that you follow the quickstart tutorials which can be found under the Learn link at the top of the page, and familiarise yourself with working with server controls. Then you should find some SQL tutorials, and run through the basic syntax.

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
11/9/2007 10:02:32 PM
Reply:

Similar Artilces:

how to use business entities in business logic and data access layers when object data source is used?Here is my code can somebody pls help me in using business entities to select,insert ,update and d
Hi, I am new to dot net ,can someone please help me in how to use business entites in dataccesslayer and businesslogiclayer. I should not use dataset instead i should use business entites to get all the links,I am posting my code here,i am posting my business entites and dataaccess layer and business logic layer.Also I have a question regarding event handlers ,how to use that using grid view.Please can any reply as soon as possible.Here is the code:   Business Entities:  namespace UsefulLinkEntity { public class UsefulLinkEntity {public string strdp_Title...

GridView Update w/ Object Data Source: Getting a reference to updating object?
I have a GridView that uses an ObjectDataSource.  I've set the DataObjectTypeName property of my object data source so my update method can accept an instance of a class as the parameter. What I'd like to do is get a reference to the instance of the object that gets passed to the update method *before* any of its properties are set (I need to run some initialization code first).  I've been looking for ways but haven't found anything.  Is this possible? I've had the same issues and Microsoft has recommended that I do not do that.  They suggest that you add enough p...

multiple update panel and object datasource insert parameters problem
Hi Guys Newbie using vs2--5, vb.net .net2.0 and ajax with SQL express Ok have a page, the page works fine, accept i added some ajax update panels onto it, this normally isnt a problem but i am also using a freetext box control and when some of my code and validation was firing it was causing an issue if it my controls were all held inside an update panel so i had to create several.  My validation now works fine but when i am trying to use my objectdatasource to add my insert parameters it cannot see the controls as they are now sat in several different update panels, this normally isn...

Inserting Data Using VB/ado.net Code-Behind is failed
HelloI am trying to insert data Using VB Code-Behind.I want to Insert the data from textbox on the webform into table of database sql 2005, simple INSERT STATEMENT Using code and not special controls or wizard. TNX  Here's an example for you: Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString) Dim cmd As New SqlCommand("INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@CompanyName, @Phone); SELECT SCOPE_IDENTITY();", conn) cmd.Parameters.AddWithValue("CompanyName", txtC...

Inserting data to the database using ADO.NET "Update" method...slow!
Hi, I am using Odbc ADO.NET code to insert data to a database. I add around 30,000 reocrds to a dataset, and then using a dataadapter if I call the "Update" method to save it takes ages - about 40 minutes!! Is this normal, surely it shouldn't take so long? Admittidly the oracle db server is slow, but not that slow ;) I'm pointing at an Oracle 9i database. The reason I use Odbc rather than the specific Oracle objects is that I also need to be able to point at other databases. I tried changing to Oracle specific objects but it made little difference to performance. My insertCommand looks li...

EAS 3.6.1 : CMP entity beans with TEXT data type : Insert/Update fails with JDBC Exceptions
Hi, My application runs on Weblogic 5.1 and Sybase EAS 3.6.1(build 36110) with Sybase ASE 12.5 as the backend. I have a CMP entity bean that maps to a database table containing a TEXT data type column. On Weblogic server, I am able to create and update these entity beans. On EAS the same code throws JDBC driver exceptions while creating and updating these entity beans. Here are the exceptions I am getting while creating a new record with this entity bean. com.sybase.jdbc2.jdbc.SybSQLException: Incorrect syntax near ')'. While updating the record with the entity ...

insert,update & delete problem for oracle db from vs.net2005 using c# & object data source
how i can refer to the varchar2 data type from vs.net 2005? how i can update, insert & delete the database ENTRY? How i can oracle stored procedure from vs.net2005?how can i pass parameters?   Hi, --> You have to use String datatype in C# to compatble with ORacle varchar2 type. --> You should have to write a stored proc for each operation then call from C# with appropriate parameters or generate insert/update/delete       statement then execute through the ExacuteNonQuery method of ASP.NET.. --> The following links will help you out how...

Object reference not set to an instance of an object (Inserting data into database)
Each time I press submit to insert data into the database I receive the following message. I use the same code on another page and it works fine. Here is the error: Object reference not set to an instance of an object. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object. Source Error: Line 125: MyCommand....

when data insert into database then only Grid is refreshed, nd update the data
<body> <form id="form1" runat="server"> <asp:ScriptManager ID="sm" runat="server"> </asp:ScriptManager> <asp:UpdatePanel ID="up" runat="server" UpdateMode="Conditional"> <ContentTemplate> <div> <asp:WebPartManager ID="WebManager" runat="server"> </asp:WebPartManager> </div> <table> <tr> <td> <asp:LinkButton ID="lnktimer1" runat="server" Text="Timer1" OnClick="lnktimer1_Click...

Insert Command Fails when I want to insert records in data table
On my site users can register using ASP Membership Create user Wizard control. I am also using the wizard control to design a simple question and answer  form that logged in users have access to. it has 2 questions including a text box for Q1 and  dropdown list for Q2. I have a table in my database called "Players" which has 3 Columns UserId Primary Key of type Unique Identifyer PlayerName Type String PlayerGenre Type Sting   On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table. I am havi...

Update table data using object data source control
Hi there I am using asp.net 2.0 to update table data using the objectdata source control and binding it to the gridview. I am unable to perform the update operation. I am using a table with cat_id as identity column and cat_name.Also I am using an stored procedure to update data. here is the code snippet. App_Code QuestCategories.cs namespace NQuestCategories { public class QuestCategoriesTable { private int _catid; private string _catname = string.Empty; public int CatId { get { return this._catid; } set { ...

Object Data Source Inserting Event with a Custom Business Object
I have a page that has an object data source which has the following Insert Method  SalesOrderBOManager.Insert(SalesOrderBO).  I need to be able to set one of the values in the SalesOrderBO object before the Insert method is called. My plan was to do this in the Inserting event of the object data source. Then I realized I didn't know how to set a property value on a custom business object.  How can I do this?    I am considering two options. Option #1 Putting label on the screen that is bound to the field in the SalesOrderBO that I nee...

TableAdapter not updating the DataTable in Dataset (insert is ok, but delete and update rows fail)
Hi, Let me try to define the situation; In the page i have a grid and save button I have a DataTable named "dt" and a TableAdapter named "ta" in my asp.net website project I have a Dataset named "DataSet1" in my App_Code folder as VS2005 suggested. In my Dataset1 , ihave a TableAdapter with "Fill,GetData" queries Also in my TableAdapter i have generated the InsertCommand, SelectCommand, UpdateCommand, DeleteCommand with DBDirectMethods I have bought a component named Janus Gridex, which has nice grid features that i like to use but the...

Insert xml data into the database using object data source and fileupload control
Hi There, I have a fileupload control which takes the xml file. I have an object datasource with an insert method. I need to insert xml data into the sql server 2005 table which has column of type xml. Can anybody please let me know how I can do that using the file upload control? FileUpload has a property called filebytes, but how can I convert filbytes (or any other property of the fileupload)  to xml data so that I can set the insert parameter of the object datasource. Thanks a lot!!  Hi,the xml data can be inserted into the table in the form of byte[]. Here is how to write...

Web resources about - Failed to update and insert the data to the .mdb - asp.net.object-datasource

Failed state - Wikipedia, the free encyclopedia
A state could be said to "succeed" if it maintains, according to philosopher Max Weber , a monopoly on the legitimate use of physical force within ...

The President's Speech Distorts the Truth to Distract From His Failed Record - Facebook
Paul Ryan hat eine Notiz mit dem Titel The President's Speech Distorts the Truth to Distract From His Failed Record geschrieben. Du kannst den ...

Facebook’s Failed Privacy PR Campaign Against Google: An Industry Practice, Poorly Done
In a spectacularly failed attempt at undermining the competition, Facebook has admitted that it hired public relations giant Burson-Marsteller ...

My own spin on the story du jour: Why The Daily failed.
My own spin on the story du jour: Why The Daily failed.

Authorization Failed - LinkedIn
Sorry you are not authorized to perform this action. To find out more please contact LinkedIn customer service .

Business Models : What consumer Internet companies had a large number of users but failed to monetize ...
Answer (1 of 46): I can't think of one. All the companies mentioned here either: a) *Did* have significant revenues: * Netscape (the same ...

Failed to load Yahoo libraries cannot load menus - Flickr - Photo Sharing!
Explore codepo8's photos on Flickr. codepo8 has uploaded 17903 photos to Flickr.

Selfie death: Polish nursing student dies after failed selfie on bridge - YouTube
A Polish nursing student on vacation in Spain fell to her death after attempting to take a selfie on a bridge in Seville. Sylwia Rajchel, 23, ...

‘Does this feel to you like a prison? If so, we have failed’
There are more than 50 young people detained at the Mafraq Juvenile Welfare Centre, and the staff never forget that they were sent there by a ...

Third time's a charm: SpaceX successfully launches new probe after two failed attempts
A new probe that will observe and examine space storms has been successfully launched after two previous failed attempts.

Resources last updated: 12/25/2015 6:30:54 AM