Error Sending SQL Command [Resolved]

[OUSoonerFan]

I have a SQL Command. This command will change. But the one i am testing with is this.

Select Distinct c1.Accountno, c1.Company, c1.contact, c1.Phone1 From Contact1 as c1 Inner join Contact2 as c2 On c1.accountno = c2.accountno And c1.U_STATE = 'CA' AND (c1.U_STATE <> 'ON' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'PA' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'CO' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'IL' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'AZ' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'NJ' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'NH' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'AK' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'AL' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'AR' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'BC' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'DE' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'GA' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'IA' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'KS' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'MA' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'MD' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> '' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'OH' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'NS' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'NH' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'NE' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'WI' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'UT' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'TX' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'TN' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'PR' OR c1.U_STATE IS NULL)



Now when i run that against the DB in the Query Analyzer it runs just fine and returns the records I need it too.

If I run it in my code I get this error:
ex.Message "'.' is an invalid name because it contains a NULL character."

Any ideas why I can not run that in code.
Thanks in advance

[OUSoonerFan]

can anyone assist wih this..or have any ideas....

[mudfish]

This is a string variable?
That you pass to ADO?

[OUSoonerFan]

Yes it is

[mudfish]

I ask the DBA he said to look at your () with all the "or" statement!
Hope it helps

[OUSoonerFan]

Well If i do this also it errors the same error.

Select * From Contact1 as c1 inner join Contact2 as c2 On c1.U_STATE = 'CA'

That returns the same error...

[mudfish]

Is it an sql error?
What sql are you running and what ADO?

[OUSoonerFan]

I amd running SQL Server 2000. Here is the code

Private Function PullGMRecords(ByVal sFilter As String, ByRef sGMConnection As SqlClient.SqlConnection) As DataSet
        PullGMRecords = New DataSet
        Dim GMCommand As New SqlClient.SqlCommand
 
        If sGMConnection.State = ConnectionState.Closed Then
            sGMConnection.Open()
        End If
        Dim GMAdapter As New SqlClient.SqlDataAdapter(GMCommand)
        Try
            GMCommand = sGMConnection.CreateCommand
            GMCommand.CommandText = sFilter
            GMCommand.CommandTimeout = 180
            GMAdapter.SelectCommand = GMCommand
            GMAdapter.Fill(PullGMRecords, "Contact1")
            sGMConnection.Close()
        Catch ex As Exception
            Showdebug("An error occured trying to pull GM Records." & vbCrLf & ex.Message, "PullGMRecords", "FlipperReadError")
        End Try
        Return PullGMRecords
    End Function

The statement is what is being sent along with a ref to the connection. Like i said wierd thing is If I run the select statement in the query analyzer it runs fine...but when i try to fill the dataset using the query it give the error.

[mudfish]

See if this will work!
Select Distinct c1.Accountno, c1.Company, c1.contact, c1.Phone1
From Contact1 as c1
Inner join Contact2 as c2 On c1.accountno = c2.accountno
And c1.U_STATE = 'CA'
AND c1.U_STATE Not in ('WI','UT','TX','TN','PR')


You most add the rest of the states

[OUSoonerFan]

Same error...works fine in SQL Query analyzer...craps out in the code

[mudfish]

Does it return a NULL in sql?

[OUSoonerFan]

No in the analyzer i get 3 records.

[salvelinus]

Look on the 9th line in your original post:
c1.U_STATE IS NULL) OR (c1.U_STATE <> '' OR c1.U_STATE IS NULL) OR (c1.U_STATE <> 'OH' OR
You don't have a state listed in the first comparison after the first OR.
Maybe that's what you want, but it stands out from the rest of your code.

[mudfish]

Good eye
I should have seen that!


Does it work with the state inserted in the code?

[OUSoonerFan]

nope same error

[salvelinus]

Try renaming your dataset. Maybe the function is confused because the dataset and function have the same name.

[OUSoonerFan]

tried that.....

[mudfish]

Can you post the error number?

[salvelinus]

That's a confusing SQL statement. I'm not sure you need to check for Null more than once, but it's hard to get a handle on it.
The error you posted has '.' as the problem. Is there a spot in the sql in the code where that character's inadvertently orphaned? Or, is there a limit on the length of an Sql statement (don't know myself).
Check sFilter before it's executed to make sure it's the correct text.

[OUSoonerFan]

I have tried that...here is the thing.I am pulling everything after the "On" From a Field in a database. Basically you build a filter in a CRM called goldmine . Then my software allows you to use the statement that was made in goldmine to pull records into my software. Instead of having to build another filter in my software. As far as I can see, I can not find an orphen '.' The wierd thing to me is if I copy the statement from sFilter and use it in the query anaylzer of SQL 2000 server against the database It runs fine. So why would it not run fine in code. I am literally copy..> paste intot the analyzer.

[salvelinus]

Well, you got me. As a test, try pasting the SQL into your function and use that instead of sFilter. If it runs then, the problem must occur in goldmine, whatever that is.
You could run sFilter through a parsing function to remove any NULL characters, maybe.

[OUSoonerFan]

Ok here is a wierd thing.
If i do this.

FilterAfter = "Select Distinct c1.Accountno, c1.Company, c1.contact, c1.Phone1  From Contact1 as c1 Inner join Contact2 as c2 On c1.accountno = c2.accountno And  " & FilterAfter

with FilterAfter being a string variable holding the rest of the statement. I get the error. But IF I do this.

FilterAfter = "Select Distinct c1.Accountno, c1.Company, c1.contact, c1.Phone1  From Contact1 as c1 Inner join Contact2 as c2 On c1.accountno = c2.accountno And  c1.U_State = 'CA'"

It will work.
Now the c1.U_State = 'CA' is the same as the filterafter it is just not being held inside the variable. Any ideas on why this would do that?

[salvelinus]

In your first example, you're giving FilterAfter a value & concatenating it w/itself. Not sure what order the operations occur, but if FilterAfter has no value before you're first statement, or empty string, that's what you're concatenating to the SELECT part of the statement. If FilterAfter does have a value, you may be overwriting it.
Try FilterAfter = <Select part>
FilterAfter += <rest of statement>

[OUSoonerFan]

nope...same error when i tried that. How would I parse it from all NULL Chars....not spaces but anything null.

[salvelinus]

Replace() any NULLs with an empty string. Actually, not sure Replace() works with NULL, but you could try it. Or loop through character by character, getting rid of NULLs. You may need Edneiss or Pirate

[mudfish]

Did you get this?

[OUSoonerFan]

ya i tried that over the weekend. Still showing the same error.

[mudfish]

The only thing I can think of is a NULL is being return from sql and in VB6 you would get an error number 94 ( I think that the number ) putting the NULL value in the record set!

[OUSoonerFan]

Ok found the problem....in the part of the SQL statement being sent from goldmine. It is giving me some trailing null chars. not spaces but chars. the trim is not working to remove them. How can I remove all the null characters out of the string?

[Andy]

have you tried converting your apostrophes into another character? That caused me a day's work of debugging once myself.

an apostrophe causes problems in DB queries.

[OUSoonerFan]

I got it. I basically split the SQL String into a char array and then looked for the asc(0). Which is a null value. Using the IEnumerator. It seems to work fine now. The apostrophies can not be changed when exceuting the SQL Query Statement. They are what tells the SQL Server that it is a string.

Thanks for everyones help.
Any time I have a problem you guys have never let me down. =)

[salvelinus]

Yay, way to go!
Not to toot my own horn, but I did question the return from goldmine, and suggested parsing it for NULL (although didn't give you a good example of how to do it, sorry).
That was a really puzzling problem.

[OUSoonerFan]

yes you did....after looking back at post over the weekend I realized the parsing for Null that you suggested was the only thing I have not tried.....so much help was offered i missed that one...lol...

Thanks Again