Can someone please help me ?

[Mac]

I am using VB.NET.

I just created a form where user enters information. Once information is
entered, user clicks ok button.

While updating the table, I am getting an error and it does not update. I
am using 2 tables, INVSHIP and PARTSQTY. Both have comon column called
part. Once it matches, I want to grab the quantity from PARTSQTY table.


I would appreciate if someone please help.

Here is my coding:



Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim strsql As String

Dim sqlsvrstr As String

Dim strcnn As String

'Dim userpassstr As String

Dim sqldbstr As String

Dim mcust As String

Dim mpallet As String

Dim mpart As String

Dim TEST As String





'

sqlsvrstr = "30.0.1.2"

sqldbstr = "m2mdata01"

strcnn = "driver=sql server; server=" & sqlsvrstr & ";" & _

"uid=sa;pwd=just4admin;database=" & sqldbstr & ";"

'

cn = New ADODB.Connection

'

rs = New ADODB.Recordset

rs.CursorType = ADODB.CursorTypeEnum.adOpenKeyset

rs.LockType = ADODB.LockTypeEnum.adLockOptimistic

'

TEST = MsgBox(Me.txtcust.Text)





mcust = Me.txtcust.Text

mpallet = Me.txtpallet.Text

mpart = Me.txtpart.Text

strsql = "update invship set invship.customer='" & mcust & " ', " & _

"invship.pallet = '" & mpallet & " ', invship.part = '" & mpart & "', " & _

"invship.qty = partsqty.qty from invship inner join " & _

"partsqty on invship.part = partsqty.part "

cn.Open(strcnn)

rs.Open(strsql, cn)

cn.Execute(strsql)

rs.Close()

cn.Close()

End Sub

End Class

 

[Douglas J Steele]

You might be better off posting to a newsgroup related to VB.Net, such as
microsoft.public.dotnet.languages.vb.data

This newsgroup is for questions about using ADO with Access, the database
product that's part of Office Professional.

However, do you have primary keys defined for your tables? They won't be
updatable unless you do.

 

[Ron Weiner]

You are probably getting the error from the line where you attempt to open
the recordset. You are attempting to open a recordset with an Action query.
This makes no sense. Remove all references to the recordset, and try your
code again. All you need is the Execute method of the Connection object,
which probably never gets executed as the rs.Open line generates the error
before you get to the cn.Execute(strSql) line.

OBTW Your example dosent look much like VB.net code to me. The vb.net way
might look like:

Imports System.Data.SqlClient
Dim cn As New SqlConnection
Dim cmd As New SqlCommand
Dim strSql as String
cn.ConnectionString = "Server=YourServer; " _
& "Database=YourDatabase; " _
& " User ID=UserLogin; Password=Password"
cn.Open()
cmd.Connection = cn
strSql = "A proper sql statement for an action query here"
cmd.CommandText = strSql
cmd.ExecuteNonQuery()
cn.Close()
cmd = Nothing
cn = Nothing