|
Try this:
CREATE TABLE StationData (StnID INT NOT NULL <code>IDENTITY </code>, Name CHAR(50), Province CHAR(20), Latitude FLOAT, Longitude FLOAT);
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
OK, thanks. It worked.
Just wondering, however, IDENTITY seems to have the same effect as AUTO_INCREMENT. What then, is the status of the AUTO_INCREMENT specifier? Obsolete? Why was SQL rejecting it?
Thanks,
Mark
|
|
|
|
|
MySQL syntax is AUTO_INCREMENT[^]
SQL Server is IDENTITY [^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I'm going to have to watch out for that. I've seen a lot of web tutorials and references that talk about AUTO_INCREMENT but it turns out that they were all talking about MySQL, not SQL. I had assumed that they would be similar in syntax but this is turning out to be not the case.
Thanks again,
Mark
|
|
|
|
|
I'm working with an existing database so splitting date values into seperate DD, MM & YYYY columns is not ideal. Is there a way to cast smalldatetime to British date format: DD/MM/YYYY ?
Thanks.
|
|
|
|
|
Look up CONVERT in SQL Books Online
CONVERT ( SMALLDATETIME , your_smalldatetime_field , 103 )
|
|
|
|
|
I need to write a stored procedure that will copy rows from one table and insert them an another table based on a unique id. Any Ideas? I am very new to writing stored procedures so any help would be greatly appreciated.
Thank You
|
|
|
|
|
To create stored procedure write following
CREATE PROCEDURE [procedureName]
After that, just write your query.
Example:
CREATE PROCEDURE copyData<br />
@selectID int <br />
<br />
AS <br />
<br />
SELECT ID, Name <br />
INTO newTable<br />
FROM origTable <br />
WHERE origTable.ID = @selectID
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
Try this
Copying a Table into an Existing Table
insert into originaltable select * from mytablecopy where [Condition]
Navi
|
|
|
|
|
Hi
How To Connect To Table DBF with OLEDB
VB.net
|
|
|
|
|
Imports System.Data.OleDb
...
Dim cnn As New OleDbConnection("Provider=VFPOLEDB.1;Data Source=D:\Temp\MyTable.dbf;")
cnn.Open()
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Dim con As New OleDbConnection
Dim da As New OleDbDataAdapter
Dim ds As New DataSet
con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=dBASE IV;User ID=Admin;Password=")
con.Open()
da = New OleDbDataAdapter("select * from table1.dbf", con)
da.Fill(ds)
"Error In Da.Fill(ds)"
|
|
|
|
|
Dim con As New OleDbConnection
Dim da As New OleDbDataAdapter
Dim ds As New DataSet
con = New OleDbConnection("Provider=VFPOLEDB.1;Data Source=c:\table1.dbf;")
con.Open()
da = New OleDbDataAdapter("select * from table1.dbf", con)
da.Fill(ds)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi,
I have a problem with an sql procedure. What i want to do is build a procedure that will add a record to a database table and after the addition of the record will return the id of that record.
So the procedure is:
CREATE PROCEDURE AddListing
@Name varchar(150),
@Description varchar(1000),
@Keywords varchar(50),
@Address varchar(200),
@Telephone varchar(50),
@Fax varchar(50),
@Mobile varchar(50),
@Email varchar(50),
@URL varchar(50),
@Aproved bit,
@Premium bit,
@UserID int,
@nextID int Output --
|
|
|
|
|
Check out this link
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx
Navi
|
|
|
|
|
It is a bad practice to get your ID using MAX(). You should define the l_id column as an IDENTITY column, and allow SQL Server to create the ID for you. Then, make your sproc like this:
CREATE PROCEDURE AddListing
@Name varchar(150),@Description varchar(1000),@Keywords varchar(50),
@Address varchar(200),@Telephone varchar(50),@Fax varchar(50),
@Mobile varchar(50),@Email varchar(50),@URL varchar(50),
@Aproved bit,@Premium bit,@UserID int,@nextID int Output --<the output
AS
SET NOCOUNT ON
INSERT INTO listings (l_name, l_desc, l_keywords, l_address, l_tel, l_fax,
l_mob, l_email, l_url, l_premium, l_approved)
VALUES @Name,@Description,@Keywords,@Address,@Telephone,@Fax,@Mobile,@Email,
@URL,@Premium,@Aproved)
<code>SET @nextID = SCOPE_IDENTITY()</code>
INSERT INTO usr_ls_line (usr_id, l_id)
VALUES (@UserID,@nextID)
return @nextID
Then, in VB.NET do something like this:
Imports System.Data
Imports System.Data.SqlClient
...
Dim conn As New SqlConnection
conn.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=mytestdb;" & _
"Integrated Security=SSPI"
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "AddListing"
Dim prm1 As New SqlParameter("@Name", SqlDbType.VarChar, 150)
prm1.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm1)
prm1.Value = "MyStaffName"
Dim prm2 As New SqlParameter("@Description", SqlDbType.VarChar, 1000)
prm2.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm2)
prm2.Value = "Whatever"
'...
<code>Dim prm5 As New SqlParameter("@nextID", SqlDbType.Int)
prm5.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm5)</code>
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
MsgBox(<code>"Returned ID=" + prm5.Value</code>)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
In this case yes, an IDENTITY column would work work, yet you can't make such a blanket statement that not using it is bad practice. Perhaps there is some special processing that must take place to assign an ID, or the ID field is not numeric.
only two letters away from being an asset
|
|
|
|
|
Mark Nischalke wrote: you can't make such a blanket statement that not using it is bad practice
Good point Mark.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
insted of using [SELECT MAX(l_id) FROM listings] use IDENT_CURRENT(listings)
IDENT_CURRENT
Returns the last identity value generated for a specified table
Navi
|
|
|
|
|
dim comm as new sqlcommand("AddListing",connection)
comm.commandtype=commandtype.storedprocedure
'For input parameters
coom.parameters.add("@name","abc")
comm.parameters.add("@nextid",sqldbtype.int).direction=parameterdirection.output
comm.executenonquery
comm.parameters("@nextid").value
Thanks & Regards
Kumar Prabhakar
abc
|
|
|
|
|
after your insert, add the following:
Select SCOPE_IDENTITY() as NewestRecordsID
|
|
|
|
|
Suppose i have an Insert trigger on a table (in MSDE) and lots of records are inserted in quick succession
How will the triggers be fired, is it guaranteed that the triggers will be fired in the same order the records were inserted?
Thanks Last modified: Monday, August 28, 2006 7:04:15 AM --
|
|
|
|
|
The trigger will always be fired in order. However, if the trigger contains switching code that performs data dependent tasks then they may not be completed in order.
If the insert is a single record insert, even in a batch, then the trigger will be fired for each insert. If the insert has multiple inserts in one go such as inserting based on a subquery, then the trigger will only be fired once and you will need to loop through the special inserted table in the trigger.
Ian
|
|
|
|
|
Thanks
One more question, Does the next trigger fires only when the previous one has returned?
|
|
|
|
|
I retrieve a Image column using SqlDataAdapter.Fill.
I then try to create a binary(photo) file From the DataRow's Image column but fail... (the photo file is to be showed in PictureBox)
Thanks for replying...
Pedestrian, Penang.
|
|
|
|