C# mysql parameters value is null?

Question

So I'm trying to pass in parameters for point strings, all my columns are marked not null in my DB however when i try to run the below code it tells me column Lat cannot be null. Is there something i'm overlooking?

string latParameter = "'Point(" + latString + ")'";
            string lonParameter = "'Point(" + lonString + ")'";

            string mySQLfinishedProcessing = " insert into zipcodes " +
              "set zipcode = '" + zipcodeString + "'" +
              ",State = '" + StateString + "'" +
              ",City = '" + CityString + "'" +
              ",lat = GeomFromText(@latParam)"+
              ",lon = GeomFromText(@lonParam)"+
            ",StateCode = '" + StateCodeString2 + "'";

            //File.AppendAllText(@"C:\sqlupdater.txt", mySQLfinishedProcessing + ";\n");

            MySqlConnection configCON = new MySqlConnection(SQLStringClass.zipCONString);
            MySqlCommand CounterLogs = new MySqlCommand(mySQLfinishedProcessing, configCON);
            CounterLogs.Parameters.Add(new MySqlParameter("@latParam", latParameter));
            CounterLogs.Parameters.Add(new MySqlParameter("@lonParam", lonParameter));
            configCON.Open();
            CounterLogs.ExecuteNonQuery();
            configCON.Close();

yes, latString and lonString has values.

string latParameter = 'Point(40.92233)' string lonParameter = 'Point(-72.63708)'

okay it appears the be cause there is only a single reference to a point, i change string latParameter = 'Point(40.92233 12)' it takes just fine. But do I really want to put both my lat/lon in 1 point column?

ugh nevermind that works fine in MySQl Edit, but in C# it still doesnt resolve the issue. The problem is the string lonParameter needs to contain a ;

here is the correct code.

 string latParameter = "Point(" + latString + " " +lonString+");";

            string mySQLfinishedProcessing = " insert into zipcodes " +
              "set zipcode = '" + zipcodeString + "'" +
              ",State = '" + StateString + "'" +
              ",City = '" + CityString + "'" +
              ",Location = GeomFromText(@latParam)"+
            ",StateCode = '" + StateCodeString2 + "'";

            //File.AppendAllText(@"C:\sqlupdater.txt", mySQLfinishedProcessing + ";\n");

            MySqlConnection configCON = new MySqlConnection(SQLStringClass.zipCONString);
            MySqlCommand CounterLogs = new MySqlCommand(mySQLfinishedProcessing, configCON);
            CounterLogs.Parameters.Add(new MySqlParameter("@latParam", latParameter));
            configCON.Open();
            CounterLogs.ExecuteNonQuery();
            configCON.Close();

Well, what is the value of latString etc? Also, why aren't the other strings parameters? Come to mention it, why can't the call to Point() use parameters? — Marc Gravell
– Marc Gravell, Commented Jun 14, 2011 at 17:12
why do i need to make them all parameters? the other values are just int's and varchar? — user222427
– user222427, Commented Jun 14, 2011 at 17:13
@mike in your smysqlfinishedprocessingstring your lat = is lowercase maybe u need to do Lat = instead cause columns are case sensitive in SQL — Laurence Burke
– Laurence Burke, Commented Jun 14, 2011 at 17:13
@Mike actually I was thinking of SQL injection. concatenating values is very very dangerous. — Marc Gravell
– Marc Gravell, Commented Jun 14, 2011 at 17:14

Amir Ismail · Accepted Answer · 2011-06-14 17:15:27Z

1

you are writing insert statment with wrong syntax. it must be

string mySQLfinishedProcessing = " insert into zipcodes(zipcode ,State ,City ,lat ,lon ,StateCode ) values ( " +
          "'" + zipcodeString + "'" +
          ",'" + StateString + "'" +
          ",'" + CityString + "'" +
          ",GeomFromText(@latParam)" +
          ",GeomFromText(@lonParam)" +
        ",'" + StateCodeString2 + "')";

answered Jun 14, 2011 at 17:15

Amir Ismail

3,8713 gold badges22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user222427 Over a year ago

No that formatting is fine, i make these values allow null and everything inserts right, i do my inserts like this allt he time

Kyle W · Accepted Answer · 2011-06-14 17:16:06Z

0

If GeomFromText is a db function, it's probably returning null for whatever you're passing in.

answered Jun 14, 2011 at 17:16

Kyle W

3,76222 silver badges36 bronze badges

Collectives™ on Stack Overflow

C# mysql parameters value is null?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related