How to use st.multiselect with pyodbc

🎈 Using Streamlit
1

I have a streamlit app that is connected to SQL Server database through Pyodbc where i allow the user to insert a user input and i run a select query based on the user input.

One of the input field is a multiselect where the user can choose multiple values, when i try to run the select query the system crash and display the below error:>

pyodbc.ProgrammingError: (‘42000’, “[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable #10: Cannot find data type READONLY. (2715) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180); [42000] [Microsoft][ODBC Driver 17 for
SQL Server][SQL Server]Parameter or variable ‘@P10’ has an invalid data type. (2724)”)

code:

status_list = ["option1","option2","optio3"]
name_search = st.text_input("enter name")
nickname_search = st.text_input("enter nickname")
nationality_stb =  st.selectbox("Select  Country ",rows_NA1,key = "n1" )                            
status_type_mstb = st.multiselect("Select Status",options= status_list)
                            


 sql = ('''
           select
              m.ID
             ,m.name
             , c1.NewColumn as first_nationality
             , c2.NewColumn as second_nationality
             , c3.status
           FROM [info] m LEFT JOIN nationality c1 ON c1.ID = m.nationality_1
              LEFT JOIN nationality c2 ON c2.ID = m.nationality_2
              LEFT JOIN status_type c3 ON c3.ID = m.status_type
          where
               (m.name LIKE CONCAT('%', ?, '%') OR ? = ' ')
          and
               (m.nickname LIKE CONCAT('%', ?, '%') OR ? = ' ')
          and
               ((c1.NewColumn = ? OR  ? = ' ') or (c2.NewColumn = ? OR  ? = ' '))
          and
              (c3.status = ?)
;''')
param1 = name_search
param2 = nickname_search                    
param3 = nationality_stb
param7 = status_type_mstb                                                       

rows = cursor.execute(sql,[
                     param1,param1,
                     param2,param2,
                     param3,param3,
                     param4,param4,
                     param7
  ]).fetchall()

testdf = pd.DataFrame.from_records(rows, columns = [column[0] for column in cursor.description])
st.table(testdf )
2

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.