how to write the insert statement when the Primary key is auto increment num?

[legendgod]

I want to insert a new record to a table, the table have 8 columns. The Primary Key "schId" which is an auto-increment number is ignored. Therefore I created the follow stored procedure:

 

CREATE PROCEDURE [dbo].[scheduleAddItem]
@startDT smalldatetime,
@endDT smalldatetime,
@Note varchar,
@sAlias varchar,
@hName varchar,
@cName varchar

AS

INSERT INTO Schedule (schStartTime, schEndTime, schNote, sId, hId, cId,
schPending) VALUES (@startDT, @endDT, @Note, @sAlias, @hName, @cName, 5)
GO

 

In my aspx page I typed the following:

' startDT & endDT are datetime variable, NoteTextBox is a textbox control, the else are string variable
DBCommand.Parameters.Add(New SqlParameter("@startDT",SqlDbType.smalldatetime, startDT))
DBCommand.Parameters.Add(New SqlParameter("@endDT",SqlDbType.smalldatetime, endDT))
DBCommand.Parameters.Add(New SqlParameter("@Note", SqlDbType.varchar,
Replace(NoteTextBox.Text, "'", "''")))       
DBCommand.Parameters.Add(New SqlParameter("@sAlias",SqlDbType.varchar, sAliasDropList.SelectedValue))
DBCommand.Parameters.Add(New SqlParameter("@hName",SqlDbType.varchar, hNameDropList.SelectedValue))
DBCommand.Parameters.Add(New SqlParameter("@cName",SqlDbType.varchar, cNameDropList.SelectedValue))

 

However, when I run the aspx, the dotNet framework return a error said the stored procedure contain error. Which part I am wrong with? Am I wrong in ignore to input the Primary key value?Any idea is helpful. Thank you.

[JABE]

Can you post the error message? Problem may not be related to the primary key field at all.

[legendgod]

Can you post the error message? Problem may not be related to the primary key field at all.

 

Because I am using traditional Chinese Framework it gives non-English error message. 'ScheduleAddItem' is my stored procedure. The translation could be ..... :

 

row 1: statement around 'ScheduleAddItem' incorrect.

 

Expection detail: System.Data.SqlClient.SqlException: Row 1: statement around 'ScheduleAddItem' incorrect.

 

source code error:

 

 

Row 72: ' ")"

Row 73:

Row 74: DBCommand.ExecuteNonQuery

Row 75: 'DBConnection.Close()

Row 76: Conn.Close()

[JABE]

Everthing seems OK, except for that line Row 72: ' ")". Any idea where that came from? Anyway, if that's not the root of the problem, try using Profiler to inspect the actual SQL statements passed by SqlClient to SQL Server. Might be some translation issues.

[legendgod]

Please forgive my know-nothing, what is Profiler? Is it a class in DotNet of a term?

[wessamzeidan]

INSERT INTO Schedule (schStartTime, schEndTime, schNote, sId, hId, cId,

schPending) VALUES (@startDT, @endDT, @Note, @sAlias, @hName, @cName, 5)

 

I'm not sure, but what is the type of hId and cId, they seem IDs to me, and you're assigning string values to them, @hName and @cName. Can you post the structure of your table.....

[legendgod]

Problem solved... some ppl told me that I missed the following 2 statement:

DBCommand.CommandType=CommandType.StoredProcedure

DBCommand.CommandText="ScheduleAddItem"

 

Thank you for all your support anyway!