Comments by mzrax - CodeProject

mzrax 18-Jun-17 14:39pm View

It's impossible to help me if you don't know what you're talking about.

Then how do you get the center point of a polygon from a query of selected points?

mzrax 25-Mar-14 21:54pm View

digimanus

Thanks for that little bit. I was able to take what you wrote and modified it a little to work for me. thanks for that. Here's what I did.

case when (select dateadd(day,0,datediff(dd,0,dayworked)) from tblDaysWorked where .... ) is not null then 1 else 0 end

Now I'm still trying to figure out my grouping by. I think I'm going to have rewrite the pivot query.

Thanks again

mzrax 25-Mar-14 21:41pm View

The print @DynamicPivotQuery command allows SQL Server users to see the SQL code in the Messages tab. It allows you to see what the full query looks like.

mzrax 24-Mar-14 22:35pm View

The problem is that I'm using a dynamic pivot table because there's quite a few names and they change all the time based on the dates. Thanks though

mzrax 24-Mar-14 20:57pm View

Thanks, I knew it was something like this but just wasn't able to figure out the part like what you did with ON t1.RowNo = t2.RowNo-1.

Thanks

M

mzrax 24-Mar-14 20:55pm View

Thanks for the links they definitely have answered other questions that I had. I knew there was a page like the Microsoft link that you put but I could never find it.

Thanks

M

mzrax 30-Oct-13 11:03am View

When I run the code in Console mode it works fine and I can put in any string. So why is it in my program I am now getting the same exception but now it says Length cannot be less than zero. Parameter name: length

mzrax 30-Oct-13 10:50am View

I've tried this and I still get the same exception.

string iText1 = xText1.Substring(0, 23);
string iText2 = xText1.Substring(24, xText1.Length);

mzrax 30-Oct-13 10:46am View

I'm sorry but I don't see your solution. What do you mean?

mzrax 8-Oct-12 22:58pm View

I have to say your solution does the trick too. Again, I'm stumped because I thought I was going to take my project in a very specific way and now that both of you have given excellent solutions I'm going to have to rethink everything.

I just wanted to say that I added an ID column and did the order by the ID and therefore I shouldn't have to worry about duplicates.

I wanted to thank you too and a big thumbs up. Now I have to figure out how to integrate this with my C# project.

Thanks
M

mzrax 8-Oct-12 22:33pm View

This is exactly what I was looking for and now that I see it I can't believe I didn't think about it.

Here's the question that I have. Is there a way to do this with C# and update the table? Would I use a foreach loop? I was hoping to do this with more C# but using SQL has definitely opened the door to another possibility. Because there's so much more to my problem and what I'm trying to do, you solution has definitly produced more questions than answers in my head.

The one thing I do have to say is a big thanks. Big thumbs up.

Thanks
M

mzrax 7-Oct-12 19:27pm View

I've added more to my question. Any ideas??

mzrax 7-Oct-12 19:27pm View

I know that I have to loop and then do a calculation but I don't know how. Can you point me in the right direction or do you have an example??

mzrax 18-Jul-12 23:01pm View

I've been able to get the table into a dataset from one DB but how do I get it from that dataset and insert it into anther DB or am I off the mark?? What would it look like??

mzrax 18-Jul-12 22:27pm View

Yes Tesxt is the actual column name. It was a mistype from the original programmer.

How do you put everything in memory and then insert it into another DB? I've been able to put it in memory from one DB but I've never been able to get it into another DB.

mzrax 31-May-12 9:19am View

VJ Reddy

Thanks

I've taken what you've done to look like this.

string filePath = @"C:\test.csv";
string kmlInput = @"-86.36762, 37.31916 , 0 -86.43890,37.31916,0 -88.96934,32.11572,0 -84.51434,32.68596,0 -82.87490,35.85792,0 -86.36762,37.31916,0 ";
string csvOutput = Regex.Replace(kmlInput,
@"(?<=[^,]+)\s+(?=[^,]+)", "\n",
RegexOptions.CultureInvariant);
System.IO.StreamWriter objWriter;
objWriter = new StreamWriter(filePath, true);
objWriter.WriteLine(csvOutput);
objWriter.Close();

This is great. I really appreciate it.

Thanks Again.

mzrax 7-Nov-11 16:15pm View

That's AWESOME!!!!!

Thanks for the help. I just had to add one line to add another column but it worked. You can't imagine how much of a relief it is to get this done. There's only one more little thing to figure out but based on this code, I think I'll figure it out.

Thanks Again

mzrax 7-Nov-11 15:21pm View

That's right because of the PK ID has to have a unique identifier. Any words would do. How about something as simple as the words Blank1, Blank2, Blank3 and so on. That way when they show up they mean nothing and should the legacy program want to overwrite it, it will.

Thanks

mzrax 7-Nov-11 10:02am View

The simple part of the table is all Access based
Column 1 -->
Name: WPID
DataType: Number
Field Size: Integer

Column 2-->
Name: ID
DataType: Text
Field Size: 12
PrimaryKey

The rest of the columns don't matter but ID column is tied to many things while my WPID that I've created altering the table has to be int in order to query some legacy tables in legacy software that can't be changed because I don't have the code for the program. I can see the tables but not the program code.

Basically I want to be able to do what you've done with the code you wrote but do it for a none PrimaryKey column. I also want it to insert the numbers in new rows after it's done the update. What I was thinking was compare what's already in the table and do an update statement and then do an insert statement for the remaining number rows required with the numbers that I picked.

The WPID column is the one that I have to put the numbers in.

Does this help??

mzrax 6-Nov-11 22:20pm View

Because the table that I'm working with does not have a incrementing column where I can control the numbers that have to go in there. The numbers that I want to put in there will change because of other variables that the table is connected with.

I'm pretty sure it can be done one of two ways either I do an insert first with the numbers counting and then I do an update to the table with the other information that has to go in there or I insert everything and then somehow I do the incrementing with some kind of update statement.

What do you think?? Think it's possible??

mzrax 6-Nov-11 22:12pm View

Bala

I figured something out. The column in the table has to be the primary key. Is there any way that I could do the same thing for a none primary key column? My primary key has to be on another column in the table.

My other question is right now the count only goes for the number of rows that are in the table. What would I have to do to have the count go larger than what's already in the table? For example if I have 60 rows of data in the table right now the count would go from 1 to 60 but my end result really has to be 1 to x, x being whatever number I choose. So there has to be an insert after the update.

I know it's complicated but I've got some really weird restrictions based on other legacy databases and tables that can't be updated or changed for a few years to come. I'm just trying to make this work.

First thing I have to ask, how to I make this work for a non primarykey column??

The second would be how do I make this work to insert more numbers than rows?

Thanks

mzrax 6-Nov-11 18:59pm View

Hello Bala

I've run your code and it seems to be almost working. I run the code and step into each loop and the number is changing but then it skips over the line that says thisAdapter.Update and gives me an error of

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

This error message is a new one for me.

What does this mean?

mzrax 8-Oct-11 16:03pm View

Bala

Thanks Again. Sure I'll vote for you. I figured out the problem. I have other code that takes the decimal degrees in another table and copies them into the tblMGRSAdded where I've added the column MGRS.

What I did was in the SQL Insert Code from tblA to tblMGRSAdded I put the round(Latitude,9) as Latitude and round(longitude,9) as Longitude there so it only carried over the point with 9 decimal points. After that your code runs and then does the conversion in a slick way.

I still don't know why the conversion works with all 13 decimal points in the console window when you run it seperate but when you when you run it with the table it only recognizes 9 decimal places.

Thanks Again

mzrax 8-Oct-11 14:12pm View

That's awesome Bala. You can't imagine how deeply thank you I am. I found something out. The stuff I've been working with is has 13 decimal points. When I remove some numbers to limit the decimal point to 9 it works. How would I limit the number of decimal points to only 9. Is there something that could look for the decimal point and then could 9 decimal points?

I can't say it enough. Thanks.

mzrax 8-Oct-11 13:59pm View

Deleted

I'm using Access 2003. I've removed all the other columns and there's only 3 columns. I'm using 4.0 .net Framework VS C# 2010. I'm not getting any error messages.

Here's my latest code. There's very little difference from your code.

private void button1_Click(object sender, EventArgs e)
{
try
{
OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\project\local.mdb");
thisConnection.Open();
OleDbCommand thisCommand = thisConnection.CreateCommand();
thisCommand.CommandText = "SELECT * from tblMGRSAdded";
OleDbDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
Geographic geoF = new Geographic(Convert.ToDouble(thisReader["longitude"]), Convert.ToDouble(thisReader["latitude"]));
MGRS mgrs = (MGRS)geoF;
string mgrsStr = mgrs.Zone + mgrs.Band + " " + mgrs.Grid + " " +
mgrs.EastString.Substring(0, 4) + " " + mgrs.NorthString.Substring(0, 4);
OleDbCommand thisUpdateCommand = thisConnection.CreateCommand();
thisUpdateCommand.CommandText = "update tblMGRSAdded set MGRS='" + mgrsStr + "' where Latitude=" +
thisReader["Latitude"] + " and Longitude = " + thisReader["Longitude"];
thisUpdateCommand.ExecuteNonQuery();
MessageBox.Show(mgrsStr);
}
thisReader.Close();
thisConnection.Close();

MessageBox.Show("OK");
Application.Exit();
}
catch (Exception ex)
{

MessageBox.Show(ex.Message);
}
}

mzrax 8-Oct-11 13:26pm View

Thanks Andre

mzrax 8-Oct-11 13:25pm View

Hello Bala

I think you're close. I've tried a couple of things and your code shows all the conversions in the code that says MessageBox.Show(mgrsStr). They all show up there one at a time. It's where I was except I did it a little different. The update is still not working.

Here's my table in Access tblMGRSAdded. Latitude and Longitude are numbers field size double and the MGRS column is text.

Latitude Longitude MGRS
34.15908051 39.00210953
30.13175774 26.67066956
13.70171642 78.88327789
-25.840868 126.9972839
-21.63534737 126.1775589
45.65699768 -66.35175323
48.68639755 -85.02711487
51.3950386 -73.58667755
46.2272377 -74.29947662
42.03953934 -89.89198303

I haven't been able to run your code to update the MGRS column.

Any help would be much appreciated.

Thanks

Comments by mzrax (Top 27 by date)