Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with Sql insert

Status
Not open for further replies.

Guest_imported

New member
Jan 1, 1970
0
I am trying to insert values into an Access database record.

sql= "insert into ordereditems (orderid,mysize,priceperunit) values (" & orderid & "," & mysize & "," & price & ")"
set rsordereditems=db.execute(sql)


It only seems to work if the (" & orderid & "," & mysize & "," & price & ") values are numbers.
Like if I make mysize = 1,2,3, etc. for small, medium, large.
If mysize is a string then I get an error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

But if its a number then the database updates. I would like it to be a string.

Can someone help me out here?

Thanks in advance,

Sid

BTW, the access recordset entry for mysize is set up to be text data type.

 
If you are inserting strings, then you need to enclose your values in single ticks --

sql= "insert into ordereditems (orderid,mysize,priceperunit) values ('" & orderid & "','" & mysize & "','" & price & "')"

:)
Paul Prewett
 
Also, you should probably enclose those values in the cstr() function to ensure that they are strings --

sql= "insert into ordereditems (orderid,mysize,priceperunit) values ('" & cstr(orderid) & "','" & cstr(mysize) & "','" & cstr(price) & "')"
 
Hi,
I'm new at this and need some direction. I created a SQL database and uploaded it to my web domain. I've been told that I need to make an ASP to enter data. Is that true and what application is very easy to use?

thanks,
Jennifer
 
Hi i am having a problem passing values from a html form to a SQL table.

It keeps on coming up with a possible type miss match error.
Here is the code i am using that drags the info from the form an i was wondering if you could point me in the right direction?

Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open SQLstr, db, adOpenStatic
SQLstr="INSERT INTO tbl_complaints(cust_id,logon_date,name,Fault,contract_no)"
SQLstr = SQLstr & "values ("
SQLstr = SQLstr & "'" & request.form("cust_id") & "','"
SQLstr = SQLstr & "'" & request.form("name") & "','"
SQLstr = SQLstr & "'" & request.form("logon_date") & "','"
SQLstr = SQLstr & "'" & request.form("Fault") & "','"
SQLstr = SQLstr & "'" & request.form("contract_no") & "','"
SQLstr = SQLstr & ")"
set RS=db.execute (SQLstr)
Set RS = Nothing

Tahnks ross
 
As Paul mentioned earlier, when constructing any SQL query, it is paramount to enclose your date types with the correct characters. For numbers you do not enclose them with anything, strings get enclosed by single quotes (') and dates get enclosed by hash (#).

Also you seem to have name and logon_date the wrong way round in your values. You must make sure you are putting your values in the same order as your fields.

One last thing, you don't need to create a Recordset Object to execute an INSERT statement, as you are not creating a recordset.

So presuming that cust_id is an integer, logon_date is a date, and name, fault and contract_no are strings you would use...


SQLstr="INSERT INTO tbl_complaints(cust_id,logon_date,name,Fault,contract_no)"
SQLstr = SQLstr & "values ("
SQLstr = SQLstr & "" & request.form("cust_id") & ",'"
SQLstr = SQLstr & "#" & request.form("logon_date")& "#,'"
SQLstr = SQLstr & "'" & request.form("name") & "','"
SQLstr = SQLstr & "'" & request.form("Fault") & "','"
SQLstr = SQLstr & "'" & request.form("contract_no") & "','"
SQLstr = SQLstr & ")"
db.execute (SQLstr)
Set db = Nothing

Hope this helps...

G -GTM Solutions, Home of USITE-
-=
 
And keep in mind, also, that hash(#) is for Access -- and the single ticks(') are for SQL Server. I'm not sure what are used for other DBMS systems, but I suspect they are different, also. You'll be able to find out what is right for your system if you use your query building tool for your respective system and then inspect the SQL that it makes.

:)
Paul Prewett
penny.gif
penny.gif
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top