database multistore

[kitjo]

2 Days Ago
different datatable update
i have a big problem yet ihave to present this project on monday
my database which i have to present next week. i have tried to play with the codes but still it has failed to retrive and update data.
i need a code that searches a word through the datatable(field ekigambo) and update the search into another table (temporary_store)it has fields
1 word1
2 lug
3 word2
4 eng
and the other table i want to store to has the same fields
the reason am doing this is because my forms can not use the first table to display information on the other table after searching it that is why i created another table that temporarilly stores the searched information so that i display it in the other form. i am working with Vb 2008
i will be greatful if you help me

Dim Connection As New SqlClient.SqlConnection("Data Source=Data Source=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\LugWdLib\LugWdLib\lugthes_source.sdf")
        Dim Command As New SqlClient.SqlCommand
        Dim sqlselect As String
        If (Connection.State = ConnectionState.Closed) Then
            Connection.Open()
        End If
        'connecting to database to seek for data
        Command.Connection = Connection
        sqlselect = "(SELECT* FROM word_name WHERE word OR ekigambo="")" & writetext.Text
        Dim check As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlselect, Connection)
        Dim word_name As DataSet = New DataSet()
        check.Fill(word_name, "word_name")
        Dim numRow = word_name.Tables("word_name").Rows.Count()


        Try
            Connection.Open()
            Command.ExecuteNonQuery()
            Connection.Close()
        Catch ex As Exception
        Finally
            Command.Dispose()
            Connection.Dispose()
        End Try




        'updating table for retrival of data
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim connection1 As New SqlClient.SqlConnection

        cmd.CommandType = System.Data.CommandType.Text
        cmd.CommandText = "UPDATE ekigambo,dest_lug,word,eng_trans WHERE "
        cmd.Connection = connection1

        connection1.Open()
        cmd.ExecuteNonQuery()
        connection1.Close()


    End Sub]

this code really failed to work. i think am not good with databases

[debasisdas]

1. Do not use reserved key words as variables(Command,Connection etc).
2. Both of your SQL is syntactically wrong.

[kitjo]

how do i need to correct it help me please

[debasisdas]

The following two SQL is syntactically wrong, and needs to be fixed.

SELECT* FROM word_name WHERE word OR ekigambo=""

UPDATE ekigambo,dest_lug,word,eng_trans WHERE

[kitjo]

to

select* from word_name where ekigambo ="" & textbox

but what am not really sure is whether this code can do what i want because i corrected and it gives me the exption

[kitjo]

Dim Connection As New SqlClient.SqlConnection("Data Source=Data Source=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\LugWdLib\LugWdLib\lugthes_source.sdf")
  
      Dim Command As New SqlClient.SqlCommand
   
      Dim sqlselect As String
   
      If (Connection.State = ConnectionState.Closed) Then
   
      Connection.Open()
   
      End If
  
      'connecting to database to seek for data
  
      Command.Connection = Connection
   
      sqlselect = "(SELECT* FROM word_name WHERE word="")" & writetext.Text
 
      Dim check As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlselect, Connection)
 
      Dim word_name As DataSet = New DataSet()
  
      check.Fill(word_name, "word_name")
  
      Dim numRow = word_name.Tables("word_name").Rows.Count()
  
       
  
       
  
      Try
  
      Connection.Open()
  
      Command.ExecuteNonQuery()
 
      Connection.Close()
  
      Catch ex As Exception
  
      Finally
  
      Command.Dispose()
  
      Connection.Dispose()
  
      End Try

[debasisdas]

Tyr using this SQL.

sqlselect = "SELECT * FROM word_name WHERE word = " & writetext.Text

[kitjo]

what i wanted was searching from two fields could this work?
sqlselect = "SELECT * FROM word_name WHERE ekigambo = '" & writetext.Text OR word='" & writetext.text
but now i need the update table from the results of the select statement
this is what i did
i declared a variable in the search form as public
dim refer as writetext.textbox
and i need to call it to the other form to display the searched results i did this because the update another table failed

[debasisdas]

try this

sqlselect = "SELECT * FROM word_name WHERE ekigambo = '" & writetext.Text & "' OR word= '" & writetext.text & "'"

You need to update the record separately in another SQL.

[kitjo]

ok thx

[kitjo]

i have really tried to handel my errors but when i search i get an exception of
key has exceeded the length of 198
i dont know if the problem is with my databse

[debasisdas]

key has exceeded the length of 198

what exactly ?