I am building a simple table, populated by ASP form, where every record should be assigned a unique ID. When working with Access I used `autonumber` datatype to keep track of every record. Can something like this be done with MS SQL server, if not what do you think is a good way to solve the problem ??
Thanks,
Robert :)
Hi,
I have developed a web database application using ASP and MS Access, however the requirement for hosting the application is that it must use an MS SQL Server database. I converted the database to SQL without any problems, and many features of the application work under SQL Server except the 'add record' function. I realised there isn't an 'autonumber' field in SQL Server (which i use as the primary key for many tables), but an 'int' field. I considered pulling out the latest int from the database, incrementing it manually and adding the new record with this number... i also noticed there is a 'unique identifier' field.
Is there a quick solution for this problem?
Thanks
Hi, I have a question, I have created a table and with a primary key called "ID". However, I want the "ID" be auto increment as well. when inserting new record into the database.I'm using vb.net. how can I do in the following format: "1", "2", "3", ............ etc. I've the code below but it's not working in the right way, what's wrong with my code?
Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim ssql As String
Dim Itemid As Integer
Dim updcmd As SqlClient.SqlCommand
Itemid = 0
mysqladap = New SqlClient.SqlDataAdapter("select MAX(Item_id) From auction where item_type= '" & (Image1.ImageUrl) & "'", mySqlConn)
Itemid = (Itemid) + 1
ssql = "insert into auction (item_id,owner_name,owner_mail,owner_mobile,owner_phone,owner_ext,item_type,item_name,item_image,item_desc,item_cost,start_date) values ('" & (Itemid) & "','" & Trim(ownertxt.Text) & "' ,'" & Trim(emailtxt.Text) & "', '" & Trim(mobiletxt.Text) & "', '" & Trim(phonetxt.Text) & "','" & Trim(exttxt.Text) & "','" & Trim(DropDownList1.SelectedValue) & "','" & Trim(itemtxt.Text) & "','" & Trim(Image1.ImageUrl) & "','" & Trim(desctxt.Text) & "','" & Trim(costtxt.Text) & "','" & Trim(Today.Date) & "')"
updcmd = New SqlClient.SqlCommand(ssql, mySqlConn)
updcmd.ExecuteNonQuery()
lblmsg.Visible = True
End SubAnyone can help me? Thanks.
Is there something similar to Access' autonumber in sql server?
I'm currently using the unique identifier datatype and it creates this looooooong id, anyway to shorten it to start with 1?
Thanks,
Anthony
Hi,
what happens when the autonumber field becomes bigger than MAX_INT?
If I get arithmetic overflow, how i can avoid this problem?
Thanks in advance
-december
I know this may be in the wrong forum, but I have a question. I am working on a system for a video store. I have rentals and sales for videos. I have set the ID fields for rentals and sales to be autonumbers and increment by 1, but I would like to have an S in front of sales IDs and R in front of rental IDs at all times. It is kind of like a constant in all autonumber ID fields. I want the S and R to be in every ID field, but the number to change. Thanks.
View 2 Replies View RelatedHiim new to ms sql server, having previously used mysql. How do i make a auto number field? What datatype shall i use for it? like autonumber for mysql. Ive tried setting my primary key field to uniqueidentifier data type but then i still need to manually add a guid key in there. i want it so it automatically generates a unique key everytime i add a new row. is this possible?!hope someone can help!thanks
View 2 Replies View RelatedHello--
I'm importing data from an Access table and, of course, one field is a primary key. This field needs to be an autonumber. The problem is the data I'm importing isn't sequential.
Can I import the data, then alter the table to make the column increment or is there a way to create the table and make the field increment, but import non-sequential data?
HELP!
Thanks,
grimey
Is there an equivalent in sql server2005 to msaccess autonumber?
I want to be able to insert values into a table and have the pk auto generated. Have tried using NEWID() function as default value for pk field but still have to stipulate NEWID() in the insert statement.
Is there a way around this?
B
HiI wonder if anyone can help with the following:when using an autonumber in access when you use .addnew you automaticallyget the field in an autonumber field i.e.rs.addnewjno=autonofldrs.updatejno will have a value. however in SQL you have to update first and then findthe record (I may be wrong) is there a way to getthe field value before doing the update in SQL Server ?TIASteve
View 8 Replies View Relatedwhat can we do if we have to get the next autonumber to be inserted {before inserting the record} in MS access
It is a simple think to get the max(id) + 1.But if tere is some record deleted then this will not work correctly
for instance i have records
id Employeename
11 a
23 b
31 c
45 d
then if delete 31 and 45 my record becomes
id Employeename
11 a
23 b
now when i get max(id) +1 it will return 24 {rather then 46 which i want's to retrieve}
Is there any way that i can get the next autonumber to be inserted prior to insertion of the record ?
{Although it is questions is not particular to sql server but i think some geek might answer it here}
I'm in the process of converting over an Access database - The existing Forms, Reports, etc are staying within the Access front-end and the Tables are now linked to the SQL database. The only problem is, most of the Tables contain Autonumber fields, so although they converted over to Identity fields, existing records work fine. When I try to add a new record, it doesn't automatically enter the next available Autonumber/Identity until I select a record which already exists to force it to update itself. When I add a new record using the original Access database, as soon as you start entering information into the new record, the next available Autonumber automatically appears. Any suggestions on forcing it to automatically appear using the SQL database and an Access form????
View 4 Replies View RelatedI'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies View RelatedHi,
I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.
Hi all,
i've a reasonable amount of experience with MS Access and less
experience with SQL Server. I've just written an .NET application that
uses an SQL Server database. I need to collate lots of data from around
the company in the simplest way, that can then be loaded into the SQL
Server database.
I decided to collect the info in Excel because that's what most people
know best and is the quickest to use. The idea being i could just copy
and paste the records directly into the SQL Server database table (in
the same format) using the SQL Server Management Studio, for
example.
Trouble is, i have a problem with line feed characters. If an Excel
cell contains a chunk of text with line breaks (Chr(10) or Chr(13))
then the copy'n'paste doesn't work - only the text up to the first line
break is pasted into the SQL Server database cell. The rest is not
pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a
memo field into SQL Server database, but with exactly the same problem.
I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases
interacting together, can someone suggest the simplest way of
transferring the data without getting this problem with the line feeds?
I don't want to spend hours writing scripts/programs when it's just
this linefeed problem that is preventing the whole lot just being
cut'n'pasted in 5 seconds!
cheers
Dominic
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
===================================================
1000| Sales_HW| -20,000.00 | 01 | 2005
===================================================
1000| Sales_SW| -10,000.00 | 01 | 2005
===================================================
1001| Cost_HW | 5,000.00 | 01 | 2005
===================================================
1001| Cost_SW | 5,000.00 | 01 | 2005
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Sales Category | Sales | Cost | Profit
=================================================
HW |-20,000.00 |5,000.00| -15,000.00
SW |-10,000.00 |5,000.00| -5,000.00
=================================================
Total |-30,000.00 |10,000.00|-20,000.00
The above report have 4 columns, with last column being a calculated field (Sales-Cost)
Guys, hope someone out there can help me with the sql command for the above report?
I need to run a data report that will query an Access_Table that has thefollowing Project info:My Form has a command_button with the following code:Private Sub Command1_Click()Set db = New Connectiondb.CursorLocation = adUseClientdb.Open "PROVIDER=MSDataShape;DataPROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path &"Access_Table.mdb;"Set adoAge = New RecordsetadoAge.Open "SELECT CustomerName, OrderNo, Invoice_Date,IIf([Invoice_Date]<Now()-30 And[Invoice_DATE]>Now()-60,[Invoice_Amount],"") AS Greaterthan30,IIf([Invoice_Date]<Now()-60,[Invoice_Amount],"") AS Greaterthan60 FROMCust WHERE [Invoice_Date]< Now()-30", db, adOpenStatic, adLockOptimisticSet DataReport1.DataSource = adoAgeDataReport1.ShowEnd SubMy DataReport1 has the following RptTextbox:RptTextbox Datafield1 CustomerName2 OrderNo3 Invoice_Date4 Greaterthan305 Greaterthan60Problem arise when trying to display the above report that has an errormessage that says "Datafield Greaterthan30 not found"Is this an SQL Query limitation where Invoice_Date Datafield can only bequeried once & if we have more than one Datafieldwith the same fieldname (Invoice_Date) within the same Data report,thereport will fail to run? Should there be one moreNew Recordset to hold a second Datafield (Invoice_Date)?can anyone help? ThanksFrom:Cady Steldyn*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Related
Hi, guys,
I'm using MSSQL 2005, so I would like to know how to generate Serial no into database?
for example:
- serial no will be increase for each time confirmation has been done, The Serial no will insert into a new field as below:
00000001
00000002
00000003
and next...
thanks and regards
Hi, I have the lovely task of overhauling some of our SQL-based systems. I've found many tables that don't have unique identifying numbers that really should have them. I've searched around and people keep mentioning the Identity field as being similar to Autonumber in Access. The only examples I could find involved setting up a new table... but I need to add (and populate) an identity column to an existing database table. Does anyone know the command for this?
Example... my table is called PACountyTown. It currently has 3 columns: County, Town, and Area. I wish to call the identity-ish field RecordID.
Thanks in advance!
hi
I have a field call: password, I want to generates some random chars as
default password for users (so user got something to login, and update
later).
Current Solution I have:
I made myself a trigger and it does generate password on insert, but
the problem with that is I have to allow NULL on that field. I
don't want NULL to be allowed, and if I don't allow null... sql server
won't allow me to insert a record (when leaving password field empty).
Reason i don't wanna allow NULL on that field, is because there may be
more applicaions using the same field from the same database.
Just in case if the other developer gets sloopy, I don't want other
application(s) allow user put to enter a null or leave it blank on my
password field.
My question is:
1) can I put a function or some sort at the "Default" field under "Design Table", if so...how?
2) any suggestions for a better implementation?
3) if (1) doesn't work, what other options would you suggest other then the current trigger i'm using?
TIA
I'm having some problems importing data from a memo column (Access) into varchar column in SQL Server.
My idea was to use slowly changing dimesion to identify modified and new rows. No matter what data type I use to convert the memo column (using Data Conversion Transformation) and then using the converted column in SCD, I get the following error :
'The SCD transform does not allow mapping between columns of different types except for DT_STR and DT_WSTR.'
What do I have to do to get Memo column 'to behave' as a string?
Same problem with a different data type - decimal (18,5) in sql server - no matter what datatype I use in Data Conversion Trans, I get the same error trying to generate scd.
Thank you for your answers.
Hi,
Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters.
Thanks.
Hello, Does anyone know if there is an equivalent of an autonumber in SQL Server.
I have tried to use the int field which I thought preformed the same function but it doesn't work for me.
The code I am using is as follows
aCommand.CommandText = "INSERT INTO customers (customer_Name, customer_Id) " + "VALUES (@customer_Name, @customer_Id)";
aCommand.Parameters.Add("@customer_Name", SqlDbType.NVarChar, 50).Value = TextBox1.Text;
aCommand.Parameters.Add("@customer_No", SqlDbType.Int, 4).Value = SqlDbType.Int;
However, when I try this my customer Id is not unique., and my cusomer_Id is always set to to the number 8.
Does anyone know a way around this?
Hi
So far, I have only used Access which has an autonumber data type so that in some of my tables the id is automatically generated.
I guess this is a simple question but is there an equivalent data type in sql server?
Thanks in advance.
hii @ll..
well m havin a probelm with this :S... i ve created a DB in access and then import that db in sql server 2000, but autonumber data type got change to int :S...
well is there any solution... currently m working on JSP which is created to SQL Server 2000 and when i user insert into command.. it shows me err cuz of tht...
plz help me out..
thanx
@kS
Does anyone know how to add an autonumber field into a table? Is this the same as uniqueidentifier?? I have a table and I need to create a field to use as Primary Key. I would appreciate any help.
-Brad B
Hello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf). Plz enlighten me on this.Thanks and Regards,Sankar.
View 1 Replies View RelatedHi all,
Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.
Bill
I have a linked table in Access to my SQL 7 database. But one field who's values are either 0 or 1 now show up as 0 or -1 when I view the linked table in Access?
Any ideas why? Thanks..
I did create the field on table as TinyInt. I created an appending query and appended the records to the SQL table. Now I have 0's or 255's in the field.
Shouldn't they be 0's and 1's instead????
What am I doing wrong?
What's the best way to convert the Yes/No fields into SQL, since I want to keep the access front end.
Thanks for any help.
I have an Access application with various DB's linked together. One of the DB's contains a field, SOURCECODE, which was mistakenly entered in as an nvarchar(4000) field in MS Sql Server. When I link the table, Access converts it to a memo field.
There will never be more than 10 chars in that field. The company that created the DB says its will be too risky to change the data type. I need to link this field to another field that's a VARCHAR.
How can I do this? Access doesn't allow the CAST feature.
Our company is migrating a Microsoft Access 2010 backend database to a SQL Server 2008 database. One of the memo fields in the Access backend can store up to 150 Kb of Unicode data. To store this data in SQL server, we found that we can use the following data types:
ntext = (2^30) - 1 = 1,073,741,823 bytes.
nvarchar(max) = (2^31) - 1 = 2,147,483,647 bytes.
Because ntext will be deprecated in future releases of SQL Server, the only good alternative to store an Access memo field in SQL server is to use nvarchar(max), which is what Microsoft recommends for large Unicode texts.Storing a large amount of text like 150 Kb in an nvarchar(max) field using only SQL server works as expected. However, if Access is used to store the data in a table linked to SQL server, the maximum number of characters allowed is only 4000. We found that this limitation is imposed by the ODBC driver that limits nvarchar(max) to 4000 characters.
The connection string we are currently using to link a table to SQL server is this:
ODBC;DRIVER={SQL Server Native Client 10.0};SERVER= SQLEXPRESS;DATABASE=TestDB;Trusted_Connection=No;UID=uid;PWD=pwd;
Any solution for this limitation storing large amounts of data in a Microsoft Access memo field mapped to an nvarchar(max) data field in a SQL Server database?
Hi,
Is there anyway that i can access subreport field in main report? I just want it to be displayed. Thats it. I can not put subreport field in main report bcz it is based on the parameter passed by main report.