i am beginners, how to make auto genrate id in store procedure and what is the code in vb.net

Question

create procedure std_reg_programs

@prgm_id int,

@prgm_name nvarchar(50),

@prgm_active bit

INTO Programs VALUES(@prgm_id,@prgm_name,@prgm_active)

END

@prgm_active

As

BEGIN

INSERT

Answer 1

You can use Table with identity column in store procedure to genrate id automatically.

If using SQL 2012 then you can also use sequence for the same.

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 2

As RohitGarg mentioned, you can use an IDENTITY column so that SQL Server assigns an incremental value for each new row.  The stored procedure example below inserts the row and returns the generated value as a single row, single column result:

CREATE TABLE dbo.Programs (
	prgm_id int NOT NULL IDENTITY
		CONSTRAINT PK_Programs PRIMARY KEY,
	prgm_name nvarchar(50),
	prgm_active bit
	);
GO

CREATE PROCEDURE dbo.std_reg_programs
	@prgm_name nvarchar(50),
	@prgm_active bit
AS

INSERT INTO dbo.Programs VALUES(@prgm_name,@prgm_active);

SELECT SCOPE_IDENTITY() AS 	prgm_id;

RETURN @@ERROR
GO

The stored procedure can be called using the VB.NET code below:

        Using connection As New SqlConnection("Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI")
            Dim command As New SqlCommand("dbo.std_reg_programs", connection)
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.Add("@prgm_name", SqlDbType.VarChar, 50).Value = "program name"
            command.Parameters.Add("@prgm_active", SqlDbType.Bit).Value = 1
            connection.Open()
            Dim pgrmId As Integer = command.ExecuteScalar()
            connection.Close()
        End Using

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 3

 Thanks Mr.Dan Guzman

command.Parameters.Add("@prgm_name", SqlDbType.VarChar, 50).Value = textbox1.text

 command.Parameters.Add("@prgm_active", SqlDbType.Bit).Value = textbox2.text

When I am sending textbox1.text=MBA and textbox2.text=0

i am getting error:

Failed to convert parameter value from a String to a Boolean.

how can i take input from user ??

Answer 4

Hi Bilal,

 

A better forum for Windows Forms data controls questions is here: http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/threads. For web forms use the ASP.NET forums (http://forums.asp.net/) Web Form Data Controls section. I’ll move this (in about an hour) to the appropriate forum. Just trying to make sure you get the best, quickest, answer.

 

Cheers, Bob

Answer 5

i am getting error:

Failed to convert parameter value from a String to a Boolean.

how can i take input from user ??

One method to convert the user-entered value to a strongly-typed value is with Boolean.Parse:

command.Parameters.Add("@prgm_active", SqlDbType.Bit).Value = Boolean.Parse(textbox2.text)

If you are using bound data controls, a better forum for related questions is the one suggested by Bob.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

how can i take input from user ??

Hi BILAL,

The input from user here should be a int type, bool type or DBNull。

You should check if the TextBox.Text could be converted to one of them.

However, I think using TextBox is not a good idea here.

If the bit column is nullable, I would suggest you using ComboBox Instead. The ComboBox should contain three items, such as True, False and Null.

Then you should convert them to True, False (Boolean type) and DBNull.Value。

If the bit column can't be null, I would suggest you using CheckBox give the user an option, such as true/false.

If there is anything unclear, please feel free to let us know.

Best Regards,

---

Bob Wu [MSFT]
MSDN Community Support | Feedback to us

Answer 7

I have combobox which is set as a Dropdownlist so,how can i put the index 0 as "select value"

ComboBox1.Items.Insert(0, "Select any value")

After executing i have got this result.

But i want this....

Answer 8

HI Bilal,

Try add the following code after the insert method.

ComboBox1.SelectedIndex = 0

Best Regards,

---

Bob Wu [MSFT]
MSDN Community Support | Feedback to us