KODOSAN! One more time for the road

Code:

SELECT CUST.CD, CUST.FNAME, CUST.LNAME, CUST.CITY, CUST.ST_CD, CUST.ZIP_CD, CUST.ADDR1, CUST.ADDR2 FROM CUST INNER JOIN ITM_PUR_HST ON CUST.CD = ITM_PUR_HST.CUST_CD WHERE CUST.CITY = '" & Text1.Text & "'" & " ITM_PUR_HST.FINAL_DT BETWEEN '" & Calendar1.Value & "' AND '" & Calendar2.Value & "' AND ITM_PUR_HST.STAT_CD = '" & "F" & "';"

lol getting SQL statement not properly ended. Looked at it over and over. You se anything I don't?


Thanks

 

I think I finally got it going because I got a not a valid month error hooray

 

Youll love this, this is there date format


2000-06-06 00:00:00

Awesome


more work

 

not a big deal. Just lop off the first 5 digits and then append it to then end of the string -1 character to drop the -

 

Code:

sql = "SELECT CUST.CUST_CD, CUST.ST_CD, CUST.FNAME, CUST.LNAME, CUST.CITY, CUST.ZIP_CD, CUST.ADDR1, CUST.ADDR2 FROM CUST, SO WHERE CUST.CUST_CD = SO.CUST_CD AND CUST.CITY ='" & Text1.Text & "' AND SO.STAT_CD ='F' AND SO.FINAL_DT BETWEEN TO_CHAR(" & date1 & ", 'yyyy-mm-dd HH:MM:SS') AND TO_CHAR(" & date2 & ", 'yyyy-mm-dd HH:MM:SS');"

That crashes VB and the oracle driver lol


so much fun

 

can you do that with SQL?


The date is part of my SQL SELECT, I keep getting errors or everything just crashes.

 

Code:

    sql = "SELECT CUST.CUST_CD, CUST.ST_CD, CUST.FNAME, CUST.LNAME, CUST.CITY, CUST.ZIP_CD, CUST.ADDR1, CUST.ADDR2 FROM CUST, SO WHERE CUST.CUST_CD = SO.CUST_CD AND CUST.CITY ='" & Text1.Text & "' AND SO.STAT_CD ='F' AND SO.FINAL_DT BETWEEN'" & date1 & "' AND '" & date2 & "';"

Wit that and the dates formatted like this

Code:

date1 = Format(Calendar1.Value, "yyyy-mm-dd")
date1 = date1 & "00:00:00"
date2 = Format(Calendar2.Value, "yyyy-mm-dd")
date2 = date2 & "00:00:00"

I keep getting a not a valid month error

 

try doing a proper inner join

sql = "SELECT CUST.CUST_CD, CUST.ST_CD, CUST.FNAME, CUST.LNAME, CUST.CITY, CUST.ZIP_CD, CUST.ADDR1, CUST.ADDR2 FROM CUST INNER JOIN SO
ON CUST.CUST_CD = SO.CUST_CD where CUST.CITY ='" & Text1.Text & "' AND SO.STAT_CD ='F' AND SO.FINAL_DT BETWEEN'" & date1 & "' AND '" & date2 & "';"

format the date afterwards.

 

I couldnt get inner join to work so I cut it out and did it how I got it now, I kept getting a not properly ended sql error until i did it like that, now its narrowed down to the date problems im having.

The date problem im having is in the SQL statement, On my connect statement it keeps shooting month errors at me.

 

I cut and paste your statement and got the same sql statement not properly ended error. Something with this doesnt like joins, If I can figure out the date ill be home free.

 

becuase you're passing the date in the wrong format.. gotta match their format.

 

Yea im trying but its not working

All of the dates in the database look like this

yyyy-mm-dd 00:00:00


I tried using

date1 = format(calendar1.value, yyyy-mm-dd)
date1 = date1 & " 00:00:00"

which makes it look exactly the same but kept getting errors or if I use to date with that it crashes VB.


man im getting frustrated with this old oracle database.

 

vb 6 crashing pissed me off so I upgraded to .net and heres what happens when I use the 00:00:00

An unhandled exception of type 'System.NullReferenceException' occurred in Project1.exe

Additional information: Object reference not set to an instance of an object.



doesnt mean much to me except frustration :/

 

what a headache

GOT IT!!!!!!!!!!

Code:

sql = "SELECT CUST.CUST_CD, CUST.ST_CD, CUST.FNAME, CUST.LNAME, CUST.CITY, CUST.ZIP_CD, CUST.ADDR1, CUST.ADDR2 FROM CUST, SO WHERE CUST.CUST_CD = SO.CUST_CD AND CUST.CITY ='" & Text1.Text & "' AND SO.STAT_CD ='F' AND SO.FINAL_DT BETWEEN TO_DATE('" & date1 & "', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" & date2 & "', 'yyyy-mm-dd hh24:mi:ss');"

Once I added the ' to the date1 and date2 it quit crashing, and my need to cry slowly slipped into the night.