get value from base into database

Go to solution

Press Ctrl / CMD + C to copy this to your clipboard.

Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

Dan 1288 posts 3921 karma points c-trib

Aug 31, 2010 @ 19:03

Hi,

For my hundredth question of the day... I have a base URL which is now correctly accepting a parameter. What I want to do is to get that parameter into a new database table I've created.

Here's what I have so far:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MyBaseProject
{
 public class UpdateValue
  {
     public static string Hello(string name)
       {
         SqlConnection conn;
           SqlCommand comm;
          string connectionString = ConfigurationManager.ConnectionStrings["umbracoDbDSN"].ConnectionString;
            conn = new SqlConnection(connectionString);
           comm = new SqlCommand("INSERT INTO budgetData (memberguid) VALUES (@memberGUID);", conn);
         comm.Parameters.Add("@memberGUID", System.Data.SqlDbType.Text);
           comm.Parameters["@memberGUID"].Value = name;
          try
           {
             conn.Open();
              comm.ExecuteNonQuery();
               return "Item added: " + name;
         }
         catch
         {
             return "Item caught: " + name;
            }
         finally
           {
             conn.Close();
         }
     }
 }
}

I obviously want to use the connection settings for the umbraco database in web.config - hence I've tried using "umbracoDbDSN" in the connection settings.

However, this is erroring:

System.NullReferenceException: Object reference not set to an instance of an object.
at MyBaseProject.UpdateValue.Hello(String name) at line 17, which is this line:

string connectionString = ConfigurationManager.ConnectionStrings["umbracoDbDSN"].ConnectionString;

Can anyone suggest a solution?

Thanks all.

Copy Link

Morten Bock 1867 posts 2140 karma points MVP 2x admin c-trib

Aug 31, 2010 @ 19:14

You should use the SqlHelper in umbraco. Something like this:

ISqlHelper sqlHelper = umbraco.BusinessLogic.Application.SqlHelper;
sqlHelper.ExecuteNonQuery("INSERT INTO budgetData (memberguid) VALUES (@memberGUID);",
    sqlHelper.CreateParameter("@memberGUID", name));

Copy Link

Matt Brailsford 4125 posts 22223 karma points MVP 9x c-trib

Aug 31, 2010 @ 19:15
0
Hey Dan,

The connection string is actualy stored in appSettings rather than the connectionStrings collection, however you are probably better off using the Umbraco static var
```
GlobalSettings.DbDSN
```
Many thanks

Matt
Copy Link
Dan 1288 posts 3921 karma points c-trib

Aug 31, 2010 @ 20:52

0

Thanks Morten/Matt. What namespaces should be referenced for the above?

Copy Link
Matt Brailsford 4125 posts 22223 karma points MVP 9x c-trib

Aug 31, 2010 @ 20:57
0
Hey Dan,
```
GlobalSettings.DbDSN
```
Is just in the umbraco namespace.

Mortens method will require
```
umbraco.BusinessLogic.Application
```
Matt
Copy Link

Dan 1288 posts 3921 karma points c-trib

Aug 31, 2010 @ 21:31

Thanks Matt. I've tried Morten's example, but get an error "the type or namespace name ISqlHelper could not be found". I'm also unsure of the context in which to GlobalSettings.DbDSN. I've tried this, but clearly I'm doing something wrong:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using umbraco;

namespace MyBaseProject
{
        public class UpdateValue
        {
                public static string Hello(string name)
                {
                        SqlConnection conn;
                        SqlCommand comm;
                        //string connectionString = ConfigurationManager.ConnectionStrings["umbracoDbDSN"].ConnectionString;
                        conn = new SqlConnection(GlobalSettings.DbDSN);
                        comm = new SqlCommand("INSERT INTO budgetData (memberguid) VALUES (@memberGUID);", conn);
                        comm.Parameters.Add("@memberGUID", System.Data.SqlDbType.Text);
                        comm.Parameters["@memberGUID"].Value = name;
                        try
                        {
                                conn.Open();
                                comm.ExecuteNonQuery();
                                return "Item added: " + name;
                        }
                        catch
                        {
                                return "Item caught: " + name;
                        }
                        finally
                        {
                                conn.Close();
                        }
                }
        }
}

Appreciate your patience on this Matt!

Copy Link

Matt Brailsford 4125 posts 22223 karma points MVP 9x c-trib

Aug 31, 2010 @ 21:45

0

Hey Dan,

What you have looks like it should work, you are using the GlobalSettings.DbDSN property correctly.

What error are you receiving with the above?

Matt

Copy Link
Dan 1288 posts 3921 karma points c-trib

Aug 31, 2010 @ 22:02

0

Actually, my bad - I'd not commented the old connection out. Sorted, so thanks very much indeed!

Copy Link
is working on a reply...

Please Sign in or register to post replies