Code for SQL INSERT INTO?

Question

Blueie 13 Junior Poster

10 Years Ago

Hello

I have started my first ever VS Web page today and have two pages open in the software: Register1.aspx and Web.config. I am hoping to devise a simple 'new user registration form' with three fields: username, password, and email. When the 'submit' button is clicked, those three fields should populate three fields in my MS Access database.

The code I have in my Web.config file looks like this:

<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="usersConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\myDatabase.mdb"
providerName="System.Data.OleDb"/>
  </connectionStrings>

  <system.web>
    <compilation debug="true" targetFramework="4.0"/>

    Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
    conn.Open()

    Dim sql As String = "INSERT INTO userlist (username,password, strEmail) VALUES (@username,@password, @strEmail)"
    Dim conn As OleDbConnection = New OleDbConnection

    cmd.Parameters.AddWithValue("@username", txtusername.Text)
    cmd.Parameters.AddWithValue("@password", txtpassword.Text)
    cmd.Parameters.AddWithValue("@strEmail", txtstrEmail.Text)

    cmd.ExecuteNonQuery()
    conn.Close()
    cmd.Dispose()
    conn.Dispose()

  </system.web>
</configuration>

Does it appear that I have that script correct, please (syntax and order)?

I also wondered that if I copied and pasted the final code into Notepad and saved it as an .aspx file, how would I compile it in Command Prompt before uploading it to my server?

Thank you.

Blueie

asp.net sql

5 Contributors
12 Replies
293 Views
5 Days Discussion Span
Latest Post 10 Years Ago Latest Post by Blueie

hericles 289 Master Poster

10 Years Ago

Well, you are opening your connection before you have defined it, and in the code segment you aren't specifying the connection string for the connection.
So as it stands the code won't run. Line 15 needs to be at line 10 and include the connection 'usersConnectionString'.

As for building from the command line, all the info you could need is over at MSDN

JorgeM 958 Problem Solver

10 Years Ago

Why are you placing your code within the web.config file? Your web.config file is to be used for configuration of the site, webserver, etc...

Your code should either go in a .aspx page within script tags, or better yet, in the .aspx code behind page within some event such as a button click event.

Have you been to asp.net's formal site? Also, they have quite a bit of tutorials to get you quickly started..

For example, see --> Build Your First ASP.NET Application with ASP.NET Web Forms

Edited 10 Years Ago by JorgeM

JorgeM 958 Problem Solver

10 Years Ago

As I mentioned above, your web.config is strictly for configuration settings. Its not for actual c#/vb code. Your code goes within the script tags of a .aspx page or better yet, in the code behind pages of your .aspx pages (.vb/.cs).

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Blueie 13 Junior Poster · Answer 1 · 2014-06-02T16:42:42+00:00

Hello Hericles

Many thanks for your reply and the link.

I have moved things about as you suggest. Does this look reasonable:

<?xml version="1.0"?>
<configuration>
<connectionStrings><add name="usersConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Domains\bayingwolf.com\db\users.mdb"
providerName="System.Data.OleDb"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.0"/>

    Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)

    conn.Open()

    Dim sql As String = "INSERT INTO userlist (username,password, strEmail) VALUES (@username,@password, @strEmail)"
    Dim conn As OleDbConnection = New OleDbConnection

    cmd.Parameters.AddWithValue("@username", txtusername.Text)
    cmd.Parameters.AddWithValue("@password", txtpassword.Text)
    cmd.Parameters.AddWithValue("@strEmail", txtstrEmail.Text)

    cmd.ExecuteNonQuery()
    conn.Close()
    cmd.Dispose()
    conn.Dispose()

  </system.web>
</configuration>

The order - not sure if it's right or not - I have things in is this:

XML version

Database here

Open connection to the database and insert data into it via SQL

Close connection

But there is no 'close users connection string' or 'close compilation', is there? I don't get any errors but quite a few warnings.

Thanks again.

Blueie 13 Junior Poster · Answer 2 · 2014-06-02T18:28:01+00:00

Many thanks for that Jorge - I'll take a look now at the link.

Cheer!

geniusvishal -1 ~Coder~ · Answer 3 · 2014-06-06T17:04:28+00:00

Also refer this article if u are using ms access as db..

http://godhc.wordpress.com/2011/04/22/aspnetsimpledb/

Blueie 13 Junior Poster · Answer 4 · 2014-06-06T19:01:29+00:00

Hello geniusvishal

Thank you for your message and the link.

In my aspx.vb file I have:

Imports Microsoft.AspNet.Identity
Imports Microsoft.AspNet.Identity.EntityFramework
Imports Microsoft.AspNet.Identity.Owin
Imports System
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Data.OleDb

Partial Class Account_Register
    Inherits System.Web.UI.Page

    Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Using conn As New OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings("usersConnectionString").ConnectionString)
            Dim Sql As String = "INSERT INTO userlist (username,password, strEmail) VALUES (@username,@password, @strEmail)"
            Dim cmd As New OleDbCommand(Sql, conn)
            conn.Open()
            cmd.Parameters.AddWithValue("@username", username.Text)
            cmd.Parameters.AddWithValue("@password", password.Text)
            cmd.Parameters.AddWithValue("@strEmail", strEmail.Text)
            cmd.ExecuteNonQuery()
            conn.Close()
        End Using
    End Sub

    Protected Sub CreateUser_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim manager = New UserManager()
        Dim user = New ApplicationUser() With {.UserName = userName.Text}
        Dim result = manager.Create(user, Password.Text)
        If result.Succeeded Then
            IdentityHelper.SignIn(manager, user, isPersistent:=False)
            IdentityHelper.RedirectToReturnUrl(Request.QueryString("ReturnUrl"), Response)
        Else
            ErrorMessage.Text = result.Errors.FirstOrDefault()
        End If
    End Sub

End Class

I don't get any errors. Do I ALSO now need to use this (the script above) in my Web.config file:

<?xml version="1.0"?>
<configuration>
<connectionStrings><add name="usersConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\myDatabase.mdb"
providerName="System.Data.OleDb"/>
</connectionStrings>
 <system.web>
   <compilation debug="true" targetFramework="4.0"/>

    Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)

    conn.Open()

    Dim sql As String = "INSERT INTO userlist (username,password, strEmail) VALUES (@username,@password, @strEmail)"
    Dim conn As OleDbConnection = New OleDbConnection

    cmd.Parameters.AddWithValue("@username", txtusername.Text)
    cmd.Parameters.AddWithValue("@password", txtpassword.Text)
    cmd.Parameters.AddWithValue("@strEmail", txtstrEmail.Text)

    cmd.ExecuteNonQuery()
    conn.Close()
    cmd.Dispose()
    conn.Dispose()

  </system.web>
</configuration>

Furthermore, in the link you kindly sent me, I need to use C#, is that right? Where do I code that C# script because under 'Solution Explorer', I cannot see a .cs folder or file.

Thanks again.

Elizabeth_Keen 0 Newbie Poster · Answer 5 · 2014-06-06T22:23:38+00:00

Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Using conn As New OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings("usersConnectionString").ConnectionString)
Dim Sql As String = "INSERT INTO userlist (username,password, strEmail) VALUES (@username,@password, @strEmail)"
Dim cmd As New OleDbCommand(Sql, conn)
conn.Open()
cmd.Parameters.AddWithValue("@username", username.Text)
cmd.Parameters.AddWithValue("@password", password.Text)
cmd.Parameters.AddWithValue("@strEmail", strEmail.Text)
cmd.ExecuteNonQuery()
conn.Close()
End Using
End Sub

Blueie 13 Junior Poster · Answer 6 · 2014-06-06T22:59:23+00:00

Isn't that what I already have, Elizabeth?

What I mean is that nowhere in my script do I refer to my database, its name, or path, so I was wondering if I need to use the following in my Web.config file:

<?xml version="1.0"?>
<configuration>
<connectionStrings><add name="usersConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\myDatabase.mdb"
providerName="System.Data.OleDb"/>
</connectionStrings>
 <system.web>
   <compilation debug="true" targetFramework="4.0"/>

    Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)

    conn.Open()

    Dim sql As String = "INSERT INTO userlist (username,password, strEmail) VALUES (@username,@password, @strEmail)"
    Dim conn As OleDbConnection = New OleDbConnection

    cmd.Parameters.AddWithValue("@username", txtusername.Text)
    cmd.Parameters.AddWithValue("@password", txtpassword.Text)
    cmd.Parameters.AddWithValue("@strEmail", txtstrEmail.Text)

    cmd.ExecuteNonQuery()
    conn.Close()
    cmd.Dispose()
    conn.Dispose()

  </system.web>
</configuration>

Having said that, I know there is some repetition such as 'cmd.Parameters.AddWithValue("@username", txtusername.Text)'

Thanks.

Blueie 13 Junior Poster · Answer 7 · 2014-06-07T00:34:00+00:00

Hello Jorge

Yes, so you did, sorry. I will place it in my aspx.vb file.

I have this in my aspx.vb file:

Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Using conn As New OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings("usersConnectionString").ConnectionString)
            Dim Sql As String = "INSERT INTO userlist (username,password, strEmail) VALUES (@username,@password, @strEmail)"
            Dim cmd As New OleDbCommand(Sql, conn)
            conn.Open()
            cmd.Parameters.AddWithValue("@username", username.Text)
            cmd.Parameters.AddWithValue("@password", password.Text)
            cmd.Parameters.AddWithValue("@strEmail", strEmail.Text)
            cmd.ExecuteNonQuery()
            conn.Close()
        End Using
    End Sub

So, I just need to add the following to the above code?

<?xml version="1.0"?>
<configuration>
<connectionStrings><add name="usersConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\myDatabase.mdb"
providerName="System.Data.OleDb"/>
</connectionStrings>

<system.web>
   <compilation debug="true" targetFramework="4.0"/>

  </system.web>
</configuration>

I don't need to use cmd.Dispose() or conn.Dispose() because I have used 'Using'. That's correct, isn't it? I am also not sure where to place things such as <?xml version="1.0"?>, or <system.web>.

Any advice would be appreciated.

JorgeM 958 Problem Solver Team Colleague Featured Poster · Answer 8 · 2014-06-07T01:44:57+00:00

Correct about the dispose and close methods. I'm not by a PC to test your code.

Here is a good overview article regarding the web.config.
http://www.codeproject.com/Articles/301726/Web-config-File-ASP-NET

Blueie 13 Junior Poster · Answer 9 · 2014-06-07T02:38:54+00:00

Thanks for the link - some heavy reading there, but needs must!