Question DELETE Code And Control Transfer Thru Enter Key

[Ashfaque]

Hi,

I am quite new to DOT NET environment.

I use develop MS Access dbs for small shops and establishments. I could say I got success in vba codings in a smooth way.

I recently turned towards DOT NET environment. My net framework ver is 3.5and I installled sql server 2005 & Studio 2008 on my laptop.

I started developing a small db for a shop that contains 2-3 forms. I wrote codelines to add & modify record successfully.

I have 2 questions here.

1.

I am trying to write code to DELETE record after calling on form thru a combo with below simple code but produces error.

Private Sub CmdDelSupp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdDelSupp.Click

conn.Execute("Delete * from T_ProductMaster where ProdCode = '" & Val(TxtProdCode.Text) & "' and ProdName='" & Val(TxtProdName.Text) & "'")

End Sub

The error display at conn line is: COMexception was unhandled

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '*'.

Whats wrong here.

2.

I want to transfer control from one text box to another (next in tab order) thru by pressing ENTER key and not by Tab key which is bydefault

I know in VB I use to write KEYCODE=13 then..... but dot net is diff than what I learned in VBA of Access and Visual Basic.

Please extend your help.

With kind regards,
Ashfaque

 

[InertiaM]

1. SQL Server syntax is "DELETE FROM", not "DELETE * FROM"

2. The following code will move focus to a named text box, but I am sure you can adapt it to change to the next by tab order.

    Private Sub FIRSTTEXTBOX_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles FIRSTTEXTBOX.KeyUp
        If e.KeyCode = Keys.Return Or e.KeyCode = Keys.Enter Then SECONDTEXTBOX.Focus()
    End Sub

 

[Hack]

Also, Val will take a string and convert it to numeric.

In SQL, string values are encapulated with single quotes, numeric values are not. There, this line should not have single quotes.

("Delete * from T_ProductMaster where ProdCode = '" & Val(TxtProdCode.Text) & "' and ProdName='" & Val(TxtProdName.Text) & "'")
'should be
("Delete from T_ProductMaster where ProdCode = " & Val(TxtProdCode.Text) & " and ProdName = " & Val(TxtProdName.Text)

 

[InertiaM]

In addition to Hack's reply, if a query doesnt return or execute, most of the time it's caused by an incorrectly formatted statement. It would make you life easier if you used parameters instead of convoluted string queries. See the link in my signature.

 

[Ashfaque]

Thanks to Hack and InertiaM,

My both fields data type are nvarchar. If I use the corrected codeline of Hack, it gives me error: (I have placed closed parenthesis which was not in updated codeline)

[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the nvarchar value 'Cos00001' to data type int.

Please advise..

 

[InertiaM]

So what are the datatypes of your ProdCode and ProdName fields?

 

[Ashfaque]

My both fields data type are nvarchar.

 

[InertiaM]

Then it should have quotes, and remove the VAL conversions.

sSQL = "Delete from T_ProductMaster where ProdCode = '" & TxtProdCode.Text & "' and ProdName='" & TxtProdName.Text & "'"

All these problems go away with parameterised queries ...

 

[Hack]

All these problems go away with parameterised queries ...

If you are wondering what he means by this, try this

Dim sSQL As String
sSQL = "Delete from T_ProductMaster "
sSQL = sSQL & "where ProdCode = @ProdCode "
sSQL = sSQL & "and ProdName = @ProdName "
        
Dim command As New SqlCommand(sSQL, conn)
    With command.Parameters
         .AddWithValue("@ProCode", TxtProdCode.Text)
         .AddWithValue("@ProdName", TxtProdName.Text)  
    End With
command.ExecuteNonQuery()

 

[Ashfaque]

Thanks both gents....

I am not that much familier with codes but struggling to work it out.

I used again Hack's codelines from..

Dim sSql.....
.....
---

Command.ExecuteNonQuery()

It says 'Type SqlCommand is not defined'

I put these codelines between the form class. Also tried keeping (Dim command As New SqlCommand(sSQL, conn)
) this line at the begining of class

But the same 'Type SqlCommand not defined" appears.

Please extend your help.

regards,

 

[Hack]

Add this

Imports System.Data.SqlClient

 

[Ashfaque]

Hi Hack,

After placing new code line 'Imports System.Data.SqlClient' in General declairaiton area, it produced below error now...

'Run-time error might occur when converting 'ADODB.Connection' to System.Data.SqlClient.SqlConnection'

And it set Green line under word 'Conn' in below line;

Dim command As New SqlCommand(sSQL, conn)

Thanks for your efforts,

Ashfaque

 

[InertiaM]

Remove any references to ADODB.

 

[Ashfaque]

I have one below module

Module AWSConnection
Public conn As New ADODB.Connection

Public Sub Connect()
conn = New ADODB.Connection
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
conn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=true;User ID=sa;Password=1234567890;DSN=AWS"
conn.Open(conn.ConnectionString)
End Sub

End Module

From where you want me to remove ref?

Please advise in detail.

Thanks,

 

[InertiaM]

You'll need to check the connection string, but change the code to :-

Public conn As SqlConnection

Public Sub Connect()
conn = New SqlConnection
conn.ConnectionString = "Data Source=10.1.198.23; Initial Catalog=TESTDB; UID=sa; PWD=1234567890;"
conn.Open()
End Sub