VB5.net code to write to a SQL 2000 table

[ITmfgCO]

I have an urgent issue at my work-place!!!
We are going through an EDI Implementation and need to insert into a Customer Address Table before we can bring the EDI data into the ERP package.
It was suggested to write a VB code to do so. I am not familiar with VB so I need some help. The VB code has to be able to accept 22 parameters
(i.e. Customer_ID, Customer_Name, Address1, Address2, Address3, City, State, .... ect) to push to the Table.
I have tested my Insert statement in SQL Query Analyzer and everything looks fine, but I need the VB code to do this for me. Here is my Insert / Values statement:


INSERT INTO "dbo"."CUST_ADDRESS" ( "CUSTOMER_ID", "NAME", "ADDR_1", "ADDR_2", "ADDR_3", "CITY", "STATE", "ZIPCODE",

"COUNTRY", "DEF_SLS_TAX_GRP_ID", "TAX_EXEMPT", "SHIPTO_ID", "ORDER_FILL_RATE", "GENERATE_ASN",

"HOLD_TRANSFER_ASN", "CUSTOMS_DOC_PRINT", "ACCEPT_830", "ACCEPT_862", "CONSOL_SHIP_LINE",

"PALLET_DETAILS_REQ", "GENERATE_WSA", "HOLD_TRANSFER_WSA" ) VALUES ( 'A200500', 'LTest', 'LAddress Test1',

'LAddress Test2', 'LAddress Test3', 'Carrollton', 'TX', '75006','USA' , NULL, 'N', '2118624474', 0.000000, 'D', 'D', 'D', 'D', 'D', 'N', 'N', 'D', 'D')

RESULT: 0



Any help on this would make my day, it is an extremely urgent issue.
I have no Idea on how to accept these 22 parameters that will be pushed
over to the desired VB code, nor do I know if anything else is required
before I use the Insert / Values statement.

Thanks in advance,

 

[SBendBuckeye]

Hello,

Where are your 22 parameters coming from? Do you need a function that accepts the 22 parameters and then puts them into your Sql Server table?

Here is a partial answer pending more information.

Imports System.Data.SqlClient

Public Sub AddToTable( _
  Parm1 As String, _
  Parm2 As Integer, etc)

  ' You need server name and database name to create a connection string.
  Dim cs As String = _
    "Server=YourServerName;Database=YourDatabaseName;Trusted_Connection=Yes" 

  ' Create and open a connection object based on connection string.
  Dim connection As New SqlConnection(cs)
  connection.Open

  ' Double quotes are delimiters in VB.Net so I changed to square brackets.
  Dim sql As String = "INSERT INTO [dbo].[CUST_ADDRESS] " & _
    "( [CUSTOMER_ID], [NAME], [ADDR_1], [ADDR_2], [ADDR_3], [CITY], [STATE], [ZIPCODE], [COUNTRY], [DEF_SLS_TAX_GRP_ID], [TAX_EXEMPT], [SHIPTO_ID], [ORDER_FILL_RATE], [GENERATE_ASN], [HOLD_TRANSFER_ASN], [CUSTOMS_DOC_PRINT], [ACCEPT_830], [ACCEPT_862], [CONSOL_SHIP_LINE], [PALLET_DETAILS_REQ], [GENERATE_WSA], [HOLD_TRANSFER_WSA] ) " & _
    "VALUES ( @Parm1, @Parm2, @Parm3, @Parm4, @Parm5, @Parm6, @Parm7, @Parm8, @Parm9, @Parm10, @Parm11, @Parm12, @Parm13, @Parm14, @Parm15, @Parm16, @Parm17, @Parm18, @Parm19, @Parm20, @Parm21, @Parm22)"

  ' Create a command object based on sql and connection object.
  Dim command As New SqlCommand(sql, connection)
  ' Set command type to text because using hard coded sql.
  command.CommandType = CommandType.Text

  ' Add parameter objects to match name and value.
  ' Prefix @ to ParmX name
  command.Parameters.Add(@Parm1, parm1)
  command.Parameters.Add(@Parm2, parm2)
  command.Parameters.Add(@Parm3, parm3), etc

  ' Execute the Insert statement.
  command.ExecuteNonQuery()

  ' Close the connection and dispose of the objects.
  connection.Close()
  command.Dispose()
  connection.Dispose()

Good Luck!

 

[ITmfgCO]

My EDI map will be passing 22 parameters to this VB code. I AM NOT 100% SURE HOW BUT SHE MENTIOND AN OBJECT. The VB code will then call a Stored Procedure (highly recommended by a lot of people on this site) and update the desired table.

Below is my code, do I seem to be going in the right direction?

I have two issues right now:
1) I'm not sure if my connection string needs to have the user Id and password (would be the sa...)
2) I'm not sure how to hold the Values the EDI map will be giving me

I DO LIKE YOUR INSERT STATEMENT A LOT BETTER THAN WHAT I WAS TRYING TO DO THO....WHAT IS THE BETTER WAY TO DO THIS?

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Dim connectionString As String = _
      "Integrated Security=SSPI;Persist Security Info=False;" + _
      "Initial Catalog=VM65TEST;Data Source=DATABASE"


' Dsn=VM65TESTDB;uid=sa;app=Microsoft® Visual Studio® 2005;wsid=EDISERVER;database=VM65TEST


    

    Public Sub InsertCustomer()
        Dim connection As SqlConnection = _
            New SqlConnection(connectionString)
        connection.Open()
        Try
            Dim command As SqlCommand = _
                New SqlCommand("InsertCustomer", connection)
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = "eNextAdd"

            command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = @custid
            command.Parameters.AddWithValue("@CustomerID", "PAULK")
            command.Parameters.AddWithValue("@CompanyName", "Pauly's Bar")
            command.Parameters.AddWithValue("@ContactName", "Paul Kimmel")
            command.Parameters.AddWithValue("@ContactTitle", "The Fat Man")
            command.Parameters.AddWithValue("@Address", "31025 La Jolla")
            command.Parameters.AddWithValue("@City", "Inglewood")
            command.Parameters.AddWithValue("@Region", "CA")
            command.Parameters.AddWithValue("@Country", "USA")
            command.Parameters.AddWithValue("@PostalCode", "90425")
            command.Parameters.AddWithValue("@Phone", "(415) 555-1234")
            command.Parameters.AddWithValue("@Fax", "(415) 555-1235")

            Console.WriteLine("Rows inserted: " + _
                              command.ExecuteNonQuery().ToString)

        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Throw
        Finally
            connection.Close()
        End Try
    End Sub



    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       
        InsertCustomer()
    End Sub
End Class

The command.Parameters.Add line in the Insert() is what I have been currently working on....

Let me know if you think any changes are needed, or throw this code out and and use yours above....

Thanks,

 

[SBendBuckeye]

Hello,

The connection string I gave you does not need a UserID. That saves you exposing your sa password in plain English.

How often will you need to run this process - once, twice, nightly, on demand, etc?

If this is a one shot deal, then there is no need for a stored procedure, the sql above will work.

You can build your parametes with only the parameter name and its corresponding value and let Sql Server infer the type from the column itself. Saves a little time. Both of the following should work for you:

command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = custid
command.Parameters.Add("@CustomerID", custid)

Presumably this object will expose its values via properties or functions. For purposes of discussion, let's say it is a MyObject and exposes a Property named CompanyName and a function GetCity which returns the city. Then you would have something like the following:

Sub AddToTable(ByVal inputObject As MyObject)
    ' All of the code from my example above and then the following
    With inputObject
        command.Parameters.Add("@CompanyName", .CompanyName)
        command.Parameters.Add("@City", .GetCity()), etc
    End With

The With construct saves you having to code inputObject.CompanyName, inputObject.GetCity, etc. Also, you would probably want to set the command up in your main method so you only do it once and then do the above in a AddParamters sub where you pass in the MyObject as an input parameter and the command object as a ByRef input parameter.

Feel free to post more questions and Good Luck!