Help I am getting an error when I run my insert statement

Please help I am getting the same error no matter how I change my
quotes.
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'Plan'.
conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
[revfinish],[responsible],[action],[title],[dept],[author],[type])
values ('" & request.Form("startdate") &"' ,"&
Request.Form("finishdate") &" ," & Request.Form("revfinish") & ",'" &
Request.Form("responsible") & "','" & Request.Form("action") & "','" &
Request.Form("title") & "','" & Request.Form("dept") & "','" &
session("empid") & "','" & Request.form("type") & "')")
'set Rs =conn.Execute("SELECT * FROM Equipment")
startdate, finshdate,revfinish are all smalldatetime
[Action], responsible, dept, author, type are all text
title is nvarchar
any Ideas?
Try the OLEDB group?
"Tescumeh" <tescumeh@.gmail.com> wrote in message
news:1165868180.115810.136290@.l12g2000cwl.googlegr oups.com...
> Please help I am getting the same error no matter how I change
> my
> quotes.
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'Plan'.
> conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
> [revfinish],[responsible],[action],[title],[dept],[author],[type])
> values ('" & request.Form("startdate") &"' ,"&
> Request.Form("finishdate") &" ," & Request.Form("revfinish") &
> ",'" &
> Request.Form("responsible") & "','" & Request.Form("action") &
> "','" &
> Request.Form("title") & "','" & Request.Form("dept") & "','" &
> session("empid") & "','" & Request.form("type") & "')")
> 'set Rs =conn.Execute("SELECT * FROM Equipment")
> startdate, finshdate,revfinish are all smalldatetime
> [Action], responsible, dept, author, type are all text
> title is nvarchar
> any Ideas?
>
|||What error message are you getting?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Tescumeh" <tescumeh@.gmail.com> wrote in message
news:1165868180.115810.136290@.l12g2000cwl.googlegr oups.com...
> Please help I am getting the same error no matter how I change my
> quotes.
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'Plan'.
> conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
> [revfinish],[responsible],[action],[title],[dept],[author],[type])
> values ('" & request.Form("startdate") &"' ,"&
> Request.Form("finishdate") &" ," & Request.Form("revfinish") & ",'" &
> Request.Form("responsible") & "','" & Request.Form("action") & "','" &
> Request.Form("title") & "','" & Request.Form("dept") & "','" &
> session("empid") & "','" & Request.form("type") & "')")
> 'set Rs =conn.Execute("SELECT * FROM Equipment")
> startdate, finshdate,revfinish are all smalldatetime
> [Action], responsible, dept, author, type are all text
> title is nvarchar
> any Ideas?
>
|||Imposter!
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Arnie" <99yoda@.newsgroup.nospam> wrote in message
news:u0e%23N%23XHHHA.3616@.TK2MSFTNGP02.phx.gbl...
> Try the OLEDB group?
>
> "Tescumeh" <tescumeh@.gmail.com> wrote in message
> news:1165868180.115810.136290@.l12g2000cwl.googlegr oups.com...
>
|||Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'Plan'.
imposter?
|||> imposter?
We're both "Arnie".
- Arnie
|||Tescumeh said (on or about) 12/11/2006 15:16:
> Please help I am getting the same error no matter how I change my
> quotes.
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'Plan'.
> conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
> [revfinish],[responsible],[action],[title],[dept],[author],[type])
> values ('" & request.Form("startdate") &"' ,"&
> Request.Form("finishdate") &" ," & Request.Form("revfinish") & ",'" &
> Request.Form("responsible") & "','" & Request.Form("action") & "','" &
> Request.Form("title") & "','" & Request.Form("dept") & "','" &
> session("empid") & "','" & Request.form("type") & "')")
> 'set Rs =conn.Execute("SELECT * FROM Equipment")
> startdate, finshdate,revfinish are all smalldatetime
> [Action], responsible, dept, author, type are all text
> title is nvarchar
> any Ideas?
>
When I have a problem like that, I find it very helpful to
break the code into parts so I can assign the SQL statement
to a variable, place a breakpoint in the code and inspect
the variable. Then I try copying the SQL and pasting it
into Query Analyzer. It almost always shows me the exact
problem.
|||Thank you I will try that.

Help I am getting an error when I run my insert statement

Please help I am getting the same error no matter how I change my
quotes.
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'Plan'.
conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
[revfinish],[responsible],[action],[title],[dept],[a
uthor],[type])
values ('" & request.Form("startdate") &"' ,"&
Request.Form("finishdate") &" ," & Request.Form("revfinish") & ",'" &
Request.Form("responsible") & "','" & Request.Form("action") & "','" &
Request.Form("title") & "','" & Request.Form("dept") & "','" &
session("empid") & "','" & Request.form("type") & "')")
'set Rs =conn.Execute("SELECT * FROM Equipment")
startdate, finshdate,revfinish are all smalldatetime
[Action], responsible, dept, author, type are all text
title is nvarchar
any Ideas?Try the OLEDB group?
"Tescumeh" <tescumeh@.gmail.com> wrote in message
news:1165868180.115810.136290@.l12g2000cwl.googlegroups.com...
> Please help I am getting the same error no matter how I change
> my
> quotes.
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'Plan'.
> conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
> [revfinish],[responsible],[action],[title],[dept],[
;author],[type])
> values ('" & request.Form("startdate") &"' ,"&
> Request.Form("finishdate") &" ," & Request.Form("revfinish") &
> ",'" &
> Request.Form("responsible") & "','" & Request.Form("action") &
> "','" &
> Request.Form("title") & "','" & Request.Form("dept") & "','" &
> session("empid") & "','" & Request.form("type") & "')")
> 'set Rs =conn.Execute("SELECT * FROM Equipment")
> startdate, finshdate,revfinish are all smalldatetime
> [Action], responsible, dept, author, type are all text
> title is nvarchar
> any Ideas?
>|||What error message are you getting?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Tescumeh" <tescumeh@.gmail.com> wrote in message
news:1165868180.115810.136290@.l12g2000cwl.googlegroups.com...
> Please help I am getting the same error no matter how I change my
> quotes.
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'Plan'.
> conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
> [revfinish],[responsible],[action],[title],[dept],[
;author],[type])
> values ('" & request.Form("startdate") &"' ,"&
> Request.Form("finishdate") &" ," & Request.Form("revfinish") & ",'" &
> Request.Form("responsible") & "','" & Request.Form("action") & "','" &
> Request.Form("title") & "','" & Request.Form("dept") & "','" &
> session("empid") & "','" & Request.form("type") & "')")
> 'set Rs =conn.Execute("SELECT * FROM Equipment")
> startdate, finshdate,revfinish are all smalldatetime
> [Action], responsible, dept, author, type are all text
> title is nvarchar
> any Ideas?
>|||Imposter!
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Arnie" <99yoda@.newsgroup.nospam> wrote in message
news:u0e%23N%23XHHHA.3616@.TK2MSFTNGP02.phx.gbl...
> Try the OLEDB group?
>
> "Tescumeh" <tescumeh@.gmail.com> wrote in message
> news:1165868180.115810.136290@.l12g2000cwl.googlegroups.com...
>|||Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'Plan'.
imposter?|||> imposter?
We're both "Arnie".
- Arnie|||Tescumeh said (on or about) 12/11/2006 15:16:
> Please help I am getting the same error no matter how I change my
> quotes.
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'Plan'.
> conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
> [revfinish],[responsible],[action],[title],[dept],[
;author],[type])
> values ('" & request.Form("startdate") &"' ,"&
> Request.Form("finishdate") &" ," & Request.Form("revfinish") & ",'" &
> Request.Form("responsible") & "','" & Request.Form("action") & "','" &
> Request.Form("title") & "','" & Request.Form("dept") & "','" &
> session("empid") & "','" & Request.form("type") & "')")
> 'set Rs =conn.Execute("SELECT * FROM Equipment")
> startdate, finshdate,revfinish are all smalldatetime
> [Action], responsible, dept, author, type are all text
> title is nvarchar
> any Ideas?
>
When I have a problem like that, I find it very helpful to
break the code into parts so I can assign the SQL statement
to a variable, place a breakpoint in the code and inspect
the variable. Then I try copying the SQL and pasting it
into Query Analyzer. It almost always shows me the exact
problem.|||Thank you I will try that.

Help getting started with @@Identity

OK, so, from what people tell me I should be using @.@.Idnetity. What Im trying to do is insert data into a table, than revrieve the id from the new row in that table, than use that id in another sql insert statment later down the road. Currnetly the way im doing it is with a sql insert, than executte the scalar, than execute a reader, which is causeing me much grief.

This is my sql statment here:

Dim

sqlInsertAsNew SqlCommand("INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@.LName, @.FName, @.FullName)", sqlConn)

From what I understand, using @.@.Identity, I can retrive data from my insert statment without using a sperate select statment. Can you guys point me in the right direction, ive looked alot in the forms and such, most I found is either assuming you know what todo, or is security related.

Well,

I just posted a very similar question to yours in the same forum. Wish I would have noticed your post first :->

Thanks

Josh

|||After reading a colleges code I figured out what to do, however I dont think I could explain it. If anyone wants to see my code, feel free to ask.|||

I do not thing you can retrive data from database without a select statement. to use @.@.identtiy, you have to have one column in the table are identity column, and you can not insert any data into this column. usually this column is used as a primary key of the table, so to get the primary key of the row which you just inserted you can do this

INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@.LName, @.FName, @.FullName)
select @.@.iidentity

this statement will return the idtentiy number of the row you just inserted. but you have to make sure on table author there is no trigger to insert data into another table with a identity column, otherwise you will get the identity number of the other table

to avoid this, you probably should alway useselect scope_identity()as much as you can

Hope this help

|||

DavidDu:

I do not thing you can retrive data from database without a select statement. to use @.@.identtiy, you have to have one column in the table are identity column, and you can not insert any data into this column. usually this column is used as a primary key of the table, so to get the primary key of the row which you just inserted you can do this

INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@.LName, @.FName, @.FullName)
select @.@.iidentity

this statement will return the idtentiy number of the row you just inserted. but you have to make sure on table author there is no trigger to insert data into another table with a identity column, otherwise you will get the identity number of the other table

to avoid this, you probably should alway useselect scope_identity()as much as you can

Hope this help

IDENTITY is not a column it is a property of a column.

Help for date in insert query

i want to save date using inert query like insert into tablname(field1,f2) values('jan',"& format(system.date.now,"dd/MM/yyyy hh:mm ") so to give error that char will not be converted to date and time.plz help its urgent.the same problem is with select query toooooo.

Hi,

You should elaborate more about the problem here. Post your code and let us know the way you are inserting data into the database and the fields types

Regards

|||

i want to insert in a data base some thing using this query:

insert into tble1(field1,field2) values('this some thing or data','"& syatem.now &"');

which kind of date it will store like this 24/05/2007 11:16 AM or 24/05/2007 .i want to store 24/05/2007 11:16 AM this one.i using format function of vb.net like format(system.date,"dd/MM/yyyy hh:mm tt") but it give me error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value".in the select statement also this problem occure. i also use thhe select query as

select * from tbl1 where pdate='"& format(variabledate,"dd/MM/yyyy hh:mm tt") &"'" and it also give me error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value".this in acess work fine but in sql server give the above error.

|||

Hi,

please try something like following

Datetime dt = newDateTime(2007,6,18,13,56,30)); //year, month, day, hour, minute, seconds

thanks,

satish.

|||

Hi there,

Use this command:

INSERT INTO TABLENAME (field1,field2) VALUES ('jan',getdate())

hope it helps you out,

gonzzas

|||

This is one of those difficult to answer questions because what you are trying to do is inheritly wrong. Datetime fields do not have a format. They represent a particular point in time. You can convert a datetime to and from a string with a specific format, but then it's no longer a datetime. You are trying to store a string with a format that sql server doesn't understand into a datetime field. SQL Server is attempting to covert your string into a real datetime so it can be stored, but the format isn't one it understands. Regardless of any of the correct formats you feed it, so long as it means the same point in time, ultimately it will get converted to the exact same number (Datetimes are internally represented by the number of days since the epoch, with the time portion being a fraction of a day).

|||

Hi again,

Here is another example of a sql command that inserts a row in a table. One of the row fields has DATETIME datatype:

Dim strAsString ="insert into users (username,password,date) values ('username1','password1',convert(datetime,'18-06-2007 23:21:00',105))"

Dim commandAsNew SqlCommand(str, conn)

Dim result = command.ExecuteScalar()

Where you see 105 it's the format chosen by me for this test. This format stores the data as dd-mm-yyyy hh:mm:ss

You can use another formats (do some light research on the subject, it should be enough) to meet your requirements: dd/mm/yyyy hh:mm:ss

gonzzas

|||

actually i solve the insert by using the query like

"insert into table tblname(f1,datef1) values('abc','"& system.datetime.now.tostring("MM/dd/yyyy hh:mm:ss") &"')"

but i still receiveing errors in select query that char conversion.

this stuff is doing in vb.net and sqlserver.

Help creating a Transact SQL Insert Statement

Hi there can anyone help me to create a SQL Insert Statement. I dont know SQL at all.

To explain, I have two web pages. search.asp and results.asp.

search.asp has the following.

Form Name: searchForm
Set to: Post
Action: results.asp
Text Field: Keyword
Drop Down: CategoryTable
Drop Down: Location
UserName Session Variable: MM_UserName

results.asp has the following SQL which pulls all the results.

SELECT SupplierName, Location, ShortDescription, TimberSpecies, CategoryTitle, Country, CustomerType
FROM Query1
WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTitle LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%' AND CustomerType = 'Trade/Retail'
ORDER BY CategoryTitle, TimberSpecies ASC

The database & form I want to insert into.

tblSearcheResults
idSearch (AutoNumber)
location (Text) "Want to insert the 'Location' result here"
category (Text) "Want to insert the 'CategoryTable' result here"
user (Text) "Want to insert the UserName Session Variable result here"
result (Text) "Want to insert the 'Keyword' result here"

Please ask if u need more info.

Mally

Hi,

INSERT INTO tblSearchResults
(
location,
category,
user,
result
)
VALUES
(
Location, --should be due to the POST action in Request.Form("Location")
category, --should be due to the POST action in Request.Form("category")
user, --should be due to the POST action in Session("MM_UserName")
keyword, --should be due to the POST action in Request.Form("keyword")
)

Variable have to be read first and the query string to be constructed before sending it to SQL Server, keep in mind that you have to quote the string while composing the SQL String.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

help creating a Stored Procedure

I have some script for creating the table i want but i need some help creating a Stored Procedure. That will Insert the UserId from the aspnet_Users Table (UserId) into the UserId Column in my table. So when a user creates an account it will put that users id into my table. The data will be retrieved by a FormView and the user can Update their info threw the FormView control..

I just need to associate the UserId from the aspnet_User Table with my table, so the data is stored per UserId in a new row for each new user..

create table dbo.custom_Profile (UserIduniqueidentifiernot nullPrimary Key,IamWeArenvarchar(50)null,InterestedInnvarchar(256)null,IntroTitlenvarchar(100)null,TellOthersnvarchar(MAX)null,MaritalStatusnvarchar(20)null,BodyTypenvarchar(50)null,Racenvarchar(20)null,Smokingnvarchar(20)null,Drinkingnvarchar(20)null,Drugsnvarchar(20)null,Educationnvarchar(256)null)go

Here is what i have so far for the stored procedure

create procedure getcustomProfile @.UserIduniqueidentifierasSELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdgoifnot exists (select *from dbo.aspnet_Userswhere UserId = @.UserId)BEGINDeclare @.UserIduniqueidentifier update (dbo.content_Profile)set UserId = @.UserIdwhere UserId = @.UserIdEndELSEBEGIN INSERT INTO dbo.custom_Profile (UserId) VALUES (@.UserId)END

|||

Hi,

I'm sure you can achieve what you want from a stored procedure, but there are easier ways. The article at:

http://davidhayden.com/blog/dave/archive/2006/01/27/2775.aspx

This shows how to create a table using SMO.

Hope this helps.

Paul

|||Thats cool something to look into later, but for now i'm just working on studying the creation of Stored Procedures. Won't learn how to create them if a program does most of the work for me, why i prefer to do it this way for now|||

Hi,

No problem with that. Mind you, think i mis-read it anyway - thought you needed to create a table from a stored procedure. You don't actually say what is wrong with what you've done so far. I might be wrong, but I don't think you need the line 'Declare @.UserIduniqueidentifier'as you have already passed this in as a parameter value.

Let me know what appears to be going wrong and I'll try and help further.

Paul

|||

I was just trying out the table and sp in my site, and when i create a new user account a new row isn't created in the column for the table..Trying to get the stored procedure to take the UserId from the aspnet_Users table example (59afcb85-c20c-4937-8ab9-a44a57e22ce0). Than Insert that UserId into my table custom_Profile (Column UserId) and do this for each new user account..

|||forgot to put in the T-SQL ...lol here you go

create table dbo.custom_Profile (UserIduniqueidentifiernot nullPrimary Key,IamWeArenvarchar(50)null,InterestedInnvarchar(256)null,IntroTitlenvarchar(100)null,TellOthersnvarchar(MAX)null,MaritalStatusnvarchar(20)null,SexualOrientationnvarchar(20)null,Heightnchar(10)null,BodyTypenvarchar(50)null,Racenvarchar(20)null,Smokingnvarchar(20)null,Drinkingnvarchar(20)null,Drugsnvarchar(20)null,Educationnvarchar(256)null,Circumcisednvarchar(20)null)gocreate procedure getcustomProfile @.UserIduniqueidentifier asSELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdgoBEGININSERT dbo.custom_Profile (UserId)VALUES (@.UserId)SELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdEND

|||

Oh and when i remove the Declare @.UserId uniqueidentifier i get a error::

Msg 137, Level 15, State 2, Line 5

Must declare the scalar variable "@.UserId".

|||

Hi,

Apologies, I misunderstood. The problem is passing of the uniqueidentifier - this will cause the SP to fail. You should change this to an nvarchar field. I messed around a little and the following SP will loop through all of the users:

CREATE procedure [dbo].[getcustomProfile] @.UserIdnvarchar(150)ASDECLARE UserInsertCURSORKEYSETFOR SELECT UserIDFROM dbo.aspnet_UsersDECLARE @.Usernvarchar(150)OPEN UserInsertFETCH NEXT FROM UserInsertINTO @.UserWHILE (@.@.fetch_status = 0)BEGININSERT dbo.custom_Profile (UserId)VALUES (@.User)FETCH NEXT FROM UserInsertINTO @.UserENDCLOSE UserInsertDEALLOCATE UserInsert

This does work, and you can use it as your starting point for the actual SP you need to call each time from your form.

Hope it helps,

Paul

|||So i need to call the SP from the code behind correct? for it to insert the UserId into the table upon creation of a new user account|||

Your logic in your original stored proc is incorrect. You have

IF NOT EXISTS(...)

UPDATE

ELSE

INSERT

If you think about it, how can you update something that doesnt exist? It should be the other way.

IF NOT EXISTS(...)

INSERT

ELSE

UPDATE

Fix your proc according to the logic above and post back if you have any issues.

|||

Hi,

Yes, you should call the SP from BLL once you've gathered together the results of your form. I'm assuming from the way you set out the SP that you're intending to do the User insert first and then then pass in the new UserID (with other parameters) - you should be setting up an output parameter from your Add_User SP. You actually don't need your If Exists part of the SP because you can check that the UserID has been created before allowing the call to this SP - the fact that it is a uniqueidentifier means it won't exist already. Personally, I always keep Insert, Update and Delete SPs seperate - but that is just personal preference.

Let e know if you need anything else on this.

Cheers,

Paul

|||

The UserId is the only data set getting populated automatically upon Registration of the site, the other columns aren't populated until they have registered. Than they get Re Directed to another page where they fill in the information via FormView in the User Control Panel. Than that information is displayed by another SqlDataSource and FormView on their profile page but without the Delete,Insert,Update etc. Basically using a Content Management system for the user profile to display textual information, on their page.

I'm going to use the UserId so i can pass it into the URL for unique pages like most communities (Myspace etc etc)..

|||Why is that the aspnet_Membership,Users,UsersInRoles etc don't use CodeBehind to pass the UserId into each table? cause thats what i want to do..Pass the UserId from the aspnet_Users Table into my table|||

Hi,

You can do all of that by customising the sqlMembershipProvider. It's not as difficult as you might think - thankfully! There's a pretty good tutorial to get you started at:

http://aspnet.4guysfromrolla.com/articles/120705-1.aspx

I hope this helps.

Paul

sql

Help : ASP var at SQL line

Hi,

I try to insert the date parameter on SQL line.
The date parameter is on ASP var declares.

I'm working with the page that have listbox, and gridview bound to msaccess.
when the user select thelistbox with other value, the page will reload.
while the page reload on server, it carry the variable that should insert on sql line,
for displaying the difference gridview records result.
So I have dynamic gridview records base on my listbox.

But, ... the page doesn't load well from the beginning. Doesn't work at all !!

I declare this at the page load.

Dim StartProjectAsString ="1/1/1996"

Sub ProjectListBox_SelectedIndexChanged(ByVal senderAsObject,ByVal eAs EventArgs)Handles PYear.SelectedIndexChanged
StartProject = PYear.SelectedValue.ToString
EndSub

The object "Pyear" is listbox :

<asp:ListBoxID="PYear"runat="server"Rows="12"CssClass="tb01"SelectionMode="Single"AutoPostBack="True">
<asp:ListItemValue="1/1/1991">1991-1995</asp:ListItem>
<asp:ListItemValue="1/1/1996"Selected="True">1996-2000</asp:ListItem>
<asp:ListItemValue="1/1/2001">2001-2005</asp:ListItem>
<asp:ListItemValue="1/1/2006">2006-2010</asp:ListItem>
<asp:ListItemValue="1/1/2011">2011-2015</asp:ListItem>
</asp:ListBox>

And the datasource for bound to gridview is

<asp:AccessDataSourceID="AccessDataSource1"runat="server"DataFile="~/App_Data/general.mdb"
SelectCommand='SELECT TBL_PROJECT.NAMEPROJECT AS Name, TBL_PROJECT.STARTPROJECT AS Start, TBL_PROJECT.ENDPROJECT AS Finish, TBL_PROJECT.JOBNO AS JobNo, TBL_PROJECT.CLIENT AS Client, TBL_PROJECT.BUILDING AS Building, TBL_PROJECT.CODEPROJECT AS Code, TBL_PROJECT.IDPROJECT AS IDPROJECT FROM TBL_PROJECT WHERE TBL_PROJECT.STARTPROJECT >= DateValue("<%=StartProject%>") And TBL_PROJECT.STARTPROJECT < DateValue("1/1/2001");'>
</asp:AccessDataSource>

It will return the error page regarding the ASP : StartProject.
If I change the variable with the string "1/1/1996" :

'SELECT TBL_PROJECT.NAMEPROJECT AS Name, TBL_PROJECT.STARTPROJECT AS Start, TBL_PROJECT.ENDPROJECT AS Finish, TBL_PROJECT.JOBNO AS JobNo, TBL_PROJECT.CLIENT AS Client, TBL_PROJECT.BUILDING AS Building, TBL_PROJECT.CODEPROJECT AS Code, TBL_PROJECT.IDPROJECT AS IDPROJECT FROM TBL_PROJECT WHERE TBL_PROJECT.STARTPROJECT >= DateValue("1/1/1996") And TBL_PROJECT.STARTPROJECT < DateValue("1/1/2001");

it will work fine, but I will miss my dynamic gridview, right ?

So, please ... help me ...

Thank you ...

The answer :

http://forums.asp.net/t/1110547.aspx

HELP ....difficulty in insert of 2 tables data with id

Hello frdz,

I have two tables in sqlserver 2005.

I have created the stored procedure for insert,update data.I m creating my application in asp.net with C#

Table-1 CUSTOMER
Fields:
customerid int identity,
cardid int,
customername varchar(20) not null,
address varchar(20) not null,
city varchar(20) not null,
emailid varchar(20)
Table-2 CARD
Fields:
cardid int identity,
cardtype varchar(20) not null,
carddetails varchar(20) not null
 INSERT INTO CUSTOMER (customername ,address,city,emailid)
 VALUES (@.customername,@.address,@.city,@.emailid)
 SELECT @.customerid = SCOPE_IDENTITY()
/* HELP HERE NOT ABLE TO GET DATA OF CARD */
 SELECT @.cardid = cardid from CARD where customerid =@.cardid

Pls tell me how to insert the data ...

There is only one cardid for only one customerid both should be unique no duplication...

One customer can have only one and one card... 

If I understand your design correctly, you'll have to insert the CARD record first and get the new identity fieldcardid into a variable. Then you insert into CUSTOMER, using the variable to populate CUSTOMERS.cardid.

This is because cardid is a foreign key in the CUSTOMER table - so it has to be generated first. Just switch your order of inserts and you should be fine. And I advise that you put both insert statements within a transaction (BEGIN TRAN and COMMIT) - if one insert fails, then the other one shouldn't be left in the table.

If you need more on stored procedures, here's a link to a free chapter ondeveloping stored procedures for sql server.

|||

Your database to me seems odd, I would think that each customer could have many cards, while each card could only have 1 customer, but you seem to have it reversed. In any case, this is how you would insert:

DECLARE @.cardid int

INSERT INTO Card(cardtype,carddetails) VALUES (@.cardtype,@.carddetails)

SET @.cardid=SCOPE_IDENTITY()

INSERT INTO Customer(cardid,customername,address,city,emailid) VALUES (@.cardid,@.customername,@.address,@.city,@.emailid)

and optionally:

SELECT @.cardid,SCOPE_IDENTITY() to return both the generated cardid and customerid

|||

Thanxs everyone for the replies...

Motley...thanxs ... ya i want that 1 customer can have only 1 card.
and optionally:
SELECT @.cardid,SCOPE_IDENTITY() to return both the generated cardid and customerid

I have created stored procedur for card and customer.

I don't understand what is the above line and how it worksSELECT @.cardid,SCOPE_IDENTITY() ??

This should be written in which stored procedure ? I mean card or customer ?

Should a single stored procedure be created for this ?

Thanxs......waiting for reply

|||

That would return a resultset with 1 row and 2 columns. The first column would be the @.cardid variable that we set earlier in the code, and the second column would be SCOPE_IDENTITY(), which is a function that returns the value of the identity column for the row that was just inserted.

When I said optionally, I meant you could put at the end of the other code like this:

DECLARE @.cardid int

INSERT INTO Card(cardtype,carddetails) VALUES (@.cardtype,@.carddetails)

SET @.cardid=SCOPE_IDENTITY()

INSERT INTO Customer(cardid,customername,address,city,emailid) VALUES (@.cardid,@.customername,@.address,@.city,@.emailid)

SELECT @.cardid,SCOPE_IDENTITY()

|||

thanxs very much....

HELP - insert record SQLExpress database

Hi! i ask you some help..I should build a simple INSERT FORM in a SQLExpress database..

but clicking ADD I have this error :

Incorrect syntax near '='.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near '='.

Source Error:

Line 38: conDatabase.Open()Line 39:Line 40: cmdInsert.ExecuteNonQuery()Line 41: Line 42: conDatabase.Close()

some solution?

the source code:

<%

@.PageLanguage="VB"Debug="true"%>

<%

@.ImportNamespace="System.Data"%>

<%

@.ImportNamespace="System.Data.SqlClient"%>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">Sub Button_clic(ByVal sAsObject,ByVal eAs EventArgs)Dim conDatabaseAs SqlConnectionDim strInsertAsStringDim cmdInsertAs SqlCommandDim myExecuteQueryAsStringDim myExecuteCmdAs SqlCommand

conDatabase =

New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\tesi\App_Data\database.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")

strInsert =

"Insert pubblicazioni (Nome, Cognome, Titolo) Values = (@.Nome, @.Cognome, @.Titolo) "

cmdInsert =

New SqlCommand(strInsert, conDatabase)

cmdInsert.Parameters.Add(

"@.Nome", txtNome.Text)

cmdInsert.Parameters.Add(

"@.Cognome", txtCognome.Text)

cmdInsert.Parameters.Add(

"@.Titolo", txtTitolo.Text)

conDatabase.Open()

cmdInsert.ExecuteNonQuery()

conDatabase.Close()

Response.Redirect(

"success.html")EndSub

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headid="Head1"runat="server"><title>iNSERIMENTO</title><LINKhref="mauro.css"rel=stylesheet>

<

scripttype="text/javascript"language="javascript">

<!--

function

popopen(){

window.open(

"upload/upload.aspx","name"," toolbar=no,directories=no,menubar=no,width=300,height=300,top=100,left=150,resizable=no,scrollbars=yes");

}

// -->

</

script>

</

head>

<

bodybgcolor="#DFE5F2"style="font-size: 12pt"><formid="form1"runat="server"><div> <asp:LabelID="Label1"runat="server"BackColor="#8080FF"BorderColor="Black"ForeColor="Black"Height="29px"Text="FORM INSERIMENTO DOCUMENTO"Width="371px"Font-Bold="True"Font-Names="Verdana"Font-Size="14pt"Font-Underline="True"></asp:Label><br/><br/><br/><B><spanstyle="font-family: Verdana">NOME </span></B><asp:TextBoxID="txtNome"runat="server"></asp:TextBox> <B><spanstyle="font-family: Verdana">COGNOME </span></B><asp:TextBoxID="txtCognome"runat="server"></asp:TextBox><br/><br/><B><spanstyle="font-family: Verdana">TITOLO </span></B><asp:TextBoxID="txtTitolo"runat="server"></asp:TextBox><br/><br/>

<ahref="javascript:popopen()">CARICA DOCUMENTO</a><br/><br/>

<asp:ButtonID="Button1"runat="server"OnClick="Button_Clic"Text="ADD"Font-Bold="True"Font-Names="Verdana"Font-Size="12pt"Width="160px"/></div><br><br></form>

</

body>

</

html>Remove the "=" sign in the INSERT statement.|||

ndinakar:

Remove the "=" sign in the INSERT statement.

damn!ehehehe ; ) thanks you....