Solved

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

Posted on 2004-09-03
8
272 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now