Solved

inserting data from asp form

Posted on 2011-02-11
52
1,191 Views
Last Modified: 2012-05-11
Hi
I have Items table. Which contain item names. which i want to do here is inserting data for those items from asp form and need to save in database table.
But this should be in a table format.

Itemname,istvalue,2ndvalue..
jasd.png
0
Comment
Question by:lucky20
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 27
  • 18
  • 4
52 Comments
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34876953
Please have a look at the following tutorials and codes that I have created
For teaching and demostrating exactly what it is that you are wanting to do.

Demo
http://ee.cffcs.com/Q_24801116/Q_24801116.asp
code
http://ee.cffcs.com/Q_24801116/Q_24801116.zip

This will show you everything you need to know plus more.
Very detailed.

Carrzkiss
0
 
LVL 1

Author Comment

by:lucky20
ID: 34890035
thank you carrzkiss..
0
 
LVL 1

Author Comment

by:lucky20
ID: 34890563
I can enter only one value through normal insert statement(sql server2005).


But I have maximum 200 records.
How can I enter all the records into data base.
Thats whay i have displayed in tabular format.
This will continue upt o 200 items.


then the insert statement will be big on ASP FORM..

sql="INSERT INTO items(ID,Item,Unit)"
				sql=sql & " VALUES "
				sql=sql & "('" & Request.Form("ID") & "',"
				sql=sql & "'" & Request.Form("Item") & "',"
				sql=sql & "'" & Request.Form("Unit") & "')"

Open in new window

0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34892158
Please view the code that I provided in the example.
It will show you everything you need.
Once you have tested the code, then come in and ask and I will assist you.
0
 
LVL 1

Author Comment

by:lucky20
ID: 34896264
' To insert an item we do this
if request.Form("MyRecord")="Insert" then
Set InsCom=Server.CreateObject("ADODB.Command")
InsCom.ActiveConnection=objConn
InsF = ProtectSQL(Trim(request.Form("MyFirst")))
InsS = ProtectSQL(Trim(request.Form("MySecond")))
InsInfo = ProtectSQL(trim(request.Form("MyInfo")))
InsCom.CommandText = "Insert into Teaching(MyFirst, MySecond, MyInfo)Values(?,?,?) "
InsCom.Parameters.Append InsCom.CreateParameter("@MyFirst", adVarChar, adParamInput, 255, InsF)
InsCom.Parameters.Append InsCom.CreateParameter("@MySecond", adVarChar, adParamInput, 255, InsS)
InsCom.Parameters.Append InsCom.CreateParameter("@MyInfo", adVarChar, adParamInput, 4000, InsInfo)
InsCom.Execute
response.Write"Record Inserted Successfully!<br /><a href=""Q_24801116.asp"" />Go Back to main page</a>"
end if

Open in new window


@ carrz

I understand your query.That is better to avoid sql injection.
This one only inserting a single record one time.If you want to enter another record you need to enter again.

But here I need to enter several Records at a time.
I have 200 records. I am entering all at a time not one after one.
I have that many textboxes toinsert data..
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34896395
I am still not following.
Are you saying that you want to create 200 new Records at one time?
Then you will need to do a loop and extend to 200.
Not really sure how to accomplish that one.
I will do some checking once i have the extra time.
Until then, maybe someone else will come in to assist.
0
 
LVL 1

Author Comment

by:lucky20
ID: 34896583
yes ..i need enter all at one time.
0
 
LVL 4

Expert Comment

by:Slim81
ID: 34897357
@lucky20,
Where are your 200 values stored now?

I understand you are wanting to build a script that will take your 200 entries and store them in your DB, which is pretty straight forward and should be easily accomplished. However, how do you plan to get your 200 entries into the script so they can actually be entered into the DB?

-Slim
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34897533
Good Question -Slim
Did not think about asking that one.
0
 
LVL 1

Author Comment

by:lucky20
ID: 34897650
<tr><td>Beef</td>
                   	<td><input type="text" value="0.00" /></td>
                    <td><input type="text" value="0.00" /></td>
                    <td><input type="text" value="0.00" /></td></tr>
                    <tr>
                      <td>chicken</td>
                      <td><input type="text" value="0.00" /></td>
                        <td><input type="text" value="0.00" /></td>
                        <td><input type="text" value="0.00" /></td></tr>
                    <tr><td>pepperoni</td>
                        <td><input type="text" value="0.00" /></td>
                        <td><input type="text" value="0.00" /></td>
                        <td><input type="text" value="0.00" /></td></tr>
                        <tr><td>pepsi20 oz</td>
                        <td><input type="text" value="0.00" /></td>
                        <td><input type="text" value="0.00" /></td>
                        <td><input type="text" value="0.00" /></td></tr>
                     <tr><td>tomatoes</td>
                     <td><input type="text" value="0.00" /></td>
                     <td><input type="text" value="0.00" /></td>
                     <td><input type="text" value="0.00" /></td></tr>  

Open in new window

......................up to 200 items


I am entring all these values from key board.This is like ordering form.      
I don't know exactly the way i am doing correct or not. I decided to do like this.
                              
0
 
LVL 4

Expert Comment

by:Slim81
ID: 34897676
@carrzkiss,
Thanks man.

Hey, I know I have said it before, but I will say it again.  You alone have helped me a ton throughout the years here on EE.  I am honest when I say that due to your involvement, my coding with Classic ASP has gained leaps and bounds as well as my ability to better understand and try to fight SQL injection. Because of you, I can actually put a dollar value to my skills.....  

I will be able to take what you have taught me with ASP and transfer those skills to PHP and beyond. Thanks!

@lucky20,
Get back to us and we can help you!
0
 
LVL 1

Author Comment

by:lucky20
ID: 34897835
@slim81/@carrz
Ok give me some help to do this..
0
 
LVL 4

Expert Comment

by:Slim81
ID: 34899098
Can you give some more detail as to what you are trying to do?

Are you making an "order form" or are you simply trying to put information that you have already gathered into your database?

Also, you mention you have 200 values already, are they just written down on a piece of paper or is it stored in an Excel document (or something else)?  It seems like a lot of work to type the data into the form fields if you already have them stored elsewhere....

-Slim
0
 
LVL 1

Author Comment

by:lucky20
ID: 34899555
@Slim

Actually the order form values are different. I don't have handy. It depends on the person whose is filling the order form to order. So the values are not constant. It varies order to order. So the person will enter manually in asp form(computer). This form is send it to other department and also it should be saved in database.

item name and quantity is very important here.
So he should fill all the items at a time not one after one.

database table  Items(itemid(fk ,hidden in asp form),itemname,quantity).

0
 
LVL 1

Author Comment

by:lucky20
ID: 34906849
@carrz,@slim

Did anyone get the clue how to insert all at a time?
0
 
LVL 4

Expert Comment

by:Slim81
ID: 34906947
Doing an insert of multiple items is easy, however I cannot grasp a few things you are doing....

As carrzkiss had mentioned earlier, performing a loop will be the way to do it.  Both he and I will be able to help with the looping.

Can you give an example of your code, even though it isn't working?
0
 
LVL 1

Author Comment

by:lucky20
ID: 34907506
@ Slim
Sure..
I am also thinking the same..put it in loop.But have no idea on it.

here Itemid is hidden type.The user can't see the itemid.(the user who are entering data don't know about itemid.they should see only item name.)

 <form method="post" action="order.asp">
                   <table border="1" >
                   <tr><th>Item </th>
                   <th>1quantityr</th>
                   <th>2quantity</th>
	     <th>3Quantity</th></tr>
                  
                   <tr><td>chicken</td>
	     <td><input type="hidden" name="itemid1" value="1"></td>
	     <td><input type="text" value="0.00" name="1" /></td>
                    <td><input type="text" value="0.00" name="2"/></td>
                    <td><input type="text" value="0.00" name="3"/></td></tr>
                    <tr><td>bacon</td>
	     <td><input type="hidden" name="itemid2" value="2"></td>
	     <td><input type="text" value="0.00" name="4" /></td>
                    <td><input type="text" value="0.00" name="5"/></td>
                    <td><input type="text" value="0.00" name="6"/></td></tr>
         </table>
<input type="submit" value="order" name="submit"/>
</form>


/*this one can only insert single row*/
<%
	If Request("submit") <> "" Then
	dim sql1
	OpenSQLConn   
	sql1="INSERT INTO order_items(Itemid,Item_name,quantity_1,quantity_2,quantity3)"
	sql1=sql1 & " VALUES "
	sql1=sql1 & "('" & Request.Form("Itemid1") & "',"
	sql1=sql1 & "'" & Request.Form("Item_name") & "',"
	sql1=sql1 & "'" & Request.Form("1") & "',"
	sql1=sql1 & "'" & Request.Form("2") & "',"
	sql1=sql1 & "'" & Request.Form("3") & "')"
	Conn.execute(sql1)
	if err<>0 then
	  Response.Write("No update permissions!")
	else
	  Response.Write("<h3>" & recaffected & " record added</h3>")
	end if
	Conn.close
	Response.Redirect("mainpage.asp")
	End if
%>

Open in new window


and this code is wrong. Here there is no item_name textbox to enter.(it is a label.But need to pass item_name into it)

here quantity1 describes---provider will send that much quantity as first time. quantity2 as second time but there is a days difference.
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34907746
Let me ask you this.
Your Form, you have
Item, Quanity1, Quanity2, Quanity3

Now, what I can gather from what you are wanting to do, you are going to have a SINGLE form with 200 Of these Entries.
So, you will have:

Chicken
Bacon
Beff
Sausage
Pizza
Water
Food
Bla
Blas
Blass
Blaas
Blaass

And for each of these items, you are wanting to create a new entry (ROW) into the database, and all this from 1 form.

Please have a look at this article, I do not have the time at the moment to create a test script for you, maybe -Slim will be able to shed some more light on this as well.

http://www.aspdeveloper.net/tiki-index.php?page=ASPFAQInsertMultipleRowsFromForm

The link will describe what you need to do.

Good Luck
Carrzkiss
0
 
LVL 1

Author Comment

by:lucky20
ID: 34908144
@carrz,

That link explaining exactly what I am doing.
But I need Some more information do this..
Thanks for the link..
0
 
LVL 1

Author Comment

by:lucky20
ID: 34918873
can anyone have the answer..please do share with me .. I want to finish this asap.but not getting any clue.
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34919339
I do not have the time to write your whole code, and do not do that anymore on EE.
But, Hopefully this will get you were you need to be.

Your Form Elements will be like this:
<tr><td>chicken</td>
<td><input type="hidden" name="itemid_1" value="1"></td>
<td><input type="text" value="0.00" name="one_1" /></td>
<td><input type="text" value="0.00" name="two_1"/></td>
<td><input type="text" value="0.00" name="three_1"/></td></tr>

Open in new window


As you will notice, I changed your hidden form element name from
item1
to
item_1
and the same with the names of your other fields.
Each one is assigned a unique name and number, this will make the sql end of it work.

Now, on all of your sections, you will do this to them, so the next on and so on will be this

bacon - itemid_2
cheese - itemid_3
pizza - itemid_4
and so on to your 200 mark.

Now, for your SQL Insert statement, you will do something like this.
(This is all untested, I just wrote it down)
<%
' you will need to add in your form elements
Set sql=Server.CreateObject("ADODB.Command")
sql.ActiveConnection=objConn
for i=1 to 200 ' this is the amount of form sections that you have to insert.
getone = request.form("one_"& i)
gettwo = request.form("two_"& i)
getthree = request.form("three_"& i)
getitemid = request.form("itemid_"& i)
if getitemid <> "" and getone <> "" and gettwo <> "" and getthree <> ""  then
sql.commandtext="insert into myitems(id, item, unit, unit2)values(?,?,?,?)"
sql.Parameters.Append sql.CreateParameter("@id", 3, 1, , getitemid)
sql.Parameters.Append sql.CreateParameter("@item", 3, 1, , getone)
sql.Parameters.Append sql.CreateParameter("@unit", 3, 1, , gettwo)
sql.Parameters.Append sql.CreateParameter("@unit2", 3, 1, , getthree)
InsCom.Execute
end if
next
%>

Open in new window

The Query above, is assuming that your database columns are all number (INTEGERS)
If they are any text, then change the 3 to 200
And add in the count of the text field.
Example:
'This
sql.Parameters.Append sql.CreateParameter("@item", 3, 1, , getone)
'would be
sql.Parameters.Append sql.CreateParameter("@item", 200, 1, 45, getone)
'If it was a text (VarChar) Column dataType and its length was set to 45 in the database.

Open in new window

------
Please review this and test it, I am pretty sure that it will work.
I have not tested it as I have to run and setup a Greenscreen room for some video shots.
Just read it and study it as well as with the other codes, and you can also check out an artice that I wrote as well on this subject.
http://www.experts-exchange.com/A_3626.html

Take Care
Carrzkiss
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34919363
Had to change something in the SQL Statement I wrote, use this one instead.

<%
' you will need to add in your form elements
Set sql=Server.CreateObject("ADODB.Command")
sql.ActiveConnection=objConn
for i=1 to 200 ' this is the amount of form sections that you have to insert.
getone = request.form("one_"& i)
gettwo = request.form("two_"& i)
getthree = request.form("three_"& i)
getitemid = request.form("itemid_"& i)
if getitemid <> "" and getone <> "" and gettwo <> "" and getthree <> ""  then
sql.commandtext="insert into myitems(id, item, unit, unit2)values(?,?,?,?)"
sql.Parameters.Append sql.CreateParameter("@id", 3, 1, , getitemid)
sql.Parameters.Append sql.CreateParameter("@item", 3, 1, , getone)
sql.Parameters.Append sql.CreateParameter("@unit", 3, 1, , gettwo)
sql.Parameters.Append sql.CreateParameter("@unit2", 3, 1, , getthree)
sql.Execute
end if
next
%> 

Open in new window

0
 
LVL 1

Author Comment

by:lucky20
ID: 34919827
I got an error here at line nuber 35
Error Type:
ADODB.Command (0x800A0D5D)
Application uses a value of the wrong type for the current operation.


This is because of itemname. How can I get that item name from form.

How can i add item name into it.ItemId is a foreignkey

<tr><td><table border="1">
                   
                   <tr><th>ItemName</th>
                   <th>Firstorder</th>
                   <th>SecondOrder</th>
                   <th>ThirdOrder</th></tr>
                   
                   	<tr><td>chicken</td>  
                    
                    <td><input type="text" value="0.00" name="one_1" /></td>  
                    <td><input type="text" value="0.00" name="two_1"/></td>  
                    <td><input type="text" value="0.00" name="three_1"/></td>
                    <td><input type="hidden" name="itemid_1" value="101"></td> </tr>
                    <tr><td>diet coke</td>  
                    
                    <td><input type="text" value="0.00" name="one_2" /></td>  
                    <td><input type="text" value="0.00" name="two_2"/></td>  
                    <td><input type="text" value="0.00" name="three_2"/></td>
                    <td><input type="hidden" name="itemid_1" value="102"></td> </tr>
   					</table></td></tr>
                    <tr><td><input type="submit" value="Submit" /></td></tr>
  <%  
  OpenSQLConn
' you will need to add in your form elements  
Set sql=Server.CreateObject("ADODB.Command")  
sql.ActiveConnection=Conn  
for i=1 to 200 ' this is the amount of form sections that you have to insert.  
getone = request.form("one_"& i)  
gettwo = request.form("two_"& i)  
getthree = request.form("three_"& i)  
getitemid = request.form("itemid_"& i)  
if getitemid <> "" and getone <> "" and gettwo <> "" and getthree <> ""  then  
sql.commandtext="insert into order_items(itemid, item, first_order,second_order,third_order)values(?,?,?,?,?)"  
sql.Parameters.Append sql.CreateParameter("@itemid", 3, 1, , getitemid)
sql.Parameters.Append sql.CreateParameter("@item", 200, 1, 30, getitemid)    
sql.Parameters.Append sql.CreateParameter("@first_order", 3, 1, , getone)  
sql.Parameters.Append sql.CreateParameter("@second_order", 3, 1, , gettwo)  
sql.Parameters.Append sql.CreateParameter("@third_order", 3, 1, , getthree)  
sql.Execute  
end if  
next  
%>

Open in new window

0
 
LVL 1

Author Comment

by:lucky20
ID: 34919882
line no 35 is wrong. we are not passing item name in to it.(It was my mistake,showing getitemid again).
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34920694
OK.
Does it work or not work?
Are you getting an error now or have you corrected it?
0
 
LVL 1

Author Comment

by:lucky20
ID: 34920933
No .
I removed Item name(lineno35) from list. executed again, got error.


ADODB.Command (0x800A0D5D)
Application uses a value of the wrong type for the current operation.
line no 34

sql.commandtext="insert into order_items(itemid, first_order,second_order,third_order)values(?,?,?,?)"    
sql.Parameters.Append sql.CreateParameter("@itemid", 3, 1, , getitemid) 

Open in new window

0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34921245
What is [itemid]
Does it exist in the database?
Is it of Type [Integer] (Number)
0
 
LVL 1

Author Comment

by:lucky20
ID: 34923118
Itemid is exist in database.(it is a integer)

this is my database table.
Itemid int  fk
Item_name varchar(20)
Quantity_1 decimal(10,2)
quantity_2 decimal(10,2)
Quantity_3 decimal(10,2)


I am not showing Itemid in the form.(it is a hidden field. because a user don't know about this. He just make sure the item name is there or not).

On the above example
I am giving item name as label. What i need to do if I want to pass the Item_name .
samp.png
0
 
LVL 1

Author Comment

by:lucky20
ID: 34943132
Any one got the solution on this? Can any one give some idea how I to insert values?
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34945198
Give this a shot.
Also, give people a chance to reply to you instead of always asking if someone has a solution for you.
Instead come in and ask if the person has had a chance to review your latest post.

Anyway.
View the code and see if it will do what you need.

Carrzkiss
<tr><td><table border="1">
                   
                   <tr><th>ItemName</th>
                   <th>Firstorder</th>
                   <th>SecondOrder</th>
                   <th>ThirdOrder</th></tr>
                   
                   	<tr><td>chicken</td>  
                    
                    <td><input type="text" value="0.00" name="one_1" /></td>  
                    <td><input type="text" value="0.00" name="two_1"/></td>  
                    <td><input type="text" value="0.00" name="three_1"/></td>
                    <td><input type="hidden" name="itemid_1" value="101"></td> </tr>
                    <tr><td>diet coke</td>  
                    
                    <td><input type="text" value="0.00" name="one_2" /></td>  
                    <td><input type="text" value="0.00" name="two_2"/></td>  
                    <td><input type="text" value="0.00" name="three_2"/></td>
                    <td><input type="hidden" name="itemid_1" value="102"></td> </tr>
   					</table></td></tr>
                    <tr><td><input type="submit" value="Submit" /></td></tr>
  <% 
  
'Itemid int  fk
'Item_name varchar(20)
'Quantity_1 decimal(10,2)
'quantity_2 decimal(10,2)
'Quantity_3 decimal(10,2)

  
   
  OpenSQLConn
' you will need to add in your form elements  
Set sql=Server.CreateObject("ADODB.Command")  
sql.ActiveConnection=Conn  
for i=1 to 200 ' this is the amount of form sections that you have to insert.  
getone = request.form("one_"& i)  
gettwo = request.form("two_"& i)  
getthree = request.form("three_"& i)  
getitemid = request.form("itemid_"& i)  
if getitemid <> "" and getone <> "" and gettwo <> "" and getthree <> ""  then  
' Not really sure if you are needing to use the itemid here or not, if it is an automatically generated number then you will not need it here, if it is not, and you grab a number from the page to store here, then it is needed here.
sql.commandtext="insert into order_items(itemid, Quantity_1,Quantity_2,Quantity_3)values(?,?,?,?)"  
sql.Parameters.Append sql.CreateParameter("@itemid", 3, 1, , getitemid)
'sql.Parameters.Append sql.CreateParameter("@item_name", 200, 1, 30, getitemid)    
sql.Parameters.Append sql.CreateParameter("@Quantity_1", 14, 1, , getone)  
sql.Parameters.Append sql.CreateParameter("@Quantity_2", 14, 1, , gettwo)  
sql.Parameters.Append sql.CreateParameter("@Quantity_3", 14, 1, , getthree)
sql.Execute  
end if  
next  
%>

Open in new window

0
 
LVL 1

Author Comment

by:lucky20
ID: 34945685
This question is inactive for 2 days. That's why I requested it.

I will try this and let you know..
0
 
LVL 1

Author Comment

by:lucky20
ID: 34945803
No error Message. But it is not inserted any new data to sql table...
0
 
LVL 1

Author Comment

by:lucky20
ID: 34946017
I have used same code you have given.. I try to execute this. I didn't get any error message. If I check database table. there is no new row.

0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34946587
I do not have the extra time right now for this.
I am uploading a new site to go like for BETA testers to start.

I will try to see what I can do later on this evening or tomorrow.
Good Luck
Carrzkiss
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34969842
I am having a heck of a time trying to get the decimal(10,2) to work in this project.
Do you really need it to be a Decimal?
What is the reason behind this?
Is it needed for another aspect of the program?

I can possibly make this work with a VarChar
And insert the 5.00 and so forth.
But right now, at this present time, the Decimal is being a complete pain in my a** in trying to get it to insert.

Also.
Do you have a ID field for this project?
As in. Do you have a field that is setup as the primary ID field
That generates a number?
1
2
3
4
5
exc...
This is also causing an issue in Deleting records since there is not an Identity.

Let me know something and I will continue to assist you with this issue.
And try to help you to understand how this all works, so that you can rely on yourself instead of on people here on EE to do your work for you.
I have been down this road myself, and still at times need help.
So EE is always here to help us all, BUT we have to try to make a stand on our on first, this shows the people that are helping us that we have atleast tried.

Also.
You have to be patient, as a majority of the people on EE have other jobs or projects that they do, and sometimes it can take hours if not days to get back to an issue.
And to have someone to do what you have been doing is really intimidating, as it makes us feel like you have not tried what we have suggested to you and there for, it makes us now want to continue.

So, with that said.
get back with me on the Decimal issue
And the Identity column.

Carrzkiss
0
 
LVL 1

Author Comment

by:lucky20
ID: 34969931
@Carrz

1).Yes I am inserting one Identity column too..

2).Then Decimal
Here Quantity is always in float values(12.32) like that..That's why I have taken datatype as decimal(10,2).
If you declared it as varchar,is it possible to convert the string type to float in this?

Yes I have tried the different way which is using for loop but not succeeded. SO finally I lost here. That's why am asking in EE.

Thanks a lot for helping me in this issue.
0
 
LVL 31

Accepted Solution

by:
Wayne Barron earned 500 total points
ID: 34970440
This uses a VarChar in the Parameter, BUT the Database stays as Decimal
So nothing needs to be changed in your Database.

You have to make sure that you DO HAVE a Identity Field.
If not, then you will have a heck of a time cleaning up your database with all those records once they are not needed.
So.
ID Field needs to be.

Code
http://ee.cffcs.com/Q_26814864/Q_26814864.zip
This will also give you a message letting you know what records have been inserted.
Also, you may need to do some error checking along the way as well, but that is for another thread.
This SHOULD work straight out of the box.
Change the following:
At the top of the insert.asp page, you will have the database connection (SQL Server I assum) Change this to fit your database.
This is tested and runs on SQL Server 2005 Ent.

Also, if anything needs to be change in the INSERT statement, like your COLUMN names, make sure that they are correct.

Good Luck
Carrzkiss
0
 
LVL 1

Author Comment

by:lucky20
ID: 34970589
@carrz..Thank you ..I will try this one now..
0
 
LVL 1

Author Comment

by:lucky20
ID: 34971088
Ok I have changed the data types from decimal to varchar.(just for checking pupose only ).

Now everything seems to be good. But I got the error at this line.

"Application uses a value of the wrong type for the current operation."


sql.Parameters.Append sql.CreateParameter("@itemid", adInteger, adParamInput, , getitemid)
0
 
LVL 1

Author Comment

by:lucky20
ID: 34971803
I have checked Connection string and other values.. everything is fine. But getting error at Itemid.

If I disable this line Removing itemid from sql query..
Now getting error at

sql.Execute
Invalid use of default parameter


Can you check again.
0
 
LVL 1

Author Comment

by:lucky20
ID: 34973029
It is adding data to sql..

This is the code i used..
<%
for i=1 to 2 'here i have taken 2 rows..,i will increase it to 200

Set sql=Server.CreateObject("ADODB.Command")  
sql.ActiveConnection=Conn 
getone = request.form("one_"& i)
gettwo = request.form("two_"& i)  
getthree = request.form("three_"& i)  
getitemid = request.form("itemid_"& i)

if getitemid <> "" and getone <> "" and gettwo <> "" and getthree <> ""  then  


sql.commandtext="insert into order_items(itemid,Quantity_1,Quantity_2,Quantity_3)values(?,?,?,?)"
sql.Parameters.Append sql.CreateParameter("@itemid", adInteger, adParamInput, , getitemid)
sql.Parameters.Append sql.CreateParameter("@Quantity_1", adVarChar, adParamInput, 15, getone) 
sql.Parameters.Append sql.CreateParameter("@Quantity_2", adVarChar, adParamInput, 15, gettwo) 
sql.Parameters.Append sql.CreateParameter("@Quantity_3", adVarChar, adParamInput, 15, getthree)
sql.Execute
response.Write "Item ID - "&getitemid&" - "&getone&" - "&gettwo&" - "&getthree&"<br />"
end if
next


Thank you Carrz
%>

Open in new window

0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34973084
The code in the zip file as provided, should have worked from the start.
As long as you are passing values.
I do not see how or why you would have gotten the errors that you received, as I had it working.
What are all of the fields in your database?
And what is the Identity field?
See, the Identity field DOES not need to be used in this statement.
If by change the itemid field is the Identity field, then you need to remove it from this statement, as the Identity field is auto-generated and cannot be written to, or read from.

Advice me.
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34973091
And why are you accepting your last post as Answer, when it is my code that you used?
You are really confusing you know that!
0
 
LVL 1

Author Comment

by:lucky20
ID: 34973145
@carrz

Thanks ..
I am confused to select the answer..
I am selecting your answer as accept solution.. by mistake i selected mine..
I don't know how to edit this...
I am looking for the editing options..

As per now It doesn't have Identity field.. I am going to add now.I just checked with those values. It works fine.
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34973405
You stated on the 1.) here: http#a34969931
That you have an Identity column.
And now you are saying that you do not have one.
That is what I was trying to get at.
You need to have the following.

ID - Identity (INT) columns.
Then the rest of your columns.
Leave the Decimal Columns alone in SQL Server, they are fine.
Leave the Parameters alone within the Code, they do what they need to do to insert the code into your database.

I have requested the the thread be re-open and the Accepted solutions be changed.
Answer: http:#34970440
Assisted: http:#34919339

Just please, read over the code and get yourself familar with it
And read this to really get a better understanding of how it all works
http://www.experts-exchange.com/A_3626.html

Take Care
Carrzkiss
0
 
LVL 1

Author Comment

by:lucky20
ID: 34973778
Yes I stated in that..
Whn I am trying to insert values I am getting errors. So I concentrated on insertion . So i tried with those columns only.
I need to insert Identity column and date column too. Here itemid is not identity column.

Thanks for requesting..

Why didn't you give this link before..
Here everything is there insert/update..Exactly what I need ..

You are so good.
But any way I got good idea on this how to do.Need to learn more..
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34974027
?? Why didn't you give this link before
If you are referring to the link to my Article.
http://www.experts-exchange.com/A_3626.html
I gave it to you here http:#a34919339

As for inserting:
You cannot insert a value into the Identity column
It is an Auto-Number, there for it cannot be written too.
It can only be read from.
You can only have "1" Identity column per Table.

Good Luck
They will be in here sometime to correct the Pointed Post.
Carrzkiss
0
 
LVL 1

Author Comment

by:lucky20
ID: 34974356
Thanks
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 34988674
To the moderators.
The suggested Answer and points are inaccruate.
Please make sure that it is done as listed below, as the inteded ANSWER
In this case, is the code that I provided to the asker, not their own code.

Answer: http:#34970440
Assisted: http:#34919339

Thank you Mods.
Carrzkiss
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question