locked
error in exceuting ---cannot find table 0 RRS feed

  • Question

  • User-1474096950 posted

    i have 2 tables from where the data is taken
    table 1 and table 2
    table1 has user, country, city
    table2 has country,city

    wht i need is

    1) selecting user with condition of country and city dropdownlists = the output can be multiple & after exceution of he query
        need to get the users


    2) selecting fields from table2 from the grid  depending on the id( i am getting the selected multiple id from the grid.== uid)

    then inserting the user , country , city in table1  with country, city selected fom table 2

    if table1 has the combination of user , country , city then update else insert

    i have written the code but not getting the desired output
    if i use in the below way ie line1 & line2 then i get no error but only the last record in for loop  gets affected
    and if i use line2 and then line1 i get

     

    M_SQLSTR = "SELECT user FROM table1 " _
                  & " WHERE country='" & drpcountry.SelectedItem.Text & "' AND city='" & drpcity.SelectedItem.Text & "'"
           USERREADER = cmdMain.ExecuteReader()

            If USERREADER.HasRows Then
                While USERREADER.Read
                   dim uname= USERREADER("user").ToString.Trim
      msgbox(uname)


               sqlstr= "select [country],[city]  FROM table2 WHERE   ID in( " & UID & ") order by country"
                       da = New sql.sqlDataAdapter(sqlstr, cn)
         da.Fill(ds, "table2")

     

                        If ds.Tables(0).Rows.Count > 0 Then
                            Dim count = ds.Tables(0).Rows.Count
                       

                            For i = 0 To count - 1
                              
                                Dim datacountry = ds.Tables(0).Rows(i).Item("country").ToString()
                                Dim datacity = ds.Tables(0).Rows(i).Item("city").ToString()
                              

                             
                               sqlstr= "IF EXISTS (SELECT user, country,city FROM table1 " _
                                           & " WHERE user='" & uname & "'" _
                                           & " AND country='" & datacountry & "' AND city='" & datacity & "')" _
                                           & " UPDATE table1 set user='" & uname & "' " _
                                           & " WHERE country='" & datacountry & "' AND city='" & datacity & "' " _
                                           & " Else " _
                                           & " INSERT INTO  table1(user,country,city) " _
                                           & " VALUES ('" & uname & "','" & datacountry & "','" & datacity & "')"
                            
                            Next          ''''''' line1
                           
       da = New sql.sqlDataAdapter(sqlstr,  cn)                                             ''''''''line2
          da.Fill(ds, "table1")                                                               ''''''''line2
                        End If
                  

                End While
            End If
    End If

    Wednesday, May 13, 2009 4:29 AM

Answers

  • User1716267170 posted

    Hi svibuk,

    This question is the same with this one. Thus I suggest you not post a duplicated question so that we can help you better.

    For this problem, I firstly suggest you to use SQL Server Profiler to track the sql queries in database, and see whether it gets expected data.

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 15, 2009 1:03 AM