|
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.
|
|
|
|
|
Thanks for the replies...
Pedestrian, Penang.
|
|
|
|
|
how can i transfer database from sql server 2000 to MS Access in using C#.
|
|
|
|
|
Open two connections, one to the SQL Server and one to Access.
Create a SqlCommand to SELECT the data you want and extract it with a SqlDataReader. While you can read the rows from the SqlDataReader send OleDbCommands to Access to INSERT the data. Remember to Close all connections when done.
|
|
|
|
|
|
is this feasible for transfering all tables in database???
|
|
|
|
|
Yes. Just repeat the process for each table.
|
|
|
|
|
I have a field called occdate which I need to query. This code works fine and retreives the record
SELECT OccID<br />
FROM dbo.People_Occ<br />
WHERE (OccDate >= 01 / 08 / 2006)
However when i add a second parameter i get no results returned
SELECT OccID<br />
FROM dbo.People_Occ<br />
WHERE (OccDate >= 01 / 08 / 2006) AND (OccDate < 01 / 08 / 2007)
Can anyone tell me why?
|
|
|
|
|
The where clause will be something along the lines of:
WHERE OccDate BETWEEN StartDate AND EndDate
But it is impossible to give you the exact syntax without knowing what database you are using.
|
|
|
|
|
Keith Malwitz wrote: The where clause will be something along the lines of
Not necessarily. It is perfectly valid to say a is greater than x and a is less than y as the OP did. In fact, it gives more clarity because the BETWEEN clause doesn't intrinsically tell you if it is inclusive or exclusive of the values used. In the OP's case he's using a mix. The start date is inclusive and the end date is exclusive, so the BETWEEN clause is just not going to work for him.
-- modified at 18:57 Sunday 27th August, 2006
|
|
|
|
|
Without knowing which database you are using this is difficult to answer. I always put the date in ISO format (i.e. yyyy-mm-dd) to ensure that the parser doesn't swap the month and date around depending on the locale. Also, I typically use parameters:
SELECT OccID
FROM dbo.People_Occ
WHERE (OccDate >= @minDate) AND (OccDate < @maxDate)
|
|
|
|
|
Try this:
SELECT OccID
FROM dbo.People_Occ
WHERE (OccDate >= '01/08/2006') AND (OccDate < '01/08/2007')
--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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
