Error connecting to Sql DATABASE

[radubalmus]

Hy I get this error connecting to my sql database "Run-time error '-2147217900 (80040e12): [Microsoft][ODBC Sql Server Driver][Sql Server]The text, ntext, and images data types cannot be compared or sorted, except using IS NULL or LIKE operator"
This is the code:

Public Sub initPCR()
Dim ordersheet1 As Worksheet
Dim query As String
Dim pcrNr, paragraphId, samples, partsvizual, partsdimensional, partstests As Integer
Dim table, partname, partnoindex, editieData, desen, pozitie, descriere, mijlocControl, observatii, test, caract, SPCs As String
Dim partno As String
Dim mesaj As Single

Set ordersheet1 = Application.ActiveWorkbook.Worksheets("New Incoming Test Order")

'get entries
table = "PCR"

    If ordersheet1.Range("C9").Text <> "" Then
        partno = ordersheet1.Range("C9").Text

        'Assemble the query
      
        query = "SELECT * FROM " + table + " WHERE partno=" + CStr(partno)  
        'Get results
        Dim MyRecordset As ADODB.Recordset
        Dim MyConnection As ADODB.Connection
        Dim count As Integer
        Set MyRecordset = Module8.selectFromDB(query)
            MyRecordset.MoveFirst
            ordersheet1.Range("E9") = MyRecordset.Fields(0).Value
            ordersheet1.Range("H9") = MyRecordset.Fields(1).Value
            ordersheet1.Range("K5") = MyRecordset.Fields(2).Value
            ordersheet1.Range("K7") = MyRecordset.Fields(3).Value
            ordersheet1.Range("K9") = MyRecordset.Fields(4).Value
            ordersheet1.Range("K11") = MyRecordset.Fields(5).Value
            ordersheet1.Range("B11") = MyRecordset.Fields(6).Value
            ordersheet1.Range("D11") = MyRecordset.Fields(7).Value
            ordersheet1.Range("H117") = MyRecordset.Fields(8).Value
            count = 14
            Do Until MyRecordset.EOF
                ordersheet1.Cells(count, 1) = MyRecordset.Fields(9).Value
                ordersheet1.Cells(count, 2) = MyRecordset.Fields(10).Value
                ordersheet1.Cells(count, 3) = MyRecordset.Fields(11).Value
                ordersheet1.Cells(count, 4) = MyRecordset.Fields(12).Value
                ordersheet1.Cells(count, 8) = MyRecordset.Fields(13).Value
                ordersheet1.Cells(count, 9) = MyRecordset.Fields(14).Value
                ordersheet1.Cells(count, 10) = MyRecordset.Fields(15).Value
                ordersheet1.Cells(count, 12) = MyRecordset.Fields(16).Value
                MyRecordset.MoveNext
                count = count + 1
            Loop
            MyRecordset.Close
            Set MyRecordset = Nothing
            Set MyConnection = Nothing
    End If
    Exit Sub

Public Function selectFromDB(query As String) As ADODB.Recordset
Dim MyConnection As ADODB.Connection
Dim MyRecordset As ADODB.Recordset
Dim sqlConnString As String

sqlConnString = getSqlConnString
Set MyConnection = CreateObject("ADODB.Connection")
Set MyRecordset = CreateObject("ADODB.Recordset")

With MyConnection
    .ConnectionString = sqlConnString
    If Err.Number <> 0 Then
        MsgBox "SQLServer Connection could not be established."
        Err.Clear
    End If
    .Open
End With
[COLOR=red]MyRecordset.Open query, MyConnection[/color]

Set selectFromDB = MyRecordset

End Function

Any ideas pls cause i run out (.
thanks



There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[xlbo]

The error message may be a bit misleading. I may be wrong but I though that text constants had to be enclosed in ' ' so:

query = "SELECT * FROM " + table + " WHERE partno='" + CStr(partno) & "'"

might be worh a shot

Have you tried plugging the SQL string straight into query analyser or running it directly on SQL Server to check that it is a valid query ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[radubalmus]

i tried olso
... WHERE partno='" + partno + "'"
i think the query is valid
because it worked before with acces!!
i swiched to sql database and i am just modifing the connection(in rest it should be the same "almost")!!!!
and i have another query looking very similar, the only diffrence is that in this one the table name is with uppercase(does it matter???)

There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[Andrzejek]

Did you try:

[blue]Debug.Print query[/blue]
MyRecordset.Open query, MyConnection

Maybe something as simple as missing space between 'partno' and '='


Have fun.

---- Andy

 

[xlbo]

Access has different syntax and ways of working with certain data types than a SQL Server database. try running the SQL through query analyser or SQL Server Enterprise Manager to test its validity

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[radubalmus]

ok
thanks guys
i have reached the conclusion that you are right: it is about the query, in the WHERE clause..





There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[radubalmus]

i am building a query depending if some cells(my interface is excel) have text or not..
it is working, but when i am trying to read from a certain field it gives me an error "80040e14"
ex: So the following works:

SELECT testorder,shortyear,orderdate,requester,tel,weno,partno,partname,samples,costcat,status,product_rating FROM test_orders WHERE orderdate>=(1/1/2007) AND (costcat='2.4 CoP')

or

SELECT testorder,shortyear,orderdate,requester,tel,weno,partno,partname,samples,costcat,status,product_rating FROM test_orders WHERE orderdate>=(1/1/2007) AND (testtype='ES Tests')

but when this query is build i have the error:

SELECT testorder,shortyear,orderdate,requester,tel,weno,partno,partname,samples,costcat,status,product_rating FROM test_orders WHERE orderdate>=(1/1/2007) AND (status='Open')

is Sql(database or language) sezitive to some words??? or what could be the problem


There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[xlbo]

There are sensitive words in SQL but that is for pretty much all SQL. A field called "Status" shouldn't really give you an issue.

However, I would be very surprised if the query really is working because you are tesating a date field against text. AFAIK, SQL expects # # around dates. (1/1/2007) will be read literally as 1 divided by 1 divided by 2007.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[radubalmus]

i don't think it is about the dates
here is where i am readind the dates

    'Get dates
    If querysheet.Cells(8, 7).Text = "" And querysheet.Cells(8, 8).Text = "" And querysheet.Cells(8, 9).Text = "" Then
        statestartdate = 0
    Else
        statestartdate = 1
    End If
    
    If querysheet.Cells(8, 13).Text = "" And querysheet.Cells(8, 14).Text = "" And querysheet.Cells(8, 15).Text = "" Then
        stateenddate = 0
    Else
        stateenddate = 1
    End If
    
    If statestartdate = 0 And stateenddate = 0 Then
        startdate = #1/1/2007#
        'Format$(cDate("31.12.1999"),"\#mm\/dd\/yyyy\#")
        datetale = " orderdate>=(" + CStr(startdate) + ")"
    ElseIf statestartdate = 1 And stateenddate = 0 Then
        startdateconv = Right(Application.ActiveSheet.Cells(8, 7).Text, 2) + " " + Right(Application.ActiveSheet.Cells(8, 8).Text, 3) + " " + Right(Application.ActiveSheet.Cells(8, 9).Text, 4)
        startdate = CDate((startdateconv))
        datetale = " orderdate>=(#" + CStr(startdate) + "#)"
    ElseIf statestartdate = 0 And stateenddate = 1 Then
        enddateconv = Right(Application.ActiveSheet.Cells(8, 13).Text, 2) + " " + Right(Application.ActiveSheet.Cells(8, 14).Text, 3) + " " + Right(Application.ActiveSheet.Cells(8, 15).Text, 4)
        enddate = CDate((enddateconv)) + CDate(#11:59:00 PM#)
        datetale = " orderdate<=(#" + CStr(enddate) + "#)"
    ElseIf statestartdate = 1 And stateenddate = 1 Then
        startdateconv = Right(Application.ActiveSheet.Cells(8, 7).Text, 2) + " " + Right(Application.ActiveSheet.Cells(8, 8).Text, 3) + " " + Right(Application.ActiveSheet.Cells(8, 9).Text, 4)
        startdate = CDate((startdateconv))
        enddateconv = Right(Application.ActiveSheet.Cells(8, 13).Text, 2) + " " + Right(Application.ActiveSheet.Cells(8, 14).Text, 3) + " " + Right(Application.ActiveSheet.Cells(8, 15).Text, 4)
        enddate = CDate((enddateconv)) + CDate(#11:59:00 PM#)
        datetale = " orderdate>=(" + CStr(Format$(startdate, "\#mm\/dd\/yyyy\#")) + ") AND orderdate<=(" + CStr(Format$(enddate, "\#mm\/dd\/yyyy\ hh\:mm\#")) + ")"
    End If
    'End get dates

There are simple solutions for almost every problem! The hard part is to see them!!!!