ADD RECORDS

D

dancer

Using ASP.net 1.1
2 QUESTIONS:
1. Why do my write commands not work?
2. This file successfully makes changes in the database record. There
surely is some simple code I could add that would *add* records to this
database. Who can tell me?

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

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

<script runat="server">

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


Dim DBConnection As OledbConnection

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

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

DBConnection.Open()

Dim DBCommand As OledbCommand

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

Dim SQLString AS String



SQLString = "UPDATE Table1 SET TheEmpName='POOOOO' WHERE TheDate='6/14/07'"

DBCommand = New OleDBCommand(SQLString, DBConnection)

DBCommand.ExecuteNonquery()

Dim DBReader AS OledbDatareader

DBReader = DBCommand.ExecuteReader()

While DBReader.Read()

Response.Write(DBReader("TheDate"))

Response.Write(DBReader("TheEmpName"))

End while

' MyDataGrid.DataSource = DBReader

'MyDataGrid.DataBind()



DBReader.Close()

DBConnection.Close()

End Sub

</script>

</head>

<body>

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

<asp:DataGrid id="MyDataGrid" runat="server"/>

</form>

</body>

</html>
 
M

Mark Rae [MVP]

Using ASP.net 1.1
2 QUESTIONS:
1. Why do my write commands not work?
2. This file successfully makes changes in the database record. There
surely is some simple code I could add that would *add* records to this
database. Who can tell me?
Click to expand...

Well, firstly if you're able to edit existing records, at least *some* of
your write commands are working... :)

1) What code are you actually using to add records to the database? I see an
UPDATE SQL statement for updating existing records - where is your
corresponding INSERT statement for adding new records...?

2) What errors are you getting back from your database?
 
D

dancer

RE:> 1) What code are you actually using to add records to the database? I
see an
UPDATE SQL statement for updating existing records - where is your
corresponding INSERT statement for adding new records...?
Click to expand...
I am not using any code to add records. That was my question: What code can
I add to add records?

RE: 2) What errors are you getting back from your database?
None.
My code does exactly what it's intended to do: updates a record according to
a criteria. The only part that does not work are these two lines:

Response.Write(DBReader("TheDate"))

Response.Write(DBReader("TheEmpName"))

That's what I meant by "why does it not write?" I wanted to SEE if it
updates the record without having to look at the database, therefore I
included those lines, but they do not "write" so that I can see the updated
field.

Thank you.
 
M

Mark Rae [MVP]

I am not using any code to add records. That was my question: What code
can I add to add records?
Click to expand...

Oh right...

The SQL syntax to add a record into a table in a Jet database is:

INSERT INTO Table (field1, Field2...) VALUES (Value1, Value2...)
RE: 2) What errors are you getting back from your database?
None.
My code does exactly what it's intended to do: updates a record according
to a criterion. The only part that does not work are these two lines:

Response.Write(DBReader("TheDate"))

Response.Write(DBReader("TheEmpName"))

That's what I meant by "why does it not write?" I wanted to SEE if it
updates the record without having to look at the database, therefore I
included those lines, but they do not "write" so that I can see the
updated field.
Click to expand...

You're issuing your UPDATE statement correctly but, if you want to see that
it has worked, you will then need to issue a SELECT statement to fetch the
record you've just updated back out of the database...
 
D

dancer

Thank you for helping me, Mark.

The INSERT INTO code works great as long as I'm inserting literals. For
example,
SQLString = "INSERT INTO Table1(TheEmpName, TheDate,
TheNotifyDate)VALUES(""Somebody"", ""10/10/98"", ""10/11/98"")"

But I need to insert variables received form an input form. When I put the
variable names in place of the literals like this:
SQLString = "INSERT INTO Table1(TheEmpName, TheDate,
TheNotifyDate)VALUES(EmpName, DateOfAccident, NotifyDate)"
I get this error message:
No value given for one or more required parameters

What is the syntax for using variables as the values?
 
M

Mark Rae [MVP]

Thank you for helping me, Mark.

The INSERT INTO code works great as long as I'm inserting literals. For
example,
SQLString = "INSERT INTO Table1(TheEmpName, TheDate,
TheNotifyDate)VALUES(""Somebody"", ""10/10/98"", ""10/11/98"")"

But I need to insert variables received form an input form. When I put
the variable names in place of the literals like this:
SQLString = "INSERT INTO Table1(TheEmpName, TheDate,
TheNotifyDate)VALUES(EmpName, DateOfAccident, NotifyDate)"
I get this error message:
No value given for one or more required parameters

What is the syntax for using variables as the values?
Click to expand...

An SQL statement is a string like any other string, so you concatenate your
variables in the usual way:

SQLString = "INSERT INTO Table1(TheEmpName, TheDate, TheNotifyDate)VALUES('"
+ EmpName + "' etc...

However, this is an extremely dangerous practice, especially in a web
application, because of something called SQL Injection, but maybe that's
another story for another day...
 
D

dancer

Hi Mark,

Can you tell me why I get a new record, but with nothing in it, even though
I get a form and fill it in and click submit?
Here's the code:
<%@ 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 EmpName as String

Dim DateOfAccident as string

Dim NotifyDate as string


SQLString = "INSERT INTO Table1(TheEmpName, TheDate,
TheNotifyDate)VALUES('"+EmpName+"','"+DateOfAccident+"','"+NotifyDate+"')"

DBCommand = New OleDBCommand(SQLString, DBConnection)

DBCommand.ExecuteNonquery()


DBConnection.Close()

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>
 
M

Mark Rae [MVP]

Can you tell me why I get a new record, but with nothing in it, even
though I get a form and fill it in and click submit?
Click to expand...

A couple of things...

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

You're instantiating an OleDbCommand object, but never actually using it
before re-instantiating it further down in the code...


Secondly:
Dim SQLString AS String
Dim EmpName as String
Dim DateOfAccident as string
Dim NotifyDate as string

SQLString = "INSERT INTO Table1(TheEmpName, TheDate,
TheNotifyDate)VALUES('"+EmpName+"','"+DateOfAccident+"','"+NotifyDate+"')"
DBCommand = New OleDBCommand(SQLString, DBConnection)
DBCommand.ExecuteNonquery()
DBConnection.Close()
Click to expand...

You're not actually populating the three data variables - therefore, you're
inserting a record into your table where all the fields are blank...

Dim EmpName as String = EmpName.Text
Dim DateOfAccident as string = DateOfAccident.Text
Dim NotifyDate as string = NotifyDate.Text
 
M

Mark Rae [MVP]

Dim EmpName as String = EmpName.Text
Dim DateOfAccident as string = DateOfAccident.Text
Dim NotifyDate as string = NotifyDate.Text
Click to expand...

Actually, that might cause errors because the string variables have the same
names as the webcontrols...

Dim SQLString AS String
Dim strEmpName As String = EmpName.Text
Dim strDateOfAccident As String = DateOfAccident.Text
Dim strNotifyDate As String = NotifyDate.Text

SQLString = "INSERT INTO Table1(TheEmpName, TheDate,
TheNotifyDate)VALUES('"+ strEmpName+"','"+ strDateOfAccident+"','"+
strNotifyDate+"')"
 
D

dancer

ok It's working now. Thanks

Now - other question - I have 47 fields. Do I have to code '"+field1+"',
etc. 47 TIMES?!!
Is there not a way to say INSERT INTO Table 1 [all] VALUE [all] in some
form?
SURELY there is a way.

Also, I have seen
With Cmd Parameters
.Add(New OleDbParameter("@field1",
frmfield1.text))
etc.
What is the difference in that and INSERT?

Thank you.
 
D

dancer

Yes, I got an error, so I put Dim TheEmpName as String = EmpName.Text, etc.,
which makes the names in the table the same as the variable names. It
works, but will it be a problem?

Thanks
 
M

Mark Rae [MVP]

Now - other question - I have 47 fields. Do I have to code '"+field1+"',
etc. 47 TIMES?!!
Yes.

Is there not a way to say INSERT INTO Table 1 [all] VALUE [all] in some
form?
No.

SURELY there is a way.
Click to expand...

You could play about with generics and dictionaries and goodness knows what
but, at the end of the day, you can't really get away from the fact that
you've to send your database a piece of SQL, so you may as well just build
it...
Also, I have seen
With Cmd Parameters
.Add(New OleDbParameter("@field1",
frmfield1.text))
etc.
What is the difference in that and INSERT?
Click to expand...

That's parameterisation - a much safer way of constructing database writes
which helps to eliminate SQL Injection. You would be well advised to adopt
this method.
 
M

Mark Rae [MVP]

Yes, I got an error, so I put Dim TheEmpName as String = EmpName.Text,
etc., which makes the names in the table the same as the variable names.
It works, but will it be a problem?
Click to expand...

No.
 
D

dancer

RE: Cmd parameters Oh I would like to try that. But every time I try I get
all kinds of errors.
What does the @ mean? Do you have to declare @something or does the
compiler understand the @ as something special?
("@something" , frmsomething.text))
Could you define the above?

By injection you mean the user adding an ' in the data?

Thank you very much.



Mark Rae said:
Now - other question - I have 47 fields. Do I have to code '"+field1+"',
etc. 47 TIMES?!!
Yes.

Is there not a way to say INSERT INTO Table 1 [all] VALUE [all] in some
form?
No.

SURELY there is a way.
Click to expand...

You could play about with generics and dictionaries and goodness knows
what but, at the end of the day, you can't really get away from the fact
that you've to send your database a piece of SQL, so you may as well just
build it...
Also, I have seen
With Cmd Parameters
.Add(New OleDbParameter("@field1",
frmfield1.text))
etc.
What is the difference in that and INSERT?
Click to expand...

That's parameterisation - a much safer way of constructing database writes
which helps to eliminate SQL Injection. You would be well advised to adopt
this method.
Click to expand...
 

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

Line break VB.NET 4
Compilation error - Parameterized update 5
Datagrid problem not displaying anything on the screen!! 1
Help with Datagrid (Surprising No Data!!) 2
RadioButtonList and Selected 1
DataGrid Paging / ICollection Error [JSCRIPT] 1
System.NullReferenceException: Object reference not set to an inst 3
Datagrid not paging 5

Members online

Total: 69 (members: 1, guests: 68)
Robots: 185

Forum statistics

Threads
473,968
Messages
2,570,153
Members
46,699
Latest member
AnneRosen

Latest Threads

Top