VB.NET: Sql Syntax

[smcinnis]

I'm using VB.net and trying to do relatively simple SQL commands to an MS SQL server but am not having any luck. Can anybody give me some sample code with the correct syntax thanks

[sashi]

Hi there,

Syntax for what? Update? Select? Insert? Delete? which one? As mentioned earlier, please try to be clearer when asking question.

[smcinnis]

Hi there,

Syntax for what? Update? Select? Insert? Delete? which one? As mentioned earlier, please try to be clearer when asking question.

sorry, if you have it for insert and select I'll try to work the rest.

[willakawill]

sorry, if you have it for insert and select I'll try to work the rest.

There are a couple of different elements that you have to control with SQL Server
1. VB syntax with ado
2. SQL Server security
3. SQL syntax

Here is something that covers all 3:

'we need to declare the ado objects
'do not declare them using the New keyword
'this degrades performance
Dim cnCon As ADODB.Connection
Dim cmdCom AS ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String
 
'open a connection to the database
   Set cnCon = New ADODB.Connection
    With cnCon
        .Provider = "SQLOLEDB"
        .Properties("Data Source") = "SQL server name here"
        .Properties("User Id") = "your username here"
        .Properties("Password") = "your password here"
        .Open
        .DefaultDatabase = "your database name here"
     End With
 
     'set up the properties of the command object
     Set cmdCom = New ADODB.Command
     With cmdCom
        .ActiveConnection = cnCon
        .CommandType = adCmdText
     End With
 
     'you have to decide what properties you want for your recordset
     Set rs = New ADODB.Recordset
     With rs
        .ActiveConnection = cnCon
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
     End With
 
    strSQL = "SELECT blah1, blah2, blah3 " _
            & "FROM MyTable " _
            & "WHERE blah4 > blah5 " _
            & "ORDER BY blah1"
 
     rs.Source = strSQL
     rs.Open
 
     strSQL = "UPDATE MyTable " _
            & "SET blah1 = 8 " _
            & "WHERE blah4 > blah5 " 
 
     'an update statement does not return a recordset
     'so we can use the command object
     cmdCom.CommandText = strSQL
     cmdCom.Execute
 

I hope this is useful for you