|
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.
|
|
|
|
|
pedestrian797 wrote: I then try to create a binary(photo) file From the DataRow's Image column but fail
And how are you trying to do that?
pedestrian797 wrote: the photo file is to be showed in PictureBox
As I recall, you don't need to write the data to a file to put it in a PictureBox
|
|
|
|
|
The PictureBox have ImageLocation property which accept the File path. It also have Image property with is System.Drawing.Image type. However my retrieved column is in DataRow.Column("Picture") format...
Pedestrian, Penang.
|
|
|
|
|
pedestrian797 wrote: However my retrieved column is in DataRow.Column("Picture") format...
That is not a format. That is a method for retrieving column data. The format will be a byte array.
pedestrian797 wrote: It also have Image property with is System.Drawing.Image type
Then create an image from the byte array. You can do this with a Bitmap (which is derived from image). Then assign the image to the PictureBox.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
