Compilation error - Parameterized update

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dancer - 03 Aug 2007 14:11 GMT

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>

Reply to this Message

Eliyahu Goldin - 03 Aug 2007 15:01 GMT

Change to

OleDbType.VarChar

Signature

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

> Using Asp.net 1.1
> Can somebody tell me why the code following gives this error message:
[quoted text clipped - 102 lines]
>
> </html>

Reply to this Message

dancer - 03 Aug 2007 18:41 GMT

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

> Change to
>
[quoted text clipped - 109 lines]
>>
>> </html>

Reply to this Message

Alexey Smirnov - 04 Aug 2007 09:08 GMT

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

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

Reply to this Message

dancer - 05 Aug 2007 14:55 GMT

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>

>> I changed to VarChar, but now I get this message:
>> No value given for one or more required parameters
[quoted text clipped - 12 lines]
> 4) execute a command
> 5) close connection

Reply to this Message

Alexey Smirnov - 06 Aug 2007 20:56 GMT

> Thank you, Alexey, for replying.
> I just don't know enough to follow you. I had the following code which
[quoted text clipped - 26 lines]
>
> DBCommand = New OledbCommand("SELECT * FROM table1, Acc")

The code looks correct, except the line with

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

Change it to "SELECT * FROM table1"

Hope this helps

Reply to this Message

Compilation error - Parameterized update

Free Magazines