I got a problem regarding autoincremental Database Fields and DataTables. Using the technique to map the DataBase to an *.xsd file and trying to insert a Row into the table always results in this kind of error:Cannot insert explicit value for identity column in table 'Cars' when IDENTITY_INSERT is set to OFF.
This only happens when I set CarIDs properties AutoIncrement = true, AutoIncrementSeed = -1, AutoIncrementStep = -1. Having AutoIncrement = false, results in this error:Column 'CarID' does not allow nulls.
Do I missunderstand something, or do I need to change some parts? I would be glad if someone could help me and figure out what I am doing wrong.Thanks a lot have a nice weekend.Regards Johannes
Filling a DataTable from SqlQuery : If SqlQuery returns some null values problem ocurrs with DataTable. Is it possible using DataTable with some null values in it? Thanks
I am quite new to ASP.net 2.0. I have had plenty of experience using ADO.net in standard windows applications. In my app I am opening a connection to an SQL database and I am creating a DataTable without a DataSet: Shared m_cnADONetConnection As New System.Data.SqlClient.SqlConnection Shared m_daDataAdapter As System.Data.SqlClient.SqlDataAdapter Shared m_rowPosition As Integer Shared m_dtContacts As New System.Data.DataTable I am initializing everything and filling my DataTable when the Page first Loads if it isnt a postback.Protected Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Form1.Load If Not Page.IsPostBack Then m_rowPosition = 0 m_cnADONetConnection.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:First ASP DatabaseApp_DataMyFirstDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" m_cnADONetConnection.Open()m_daDataAdapter = New System.Data.SqlClient.SqlDataAdapter("Select * From Books", m_cnADONetConnection)m_cbRefillCommand = New System.Data.SqlClient.SqlCommand m_daDataAdapter.Fill(m_dtContacts) Me.ShowCurrentRecord() End IfEnd Sub The Me.ShowCurrentRecord Sub assigns the values of the current record(row) in the DataTable via (m_rowPosition) to TextBox controls on the form: I also have record navigation buttons on my form: << < > >> Moving me from record to record (row to row) by incrementing or decrementing m_rowPosition All is good! I am able navigate the DataTable and the textboxes change their text properties accordingly from record to record. The only other control on my form is a button which I'm coding its click event to save changes that I made to the current row (record) by changing the values in the textboxes then clicking save. Here is my code:Protected Sub ButtonSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonSave.Click If m_dtContacts.Rows.Count <> 0 Then m_dtContacts.Rows(m_rowPosition).BeginEdit()m_dtContacts.Rows(m_rowPosition)("Title") = TBTitle.Text m_dtContacts.Rows(m_rowPosition)("Author") = TBAuthor.Textm_dtContacts.Rows(m_rowPosition)("YearPublished") = TBYearPublished.Text m_dtContacts.Rows(m_rowPosition)("Price") = TBPrice.Textm_dtContacts.Rows(m_rowPosition)("LastReadOn") = TBLastReadOn.Textm_dtContacts.Rows(m_rowPosition)("PageCount") = TBPageCount.Text m_dtContacts.Rows(m_rowPosition).AcceptChanges() m_dtContacts.Rows(m_rowPosition).EndEdit() m_dtContacts.AcceptChanges() m_daDataAdapter.Update(m_dtContacts) End Sub After I click save I can navigate through my records and back to the one I just changed and updated and all is well. The changes were made in the table. However, when I close the page and exit out of Visual Web Developer and reopen the database: THE CHANGES WERENT UPDATED!!! This worked all the time in VB2005.net when developing a standard windows app. Can I use the same approach I was using in my code above or am I missing something.
I have read and searched all over and what I'm thinking is that my UpdateCommand, InsertCommand, DeleteCommand, SelectCommand are empty. Do I have to do it this way?
Hi GuysI am facing a problem while updating a DataTable to database using sqldataadapter.In my application I am fetching a dataTable from Database and filling values into textboxes on the UI.User has given facility to change or add new texboxes (new row) on the fly .(Textboxes on the UI are like in a row(tr) having two textboxes in each row.)I am again then converting a new (empty) datatable from scrap and filling its rows with the value of textboxes on submit button event.The datatable which I have created has the same schema as the database table.Now what I want here is that changed value should be reflected to the already existed rows in database and only new rows should be inserted.I am using a SQLDataAdapter having two sqlcommands , one for update with update procedure name and parameter mapping and another for insert with parameter mapping.But SqlDataAdapter is inserting fresh new rows all the time in the database table not updating the older one.Please help me in the matterThanks & RegardsVishal Sharma
Hi everybody, I am using VB Webforms as my front end and MSDE 2000 as my back end. I am not able to drop a database after executing the following vb code on that database: Dim vConnection As New SqlConnection(vConnectionString) Dim vAdapter As New SqlDataAdapter("SELECT party_type_code, party_type_name, party_type_imported" & _ " FROM Party_Type" & _ " ORDER BY party_type_name", vConnection) Dim vDataTable As New DataTable() vAdapter.Fill(vDataTable) vAdapter.Dispose() vConnection.Dispose() On trying to drop the database using the SQL Command "DROP DATABASE PPCABCD2005", I get the error message:Cannot drop the database 'PPCABCD2005' because it is currently in use ...However, if I don't execute the above code, I am able to drop it. Why is it so? What is the solution to this problem?
Hi All!! I need to export a whole datatable to the database, through bulk insert or any other method..Can any body help me in that??Yes, col's in the database table and datatable are same!!Thx,Regards,Nilee..
I'm working with a legacy database whose structure I cannot change. I'm building a web-based editor for one of the DB's tables. This table has a Primary Key called "Master_Idx" that is an Identity (autonumber) field.To start, I query the DB and populate a data table which is cached. This is used to "feed" the web form. Any additions, changes, and deletions are reflected in this cached table.The user then has the flexibility to press Save at any time, which ensures that the database is modified accordingly.Here's my question:When new records are added, I assign them a Master_Idx value, the first of which was calculated to be 1 + the Max(Master_Idx) value when the editing session started. But when I insert these records back into the database it's possible that another user might have also been doing similar editing (of different records from the same table), so there's no guarantee that the Master_Idx values in the data table will be identical to those in the DB. When I insert a record from the cached table back into the DB, what technique can I use to check what value was assigned to the Master_Idx? If it's not the same as in the cached table then I need to update it locally in a few places.I hope all of this makes sense but if it doesn't then let me know and I'll explain it in another way.Robert W.
Hi I am trying to use the results of a datatable as an input to a ne SQL statement. The datatable is created and populted by the source below after previously creating MyLookupCommand MyLookupReader = MyLookupCommand.ExecuteReader(CommandBehavior.CloseConnection) Dim Mydatatable = New DataTable() Mydatatable.Load(MyLookupReader)
I now create a new SQL string allong the lines of SELECT tableA.field1 FROM TableA INNERJOIN Mydatatable ON tableA.field2 = Mydatatable.Filed9 However I get the following error Invalid object name 'Mydatatable'. Any suggestions on how to resolve this.
possibley of interest is that I would have like to have done this in 1 SQL but the results in Mydatatable are from a SELECT DISTINCT and Field1 is a text filed that can't be used in a DISTINCT statement, hence using two statements. Many thanks in advance
Building the database I have come across different databases some that add a default value for every field and some that don't. I feel it is a hassle to add a default value, keep track if it is added. I guess with a default value there would be no "NULL" values in the database but one could also make sure in the C# code that all the fields have a value when inputed and on the way out check for nulls. What is the right way???? Pros and cons.........
Hi, I am saving a couple of paragraphs of text into a varchar(max) field in a SQL database table. But when I try to use display the information again, then all the linefeed and return characters seem to be removed. How do I keep this text formating in the database field? I am sure that I am missing something simple here, but I can not figure out what. Can someone please help with some advise? Thanks RegardsJan
Hi there, I have a fairly generic question regarding database design. I am working on my first database using SQL Express. I am testing the database by inserting records into the pre defined tables. Sometimes I forget to input text into a non null field. When this happens it displays an error and then skips a few rows which makes my row numbering messy. For example say I have Row 1, Row 2 Row 3 all nicely formatted but on Row 4 I make a mistake this row then disapears and their is gap between 3 and 5 where 4 was. Although I am just testing, I was wondering what happens in a real scenario if an inexperienced data input person makes a few mistakes it makes the database numbering systems look bad. In this case is it common to make most fields NULL to allow for admin errors??
I am designing a table where the object(s) that the table represents could have hundreds of boolean attributes. Which method of design would you chose for this scenario:Keep the booleans in the original object's table, potential for hundreds of nulls in a rowCreate a 2 more tables, one that has the boolean value names & ID. Another that relates an object (in original table) to a boolean value name/ID. No nulls, lots of joiningSo second method would probably normalize it, but I would suffer a performance cost, whereas the 1st method would be the easiest/quickest for joins but tons of null records. ThanksBen
I have an SQL 2000 database with several tables that are populated using various forms. One or more of the forms is causing the several fields to fill in with data input plus empty spaces to the right for the full character length assigned. Is there a routine or database feature in Visual Studio that can trim off all trailing spaces in all fields in a database table? Is there a compacting routine that might be called? Thank you
Hi, i need to know how to encrypt a field in the sql server database. I want to hide some text field in a user defined table. thanks in advance. regards mihir
Hi, I'm wondering if this is possible. I want to use a datatable as a parameter in my SQL query. The error I am getting is "No mapping exists from object type System.Data.DataColumn to a known managed provider native type." Any help is appreciated. Here is the code. Private Function GetAvailableTutors(ByVal qualifiedTutorsDataSet As DataTable) As DataTable Dim TutorAvailabilityDataTable As DataTable = New DataTable() Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ASC_Schedule").ConnectionString) Dim myCommand As SqlCommand = _ New SqlCommand("SELECT TutorID, TimeIndex FROM T_Hours WHERE SessionID > 0 AND TutorID IN (@TutorList) AND DateDropped > GetDATE() ORDER By T_QualifiedTutors.TutorID", myConnection) myCommand.Parameters.AddWithValue("@TutorList", qualifiedTutorsDataSet.Columns.Item("TutorID")) Dim myDataAdapter3 As SqlDataAdapter = New SqlDataAdapter() myDataAdapter3.SelectCommand = myCommand myDataAdapter3.Fill(TutorAvailabilityDataTable) Return TutorAvailabilityDataTable
the usual way to bid a gridview is to data soursce is there a way to do the folowing , creat a data table from the gridview shown valus " currunt page "
Hi, I am experimenting to make a datatable in C# code in a page. This table should be a disconected table with Only valid for the present session. I try the following code: public partial class Default2 : System.Web.UI.Page { DataTable DT = new DataTable("TEST");
protected void Button1_Click(object sender, EventArgs e) { for (int i = 1; i < 20; i++) { DataRow MyRow = DT.NewRow(); MyRow["Col1"] = i; DT.Rows.Add(MyRow); } } } For one reason or the other. if I click the button I get the message that Col1 dus not make part of the table TEST. It turns out that there are no columns added to the table. altroug the code in the page load part has been run. I suppose I have to do something with the session state to make my DataTable persistent, but I have no idea what. Can somebody help me out? Thanks! Rob
Is the merge method, what will work in this case ? I have two datatables with the exact same structure. How can I append the rows from table 2 onto the bottom of table 1 ? Is looping through the rows collection the only way ?
Hi,Is it possible to store data that is retrieved by SqlDataSource in a... let's say... DataTable? I mean, If I dragged and dropped a GridView and an SqlDataSource and then I set up those controls in page designer in Visual Studio, is it possible at some time later to retrieve the data retrieved by the SqlDataSource and then store the data in a DataTable?Best regards,Haris
hai This is rameshi had a small doubt in this public DataTable GetStatelist() { DataTable returnValue = null; SqlCommand selcommand = new SqlCommand(); selcommand.CommandType = CommandType.Text; selcommand.CommandText = "select * from USER_MASTER"; selcommand.Connection = _connectionobject; try { _connectionobject.Open(); DataSet ds = new DataSet(); SqlDataAdapter ada = new SqlDataAdapter(selcommand); ada.Fill(ds); _connectionobject.Close(); return returnValue; ; } catch (Exception exception) { if (_connectionobject.State == ConnectionState.Open) { _connectionobject.Close(); } throw new Exception(exception.Message); } if (_connectionobject.State == ConnectionState.Open) { _connectionobject.Close(); } return returnValue; ; } after compiling its show the return value is nullwhat is error in my codinghow i can return the value to the corresponding function if (!IsPostBack) { GridView1.DataSource=database.GetStatelist(); GridView1.DataBind(); } i am waiting for the result
I am fetching records from a table and putting in a datatable using the "sqlda.fill(datatable)" when i see the datable in "data visualizer" i find no rows. It is confused eventhough it does not throw any error
I have a table containing prices. This table will be queried very often to provide quotes for clients.So to ease the burden on the server I want to cache the table and then just query the cached version.However it seems that I can only cache the table as a datatable. This means I have to query the datatable to get the prices for each quote.I'm not sure how to query a datatable. Is ther syntax similar to querying a SQL table?In fact is this best way to go about things?Any help would be appreciated.G
In my appllication, i am having a DataTable... For that DataTable i have to write a query for a expression... This epression is more of mathematical expression type. Here is the sample expression-
How i can write a query for such a equation...? Is it possible to use logical & mathematical operator in the equation & to write a query for this type of equation... Is there is any good article on this...
Hi, how do i do a select statement with asp.net to return a record if a field is null. I have the following code: SqlCommand cmd = new SqlCommand("SELECT * FROM Categories WHERE ParentId = @ParentId", cn);.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId != null ? (object) parentId : DBNull.Value;cn.Open(); The variable parentId is a nullable int. However when i pass in a null value it doesn't return anything even though there are records in the database that have a null value for the ParentId field. Appreciate if someone could tell me what i am doing wrong. Thanks
Hello. I need some help constructing a query i need to run on my database. I need to add 2 fields to every table in my databse. However, some of the tables already have1 or both these fields so i need to somehow do a check if the dield already exists. If it does not create the fields. Im using a MS SQL express 2005 server. Could anyone help me construct this. Im pretty novice at SQL. Thanks.
I am executing a stored procedure in one database (Database1) that pulls data from another database (Database2) that is the back end for a third party application. Some of the fields in that other database are now encrypted. I need to decrypt those fields but since the query is running in a database other than where the data lives (which is also where the symmetric key + cert lives), I am getting the following error: "Cannot find the symmetric key" Below is an example of what I am running in the stored procedure:
OPEN SYMMETRIC KEY [XXXXKey] DECRYPTION BY CERTIFICATE [XXXX_CERT]; select CONVERT(Varchar(50), DECRYPTBYKEY( <ENCRYPTED FIELD> )) FROM Database2.dbo.TABLE1 CLOSE SYMMETRIC KEY [XXXXKey];
What do I need to add to Database1 so the stored procedure can decrypt the data it pulls from Database2?
I have the following: Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapter Dim dF As Data.DataTable = ResourceAdapter.GetDataByDistinct Dim numRows = dF.Rows.Count Dim dS As Data.DataTable = ResourceAdapter.CountQuery Dim sumRows = dS.Rows.Count DataList1.DataSource = dF DataList1.DataBind() LblCount.Text = numRows LblSum.Text = sumRows numRows is the number of records in a particular category. CountQuery is a method that returns the total number of records in all the categories. I don't know how to get the result of that query, the code line in bold isn't right. How do I get this number?Diane
I'm trying this code but nothing is being displayedSqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["STRING_CON"]); SqlDataAdapter da = new SqlDataAdapter("my_sproc", conn);DataTable dt = new DataTable(); DataRow dr; //Adding the columns to the datatabledt.Columns.Add("CategoryIdIn"); dt.Columns.Add("CategoryNameVc");foreach (DataGridItem item in gd_freq.Items) { dr = dt.NewRow(); dr[0] = item.Cells[0].Text; dr[1] = item.Cells[1].Text; dt.Rows.Add(dr); }//ForEach da.Fill(dt); gd_freq.DataSource = dt; gd_freq.DataBind();
