VB5 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,

 

[gmmastros]

Take a look here: thread222-1374896

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ITmfgCO]

George,
Is there any way to do this without creating / calling a stored procedure?

 

[gmmastros]

yes. Of course there is. But I don't recommend it.

Is there a reason why you don't want to create a stored procedure?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Andrzejek]

I would say get rid of " (double quotes).
Put ' (single quotes) around strings, (but not numbers) in your INSERT statement.

Also, if your table is not going to change, you may just do:

INSERT INTO dbo.CUST_ADDRESS 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')

and make sure you dont have any single quotes in your variables, like an address. If you can have it - use:

Replace(strAddress, "'", "''")

Replace one with two single quotes.


Have fun.

---- Andy

 

[gmmastros]

the Replace function is new to vb[!]6[/!].

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

To reiterate:

<Is there a reason why you don't want to create a stored procedure?

If you want to know one of the reasons why George thinks this is important, investigate SQL injection attacks. How much liability insurance can you afford?



Bob

 

[Andrzejek]

I hope ITmfgCO is using VB[red]6[/red]

Have fun.

---- Andy

 

[gmmastros]

The subject line seems to say it all.

[tt][blue]VB5 code to write to a SQL 2000 Table[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ITmfgCO]

I have Visual Studio 2005 (VB express 5)..... I would rather not use a stored procedure, it just seems to complicate things.
The 22 Parameters will be passed for an EDI user exit map, this VB code is supposed to populate a Temp table.
I then have a stored procedure to go to this temp table (when edited) do some manipulation, then populate the
"Actual" table the ERP package uses. I needed to go down this road because of data accuracy in the ERP....

 

[Andrzejek]

Well, that shows that we were both missled, although I was way off. He is using VB.NET :-(

Have fun.

---- Andy

 

[ITmfgCO]

Andrzejek,
I don't think I'm using VB.net.......

 

[ITmfgCO]

I did look at the box I guess I'm wrong this is VB.NET...Sorry guys, thanks for the help, I will re-post with proper subject line....

 

[JoeAtWork]

Just to flog a dead horse....

Considering he just needs this as a one-off solution to transfer data from the old system to the new, I don't think we need to pontificate on using a stored procedure. This sounds like a just-make-it-work-once-then-never-look-at-it-again type of project.

 

[BobRodes]

<This sounds like a just-make-it-work-once-then-never-look-at-it-again type of project.

Ah yes, I have made SO much money revisiting just this type of project when it no longer meets changing needs. In fact, I specialize in this to some degree. So thanks Joe, for your advocacy of the type of project that maintains my income stream. ;-)

Bob

 

[gmmastros]

If this truly was a once and done thing, then my suggestion would be to forget about VB entirely and use the import functionality from SQL Server.

If this is SQL Server 2000, simply open Enterprise Manager, right click the database, click all tasks, click import data.

There is similar functionality for SQL Server 2005.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

Finally, there is a LOT of confusion regarding Microsoft's use of the term VB, as to whether or not it refers to .Net. I'm quite sure it's intentional.

Bob

 

[ITmfgCO]

Sorry Guys for the Delayed response, I've been real busy on this issue....
To clear some things up, This is not meant to be a one time thing. My EDI map will be passing 22 parameters to this VB code. Which will call a Stored Procedure (highly recommended by you guys) and update the desired table.

Below is my code, 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

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....

I realize this is the wrong Forum for this code but people are still continuing to comment so.....

 

[ITmfgCO]

Also....
That DSN line after the connection string should have been commented out..... something I was trying....

 

[gmmastros]

There are 2 authentication methods that you can use with SQL Server: Windows Authentication and SQL Server Authentication.

When you use windows authentication, the account you use to log in to windows is passed along to SQL Server to authenticate your login, therefore, you do not need a user name and password.

When you use SQL Authentication, you must supply a user name and password.

I cannot stress enough the importance of NOT using sa to log in to the database. The SA user has system administrator priveleges and can literally do ANYTHING. This would include dropping tables, dropping databases, dropping other users, and, under the right circumstances, could even format the hard drive of the server. In case you were wondering... I do mean to scare you.

There is an excellent resource for connection strings at:

http://www.connectionstrings.com

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom