Compilation error - Parameterized update

D

dancer

Using Asp.net 1.1
Can somebody tell me why the code following gives this error message:

Compilation Error
Description: An error occurred during the compilation of a resource required
to service this request. Please review the following specific error details
and modify your source code appropriately.

Compiler Error Message: BC30456: 'String' is not a member of
'System.Data.OleDb.OleDbType'.

Source Error:


Line 21: Dim TheNotifyDate as string = NotifyDate.Text
Line 22:
Line 23: Dim parameterTheEmpName as OleDbParameter = new
OleDbParameter("@TheEmpName", OleDbType.String)
Line 24:
Line 25: Response.Write("Value of TheEmpName = " & TheEmpName & "<br>")


<%@ Page Language="VB" Debug="true" %>

<%@ Import Namespace="System.Data.Oledb" %>



<script language= "VB" runat="server">

'Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)

Sub btnSendDatabase_OnClick(Source As Object, E As EventArgs)


Dim DBConnection As OledbConnection

DBConnection = New OledbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _

"Data Source=C:\Inetpub\wwwroot\Acc.mdb" )

DBConnection.Open()

Dim DBCommand As OledbCommand

DBCommand = New OledbCommand("SELECT * FROM table1, Acc")

Dim SQLString AS String


Dim TheEmpName as String = EmpName.Text

Dim TheDateOfAccident as string = DateOfAccident.Text

Dim TheNotifyDate as string = NotifyDate.Text

Dim parameterTheEmpName as OleDbParameter = new
OleDbParameter("@TheEmpName", OleDbType.String)

Response.Write("Value of TheEmpName = " & TheEmpName & "<br>")




SQLString = "INSERT INTO Table1(TheEmpName, TheDate,
TheNotifyDate)VALUES(@TheEmpName,@TheDateOfAccident,@TheNotifyDate)"

DBCommand = New OleDBCommand(SQLString, DBConnection)

DBCommand.ExecuteNonquery()

Response.Write("Value of TheEmpName = " & TheEmpName & "<br>")

Dim Cmd as New OleDbCommand(SQLString, DBConnection)

With cmd.Parameters:

..Add(New OleDbParameter("@TheEmpName", EmpName.Text))

..Add(New OleDbParameter("@TheDateOfAccident", DateOfAccident.Text))

..Add(New OleDbParameter("@TheNotifyDate", NotifyDate.Text))

end With


DBConnection.Close()

Response.Write("Value of TheEmpName = " & TheEmpName & "<br>")



End Sub

</script>

</head>

<body>

<form id="form1" runat="server">



Employee's Name: <asp:textbox id="EmpName" runat=server columns="45"/>

<asp:textbox id="DateofAccident" runat=server /></asp:textbox>

<font face="Verdana" Size="2">Date Employer Notified <asp:textbox
id="Notifydate" runat=server/>

<asp:Button id="btnSendDatabase" text="Submit"
OnClick="btnSendDatabase_OnClick" runat="server" />


</form>

</body>

</html>
 
D

dancer

I changed to VarChar, but now I get this message:
No value given for one or more required parameters
Line 32: DBCommand.ExecuteNonquery()



Eliyahu Goldin said:
Change to

OleDbType.VarChar

--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin


dancer said:
Using Asp.net 1.1
Can somebody tell me why the code following gives this error message:

Compilation Error
Description: An error occurred during the compilation of a resource required
to service this request. Please review the following specific error details
and modify your source code appropriately.

Compiler Error Message: BC30456: 'String' is not a member of
'System.Data.OleDb.OleDbType'.

Source Error:


Line 21: Dim TheNotifyDate as string = NotifyDate.Text
Line 22:
Line 23: Dim parameterTheEmpName as OleDbParameter = new
OleDbParameter("@TheEmpName", OleDbType.String)
Line 24:
Line 25: Response.Write("Value of TheEmpName = " & TheEmpName & "<br>")


<%@ Page Language="VB" Debug="true" %>

<%@ Import Namespace="System.Data.Oledb" %>



<script language= "VB" runat="server">

'Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)

Sub btnSendDatabase_OnClick(Source As Object, E As EventArgs)


Dim DBConnection As OledbConnection

DBConnection = New OledbConnection("Provider=Microsoft.Jet.Oledb.4.0;" &
_

"Data Source=C:\Inetpub\wwwroot\Acc.mdb" )

DBConnection.Open()

Dim DBCommand As OledbCommand

DBCommand = New OledbCommand("SELECT * FROM table1, Acc")

Dim SQLString AS String


Dim TheEmpName as String = EmpName.Text

Dim TheDateOfAccident as string = DateOfAccident.Text

Dim TheNotifyDate as string = NotifyDate.Text

Dim parameterTheEmpName as OleDbParameter = new
OleDbParameter("@TheEmpName", OleDbType.String)

Response.Write("Value of TheEmpName = " & TheEmpName & "<br>")




SQLString = "INSERT INTO Table1(TheEmpName, TheDate,
TheNotifyDate)VALUES(@TheEmpName,@TheDateOfAccident,@TheNotifyDate)"

DBCommand = New OleDBCommand(SQLString, DBConnection)

DBCommand.ExecuteNonquery()

Response.Write("Value of TheEmpName = " & TheEmpName & "<br>")

Dim Cmd as New OleDbCommand(SQLString, DBConnection)

With cmd.Parameters:

.Add(New OleDbParameter("@TheEmpName", EmpName.Text))

.Add(New OleDbParameter("@TheDateOfAccident", DateOfAccident.Text))

.Add(New OleDbParameter("@TheNotifyDate", NotifyDate.Text))

end With


DBConnection.Close()

Response.Write("Value of TheEmpName = " & TheEmpName & "<br>")



End Sub

</script>

</head>

<body>

<form id="form1" runat="server">



Employee's Name: <asp:textbox id="EmpName" runat=server columns="45"/>

<asp:textbox id="DateofAccident" runat=server /></asp:textbox>

<font face="Verdana" Size="2">Date Employer Notified <asp:textbox
id="Notifydate" runat=server/>

<asp:Button id="btnSendDatabase" text="Submit"
OnClick="btnSendDatabase_OnClick" runat="server" />


</form>

</body>

</html>
Click to expand...
Click to expand...
 
G

Guest

I changed to VarChar, but now I get this message:
No value given for one or more required parameters
Line 32: DBCommand.ExecuteNonquery()
Click to expand...

Hi dancer,

"No value given for one or more required parameters" says that the
parameterTheEmpName parameter you have initiated has no value.

Dim parameterTheEmpName as OleDbParameter = new
OleDbParameter("@TheEmpName", OleDbType.VarChar)
parameterTheEmpName.Value = "here_is_your_value"

'After that add the parameter to the command object using the
Parameters collection
{yourCommandNameObject}.Parameters.Add(parameterTheEmpName)

Then look at your code.

You've created the parameterTheEmpName and you don't use it.

Moreover, after that you do

SQLString = "INSERT INTO Table1(TheEmpName, TheDate,
TheNotifyDate)VALUES(@TheEmpName,@TheDateOfAccident,@TheNotifyDate)"
DBCommand = New OleDBCommand(SQLString, DBConnection)
DBCommand.ExecuteNonquery()

Where you created a new OleDBCommand, referred to the @TheEmpName
(again with no value) and other parameters and executed that
DBCommand.

After that you created another OleDbCommand, created and attached a
new @TheEmpName (this time with a value as EmpName.Text) and closed
the connection.

What's the logic behind this?

Basically, you should

1) open a connection
2) create a new command
3) attach all parameters
4) execute a command
5) close connection
 
D

dancer

Thank you, Alexey, for replying.
I just don't know enough to follow you. I had the following code which
worked with no problem.. But I wanted to change to a Parameterized query.
Could you do me the favor of changing my code to that which uses parameters
correctly?
Then I think I will be able to understand. (The Response.Write statements
are just for checking.)
I'll be forever in your debt!!
<%@ Page Language="VB" Debug="true" %>

<%@ Import Namespace="System.Data.Oledb" %>

<script language= "VB" runat="server">

'Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)

Sub btnSendDatabase_OnClick(Source As Object, E As EventArgs)


Dim DBConnection As OledbConnection

DBConnection = New OledbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _

"Data Source=C:\Inetpub\wwwroot\Acc.mdb" )

DBConnection.Open()

Dim DBCommand As OledbCommand

DBCommand = New OledbCommand("SELECT * FROM table1, Acc")

Dim SQLString AS String


Dim TheEmpName as String = EmpName.Text

Dim TheDateOfAccident as string = DateOfAccident.Text

Dim TheNotifyDate as string = NotifyDate.Text


Response.Write("Value of TheEmpName = " & TheEmpName & "<br>")




SQLString = "INSERT INTO Table1(TheEmpName, TheDateOfAccident,
TheNotifyDate)VALUES('"+TheEmpName+"','"+TheDateOfAccident+"','"+TheNotifyDate+"')"

DBCommand = New OleDBCommand(SQLString, DBConnection)

DBCommand.ExecuteNonquery()

Response.Write("Value of TheEmpName = " & TheEmpName & "<br>")


DBConnection.Close()

Response.Write("Value of TheEmpName = " & TheEmpName & "<br>")



End Sub

</script>

</head>

<body>

<form id="form1" runat="server">



Employee's Name: <asp:textbox id="EmpName" runat=server columns="45"/>

<asp:textbox id="DateofAccident" runat=server /></asp:textbox>

<font face="Verdana" Size="2">Date Employer Notified <asp:textbox
id="Notifydate" runat=server/>

<asp:Button id="btnSendDatabase" text="Submit"
OnClick="btnSendDatabase_OnClick" runat="server" />


</form>

</body>

</html>
 
G

Guest

Thank you, Alexey, for replying.
I just don't know enough to follow you. I had the following code which
worked with no problem.. But I wanted to change to a Parameterized query.
Could you do me the favor of changing my code to that which uses parameters
correctly?
Then I think I will be able to understand. (The Response.Write statements
are just for checking.)
I'll be forever in your debt!!
<%@ Page Language="VB" Debug="true" %>

<%@ Import Namespace="System.Data.Oledb" %>

<script language= "VB" runat="server">

'Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)

Sub btnSendDatabase_OnClick(Source As Object, E As EventArgs)

Dim DBConnection As OledbConnection

DBConnection = New OledbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _

"Data Source=C:\Inetpub\wwwroot\Acc.mdb" )

DBConnection.Open()

Dim DBCommand As OledbCommand

DBCommand = New OledbCommand("SELECT * FROM table1, Acc")
Click to expand...

The code looks correct, except the line with

DBCommand = New OledbCommand("SELECT * FROM table1, Acc")

Change it to "SELECT * FROM table1"

Hope this helps
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ADD RECORDS 14
If Then within Subroutine that uses Parameters 0
ERROR MESSAGE CITES DIFFERENT LINE FROM PROBLEM 6
Compiler Error Message 1
Line break VB.NET 4
Update element value ViewWeb2 .net Control 1
Cannot pass Null value for parameter ??? 3
Insert, Update and Delete. Can somebody please help me? 3

Members online

Total: 62 (members: 2, guests: 60)
Robots: 302

Forum statistics

Threads
473,968
Messages
2,570,152
Members
46,697
Latest member
AugustNabo

Latest Threads

Top