SQL insert hjælp

Mangler du ikke dit connection objekt? Det er den som skal eksikvere. Desuden skal du have nogle værdier i sætningen:

SQL = "INSERT INTO Orders (CustomerID, OrderDate, PaymentAmount, ProductName) VALUES (1, '20080228', 123.25, 'Test produkt');"
conn.execute( SQL )

har det sårn her

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.MapPath("dbs/db1.mdb")

SQL = "INSERT INTO Orders (CustomerID, OrderDate, PaymentAmount, ProductName) VALUES (1, '20080228', 123.25, 'Test produkt');"
Conn.Execute (SQL)

men det virker ikke helt'

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/jimmy/bestillinger.asp, line 42

Hvilken datatype er de forskellige felter i databasen (CustomerID, OrderDate, PaymentAmount, ProductName)? nvarchar, int, datetime...??

har du prøvet at køre sql'en i access' query designer? - det er som regel en god ide at få det til at virke der først, fordi du så får en bedre feedback.

husk der skal # på hver side af datoer og ' på hver side af tekststrenge.

/Kim

ok det er ikke insert funktionen der ike virker :) det er det her der er noget galt med

strSQL = "SELECT * FROM Orders"
Set rs = Conn.Execute (strSQL)

OrderID = Rs("OrderID")
CustomerID = Rs("CustomerID")
OrderDate = Rs("OrderDate")
PaymentAmount = Rs("PaymentAmount")
ProductName = Rs("ProductName")

response.write("<table align='center' border='1' width='500'>")
response.write("<tr>")
response.write("<th>" & "Ordre id" & "</th>")
response.write("<th>" & "Kunde nr" & "</th>")
response.write("<th>" & "Ordre dato" & "</th>")
response.write("<th>" & "Beløb kr" & "</th>")
response.write("<th>" & "Produkt" & "</th>")
response.write("</tr>")
response.write("<tr align='center'>")
response.write("<td>" & OrderID & "</td>")
response.write("<td>" & CustomerID & "</td>")
response.write("<td>" & OrderDate & "</td>")
response.write("<td>" & PaymentAmount & "</td>")
response.write("<td>" & ProductName & "</td>")
response.write("</tr>")
response.write("</table>")

prøv med denne connectionstring i stedet..

Conn.Open Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("dbs/db1.mdb")

problemet det var åbenbart den ikke hentede alt ud af databasen fordi da jeg kiggede i databasen var der blevet oprettet en masse men den gider kun vise den første

du kan bruge et do-while loop til at loope igennem alle records, så det ikke kun er den første, der vises.  husk rs.movenext før loop, da det er den, der skubber videre til næste record.  (hvis du glemmer den, så har du en uendelig løkke).

strSQL = "SELECT * FROM Orders"
Set rs = Conn.Execute (strSQL)

do while not rs.eof

OrderID = Rs("OrderID")
CustomerID = Rs("CustomerID")
OrderDate = Rs("OrderDate")
PaymentAmount = Rs("PaymentAmount")
ProductName = Rs("ProductName")

response.write("<table align='center' border='1' width='500'>")
response.write("<tr>")
response.write("<th>" & "Ordre id" & "</th>")
response.write("<th>" & "Kunde nr" & "</th>")
response.write("<th>" & "Ordre dato" & "</th>")
response.write("<th>" & "Beløb kr" & "</th>")
response.write("<th>" & "Produkt" & "</th>")
response.write("</tr>")
response.write("<tr align='center'>")
response.write("<td>" & OrderID & "</td>")
response.write("<td>" & CustomerID & "</td>")
response.write("<td>" & OrderDate & "</td>")
response.write("<td>" & PaymentAmount & "</td>")
response.write("<td>" & ProductName & "</td>")
response.write("</tr>")
response.write("</table>")

rs.movenext
loop

Nok nærmere sådan:

strSQL = "SELECT * FROM Orders"
Set rs = Conn.Execute (strSQL)
esponse.write("<table align='center' border='1' width='500'>")
response.write("<tr>")
response.write("<th>" & "Ordre id" & "</th>")
response.write("<th>" & "Kunde nr" & "</th>")
response.write("<th>" & "Ordre dato" & "</th>")
response.write("<th>" & "Beløb kr" & "</th>")
response.write("<th>" & "Produkt" & "</th>")
response.write("</tr>")
while not rs.eof
  response.write("<tr align='center'>")
  response.write("<td>" & Rs("OrderID") & "</td>")
  response.write("<td>" & Rs("CustomerID") & "</td>")
  response.write("<td>" & Rs("OrderDate") & "</td>")
  response.write("<td>" & Rs("PaymentAmount") & "</td>")
  response.write("<td>" & Rs("ProductName") & "</td>")
  response.write("</tr>")
  rs.MoveNext
wend
response.write("</table>")

hov, jeg var lidt for hurtig der... prøv denne i stedet :)

strSQL = "SELECT * FROM Orders"
Set rs = Conn.Execute (strSQL)

response.write("<table align='center' border='1' width='500'>")
response.write("<tr>")
response.write("<th>" & "Ordre id" & "</th>")
response.write("<th>" & "Kunde nr" & "</th>")
response.write("<th>" & "Ordre dato" & "</th>")
response.write("<th>" & "Beløb kr" & "</th>")
response.write("<th>" & "Produkt" & "</th>")
response.write("</tr>")

do while not rs.eof

OrderID = Rs("OrderID")
CustomerID = Rs("CustomerID")
OrderDate = Rs("OrderDate")
PaymentAmount = Rs("PaymentAmount")
ProductName = Rs("ProductName")

response.write("<tr align='center'>")
response.write("<td>" & OrderID & "</td>")
response.write("<td>" & CustomerID & "</td>")
response.write("<td>" & OrderDate & "</td>")
response.write("<td>" & PaymentAmount & "</td>")
response.write("<td>" & ProductName & "</td>")
response.write("</tr>")

rs.movenext
loop

response.write("</table>")

ok 1000 tak tror jeg har forstået det nu

Nogen der kan hjælpe mig med den her

dim CustomerID, Orderdate, PaymentAmount, ProductName
CustomerID = 5
OrderDate = request.querystring("OrderDate")
PaymentAmount = request.querystring("PaymentAmount")
ProductName = request.querystring("ProductName")
conn.close

if request.querystring("tjeck") = "ja" then
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.MapPath("dbs/db1.mdb")
SQL = "INSERT INTO Orders (CustomerID, OrderDate, PaymentAmount, ProductName) VALUES (1, 20, 15, 'Test produkt');"
Conn.Execute (SQL)
end if

vil godt have så de der values de bliver til de der querystrings

SQL = "INSERT INTO Orders (CustomerID, OrderDate, PaymentAmount, ProductName) VALUES ("& cLng(CustomerID) &", "& cLng(OrderDate) &", "& cLng(PaymentAmount) &", '"& replace(ProductName,"'","''") &"');"

1000 tak smid et svar hvis det er

.o) <-- One Eyed Jack

:)

kimiwan >>
http://www.eksperten.dk/spm/821591