VB.NET & MS Access Issue

[Jeigh1405241495 0]

Alright, I haven't had much experience with vb.net or ms access as it is, let alone using them together, so I need some advice on the best way to do this.

I need to create a program that basically is a form to fill out with information, and upon filling it out it can be saved. Saving consists of making a row in a ms access database and placing each field as a column entry within this new row. Then I need to be able to retrieve this information from the DB and fill out the form as it was originally if the user chooses to load.

This is all fine and wasn't hard to accomplish BUT that only works properly if I fill out all the fields of the form. Leaving blanks causes it to crash since I'm basically creating an SQL statement with

quer = "insert into table (field1, field2, field3, field4) values('" & txtfield1.Text & "','" & txtfield2.Text & "','" & txtfield3.Text & "','" & txtfield4.Text & "')"

Now I am not understanding why this happens, since I can manually place either null or blank spaces (as in actual keyed spaces) in the database as entries and it works fine, so I don't see why this would cause a problem this way. I know a work around for this (basically check if each field is empty and then only add it to the sql statement if it is occupied) howevere there are ALOT of fields and this would take alot of monotonous typing to achieve. If anyone knows of a simpler way to do this, it would be greatly appreciated.

[miCRoSCoPiC^eaRthLinG 0]

Your code seems fine - no errors there. Can you attach the project here? Have to see the greater picture to be able to tell.

[faulty.lee 0]

Did you get any message after it crashed? SQL error?

Your code seems fine - no errors there. Can you attach the project here? Have to see the greater picture to be able to tell.

[iGuest 3]

VB.Net & MS Access Solution

VB.NET & MS Access Issue

 

Replying to Jeigh

Hie Jeigh. Your code does look alright. But there are two things you probably should look into.

 

1 - make sure the MS Access table columns for which blank values are possible scenarios allow for "Null" entries

2 - The string values you want to put into the Access database should not have the ' character. This character should be used only as pat of the SQL syntax. You might want to clean the user input and replace the ' characters with something more closely ressembling like `.

 

Hope this helps

 

-reply by Seej

[iGuest 3]

How to attach Miscrosoft access Database to Visual Basic .Net

VB.NET & MS Access Issue

 

Guys could someone help me in this one???? plsss I want to attach my database to VB.Net but I have no clue on how to do it... PLsss help

 

-reply by Ryan

[iGuest 3]

assemly error,indexoutofrange error at vb.net

VB.NET & MS Access Issue

 

I have created a database in ms access2000,and at at vb.Net I hv created search,insert,update,del buttons.

 

Problems are listed bellow:

 

1:index out of range-"when I search at input box"

2:after running 2 times the program error shows"could not load file or assembly'program name'.Exception from HR0x80131407,

Codes are given bellow

 

Imports System

Imports System.Data

 

Imports System.Data.OleDb

 

Public Class Form1

 

Dim cnnOLEDB As New OleDbConnection

 

Dim cmdOLEDB As New OleDbCommand

 

Dim cmdInsert As New OleDbCommand

 

Dim cmdUpdate As New OleDbCommand

 

Dim cmdDelete As New OleDbCommand

 

Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:Documents and SettingsronyMy DocumentsTestDB.Mdb;"

 

Private Sub Form1_Load(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles MyBase.Load

Try

cnnOLEDB.ConnectionString = strConnectionString

Catch ext As OleDbException

MsgBox(ext.Message, MsgBoxStyle.Critical, "OLEDB Error")

 

 

cnnOLEDB.Open()

End Try

End Sub

 

Private Sub btnSearch_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles btnSearch.Click

 

txtAearchResult.Text = ""

 

Dim vSearch As String = InputBox("Enter Integer number to search name:")

 

If vSearch <> "" Then

 

cmdOLEDB.CommandText = "SELECT ID FROM Authors WHERE ID=" & CInt(vSearch)

 

cmdOLEDB.Connection = cnnOLEDB

 

Dim rdrOLEDB As OleDbDataReader = cmdOLEDB.ExecuteReader()

 

If rdrOLEDB.Read = True Then

 

txtAearchResult.Text &= rdrOLEDB.Item(0).ToString & " " & _

rdrOLEDB.Item(1).ToString()

 

rdrOLEDB.Close()

 

Exit Sub

 

Else

 

MsgBox("Record not found")

 

Exit Sub

 

End If

 

Else

 

MsgBox("Enter search value.")

 

Exit Sub

 

End If

 

End Sub

 

Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click

 

If txtID.Text <> "" And txtAuthor.Text <> "" Then

 

cmdInsert.CommandText = "INSERT INTO Authors (ID, Author) VALUES ('" & txtID.Text & "', '" & txtAuthor.Text & "');"

 

'MsgBox(cmdInsert.CommandText)

 

cmdInsert.CommandType = CommandType.Text

 

cmdInsert.Connection = cnnOLEDB

'create con obj

cnnOLEDB.ConnectionString = strConnectionString

'''''''''

cnnOLEDB.Open()

 

cmdInsert.ExecuteNonQuery()

 

MsgBox(txtID.Text = "Record inserted.")

 

txtAuthor.Text = ""

 

'cnnOLEDB.Open()

Else

 

MsgBox("Enter the required values:" & _

vbNewLine & "1. ID" & vbNewLine & "2.Author")

 

End If

cnnOLEDB.Close()

cmdInsert.Dispose()

cnnOLEDB.Dispose()

 

 

End Sub

 

Private Sub btnUpdate_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles btnUpdate.Click

 

If txtID.Text <> "" And txtAuthor.Text <> "" Then

 

cmdUpdate.CommandText = "UPDATE Authors SET Author = '" & txtAuthor.Text & "'WHERE ID = " & txtID.Text & ";"

 

'MsgBox(cmdUpdate.CommandText)

 

cmdUpdate.CommandType = CommandType.Text

 

cmdUpdate.Connection = cnnOLEDB

'''''''''

cnnOLEDB.Open()

cmdUpdate.ExecuteNonQuery()

 

MsgBox(txtID.Text = "Record updated.")

 

txtAuthor.Text = ""

 

Else

 

MsgBox("Enter the required values:" & vbNewLine & "1. ID" & vbNewLine & "2.Author")

 

End If

cnnOLEDB.Close()

cmdUpdate.Dispose()

cnnOLEDB.Dispose()

End Sub

 

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

 

If txtID.Text <> "" Then

 

cmdDelete.CommandText = "DELETE FROM Authors WHERE ID = " & _

txtID.Text & ";"

 

'MsgBox(cmdDelete.CommandText)

 

cmdDelete.CommandType = CommandType.Text

 

cmdDelete.Connection = cnnOLEDB

'''''''''

cnnOLEDB.Open()

cmdDelete.ExecuteNonQuery()

 

MsgBox(txtID.Text = "Record deleted.")

 

txtAuthor.Text = ""

 

cmdDelete.Dispose()

 

Else

 

MsgBox("Enter the required values:" & vbNewLine & "1. ID")

 

End If

cnnOLEDB.Close()

cmdUpdate.Dispose()

cnnOLEDB.Dispose()

End Sub

 

End Class

 

-reply by tarique

[magiccode91405241511 0]

It seems that the database fields have a property with NOT NULL set.Could you able post the database scheme here used with your application ?

[Quatrux 4]

Hello? it was 2-3 years ago, why not send him a PM instead