Solved

Can memory variables in the vb program have the same name as field names in the tables?

Posted on 2004-09-03
8
276 Views
Last Modified: 2010-04-23
I have Dimed all my variable names with the same names as my field names.  Now, when I try to write the record is gives me this error message.

The name admDate is not permitted in this context.  Only contraints, variables, or expressions are allowed here.  Column names are not permitted.

????????


Here is the code
For m4 = 1 To 60
                    objCommand2.CommandText = "Insert Into itemAnalysisDetail " _
                                & "(admDate, studentId, teacherId, objectiveNumber, itemCorrectResp, studentResp) " & _
                                "VALUES(admDate, studentId, teacherId, mathobj(m4), mathItemCorrectRespArray(m4), mathStudentRespArray(m4))"
                    objConnection2.Open()
                    objCommand2.ExecuteNonQuery()
                    objConnection2.Close()
                Next


Thanks
Kenny
0
Comment
Question by:kwh3856
  • 4
  • 4
8 Comments
 
LVL 7

Accepted Solution

by:
natloz earned 500 total points
ID: 11976997
I don't think you can do this actually...you need to split out your string...the values from your code cannot be in the "" of the main string...you have to concatenate the values together....note single quotes needed for strings...I am assuming your Arrays and IDs are numeric...if not you will have to add in the single quotes.

objCommand.CommandText = "Insert Into itemAnalysisDetail " & "(admDate, studentId, teacherId, objectiveNumber, itemCorrectResp, studentResp) " & _
"VALUES( '" & admDate & "'," & studentId & "," & teacherId "," &  readingElaobj(r4) & "," & readingElaItemCorrectRespArray(r4) & "," & readingElaStudentRespArray(r4) & ")"
0
 

Author Comment

by:kwh3856
ID: 11977030
Is there a better way to accomplish this task.  I just have a bunch of variables I want to save back down to the database.  Any other ideas?????

Thanks
Kenny
0
 

Author Comment

by:kwh3856
ID: 11977106
Thanks for the help.  If you know an easier way I sure would like to know.

Thanks
Kenny
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:natloz
ID: 11977115
This is pretty standard SQL string building in VB
If this is a SQL database...I find it much easier to create a stored procedure...then pass the values to the stored procedure, this way if  you have to make any changes to the the insert...you only need to change the stored procedure.
0
 

Author Comment

by:kwh3856
ID: 11977142
Can you point me in a direction where I can learn about stored procedures????

Thanks
Kenny
0
 
LVL 7

Expert Comment

by:natloz
ID: 11977167
Here is a sample where a user logs into the system by passing values to a stored procedure in SQL...
The stored procedure is created using SQL Query Analyzer...

'************************************************
    'The user wants to attempt to login to the system
    '************************************************
    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        'Variables and objects
        Dim cDB As clsDBConn = New clsDBConn 'Connection Class
        Dim strConn As String = cDB.getStrConn 'Get connection string for database
        Dim oConn As New SqlConnection 'SQL connection object
        Dim intReturnID As Integer 'Return ID from Stored Procedures
        Dim oComm As SqlCommand 'SQL Command object
        Dim bFail As Boolean = False 'Test for failure

        'Validate form has data
        If Me.txtUsername.Text = "" Then
            MsgBox("Please enter a User Name.")
            Me.txtUsername.Focus()
            bFail = True
        Else
            If Me.txtPassword.Text = "" Then
                MsgBox("Please enter a Password.")
                Me.txtPassword.Focus()
                bFail = True
            End If
        End If

        If bFail = False Then 'All is well
            'Connect to SQL server and validate login
            Try
                'Pass connection string to SqlConnection
                oConn.ConnectionString = strConn

                'SQL Database statements
                oComm = New SqlCommand
                oComm.CommandType = CommandType.StoredProcedure
                oComm.CommandText = "spValidateLogin"
                oComm.Connection = oConn

                'Send in parameters
                oComm.Parameters.Add(New SqlParameter("@varUserName", SqlDbType.VarChar)).Value = Me.txtUsername.Text
                oComm.Parameters.Add(New SqlParameter("@varPassword", SqlDbType.VarChar)).Value = Me.txtPassword.Text

                'Return value from stored procedure
                Dim sParam As SqlParameter
                sParam = New SqlParameter
                sParam.ParameterName = "@ReturnID"
                sParam.SqlDbType = SqlDbType.Int
                sParam.Direction = ParameterDirection.Output
                oComm.Parameters.Add(sParam)

                'Open connection and execute oComm
                oConn.Open() 'Open connection
                oComm.ExecuteNonQuery() 'Execute stored procedure
                intReturnID = oComm.Parameters("@ReturnID").Value 'Get Return value
                oConn.Close() 'Close connection

                'Analyze Return value for errors
                If intReturnID < 0 Then 'All errors are less than 0
                    MsgBox("Invalid Login, please try again.")
                    Me.txtUsername.Focus()
                Else
                    Dim frmMain As frmMain = New frmMain
                    'Set the global variable for user logged in
                    _intUserID = intReturnID

                    'Open Main form and hide Login screen
                    frmMain.Show()
                    Me.Hide()
                End If

            Catch
                MsgBox("Error connecting to SQL Server")
            Finally
                oConn.Close() 'Close connection
            End Try
        End If
    End Sub


***************HERE IS THE STORED PROCEDURE*********
--Used to validate a user login...empty result is invalid
create proc spValidateLogin
     @varUsername varchar(25),
     @varPassword varchar(25),
     @ReturnID int output as
     
     SET NOCOUNT ON

     select * from tblUser where varUsername = @varUsername and varPassword = @varPassword

     if @@rowcount = 0 --if nothing came back from the query
          begin
               set @ReturnID = -3 --Invalid Login
          end
     else
          begin
               set @ReturnID = (select pkUserID from tblUser
                              where varUsername = @varUsername)
          end

     select @ReturnID
     return @ReturnID
GO
0
 
LVL 7

Expert Comment

by:natloz
ID: 11977180
The Stored Procedure is not a VB.Net thing...it is a SQL Server thing...once you have created the Stored Procedure...you can call it from VB and Pass parameters to it.
0
 

Author Comment

by:kwh3856
ID: 11977190
Thanks...I think I understand...


Thanks
kenny
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql server insert 13 46
i have to take the screenshot of command prompt? how to do this? 1 58
Passing data between Forms 3 26
Code enhancement 4 32
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question