locked
VB Integer into SQL Server 2008 RRS feed

  • Question

  • Hello,

    I am trying to write two integers into an SQL Server 2008 database. 

    QuizID and Answer have their data type in SQL set as 'int' and Question is 'varchar'. ReadQuizID and FinalAnswer are integers. When I run this code to write to the server I get an error saying "Conversion from string "Insert Into Questions(QuizId,Que" to type 'Double' is not valid.".

    myCommandWrite.CommandText = "Insert Into Questions(QuizID,Question,Answer) Values('" + ReadQuizID + "','" + FinalQuestionString + "','" + FinalAnswer + "')"

    If I replace the string with:

    myCommandWrite.CommandText = "Insert Into Questions(QuizId,Question,Answer) Values('4','" + FinalQuestionString + "','4')"

    then the data is input correctly, so I believe the problem is with entering the two integers.

    Thanks for any help

    Monday, February 13, 2012 8:19 PM

Answers

All replies

  • To simplify this task and avoid possible errors, make use of the myCommandWrite.Parameters property:
    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx


    Armin

    Monday, February 13, 2012 8:28 PM
  • What are the data types of the QuizID, Question, and Answer columns in your database table?

    But I agree with Armin, if you use parameters, you won't have this issue.  Also, your current code is wide open to a sql injection attack.  It is very insecure.

    Chris

    Tuesday, February 14, 2012 9:53 PM
  • QuizID and Answer is set as 'int' and Question is set as 'varchar(50)'. It's just a school project so I don't really have to consider anything like that :) 

    I've had a look at the parameters and my understanding is that instead of writing the value to the server directly, I would set a parameter and assign the value to the parameter, writing the parameter to the server? I hope that makes as much as sense as it did when I wrote it :)

    I'll give it a try. Thanks

    Wednesday, February 15, 2012 1:57 AM
  • Hi,

    I just tryed the parameters and it works! However, what I am doing is producing random questions (very basic maths questions) and writing them to the server. So I have the 

    myCommandWrite.ExecuteNonQuery()

    stuff in a loop. So it generates a new question and answer, and writes it to the server. The first question and answer is generated and with the parameters is saved correctly. However on the second run it gives an error :"The variable name '@QuizID' has already been declared. Variable names must be unique within a query batch or stored procedure.". How would you recommend I write a number of things quickly to the server?

    Thanks for your help.

    EDIT

    Don't worry, I moved the execute stuff to another sub and called it with the variables. Thanks for your help!

    EDIT

    • Edited by robertsimpkin1 Wednesday, February 15, 2012 11:16 AM Found the answer
    Wednesday, February 15, 2012 10:58 AM