Stored Procedure or Trigger for Autogenerate Colunm - Database Discussion Boards

leoinfo4-Jul-08 4:26

4-Jul-08 4:26

You should replace *for insert* with *INSTEAD OF INSERT*.
When FOR is the only keyword specified, AFTER is used by default.

CREATE TRIGGER dbo.insertNum
ON testIcreTrigger  
INSTEAD OF INSERT 
AS
BEGIN
  INSERT INTO testIcreTrigger (Num)
  SELECT MAX(Num)+1 FROM hitTheatre
END

begin tran... commit and stored procedures/functions

blakey4043-Jul-08 0:21

blakey404

3-Jul-08 0:21

Quick question... i'm feeling a bit thick today...

Does...

Begin Tran
stored procedure 1
stored procedure 2
stored procedure 3
rollback

...work? ie are the contents of stored procedures also rolled back?

Re: begin tran... commit and stored procedures/functions

DerekFL3-Jul-08 4:52

DerekFL

3-Jul-08 4:52

Yes they are.

Re: begin tran... commit and stored procedures/functions

blakey4043-Jul-08 5:11

blakey404

3-Jul-08 5:11

magic. cheers.

Unable to view SQL Report with normal login

Ankur.Bakliwal3-Jul-08 0:09

Ankur.Bakliwal

3-Jul-08 0:09

Hello All,

Please help me..

using admin login report showing records.
but i m Unable to view SQL Report normal login

is there any solution...

Thanks,
Ankur Bakliwal

installation

Kissy162-Jul-08 23:50

Kissy16

2-Jul-08 23:50

Hai all,
Can i install sqlserver2000 and 2005 in same folder.
I have already installed sqlserver2000 in my XP,and i want to install sqlserver2005.
I dont if it raises any problem or not,if not,wat kind of things i have to consider.
Give me some answer.

can i install in different folders.my sqlserver2000 and vs2003 are installed in C:/
and vs2005 in D:/,now where i have to install my sqlserver2005.

kissy

Re: installation

John_Adams3-Jul-08 5:35

John_Adams

3-Jul-08 5:35

Hi,

I am not sure whether SQLServer 2000 and SQLServer 2005 can be installed in same folder. But they can be installed on same machine in different folders. However, you can not have two default instances of any version of SQL Server but you can have ten different installations of SQLServer 2000 and SQLServer 2005 as long as they are all named differently, they can co-exist with out an issue.

Hope this helps.

Regards,
John Adams
ComponentOne LLC

Stored Procedure or Trigger for Autogenerate Colunm

rrrriiizz2-Jul-08 20:45

rrrriiizz

2-Jul-08 20:45

Hello All,
Am creating a small Projects using Vb.Net and Sql server 2005, in that i want to get a input automatically for one column(Eno column), that column is a alphanumeric column, i want to store information automatically like below,

if the Employeename is 'Anton' it will automatically fill the Eno as A001, if Employeename is 'Brail' then it will fill 'Boo1' if again Employeename is 'Berg' then 'b002' and so on. can i get that by adding a trigger or stored Procedure, if so then tell simply code examle for this, this very urgent for me, so Pleeeeeeeeeeeeease help me.

Thanks
Riz

Re: Stored Procedure or Trigger for Autogenerate Colunm

Mycroft Holmes2-Jul-08 20:56

Mycroft Holmes

2-Jul-08 20:56

I am going to assume you are trying to create a key field for your employee, if so then:
DO NOT ADD INTELLIGENCE TO A PRIMARY ID/KEY FIELD, you are doing this by adding the first character of the surname. This is wrong, it should simply be the record identifier, use the surname field for search/sorting NOT the ID field.

Generating an employee number for human consumption (timecards or something) is a different matter. I would put it in the inserting stored procedure (I never use triggers)

Before the insert get the count of existing surnames with the 1st char
Select @N = (count(*) + 1) From Employee where Surname like 'A%' (this fails when the employee changes surnames - gets married and is the reason for the initial comment)
or
Select top 1 employee where ENO like 'A%'
order by ENO desc

Add the text to the ENO field

Never underestimate the power of human stupidity
RAH

Re: Stored Procedure or Trigger for Autogenerate Colunm

rrrriiizz2-Jul-08 21:34

rrrriiizz

2-Jul-08 21:34

Thank you somuch for your speedy reply. i will try the code and let you know the result, once again i thank you.

Thanks
RIZ

Re: Stored Procedure or Trigger for Autogenerate Colunm

rrrriiizz3-Jul-08 1:09

rrrriiizz

3-Jul-08 1:09

Hello All,

Below Code generates Error in if, Please let me know whats wrong in this

create procedure refno_stock
as
begin
declare @stockrefno as varchar(50),
@category as varchar(50)
if @category = 'Desktop'
begin
select ( count(*)+1) as Stockrefno from stockmaster where category like'd%';
end if
if @category = 'Laptop'
begin
select ( count(*)+1) as Stockrefno from stockmaster where category like'L%';
end if
if @category = 'HDD'
begin
select ( count(*)+1) as Stockrefno from stockmaster where category like'H%';
end if

Re: Stored Procedure or Trigger for Autogenerate Colunm

Mycroft Holmes4-Jul-08 0:26

Mycroft Holmes

4-Jul-08 0:26

you use END instead of END IF

Never underestimate the power of human stupidity
RAH

Re: Stored Procedure or Trigger for Autogenerate Colunm

rrrriiizz5-Jul-08 1:35

rrrriiizz

5-Jul-08 1:35

Again It shows error in Last End
ie Incorrect sysntax near END, Line 17

Trigger with Time

obarahmeh2-Jul-08 20:08

obarahmeh

2-Jul-08 20:08

Hi,
I need to creat a trigger on 'myTable' after 'insert' new records...I need to send an email every 30 minutes if Insert happenned...Any one plz can help???

I searched over the Internet, but nothing is useful for this purpose.

And in the future, my goal is to send this email depending on the time specified by the user after Insert.

Kind Regards
OBarahmeh

Re: Trigger with Time

Mycroft Holmes2-Jul-08 21:00

Mycroft Holmes

2-Jul-08 21:00

This is not a "trigger" or an procedure operation. You would need to create and schedule a job in SQL Server to acheive this. It would have to poll the target table to identify the records inserted say every 2 minutes and if there are records then execute the mailing stored proc.

Never underestimate the power of human stupidity
RAH

Re: Trigger with Time

obarahmeh2-Jul-08 22:50

obarahmeh

2-Jul-08 22:50

Dear Holmes,
I am not proffessional in SQL Server...can you please tell me how to do that (create and schedule a job) that send email when insert happens on MyTable???

Thanks

Kind Regards
OBarahmeh

sqlDataReader Execution in a Function

IlseBrase2-Jul-08 18:29

IlseBrase

2-Jul-08 18:29

Hi, I'm still a beginner in this. Creating code in vb to log in a user. The username and password typed into the textbox must be verified against the username and password in the database. I use a SqldataReader to perform this action but can't seem to get the syntax correct... please can you help me.

Here is the Function and the relevant button :

Protected Sub butLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles butLogin.Click
If CheckPassword(txtusername.Text, txtpassword.Text) Then
'FormsAuthentication.RedirectFromLoginPage(txtusername.Text, True)
Response.Redirect("Default.aspx")
Else
MsgBox("username or password not found. try again")
End If
End Sub

Private Function CheckPassword(ByVal username As String, ByVal password As String) As Boolean
Dim bsuccess As Boolean
Dim command As New SqlCommand("SELECT (Username,Password) from UserInfo WHERE Username = '" & txtusername.Text & "'", connection)
Try
Dim drdUsers As SqlDataReader
drdUsers = command.ExecuteReader
connection.Open()
While drdUsers.Read
If txtpassword.Text = drdUsers.Item("Password") Then bsuccess = True
End While
connection.Close()
Catch
bsuccess = False
connection.Close()
End Try
Return bsuccess

End Function
Sigh | :sigh:

Re: sqlDataReader Execution in a Function

Vimalsoft(Pty) Ltd3-Jul-08 4:03

Vimalsoft(Pty) Ltd

3-Jul-08 4:03

Firstly you should post this in vb Forum,

Secondly your Code is messy, it seems you dont know where to put your code. You are Executing the command outside your connection. Open the Connection and Execute your command and Close the Connection and Close the reader and after that you dont need to loop through the reader. Here is my Suggestion, why dont you use a StoredProcedure that returns number of rows ,and when you call that Stored Procedure you will test for records that are returned. if you planned it well you will be looking for an integer that is "1" or less than one , e.g

if(intTeturned < 1)
{
   //Invalid Login
}
else
{
//Valid Login , then you can redirect your users to the page you want.
}
}

Hope this Helps

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

SQL image data

nelsonpaixao2-Jul-08 14:02

nelsonpaixao

2-Jul-08 14:02

Hi,

I created a kind of employees application with all sorts of data. I working with SQL server and C#.

I have most string and int data, but also some images.
But i use the "string-with-path-method" to get to the images.
It works ok, but since the images are in a folder, it seems that they are not as private like the other data.

I dont know how to work with the images in SQL server, so i use that method.

Can anyone help me here? Confused | :confused:

Thanks

Re: SQL image data

Giorgi Dalakishvili2-Jul-08 21:58

Giorgi Dalakishvili

2-Jul-08 21:58

Here you are: How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET[^]

Giorgi Dalakishvili

#region signature
my articles
#endregion

Re: SQL image data

Hesham Amin2-Jul-08 22:08

Hesham Amin

2-Jul-08 22:08

Hello nelsonpaixao, Chech this article :

2. Uploading / Downloading Pictures to / from a SQL Server
[^]

Hesham A. Amin
My blog

Connection-string [modified]

nelsonpaixao2-Jul-08 13:59

nelsonpaixao

2-Jul-08 13:59

Hi,
I design a windows application that needs to pull data from a SQL server database.
It works just fine!!!.

But, when i install the application in another computer i get problems,I need to attach the database in SQL server in order to work.

So, i want to get a nice "connection string" to pull data from the database without no further "attachments"!
Can anyone help me? Confused | :confused:

(I usually use *.udl files to see the connection-string)

Thanks

modified on Wednesday, July 2, 2008 9:29 PM

Re: Connection-string

rrrriiizz2-Jul-08 19:31

rrrriiizz

2-Jul-08 19:31

Please do ODBC Connctivity. did you know any thing about that?
You should first define a ODBC connectivity in your Projects. After that first you establish ODBC Connection in your client system, after install your exe in the particular system, it will work definately.

Re: Connection-string

Mycroft Holmes2-Jul-08 21:12

Mycroft Holmes

2-Jul-08 21:12

ODBC will require that you establish an ODBC connection from the client - therefore you need to manage the client as well as your app.

I suggest this site
clickety[^]

Never underestimate the power of human stupidity
RAH

Oracle instant client

Muammar©

2-Jul-08 1:00

Any idea how to create an SID for the oracle instant client??

Many thanks

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.