help:can"t add data into DB using asp .net

Sub add_Click(sender As Object, e As EventArgs)
head.text=SerialNumber1.text


'msgBox SerialNumber

dim dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data
source=" & server.mappath("/db/equipment.mdb"))
dbconn.Open()


sql="INSERT INTO test (SerialNumber) VALUES (@SerialNumber1)"
'(@SerialNumber1)"
'SerialNumber1.text=""
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
'portable.DataSource=dbread
'portable.DataBind()
dbread.Close()
dbconn.Close()

End Sub

i am beginner using ASP .net
i tried to add in the SerialNumber1 value into SerialNumber in the test
table...
but it returned "Exception Details: System.Data.OleDb.OleDbException:
No value given for one or more required parameters."


[OleDbException (0x80040e10): No value given for one or more required
parameters.]
Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(O bject o,
Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack) +743
ASP.addDB_aspx.add_Click(Object sender, EventArgs e) +211
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +83

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEven tHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
+33
System.Web.UI.Page.ProcessRequestMain() +1263

Re: help:can"t add data into DB using asp .net

zheetee@gmail.com wrote:
> Sub add_Click(sender As Object, e As EventArgs)
> head.text=SerialNumber1.text
>
>
There was no way for you to know it (except maybe by browsing through some
of the previous questions before posting yours - always a recommended
practice) , but this is a classic asp newsgroup.
While you may be lucky enough to find a dotnet-knowledgeable person here who
can answer your question, you can eliminate the luck factor by posting your
question to a group where those dotnet-knowledgeable people hang out. I
suggest microsoft.public.dotnet.framework.aspnet.

There are several issues with your code. Read on.


> 'msgBox SerialNumber

msgbox? In server-side code? Not good. :-)

>
> dim dbconn,sql,dbcomm,dbread

OK, you're not coding in vbscript anymore. This is VB.Net: a strongly-typed
language. Always declare your variables with appropriate data types:
dim varname As datatype

Also, VB.Net supports declaring and assigning values to variables in a
single line of code. So this:

> dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data
> source=" & server.mappath("/db/equipment.mdb"))

should be this:

dim dbconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data
source=" & _
server.mappath("/db/equipment.mdb"))

> dbconn.Open()
>
>
> sql="INSERT INTO test (SerialNumber) VALUES (@SerialNumber1)"

The OLEDbCommand object does not support using named parameters. Instead,
use question marks for parameter markers:
dim sql as String="INSERT INTO test (SerialNumber) VALUES (?)"

> '(@SerialNumber1)"
> 'SerialNumber1.text=""

This:
> dbcomm=New OleDbCommand(sql,dbconn)

should be:
dim dbcomm As New OleDbCommand(sql,dbconn)

> dbread=dbcomm.ExecuteReader()

Your sql statement does not return records. Do not try and open a reader. Do
not use ExecuteReader. Use ExecuteNonQuery.

Also, you need to set the command type and create the Parameters collection,
like this (use the appropriate datatype for your serial number field - this
example uses integer.):

with dbcomm
.commandtype=CommandType.Text
.Parameters.Add("@SerialNum", OleDbType.Integer).Value = 239
end with
Try
dbcomm.ExecuteNonQuery
Catch ex as exception
'handle the error
Finally
dbcomm.dispose
dbconn.close
dbconn.dispose
End Try

> 'portable.DataSource=dbread
> 'portable.DataBind()
> dbread.Close()
> dbconn.Close()
>
> End Sub
>

Follow up in the aspnet group.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: help:can"t add data into DB using asp .net

i tried coding below...

dim dbconn = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" &
server.mappath("/db/equipment.mdb"))
dbconn.Open()

dim sql="INSERT INTO test (SerialNumber) VALUES (?)"
'dim sql="INSERT INTO test (SerialNumber) VALUES (@SerialNumber1)"
dim dbcomm = New OleDbCommand(sql,dbconn)

with dbcomm
.commandtype=CommandType.Text
.Parameters.Add("@SerialNumber1", OleDbType.Integer).Value =
239
end with
Try
dbcomm.ExecuteNonQuery
Catch ex as exception
'handle the error
Finally
dbcomm.dispose
dbconn.close
dbconn.dispose
End Try

but it return some error regarding the commandtype


after that i use asp .net matrix...and wrote some coding like below
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole
DB Services=-4; Data Source=C:\Documents an"& _
"d Settings\AliceLee\My Documents\db\equipment.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "INSERT INTO [test]
([SerialNumber]) VALUES (SerialNumber1)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

'Return rowsAffected

it returned--> Exception Details: System.Data.OleDb.OleDbException: No
value given for one or more required parameters.

can i know what is the problem???
why cant get the add the SerialNumber1.text into access???

Re: help:can"t add data into DB using asp .net

zheetee@gmail.com wrote:
> i tried coding below...

Please follow up in the aspnet group.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"