SQlite

[ceaser]

Hi

I need help again!:sign0085:

In a road design I have a database which holds the horizontal alignment, vertical alignment, widths, crossfalls, batters, etc. With the batters I have the following parameters: which side of the road (either left or right), the condition (cut or fill), the batter slope for the condition, the stake (the distance along the alignment, etc.

My erase routine is as follows:

Sub EraseBat_Click
If Combobox3.Text<>"" AND Combobox4.Text<>"" AND Combobox6.Text<>"" Then
i=Msgbox ("Are You Sure To Erase The Batter At SV " & Combobox6.Text & "? ","Erase Batter",cMsgboxYesNo,cMsgboxQuestion)
If i=cYes Then
cmd.AddParameter("Value1")
cmd.CommandText = "Delete From Batter Where Stake = @value1" cmd.SetParameter("Value1",Combobox1.Text)
cmd.ExecuteNonQuery
ComboBox2.Text="" :ComboBox3.Text="" :ComboBox4.Text="" :ComboBox5.Text="" :ComboBox6.Text=""
Textbox13.Text="" :Textbox15.Text="" :Textbox16.Text="" :Textbox17.Text=""
ComboBox3.Focus
End If
Else
Msgbox ("Select A Valid Batter To Erase", "Erase Batter", cMsgboxOK, cMsgboxExclamation)
Combobox6.Focus
End If
End Sub

What I would like to do is to erase the design batter which has more than one condition which is true....i.e. delete where the stake value, side and condition meets a criteria.

Thanks
Michael

 

[Erel]

You can use expressions with AND or OR to combine several conditions.

DELETE FROM Batter WHERE (Stake = @value1 AND side = @value2) OR (Stake = ...)

 

[ceaser]

Thanks Erel!:sign0188:

Regards
Michael

 

[ceaser]

Hi Erel

I am still doing something wrong!:sign0148:

Herewith my code:

Sub EraseBat_Click
If Combobox3.Text<>"" AND Combobox4.Text<>"" AND Combobox6.Text<>"" Then
i=Msgbox ("Are You Sure To Erase The Selected Batter?","Erase Batter",cMsgboxYesNo,cMsgboxQuestion)
If i=cYes Then
cmd.AddParameter("Value1","Value2","Value3","Value4")
cmd.CommandText = "Delete From Batter Where Side = @value1 and Cond = @value2 and Set1 = @value3 and Stake = @value4"
cmd.SetParameter("Value1",Combobox3.Text,"Value2",Combobox4.Text,"Value3",Combobox2.Text,"Value4",Combobox6.Text)
cmd.ExecuteNonQuery
ComboBox2.Text="" :ComboBox3.Text="" :ComboBox4.Text="" :ComboBox5.Text="" :ComboBox6.Text=""
Textbox13.Text="" :Textbox15.Text="" :Textbox16.Text="" :Textbox17.Text=""
ComboBox3.Focus
End If
Else
Msgbox ("Select A Valid Batter To Erase", "Erase Batter", cMsgboxOK, cMsgboxExclamation)
Combobox6.Focus
End If
End Sub

Thanks
Michael

 

[Erel]

Please describe what is going wrong (there is an error, nothing happens, all the database gets deleted...)

I recommend you to use the 'code /code' tags. It makes the post clearer.

There is an extra space in this line:

cmd.AddParameter("Value1","Value2","Value3",[COLOR=Red]"Value 4"[/COLOR])

 

[ceaser]

Hi Erel

Nothing happens:sign0148:

Here is the code again:

Sub EraseBat_Click
   If Combobox3.Text<>"" AND Combobox4.Text<>"" AND Combobox6.Text<>"" Then
      i=Msgbox ("Are You Sure To Erase The Selected Batter?","Erase Batter",cMsgboxYesNo,cMsgboxQuestion)
      If i=cYes Then
         cmd.AddParameter("Value1","Value2","Value3","Value4")
         cmd.CommandText = "Delete From Batter Where Side = @value1 and Cond = @value2 and Set1 = @value3 and Stake = @value4"
         cmd.SetParameter("Value1",Combobox3.Text,"Value2",Combobox4.Text,"Value3",Combobox2.Text,"Value4",Combobox6.Text)
         cmd.ExecuteNonQuery
         ComboBox2.Text="" :ComboBox3.Text="" :ComboBox4.Text="" :ComboBox5.Text="" :ComboBox6.Text="" 
         Textbox13.Text="" :Textbox15.Text="" :Textbox16.Text="" :Textbox17.Text="" 
         ComboBox3.Focus
      End If
   Else
      Msgbox ("Select A Valid Batter To Erase", "Erase Batter", cMsgboxOK, cMsgboxExclamation)
      Combobox6.Focus
   End If
End Sub

Thanks
Michael

 

[Cableguy]

Acording to the help docs, the combobox has NO text propertie...

Therefore

If Combobox3.Text<>"" AND Combobox4.Text<>"" AND Combobox6.Text<>"" Then

may not be evaluated...
Use Combobox.Item(combobox.selectedindex) in substitution of combobox.text

 

[ceaser]

Hi Cableguy

This is very confusing! I have been promoted to "Senior" status, but maybe Erel should demote me back to "Newbie"!

I have been using Combobox.text a lot in my program and it works!

I am attaching another part of my code where I have been using "Combobox.text" and it works!

Sub EraseSurf_Click
   If Combobox1.Text<>"" Then
      i=Msgbox ("Are You Sure To Erase The Selected Data At SV " & Combobox1.Text & "? ","Erase Data",cMsgboxYesNo,cMsgboxQuestion)
      If i=cYes Then
         cmd.AddParameter("Value1")
         If Code=1 Then
            cmd.CommandText = "Delete From Crossfall Where Stake = @value1"
         Else If Code=2 Then
            cmd.CommandText = "Delete From Width Where Stake = @value1"
         Else If Code=3 Then
            cmd.CommandText = "Delete From Edge Where Stake = @value1"
         Else If Code=4 Then
            cmd.CommandText = "Delete From Shoulder Where Stake = @value1"
         Else If Code=5 Then
            cmd.CommandText = "Delete From StakeOffset Where Stake = @value1"
         Else If Code=6 Then
            cmd.CommandText = "Delete From GradeOffset Where Stake = @value1"
         Else If Code=7 Then
            cmd.CommandText = "Delete From VerPi Where Stake = @value1"
         End If
         cmd.SetParameter("Value1",Combobox1.Text)
         cmd.ExecuteNonQuery
         Combobox1.Text="" :Textbox8.Text="" :Textbox8.Text="" :Textbox8.Text="" :Textbox8.Text="" :Textbox8.Text="" 
         ShowSurf
      End If
   Else
      Msgbox ("Selection Invalid To Erase", "Erase Data", cMsgboxOK, cMsgboxExclamation)
      Combobox1.Focus
   End If
End Sub

Regards
Michael

 

[Erel]

SetParameter should be used to set the value of one parameter only. You should use:

cmd.SetParameter("Value1",Combobox3.Text)
cmd.SetParameter("Value2",Combobox4.Text)
...

ComboBox.Text does return the displayed value.
Note that on the desktop the user can change this value to a value that doesn't appear in the list.

 

[ceaser]

Still does nothing:sign0148:

I have changed my code as follows:

Sub EraseBat_Click
   If Combobox3.Text<>"" AND Combobox4.Text<>"" AND Combobox6.Text<>"" Then
      i=Msgbox ("Are You Sure To Erase The Selected Batter?","Erase Batter",cMsgboxYesNo,cMsgboxQuestion)
      If i=cYes Then
         cmd.AddParameter("Value1","Value2","Value3","Value4")
         cmd.CommandText = "Delete From Batter Where Side = @value1 and Cond = @value2 and Set1 = @value3 and Stake = @value4"
         cmd.SetParameter("Value1",Combobox3.Text)
         cmd.SetParameter("Value2",Combobox4.Text)
         cmd.SetParameter("Value3",Combobox2.Text)
         cmd.SetParameter("Value4",Combobox6.Text)
         cmd.ExecuteNonQuery
         ComboBox2.Text="" :ComboBox3.Text="" :ComboBox4.Text="" :ComboBox5.Text="" :ComboBox6.Text="" 
         Textbox13.Text="" :Textbox15.Text="" :Textbox16.Text="" :Textbox17.Text="" 
         ComboBox3.Focus
      End If
   Else
      Msgbox ("Select A Valid Batter To Erase", "Erase Batter", cMsgboxOK, cMsgboxExclamation)
      Combobox6.Focus
   End If
End Sub

Thanks
Michael

 

[Erel]

AddParameter also expects only one argument. SQL

Try to (optimize) compile your code. All these errors will pop up.