# VB2005 - inserting data into an MS database



## Akumos (May 6, 2010)

Hi All

I have an MS Access database with the following fields:

ID - integer
title - text
genre - text
length - text
actor1 - integer
plot - text
certificate - text
genreScore - integer

and I'm trying to connect my VB application to insert from text boxes. I have the code below so far but keep getting an error 'data type mismatch in criteria expression'. I assume it means something along the lines of... trying to insert text into a number field.

Any help is greatly appreciated...

   Private Sub btnAddMovie_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddMovie.Click

        Dim idNum As Integer = Val(txtID.Text)
        Dim titleString As String = txtTitle.Text
        Dim genreString As String = txtGenre.Text
        Dim lengthString As String = txtLength.Text
        Dim actor1Num As Integer = Val(txtActor.Text)
        Dim plotString As String = txtPlot.Text
        Dim certString As String = txtCert.Text
        Dim scoreNum As Integer = Val(txtScore.Text)

        'titleString = Replace(titleString, "'", "''")
        'genreString = Replace(genreString, "'", "''")
        'lengthString = Replace(lengthString, "'", "''")
        'plotString = Replace(plotString, "'", "''")
        'certString = Replace(certString, "'", "''")

        Dim SQLString As String = "INSERT INTO movie (ID, title, genre, length, actor1, plot, certificate, genreScore) VALUES ('idNum.Integer','titleString.Text','genreString.Text','lengthString.Text','actor1Num.Integer','plotString.Text','certString.Text','scoreNum.Integer')"

        Dim dbPath = "C:\Users\Adam\Documents\movies.mdb"

        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dbPath

        Dim dbCon As New OleDbConnection(connectionString)



        Try

            dbCon.Open()

            Dim cmd As New OleDbCommand(SQLString, dbCon)
            cmd.ExecuteNonQuery()

            dbCon.Close()

        Catch ex As System.Exception
            MessageBox.Show("Error: " & vbNewLine & ex.Message)
            Exit Sub

        End Try










    End Sub
End Class


----------



## Akumos (May 7, 2010)

Help please!!


----------



## Kreij (May 7, 2010)

Try removing the quotes from around the "VALUES" (they are already of a correct type) as it may be tconverting everything to strings.
I think you can just use the variable names too (ie. titleString instead of titleString.Text).


----------



## Akumos (May 7, 2010)

Thanks Kreij

After messing with the code i'm getting a different error now....

No given value for one or more required parameters

But I'm insterting into all the fields, in the correct order... Any ideas?


----------



## Kreij (May 7, 2010)

Can you post your SQL query string?


----------



## Akumos (May 7, 2010)

Thanks for the help!!

SQL String:

 Dim SQLString As String = "INSERT INTO movie (ID, title, genre, length, actor1, plot, certificate, genreScore) VALUES (idNum,titleString,genreString,lengthString,actor1Num,plotString,certString,scoreNum)"


----------



## Kreij (May 7, 2010)

There is a space between actor1 and Num.


----------



## Akumos (May 7, 2010)

I don't know why it pasted like that, I double checked and no spaces...

Any other ideas?


----------



## Kreij (May 7, 2010)

Are any of your DB columns configured to not allow nulls? 
Could it be trying to set something to null that has a required value?


----------



## Akumos (May 7, 2010)

All of them are not null, but I'm inserting a value into into all the colums in the db anyway.

I put quotes back around the values and now I get the mismatch error again, but I can't see anything mismatched...

SQL now reads:

 Dim SQLString As String = "INSERT INTO movie VALUES ('idNum', 'titleString', 'genreString', 'lengthString', 'actor1Num', 'plotString', 'certString', 'scoreNum')"


----------



## Kreij (May 7, 2010)

Maybe print out the completed sql query string after it is created to see what it is actually sending.


----------



## Akumos (May 7, 2010)

ok cool, how do I do that lol..... (ps. thanks for your help!!)


----------



## Kreij (May 7, 2010)

One was is in a MessageBox.
MessageBox.Show(SQLString) or you could send it to the console using Console.Writeln


----------



## Akumos (May 7, 2010)

it's not pointing to my variables, what am I doing wrong?:


----------



## Kreij (May 7, 2010)

Remove the quotes.
Sorry if I'm a little slow, I'm at work.


----------



## Akumos (May 7, 2010)

lol, a busy man!

Tried it, still only showing what I type there!


----------



## Akumos (May 7, 2010)

Thought a screenshot in program might help so you can see the colour changes:


----------



## Kreij (May 7, 2010)

Ack what am I thinking. lol

Dim SqlString as string = "INSERT INTO Movie (" + idNum + "," + titleString + "," .....


----------



## Akumos (May 7, 2010)

Brilliant, thanks Kerij!

Half way there now lol, I get an error telling me I can't convert to a double:


----------



## Kreij (May 7, 2010)

I'm not really great at VB lol
Try using "&" instead of "+"
They have different rules for concantenation.


----------



## Akumos (May 7, 2010)

lol it got me past the error! Thanks Kreij!

Now my newest error which I can't get around:

syntax error (missing operator) in qery expression 'Ship sinks,'.

(ship sinks being my made up plot).....


----------



## Kreij (May 7, 2010)

If the string contains commas you may have to uses backslashes so the compiler knows they are literals. "Ship sinks\, everyone dies"


----------



## Akumos (May 7, 2010)

Kreij said:


> I "Ship sinks\, everyone dies"



EVERYBODY??




Anyways, does this apply to spaces too? because when I take space out it goes back to the old error of 'No value given to one or more required parameters'.


----------



## Kreij (May 7, 2010)

You should not have to with spaces. You can put single quotes in for the string too.

MyString = "hello there" should be fine.
Mystring = "insert into movie(" + isNum + ", *'*" + someStringwithSpacesOrCommas + "*'*, ...


----------



## Akumos (May 8, 2010)

Got it, worked!

Thanks for allllllllllllllllllllllllll your help Kreij!!


----------

